Ansible playbook configuration for MySQL8 (how to change root password etc.)

mysql
Ansible playbook configuration for MySQL8 (how to change root password etc.)
Page content

MySQL major version 8 was released on April 2018. I introduce how to write MySQL 8 ansible playbook.

Motivation

I usually use AWS. AWS has a database managed service called RDS, and RDS supports MySQL 5.x series. To conduct in-house training using MySQL 8, I needed to make AMI of MySQL 8 with ansible.

Environment

  • CentOS 7
  • Ansible 2.6.1
  • Packer 1.1.3

Configuration sample of ansible playbook

In this case, I describe only the main task definition part in Ansible, I omit Packer configuration because it becomes redundant.

The sample of ansible playbook is now as follows.

 1---
 2- name: download epel-release
 3  yum:
 4    name: https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
 5    state: present
 6- name: delete mariadb
 7  yum:
 8    name: mariadb-libs
 9    state: removed
10- name: install mysql
11  yum:
12    name: "{{ item }}"
13    state: present
14  with_items:
15    - mysql-community-devel*
16    - mysql-community-server*
17    - MySQL-python
18- name: copy my.cnf
19  copy:
20    src: ../files/etc/my.cnf
21    dest: /etc/my.cnf
22    mode: 0644
23- name: enable mysql
24  systemd:
25    name: mysqld
26    state: restarted
27    enabled: yes
28- name: get root password
29  shell: "grep 'A temporary password is generated for root@localhost' /var/log/mysqld.log | awk -F ' ' '{print $(NF)}'"
30  register: root_password
31- name: update expired root user password
32  command: mysql --user root --password={{ root_password.stdout }} --connect-expired-password --execute="ALTER USER 'root'@'localhost' IDENTIFIED BY '{{ mysql.root.password }}';"
33- name: create mysql client user
34  mysql_user:
35    login_user: root
36    login_password: "{{ mysql.root.password }}"
37    name: "{{ item.name }}"
38    password: "{{ item.password }}"
39    priv: '*.*:ALL,GRANT'
40    state: present
41    host: '%'
42  with_items:
43    - "{{ mysql.users }}"

Points

Now, I explain the above .yaml settings.

Remove mariadb-libs

I delete MariaDB modules that is installed on Centos7 in default. MariaDB modules conflict MySQL modules when install.

1- name: delete mariadb
2  yum:
3    name: mariadb-libs
4    state: removed

Install MySQL-python

If use mysql_user module in ansible, need to install MySQL-python to provisioning host. In addition, MySQL-python works on only Python 2.

1- name: install mysql
2  yum:
3    name: "{{ item }}"
4    state: present
5  with_items:
6    - mysql-community-devel*
7    - mysql-community-server*
8    - MySQL-python # Here

Change default authentication plugin in MySQL

MySQL 8 changes default authentication plugin to strengthen security. For details, see here.

To use the previous authentication plugin, I edit my.cnf and add default-authentication-plugin = mysql_native_password block as follows.

1[mysqld]
2default-authentication-plugin=mysql_native_password

And then, copy to /etc/my.cnf .

1- name: copy my.cnf
2  copy:
3    src: ../files/etc/my.cnf
4    dest: /etc/my.cnf
5    mode: 0644

Restart mysqld daemon to apply the settings.

1- name: enable mysql
2  systemd:
3    name: mysqld
4    state: restarted
5    enabled: yes

Get root password written in log file and initialize user

This is the most troublesome point.

MySQL 8 outputs root user’s initial password in /var/log/mysqld.log

To change default password for root user, I save the password text to variable using register, and change password with mysql command.

1- name: get root password
2  shell: "grep 'A temporary password is generated for root@localhost' /var/log/mysqld.log | awk -F ' ' '{print $(NF)}'"
3  register: root_password # save to variable
4- name: update expired root user password
5  command: mysql --user root --password={{ root_password.stdout }} --connect-expired-password --execute="ALTER USER 'root'@'localhost' IDENTIFIED BY '{{ mysql.root.password }}';"

I explain the reason why I use command module instead of mysql_user module .

For example, I initially configured as follows. In this case, MySQL-python connects to database as root user and change root user’s own password.

1-  mysql_user:
2    login_user: root
3    login_password: "{{ root_password }}"
4    name: root
5    password: "{{ sometinng new password }}"
6    priv: '*.*:ALL,GRANT'
7    state: present
8    host: '%'

However, the following error occurs. This error is reported in github issue.

1unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials

Beacause of the issue, using mysql command with command module until the issue fixes is better.

Create user to connect database

mysql_user module creates mysql user to connect database. login_user block is a user who creates a user(root), and login_password block is the password used to authenticate with.

To the host block, set the host of the connection source appropriately. % means any. When the host block is not set, only connections from localhost are accepted .

 1- name: create mysql client user
 2  mysql_user:
 3    login_user: root
 4    login_password: "{{ mysql.root.password }}"
 5    name: "{{ item.name }}"
 6    password: "{{ item.password }}"
 7    priv: '*.*:ALL,GRANT'
 8    state: present
 9    host: '%' # When host is not set, only connections from localhost are accepted
10  with_items:
11    - "{{ mysql.users }}"

Conclusion

It is available to

  • Install mysql with uninstalling mariadb modules
  • Change root user default password with getting password from /var/log/mysqld.log
  • Create user to connect database, and change accessible host