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. This took me a while to figure out, there are 

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”. 

 

Leave a Reply

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

Time limit is exhausted. Please reload CAPTCHA.