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: "setup.sh"
and create the setup.sh shell script.
#!/bin/bash # # 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 vars: - docroot: /var/www/html tasks: - 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 with_items: - 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