Ansible and Loading MySQL Databases Part I

Automation software like Puppet, Chef, and Ansible can quickly load software on virtual servers. But how do you get your MySQL database instances loaded on these new servers? And how do you do it securely? Lets start with a fairly common pairing.

Ansible and Vagrant work very well together and the documentation for getting both to work together is rather extensive. BTW the newest version VirtualBox is 5.0 was recently released with a large number of improvements. Follow the documentation at their respective sites to get VirtualBox, Vagrant, and Ansible installed.

The linchpin is the Vagrantfile which controls how Vagrant starts the server. Inside this file we pull in an Ansible playbook.

The above playbook installs Apache and PHP, creates a virtual host by copying over configuration files to the new server, grabs the website files from git, and then restarts the Apache service so the new configuration & files take effect.

As you can imagine, it is easy to add another item for MySQL and have Ansible start up a fresh install of MySQL. That may be fine for a fresh install but what about cases where you want to use existing databases? Copying over static files may work in some cases but what if you want to copy a live database from another server? Or you need to setup a new replication slave? And how do you do this without exposing passwords??

But these modules may not be what you want. For instance, the replication module does not use GTIDs which means you have to know where in the log file on the master that you need to start replication (and that offset may be a moving target). I prefer to use the MySQL Utilities for coping grants, databases, and setting up replication. So next time I will cover how to get the virtual system running, installing the desired LAMP stack software, and then copying over a complete database with users.