How to automate the installation of MySQL 5.6 and above

Posted by Matt Bryson on 14-Apr-2016 13:24:59
Find me on:

With the release of MySQL 5.6, there was no longer a default user account with an empty password. For security reasons the root account is now allocated a random password when MySQL is installed, which is written to a log file.

You then look up the password from the log file, and use it to login and change it to something else...

$ sudo grep 'temporary password' /var/log/mysqld.log 
$ mysqladmin -u root --password=RANDOM_PASSWORD_FROM_LOG password myNewSuperSecretPassword1!

This is all well and good, unless you are automating deployment of MySQL. We use vagrant/ansible to spin up our local dev servers, and as soon as we upgraded MySQL, all our MySQL commands started failing as they could no longer authenticate.

There was no obvious way to install with a predefined password, or no password, so we came up with the following to automate setting up MySQL.

Using grep and awk we can pull out the password from the file and set it to a bash variable.

$ mysql_secret=$(sudo grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}')
$ mysqladmin -u root --password=RANDOM_PASSWORD_FROM_LOG password myNewSuperSecretPassword1!
$ mysql_secret=$(sudo grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}')
$ echo $mysql_secret

We can then use this to auth with the mysql cli and change the password.

$ mysqladmin -u root --password=${mysql_secret} password myNewSuperSecretPassword1!

To roll this up into a ansible playbook, I just wrote it as a shell one-liner, where the user and password are set as vars in the playbook.

name: reset mysql 5.6+ default password (no longer empty)
shell: mysql_secret=$(sudo grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}') && mysqladmin -u  --password=${mysql_secret} password 

So, a very terse example that would instal mySQL 5.7 on CentOS 6, set a known root password and then create a database would be...

- hosts: all
dbuser: root
dbpassword: MySuperSecretPassword1!
database: myDatabaseName
- name: Update YUM with latest MYSQL repo
shell: sudo rpm -Uvh
ignore_errors: yes
- name: Install Dependencies.
yum: name= state=installed
- mysql-community-client
- mysql-community-server
- name: Ensure MYSQLD is running and starts at boot
service: name=mysqld state=started enabled=yes
- name: Install ansible dependencies python Mysql package #required for mysql_db tasks
yum: name=MySQL-python state=present
- name: reset mysql 5.6+ default password (no longer empty)
shell: mysql_secret=$(sudo grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}') && mysqladmin -u  --password=${mysql_secret} password 
- name: Create databse
mysql_db: login_user= login_password= name= state=present collation=utf8_general_ci
- name: Run Databse build script
shell: cat /tmp/db/deploy_db.sql | mysql -u  --password= 


Topics: Ansible, Deployment, Database, yum, CentOS, Linux, MySQL, Vagrant, Provisioning, Technology

About the author

Matt Bryson is an experienced systems engineer with over 15 years Development and Systems Operations experience.

Product Briefing

In this short product briefing, see how iDetailAid customers are simplifying the creation, update, localisation and upload of CLM content.

New Call-to-action

Subscribe to Email Updates

Recent Posts