High Availability MySQL Design Manual
*****************************************

1. Objectives

    Our primary objective is to provide a MySQL database to our stakeholders
that experiences no unscheduled downtime without requiring user level awareness
or any modifications to existing applications.  Such a system should remain
fully functional in the event of any single failure of database software,
machine, networking equipment and building power.

    A secondary objective is to provide performance similar to or better than
our existing database systems.  The primary metrics for determining relative
performance being latency and throughput.

2. Design

3. Operations

    3.1 Designate a master

        To designate a MySQL node as a master for the purposes of replication
        it is necessary to create a database user with permission to replicate
        that slave nodes can then use to connect and receive transaction logs.

        Anthony Brennan recommends creating a seperate user with a unique
        password per slave node, so that nodes can be removed without
        interfering with system operations.

        Applying the following salt configuration directive to the master is
        sufficient:

        ${slave_node_hostname}_user:
            mysql_user.present:
                - password_hash: "${output of SELECT PASSWORD('your_passwd');"

        ${slave_node_hostname}slave:
            mysql_grants.present:
                - grant: replication slave
                - database: "*.*"
                - user: ${slave_node_hostname}_user

    3.2 Activate a slave

Appendix A: Initial Setup

    1: Create containers
        On each host {dc-3558-411, mc-3015-411, m3-3101-411} create a container
        for a MySQL node.

            root@dc-3558-411:~# lxc-create -t cscf-ubuntu -n \
mysql-102.cs.uwaterloo.ca

            root@mc-3015-411:~# lxc-create -t cscf-ubuntu -n \
mysql-104.cs.uwaterloo.ca

            root@m3-3101-411:~# lxc-create -t cscf-ubuntu -n \
mysql-106.cs.uwaterloo.ca

    2: Allocate NVME storage
        On each host {dc-3558-411, mc-3015-411, m3-3101-411} create a logical
        volume for database information

            root@dc-3558-411:~# lvcreate -L 100G -n mysql-102.cs intel_nvme
            root@dc-3558-411:~# mkfs -t ext4 \
/dev/mapper/intel_nvme-mysql--102.cs
            root@mc-3015-411:~# lvcreate -L 100G -n mysql-104.cs intel_nvme
            root@dc-3558-411:~# mkfs -t ext4 \
/dev/mapper/intel_nvme-mysql--104.cs
            root@m3-3101-411:~# lvcreate -L 100G -n mysql-106.cs intel_nvme
            root@dc-3558-411:~# mkfs -t ext4 \
/dev/mapper/intel_nvme-mysql--106.cs

    3. Add entries to mount NVME storage inside containers
        On each host {dc-3558-411, mc-3015-411, m3-3101-411} create an entry
        in each containers fstab file.

            root@dc-3558-411:~# echo /dev/mapper/intel_nvme-mysql--102.cs\
/var/lib/lxc/mysql-102.cs.uwaterloo.ca/rootfs/var/lib/mysql ext4\
defaults,create=dir
            root@mc-3015-411:~# echo /dev/mapper/intel_nvme-mysql--104.cs\
/var/lib/lxc/mysql-104.cs.uwaterloo.ca/rootfs/var/lib/mysql ext4\
defaults,create=dir
            root@m3-3101-411:~# echo /dev/mapper/intel_nvme-mysql--106.cs\
/var/lib/lxc/mysql-106.cs.uwaterloo.ca/rootfs/var/lib/mysql ext4\
defaults,create=dir

    4. Create a unique server-id per MySQL instance
        On each MySQL node {mysql-102, mysql-104, mysql-106} create a MySQL
        configuration file with a unique server-id parameter.

            root@mysql-102:~# echo "server-id = 1" >> \
/etc/mysql/conf.d/server-id.cnf
            root@mysql-104:~# echo "server-id = 2" >> \
/etc/mysql/conf.d/server-id.cnf
            root@mysql-106:~# echo "server-id = 3" >> \
/etc/mysql/conf.d/server-id.cnf

    5. Configure each MySQL instance to listen on its network address
        On each MySQL node {mysql-102, mysql-104, mysql-106} append a
        "bind-address" configuration directive to the host specific
        configuration file.

            root@mysql-102:~# echo "bind-address = ${NODE_IP}" >> \
/etc/mysql/conf.d/server-id.cnf
            root@mysql-104:~# echo "bind-address = ${NODE_IP}" >> \
/etc/mysql/conf.d/server-id.cnf
            root@mysql-106:~# echo "bind-address = ${NODE_IP}" >> \
/etc/mysql/conf.d/server-id.cnf

-- AnthonyBrennan - 2016-02-18