This Ansible script will fully rotate your MySQL root account passwords (or change any MySQL account passwords if you change the script) and implement so you don’t have to keep putting the password in.

Test Bed

  1. Ansible control server running Ubuntu 18.04
  2. Ubuntu 18.04 Bionic test server running mySQL 5.7.25


  1. Ansible control server
  2. SSH keys established between Ansible control server and destination server


  1. Install mySQL package with required dependancies
  2. Stop mySQL service
  3. Set mySQL environment variables
  4. Start mySQL
  5. Change mySQL root password to a mySQL native password (native is very important!)
  6. Copy .my.cnf from local source to ~
  7. mySQL flush privileges
  8. Stop mySQL service
  9. Unset mySQL environment variables
  10. Start mySQL


  - hosts: test

    - global_vars/config.yml
    - name: Install mySQL Server
      apt: name=mysql-server state=present
    - name: Install Additional Dependencies
          - postfix-mysql
          - dovecot-imapd
          - dovecot-lmtpd
          - dovecot-mysql
          - python-pymysql
          - mysql-client
          - mysql-common
        state: present
# MySQL 
    - name: Ensure mySQL is running and starts on boot
      service: name=mysql state=started enabled=true

    - name: Stop MySQL
      service: name=mysql state=stopped
    - name: set environment variables
      shell: systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
    - name: Start MySQL
      service: name=mysql state=started
    - name: sql query
      command:  mysql -u root --execute="ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '"{{ mysql_root_password }}"';"
    - name: Create ~/.my.cnf
        path: ~/.my.cnf
        state: touch
    - name: Insert into ~/.my.cnf
        path: ~/.my.cnf
        block: |
          user={{ mysql_user }}
          password={{ mysql_root_password }}
    - name: sql query flush
      command:  mysql -u root --execute="FLUSH PRIVILEGES"
    - name: Stop MySQL
      service: name=mysql state=stopped
    - name: unset environment variables
      shell: systemctl unset-environment MYSQLD_OPTS
    - name: Start MySQL
      service: name=mysql state=started


password={{ mysql_root_password }}


mail_db_host: localhost
mysql_user: root
mysql_root_password: changepwhere


ansible-playbook sql.yml

Other Considerations

You will need to remove lines 7 to 19 if you are not installing MySQL for the first time.

If any applications are using the account you are rotating, the application will auth fail (I would hope your not using root for app authentication) – if you use this against any other username this will need to be considered.


The gotcha for a lot of people (from what I’ve read on blogs/github) is that when the mysql root password changes you also need to change it from “auth_socket” to “mysql_native_password”. 


2 Responses to “Ansible – mySQL root password change on Ubuntu”

  1. Dave

    Playbook is failing at this point:
    – name: sql query flush
    command: mysql -u root –execute=”FLUSH PRIVILEGES”
    with access denied error:
    ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)”, “stderr_lines”: [“ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)”], “stdout”: “”, “stdout_lines”: []}

    • Chris

      Hi Dave, you will need to create a “.my.cnf” file in the root of the user you are executing the playbook from. You will need to put the following in and modify to your requirements:


Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.