database: Get ready for production!

To enable backups, the contents of /var/lib/mysql are now stored in a
logical volume managed by LVM. We can take a snapshot of this volume in
a matter of seconds, meaning we can take a clean, local snapshot of the
state of the database with only small amount of downtime. The snapshot
can then be mounted and its contents copied out of the system while the
MariaDB service is running again.

5 files changed, 78 insertions, 39 deletions

diff --git a/README.mdwn b/README.mdwn
index 46a5fe64..d7a40702 100644
--- a/README.mdwn
+++ b/README.mdwn
@@ -77,34 +77,21 @@ To deploy a development instance:
 To deploy this system to production:
 
     packer build -only=production database/packer_template.json
-    nova boot \
-        --flavor dc1.1x1 --image 'database-mariadb' \
-        --key-name=<your-keypair> database-mariadb \
-        --nic='net-id=d079fa3e-2558-4bcb-ad5a-279040c202b5,v4-fixed-ip=192.168.222.30'
+    nova boot database-mariadb \
+        --key-name=<your keypair> \
+        --flavor dc1.1x1 \
+        --image 'database-mariadb' \
+        --nic='net-id=d079fa3e-2558-4bcb-ad5a-279040c202b5,v4-fixed-ip=192.168.222.30' \
+        --user-data ./baserock-ops-team.cloud-config
     nova volume-create \
         --display-name database-volume \
         --display-description 'Database volume' \
-        10
-    nova volume-attach database-mariadb <volume ID> auto
-
-    nova floating-ip-associate database-mariadb <some floating IP>
-
-    # Set up the volume inside the machine
-    echo <IP> > dbhost
-    ansible \* -i dbhost --user=fedora --sudo -m shell \
-        -a "mkfs.ext4 /dev/vdb -L database-volume"
-    ansible \* -i dbhost --user=fedora --sudo -m lineinfile \
-        -a "dest=/etc/fstab create=yes line='LABEL=database-volume /var/lib/mysql ext4 defaults 1 2'"
-    ansible \* -i dbhost --user=fedora --sudo -m shell \
-        -a "mount -a"
+        --volume-type Ceph \
+        100
+    nova volume-attach database-mariadb <volume ID> /dev/vdb
 
-    # FIXME: here we start the service before setting the root password!!!!
-    ansible \* -i dbhost --user=fedora --sudo -m service \
-        -a "name=mariadb enabled=true state=started"
-
-    ansible-playbook -i dbhost --user=fedora database/user_config.yml
-
-    nova floating-ip-disassociate database-mariadb <some floating IP>
+    ansible-playbook -i hosts database/instance-config.yml
+    ansible-playbook -i hosts database/instance-mariadb-config.yml
 
 
 OpenID provider
@@ -199,3 +186,22 @@ To run an ad-hoc command (upgrading, for example):
 
     ansible-playbook -i hosts fedora -m command -a 'sudo yum update -y'
     ansible-playbook -i hosts ubuntu -m command -a 'sudo apt-get update -y'
+
+Backups
+-------
+
+The database server doesn't yet have automated backups running. You can
+manually take a backup like this:
+
+    sudo systemctl stop mariadb.service
+    sudo lvcreate \
+        --name database-backup-20150126 \
+        --snapshot /dev/vg0/database \
+        --extents 100%ORIGIN \
+        --permission=r
+    sudo systemctl start mariadb.service
+    sudo mount /dev/vg0/database-backup-20150126 /mnt
+    # use your preferred backup tool (`rsync` is recommended) to extract the
+    # contents of /mnt somewhere safe.
+    sudo umount /dev/vg0/database-backup-20150126
+    sudo lvremove /dev/vg0/database-backup-20150126
diff --git a/database/local.yml b/database/image-config.yml
index 71ec333b..efa1843d 100644
--- a/database/local.yml
+++ b/database/image-config.yml
@@ -10,6 +10,9 @@
     args:
       creates: /var/log/journal
 
+  - name: install lvm2 tools
+    yum: name=lvm2 state=latest
+
   - name: install MariaDB
     yum: name={{ item }} state=latest
     with_items:
diff --git a/database/instance-config.yml b/database/instance-config.yml
new file mode 100644
index 00000000..a266c4ce
--- /dev/null
+++ b/database/instance-config.yml
@@ -0,0 +1,37 @@
+# Instance configuration for Baserock database server.
+#
+# This script expects a volume to be available at /dev/vdb.
+---
+- hosts: database-mariadb
+  sudo: yes
+  vars:
+    DATABASE_VOLUME_SIZE: 25g
+  tasks:
+    - name: ensure system up to date
+      yum: name=* state=latest
+
+    - name: ensure LVM metadata service is running
+      service: name=lvm2-lvmetad enabled=yes state=started
+
+    # We use LVM on the storage volume to allow taking a snapshot of the
+    # database as part of the database backup procedure.
+    - name: LVM logical volume group on /dev/vdb
+      lvg: vg=vg0 pvs=/dev/vdb
+
+    - name: logical volume for database
+      lvol: vg=vg0 lv=database size={{ DATABASE_VOLUME_SIZE }}
+
+    # This will NEVER overwrite an existing filesystem. Unless you add
+    # 'force=yes' to the arguments. So don't do that. See:
+    # http://docs.ansible.com/filesystem_module.html.
+    - name: ext4 filesystem on /dev/vg0/database
+      filesystem: fstype=ext4 dev=/dev/vg0/database
+
+    - name: mount database logical volume
+      mount: src=/dev/vg0/database name=/var/lib/mysql fstype=ext4 state=mounted
+
+    - name: ensure mysql user owns /var/lib/mysql
+      file: path=/var/lib/mysql owner=mysql group=mysql mode=600 state=directory
+
+    - name: restart the MariaDB service
+      service: name=mariadb enabled=true state=restarted
diff --git a/database/user_config.yml b/database/instance-mariadb-config.yml
index a9be0332..a873b9af 100644
--- a/database/user_config.yml
+++ b/database/instance-mariadb-config.yml
@@ -1,22 +1,15 @@
-# User account configuration for Baserock database server.
+# MariaDB configuration for Baserock database server.
 #
-# If you're setting up a production deployment, you'll need to temporarily give
-# the database instance a public floating IP, then edit 'hosts' in this file
-# to point to that IP and run:
-#
-#   ansible-playbook database/user_config.yml
-#
-# The relevant .database_password.yml files will need to be available too.
-# You should then remove the floating IP from the instance (you can re-add one
-# any time you want to remotely administer the database).
+# The relevant .database_password.yml files will need to be available already.
+# Create these manually and keep them somewhere safe and secret.
 ---
-- hosts: all
+- hosts: database-mariadb
   vars_files:
     - root.database_password.yml
     - baserock_openid_provider.database_password.yml
     - baserock_storyboard.database_password.yml
   tasks:
-    - name: configuring the root database user
+    - name: creating root database user
       mysql_user: |
         name=root
         password={{ root_password }}
@@ -39,8 +32,8 @@
         login_host=127.0.0.1
         login_user=root
         login_password={{ root_password }}
-        collation='utf8_unicode_ci',
-        encoding='utf8',
+        collation=utf8_unicode_ci
+        encoding=utf8
       with_items:
         - openid_provider
         - storyboard
diff --git a/database/packer_template.json b/database/packer_template.json
index c82726b7..2afd78ef 100644
--- a/database/packer_template.json
+++ b/database/packer_template.json
@@ -26,7 +26,7 @@
         },
         {
             "type": "ansible-local",
-            "playbook_file": "database/local.yml",
+            "playbook_file": "database/image-config.yml",
             "command": "sudo ansible-playbook"
         },
         {