This Ansible script will fully rotate your MySQL root account passwords (or change any MySQL account passwords if you change the script) and implement my.cf 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

Requirements

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

Overview

  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

sql.yml

etc/mysql/.my.cf

global-vars/config.yml

Execution

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.

Conclusion

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”: []}

    Reply
    • 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:
      [client]
      user=mysqluser
      password=mysqlpass

      Reply

Leave a Reply

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

Time limit is exhausted. Please reload CAPTCHA.