Ansible and Loading MySQL Databases Part II

Loading databases on virtual systems is not as straight forward as it should be. Last time the foundation for using Ansible was set down on our quest to be able to have fully functional database servers on virtual servers automatically. But that prompted a few of you to remind me that you do not always need Ansible.

Without Ansible
You can have Vagrant do the work. Modify the vagrant file to run a script to run a script when provisioned, such as
config.vm.provision :shell,path: ""
and create the shell script.

# example setup script for LAMP stack on Vagant box
sudo apt-get -y update
sudo apt-get -y install apache2 php5 libapache2-mod-php5
## Set passwords for MySQL server
sudo debconf-set-selections --verbose <<< 'mysql-server-5.5 mysql-server/root_password password wordpass'
sudo debconf-set-selections  --verbose <<< 'mysql-server-5.5 mysql-server/root_password_again password wordpass'
sudo apt-get -y install mysql-server php5-mysql php5

This approach works great and would be fairly simple to maintain. Secure? No. Having a password in plain text is a major no-no.

With Ansible
The playbook.yml has evolved since the last blog entry. This example is a bit pedantic but was written that way for clarity. This playbook will set up a LAMP server. Note that Step 3 where the MySQL server is installed will need modification for the upcoming MySQL 5.7 release to force a password as it will require an insecure install (mysql_install_db –insecure) see here for details, as 5.7 will assign a random root password by default.

# Playbook.yml
- hosts: all
  remote_user: vagrant
  sudo: yes
   - docroot: /var/www/html
    - name: 1. Install Apache
      apt: name=apache2 state=present

    - name: 2. Install PHP Module for Apache
      apt: name=libapache2-mod-php5 state=present

    - name: 3. Set MySQL root password before installing
      debconf: name='mysql-server' question='mysql-server/root_password' value='wordpass
      debconf: name='mysql-server' question='mysql-server/root_password_again' value='wordpass' vtype='password'

    - name: 4. Install Mysql
      apt: name={{ item }} state=latest
        - php5-mysql
        - mysql-server
        - python-mysqldb

    - name: 5. Copy Homepage
      copy: src=index.php dest=/var/www/html/index.php mode=0644

    - name: 6. Start Apache
      service: name=apache2 state=running enabled=yes

    - name: 7. Copy database over
      copy: src=../Downloads/world_innodb.sql dest=/tmp/world.sql

    - name: 8. Create database
      mysql_db: name=world state=present login_user='root' login_password='wordpass'

    - name: 9. Import data
      mysql_db: name=world state=import target=/tmp/world.sql login_user='root' login_password='wordpass'

In Step 7 the World database is copied over to the new server. This file was created bu running mysqldump on the database.

Step 8 is where Ansible helps us a great deal. There are core modules in Ansible just for dealing with MySQL. Here mysql_db is used to create the database. The same command is used in the next step to import the SQL file (moved in step 7) into the database. See the Database Modules docs for details.

Problems? We still have the root password in plain text but hopefully stored in a directory with strong enough permissions to keep most at bay. Ansible does have a vault to hold sensitive information like password.

Reading databases (and probably several at the same time) from a SQL file generated by mysqldump is not going to be optimal. Transportable table spaces or using a LVM dump may be a better solution. Or using mysqldbcopy from the MySQL Utilities for a hot copy from a currently running server.

But those problems can wait for another installment

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| = "tutorial"
  config.vm.hostname = "" :private_network, ip: ""
  config.hostmanager.enabled = true
  config.hostmanager.manage_host = true
  config.vm.provision "ansible" do |ansible|
    ansible.playbook = "playbook.yml"


The playbook is a list of actions to be undertaken to set up the desired software.

- hosts: all
  sudo: true
  gather_facts: false
   - name: Install Packages
     apt: name={{item}} state=installed
      - 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

    - 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.

Indexing MySQL JSON Data

“MySQL’s JSON data type is great! But how do you index the JSON data?” I was recently presenting at the CakePHP Cakefest Conference and was asked that very question. And I had to admit I had not been able to play, er, experiment with the JSON datatype to that level. Now I have and it is fairly easy.

1. Create a simple table
mysql> desc colors;
| Field | Type | Null | Key | Default | Extra |
| popular_name | char(10) | YES | | NULL | |
| hue | json | YES | | NULL | |
2 rows in set (0.00 sec)

2. Add in some data
INSERT INTO `colors` VALUES ('red','{\"value\": \"f00\"}'),('green','{\"value\": \"0f0\"}'),('blue','{\"value\": \"00f\"}'),('cyan','{\"value\": \"0ff\"}'),('magenta','{\"value\": \"f0f\"}'),('yellow','{\"value\": \"ff0\"}'),('black','{\"value\": \"000\"}');

3. SELECT some data
Use the jsb_extract function to efficiently search for the row desired.
mysql> select jsn_extract(hue, '$.value') from colors where jsn_extract(hue, '$.value')="f0f";
| jsn_extract(hue, '$.value') |
| "f0f" |
1 row in set (0.00 sec)

But how efficient is that? Turns out we end up doing a full table scan.

mysql> explain select jsn_extract(hue, '$.value') from colors where jsn_extract(hue, '$.value')="f0f";
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | colors | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | Using where |

4 Add a VIRTUAL column to index quickly
mysql> ALTER TABLE colors ADD value_ext char(10) GENERATED ALWAYS AS (jsn_extract(hue, '$.value')) VIRTUAL;
This will add a virtual column from the value data in the hue column.

5 Index the New Column
mysql> CRATE INDEX value_ext_index ON colors(value_ext);

Now the EXPLAIN shows us that we are more efficient.
mysql> explain select jsn_extract(hue, '$.value') from colors where value_ext="f0f";
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | colors | NULL | ref | value_ext_index | value_ext_index | 11 | const | 1 | 100.00 | NULL |
1 row in set, 1 warning (0.00 sec)


JSON and the MySQL Argonauts

The MySQL 5.7.7 JSON lab release has been getting a lot of attention. At a recent conference, I was cornered by a developer who wanted to jump in with both feet by running this release on his laptop on the flight home. Jason and the Argonaughts Movie Poster However the developer was not sure how to begin.

1. Down load the MySQL JSON release from You will get the choice of a Linux binary or source code. Please grab the binary if you are using Linux and un-gzip/tar the download.

2. Shut down the current running version of MySQL. I was lucky in this case that the developer was using a recent copy of Ubuntu.

3. Change directory to the ~/Downloads/mysql-5.7.7-labs-json-linux-el6-x86_64 directory.

4. sudo ./bin/mysqld_safe –user=mysql&

5. ./bin/mysql -u root -p, then provde the password.

6. Enter a \s to get the status. This will confirm that you are using the JSON labs release.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.7-labs-json MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


If you are doing more than simple tests, run mysql_upgrade to update the system tables. But you can skip this step for a quick and dirty exploration but do not expect your JSON data to be around when you go back to the previous version of MySQL.

7. Now you can start testing the JSON data type. I recommend starting with reading JSON Labs Release: JSON Functions, Part 1 — Manipulation JSON Data, JSON Labs Release: JSON Functions, Part 2 — Querying JSON Data, and JSON Labs Release: Native JSON Data Type and Binary Format. Then follow up with JSON Labs Release: Effective Functional Indexes in InnoDB to understand how to create indexes.