Planet MySQL :: Planet MySQL - Archives - 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.

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.

MySQL Links