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.
Vagrant.configure(2) do |config|
config.vm.box = "tutorial"
config.vm.hostname = "phptek.dev"
config.vm.network :private_network, ip: "192.168.33.10"
config.hostmanager.enabled = true
config.hostmanager.manage_host = true
config.vm.provision "ansible" do |ansible|
ansible.playbook = "playbook.yml"
end
end
The playbook is a list of actions to be undertaken to set up the desired software.
---
- hosts: all
sudo: true
gather_facts: false
tasks:
- name: Install Packages
apt: name={{item}} state=installed
with_items:
- apache2
- php5
- name: Create Virtual Host
copy: src=vhost.conf dest=/etc/apache2/sites-enabled/000-default.conf
- name: Web site
git: repo=/home/vagrant/our-site/ dest=/var/www/site
handlers:
- name: reload apache
service: name=apache2 state=reloaded
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.
Shell>vagrant provision
[default] Running provisioner: ansible...
PLAY [all] ********************************************************************
TASK: [Install Packages] ******************************************************
ok: [default] => (item=apache2,php5)
TASK: [Create Vitual Host] ****************************************************
ok: [default]
TASK: [Web site] **************************************************************
ok: [default]
PLAY RECAP ********************************************************************
default : ok=4 changed=0 unreachable=0 failed=0
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??
Ansible offers four MySQL Database Modules:
- mysql_db – Add or remove MySQL databases from a remote host.
- mysql_replication (E) – Manage MySQL replication
- mysql_user – Adds or removes a user from a MySQL database.
- mysql_variables – Manage MySQL global variables
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.
Posted by Stoker 
Open Source DBA's Blog 