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

---
  - hosts: test

    var_files:
    - global_vars/config.yml
   
    - name: Install mySQL Server
      apt: name=mysql-server state=present
    - name: Install Additional Dependencies
      apt:
        pkg:
          - 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
      file:
        path: ~/.my.cnf
        state: touch
    - name: Insert into ~/.my.cnf
      blockinfile:
        path: ~/.my.cnf
        block: |
          [client]
          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

etc/mysql/.my.cf

[client]
user=root
password={{ mysql_root_password }}

global-vars/config.yml

mail_db_host: localhost
mysql_user: root
mysql_root_password: changepwhere

Execution

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.

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.