CS MySQL Database High-Availability Testing Notes for System Administrators - DRAFT

This document is for historical use only, as of 8 April, 2016. Current details are kept in MySQLHAOperationsManual

This document assumes:

• You have credentials to access mysql-10[246].cs.uwaterloo.ca and InfoBlox. Credentials for various remote management interfaces may also be useful.
• mysql 5.5
• the high-availability architecture is one master with two slaves using asynchronous replication
• 102 denotes mysql-102.cs, the master
• 104 denotes mysql-104.cs, the first slave of 102
• 106 denotes mysql-106.cs, the second slave of 102
• mysql.cs is the hostname of the service that clients connect to and has the same IP address as 102

Step 0: Find which servers are up and identify master and slaves

• linux.cscf# for i in mysql-102.cs mysql-104.cs mysql-106.cs ; do echo ; echo $i ; ssh -x $i "mysqladmin status processlist" ; done
  • Normally, all 3 hosts have a mysqld running with significant uptime.
  • The master processlist should include two slave threads, one for each slave server.
  • Each slave processlist should include two replication threads, one reading events from the master and one executing them.
• Need to know which of mysql-10[2,4,6].cs.uwaterloo.ca is current master (if any) and/or which slaves are currently/recently slaving to.
• If no current master, most recent master needs to be unambiguously powered down before continuing. Proceeding without verified removal of recent master risks corruption of data!
• master, slaves - mysql> show slave status\G and look for Master_Host value. On slaves, Master_Host is recognized master. On master, Master_Host should return Empty_set
• If slaves report different Master_Host values, stop troubleshooting and report outage to responsible CSCF staff. Continuing risks data loss.
• If master as reported consistently by all slaves is not reachable, follow as in below Restoring Broken Old Master After Failover : # lxc-info --name mysql-102.cs.uwaterloo.ca and verify State: STOPPED. If not stopped, force container stop lxc-stop mysql-102.cs.uwaterloo.ca and recheck state. Do not proceed until former master is verified stopped.

Normal operation:

On denoted systems {master, slaves}:

• master, slaves - # lsof -i | grep LISTEN | grep mysql shows mysqld listening on correct interface(s)
• master, slaves - mysql> show variables like 'read_only'; shows read_only is OFF for master and ON for slaves
• master, slaves - # mysqladmin status processlist
• (DETAILS) slaves - make batch updates that will take approx. 5 minutes wall-clock ( eg., output of perl -e 'print"pKey,numberOfCPUs\n";for($i=1; $i<5; $i++){ print "15349,$i\n";}' )
• master, slaves - # mysqladmin status processlist (under load)
• master - mysql> show master status\G
• slaves - mysql> show slave status\G
• master - # pt-table-checksum --defaults-file=/etc/mysql/pt_checksum.cnf --replicate=percona.checksums --ignore-databases mysql --no-check-binlog-format
• master - # ls -l /var/log/mysql/binlog.*
• (Purpose? Not generic) master - something like # mysqlbinlog --start-position=29972483 /var/log/mysql/binlog.000009 or --start-datetime='2004-12-25 11:25:56'
• master - mysql> select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH, AUTO_INCREMENT from information_schema.tables where TABLE_SCHEMA = 'equipment'
• master, slaves - should be clean # ls -l  /var/log/mysql.err

Slave fail and recover via stop slave:

• target slave - mysql> stop slave;
• slaves - show slave status
• master - how does pt-table-checksum react? Does it show one slave is OK and the other is down or out of sync?
• target slave - mysql> start slave;
• target slave - mysql> show slave status\G - how long does it take to catch up?
• check error logs. master and non-target slave should be clean. target slave shows slave stop, start, catchup?

Slave fail and recover via service:

• target slave - # service mysql stop
• target slave - # tail -f /var/log/mysql/error.log Look for correct shutdown. Note binlog stop file and position.
• master - how does pt-table-checksum react?
• target slave - # service mysql start
• target slave - # tail -f /var/log/mysql/error.log Look for correct startup. Note binlog start file and position.
• target slave - mysql> show slave status\G - how long does it take to catch up? Note binlog current file and position.
• check error logs. master and non-target slave should be clean. target slave shows no errors.

Complete Failover: (master is not providing service: move master to an available slave and bring former master back as slave)

Caution, follow this procedure carefully; there are subtle reasons for doing these operations and in this order. However, it can't handle all possible failure situations.

• decide that failover is appropriate (see normal operation manual).
• check that slaves are equally up to date, e.g.: 104 and 106 - mysql> show slave status
• redirect clients to target slave/new master eg. 104
  • check TTL (window clients will transition in)
  • use infoblox to change IP address and PTR record for mysql.cs to the new master (cannot be done directly in inventory, but can be done via Infoblox Command Line Interface -- At this point clients will start transitioning to new master, but new master is still firewalled. Alternatively, use InfoBLox GUI, search for "mysql.cs" with search tool in upper right corner of interface. Open record and update Host and PTR records to new master IP address: mysql-102 -> 10.15.152.27, mysql-104 -> 10.15.154.27, mysql-106 -> 10.15.155.27
• take down former master and ensure it cannot come back by accident. This must happen before new master becomes writeable.
  • if possible: on former master # service salt-minion stop and break(Need details for "break"!!)
  • if possible: on former master change firewall to block all client traffic: Uncomment MySQL(REJECT)   net            $FW on final line of /etc/shorewall/rules and then restart firewall by running shorewall restart
  • do only the first one possible:
    • former master # service mysql stop and break (details!!)
    • lxc container stop; and break container by altering config file: # lxc-info --name mysql-102.cs.uwaterloo.ca and verify State: STOPPED. If not stopped, force container stop lxc-stop mysql-102.cs.uwaterloo.ca and recheck state. Do not proceed until former master is verified stopped.
    • take down the IAAS lxc host and prevent it from starting
    • stop the world from reaching host - ona port shutoff (with cMySQL(REJECT) net $FWomment): Search for former master port at ONA by MAC/IP address: mysql-102 -> 00:16:3e:20:7b:cf/10.15.152.27, mysql-104 ->00:16:3e:86:2b:a9/10.15.154.27, mysql-106 -> 00:16:3e:3c:8d:61/10.15.155.27
• # service salt-minion stop on former slaves to avoid changes being pushed from salt master during these steps.
• firewall rule change: comment out MySQL(REJECT)   net            $FW on new master and then restart firewall by running shorewall restart -- At this point clients that have transitioned to 104 will find it works, but is read-only. Check
• mysql on slave: change master to new master (needs file position: run show master status on new master, record log file name and log position.)
  • Replace the MASTER PASSWORD with the password recorded on each host in /etc/mysql/{$host}_slave.cnf (the passwords are the same for all three hosts).

root@mysql-106:~# mysql -e 'STOP SLAVE'
root@mysql-106:~# mysql -e 'CHANGE MASTER TO MASTER_HOST="mysql-104.cs.uwaterloo.ca",MASTER_USER="mysql-106_slave", MASTER_PASSWORD="{$slave_password}", MASTER_LOG_FILE="binlog.000002", MASTER_LOG_POS=332'
root@mysql-106:~# mysql -e 'CHANGE MASTER TO MASTER_HOST="mysql-104.cs.uwaterloo.ca",MASTER_USER="mysql-106_slave", MASTER_LOG_FILE="binlog.000002", MASTER_LOG_POS=332'

• mysql on new master: stop slave; reset slave all; edit /etc/mysql/conf.d/server-id.cnf to comment out read_only and service mysql restart

At this point, 104 will have active client writes.

• Typically, the most important client host is the web server, currently cs.uwaterloo.ca. If it still has the old IP address cached, clear the cache.
  • On the web server: nscd --invalidate hosts
• Test normal operation (operation manual)... especially:
  • pt-table-checksum on new master
  • check that apps are working. This includes www.cs.uwaterloo.ca, CSCF Inventory, others

• new master, slave: service salt-minion start

The normal procedure is that the new master stays master; the former master is repaired as required and returned to service as a slave of the new master.

Restoring Broken Old Master After Failover

Caution: this process is meant to cover most situations- output of commands should be reviewed to make sure they make sense. We haven't documented all of the edge-cases of failover and recovery, so if there are missing parts, they should be filled in below.

• decide that we're ready to fix the old master (does NOT need to happen during/right after the emergency of failing over the database- it will wait until there's time).
• if the old master lxc container (eg, 102) is up and you can ssh in from linux.cscf, skip ahead to the "mysql config" section below. Otherwise:
• To avoid some trial-and-error checking, you should find out how the old master was brought down- restore will depend in part on their work. Check the ST for the failover, and possibly check with the staff-person who failed over the machine. If there are no hints, you can follow the following steps in increasing order of complexity.

• you need to know the IAAS lxc host, which you can find by checking the Infrastructure containers list: https://cs.uwaterloo.ca/cscf/internal/infrastructure/inventory/virtual-host-index/
  • If that list isn't conclusive, search for your LXC container in inventory and hopefully either the inventory record, or its eDocs page, will list its last known location.
• if the IAAS lxc host is up and you can ssh in from linux.cscf, do so and restart the lxc container, in a similar fashion to:

ubuntu1404-202:~# ssh dc-3558-411
Welcome to Ubuntu 14.04.4 LTS (GNU/Linux 3.19.0-56-generic x86_64)

root@dc-3558-411:~# lxc-info --name mysql-102.cs.uwaterloo.ca
Name:           mysql-102.cs.uwaterloo.ca
State:          STOPPED
root@dc-3558-411:~# lxc-start -d -n mysql-102.cs.uwaterloo.ca

• if the IAAS lxc host is not up, you can restart it by: finding it in inventory and determining its lom-cs{barcode}.cs.uwaterloo.ca hostname.

See https://cs.uwaterloo.ca/cscf/internal/infrastructure/inventory/IAAS-hardware-index/ for current data. Last seen 2016-04-04:

	Management interface	MySQL system	Container hostname
	https://lom-cs009701.cs.uwaterloo.ca	mysql-102	dc-3558-411.cloud.cs.uwaterloo.ca
	https://lom-cs009728.cs.uwaterloo.ca	mysql-104	mc-3015-411.cloud.cs.uwaterloo.ca
	https://lom-cs009732.cs.uwaterloo.ca	mysql-106	m3-3101-411.cloud.cs.uwaterloo.ca

Note that the lom-cs* interfaces can only be reached from systems on VLAN 15 (eg. CSCF workstations).

• • visit the URL for the lom-cs hostname. Credentials for lom-cs* interfaces can be found in CSCF safe under "LOM".
  • once logged in, confirm that the machine is powered off and select the "Power ON / OFF" option to turn it on. A minute later, the LOM page should auto-update from displaying a thumbnail of a booting machine and show a thumbnail of a command-prompt. it should now be accessible via ssh; proceed with the "IAAS lxc is up" item above.
• if, after restarting the machine, it is not ssh accessible, the ona port might have been shut off.
  • search for the IAAS lxc host's regular IP address (ie., the IP for dc-3558-411, not the IP for the LOM); find its port. On the port's page, look for its status (enabled or disabled); if disabled, a comment explaining why should be present. If it's something you are comfortable re-enabling, erase the comment and re-enable; hopefully ssh to the IAAS lxc host will succeed and and you may proceed with the "IAAS lxc is up" item above.

Mysql Config:

• ssh to the old master host.
• Check that firewall is configured to reject incoming connections from MySQL clients. Ensure MySQL(REJECT)   net            $FW is NOT commented out at the bottom of /etc/shorewall/rules
• Restart firewall by running shorewall restart
• edit /etc/mysql/conf.d/server-id.cnf to uncomment read_only
• "unbreak" /usr/sbin/mysqld if it was renamed to 'mysqld-x' - possibly =mv /usr/sbin/mysqld{-x,}
• service mysql start should work and result in "mysql start/running, process nnnnnn" not "start: Job failed to start".
• verify mysql is running clean: check /var/log/mysql/error.log for problems, and run mysql from the command-line. If it runs, onward to configuring as a slave.

Configuring as a slave:

• currently this host is operating as a read-only master with no slaves.
• show slave status; should return an empty set.
• show master status; should return a binlog file and position. Note these results.
• The master binlog on this host will have stopped just before that position; hopefully cleanly. You can review the previous binlog file running mysqlbinlog /var/log/mysql/binlog.{nnnnnn} |less
• to configure as a slave, we need to find the binlog transaction on the new master which corresponds to the last transaction on this machine.
  • If there is a clean match, the new master was caught up to this old master. If there isn't, you might need to be clever.
• Follow the above instructions for re-slaving 106 from 104: "mysql on 106: change master to 104 ( needs file position: run show master status on 104 )"

Move Some (but not all) Databases from Old MySQL server to New HA Cluster

This section assumes:

• mysql 5.5
• the high-availability architecture is one master with two slaves using asynchronous replication
• 102 denotes mysql-102.cs, the first slave of 104
• 104 denotes mysql-104.cs, the master
• 106 denotes mysql-106.cs, the second slave of 104
• 172 denotes mysql-172.cs, the old server
• mysql.cs is a CNAME or extra A record for mysql-172.cs
• mysqltest.cs is a CNAME or extra A record for mysql-104.cs

First, copy the selected databases from the old server to the new (and prepare for replication). This is not time sensitive.

• On the old mysql server:
  • In general, you can use mysqldump to dump the databases you want to move. For the March 2016 move of mysql.cs, use mysqlbackupgrants to dump all the grants and mysqldumpexcept to dump all the databases except a few we need to ignore. Run the dump twice, separated by enough time to get an unambiguous binlog position.
    • PATH="/usr/local/lib/mysql/bin:$PATH"
    • mysqlbackupgrants ; mysqldumpexcept ; echo ; sleep 5 ; mysql --defaults-file=/etc/mysql/debian.cnf -e 'show master status' ; echo ; sleep 5 ; mysqldumpexcept ; mysqlbackupgrants
  • Compare the dumps.
    • diff `ls -tr /var/backups/mysql/*-show-grants.sql | tail -2`
    • diff -rs `ls -dtr /var/backups/mysql/*-mysqldumpexcept | tail -2`
  • Repeat the above dump and compare until there is no difference in the dumps. For the current load on mysql.cs, this almost always succeeds the first time.
  • Note the binlog file name and position, for use in the CHANGE MASTER command later.
  • If it doesn't already exist, grant replication slave for the new master.
    • slavepassword=`dd if=/dev/random status=none bs=24 count=1 | base64 | tr -d /+`

    • mysql --defaults-file=/etc/mysql/debian.cnf -e "GRANT REPLICATION SLAVE ON *.* TO 'mysql-104_slave'@'mysql-104.cs.uwaterloo.ca' IDENTIFIED BY '$slavepassword' "

  • if there is already a replication slave, note the password recorded on each host in /etc/mysql/{$host}_slave.cnf (the passwords are the same for all three hosts).
• On asgard:
  • Wait for the hourly rsync cron job to copy your dumps. Or rsync it now.
    • rsync -a  mysql.cs:/var/backups/mysql/  /mysql/backups/mysql.cs
  • Then, copy one of the identical dumps to the new master.
    • thegrants=`ls -tr /mysql/backups/mysql.cs | grep .-show-grants.sql | tail -1`
    • echo thegrants=$thegrants ; ls -l /mysql/backups/mysql.cs/$thegrants    # found the correct grants?
    • thedump=`ls -tr /mysql/backups/mysql.cs | grep .-mysqldumpexcept | tail -1`
    • echo thedump=$thedump ; ls -ld /mysql/backups/mysql.cs/$thedump    # found the correct dump?
    • tmpdir=`ssh mysqltest.cs mktemp -d`
    • echo tmpdir=$tmpdir    # got a good tmpdir?
    • rsync -a  /mysql/backups/mysql.cs/$thegrants  /mysql/backups/mysql.cs/$thedump  mysqltest.cs:$tmpdir
• On the new master:
  • Init.
    • thegrants=value-from-above
    • thedump=value-from-above
    • tmpdir=value-from-above
    • cd $tmpdir
  • Load the grants needed by the selected databases(s). E.g.:
    • (Details to be added later...)
  • Load the selected database(s). E.g.:
    • ls -l $thedump/equipment.sql    # looks OK?
    • mysql <$thedump/equipment.sql

Second, start replication of the copied database(s) from the old server to the new.

• On the new master:
  • Start replication from the old mysql server.
    • CHANGE MASTER TO  MASTER_HOST='mysql-172.cs.uwaterloo.ca', MASTER_USER='mysql-104_slave', MASTER_PASSWORD='slavepassword-from-above', MASTER_LOG_FILE='binlog-File-from-above', MASTER_LOG_POS=Position-from-above;
    • Create a temporary config file.

mysql-104# cat /etc/mysql/conf.d/temp-slave.cnf
# Configuration used only while we are moving the indicated database to this host.
[mysqld]
replicate-do-db	= 'equipment'

• • • Restart mysqld service mysql restart
    • Confirm the slave has caught up mysql -e 'show slave status\G'
• On the old mysql server (172), set up grants (if they don't already exist) for pt-table-checksum.
  • GRANT PROCESS, REPLICATION SLAVE, SELECT, SUPER ON . TO 'pt_checksum'@'localhost' IDENTIFIED BY PASSWORD '*33...9E';
  • GRANT ALL PRIVILEGES ON `percona`.* TO 'pt_checksum'@'localhost';
  • GRANT PROCESS, REPLICATION SLAVE, SELECT, SUPER ON . TO 'pt_checksum'@'mysql-%.cs.uwaterloo.ca' IDENTIFIED BY PASSWORD '*33...9E';
  • GRANT ALL PRIVILEGES ON `percona`.* TO 'pt_checksum'@'mysql-%.cs.uwaterloo.ca';
• On the old server, check that old and new servers have identical copies of the database being moved.
  • 172# pt-table-checksum --defaults-file=/etc/mysql/pt_checksum.cnf --replicate=percona.checksums --databases equipment --no-check-binlog-format --no-check-replication-filters
• On the new server, check slave is still OK, because with this temporary setup, some types of pt-table-checksum errors can break replication.
  • 104# mysql -e 'show slave status\G'

At this point, the old and new servers have identical copies of the database, clients are using the old server, and any changes on the old server are replicated to the new.

• Optionally, check the stability of the temporary slave setup by leaving it running overnight.

The following preparation should be done shortly before client visible downtime starts.

• If telling the clients to use a different server is done via DNS changes:
  • Find the current TTL for the server hostname clients use. It should already be low (5 minutes is typical), not the default of 3 hours.
  • Decrease the TTL to 1 minute. This is a major part of client visible downtime.
  • Wait for the previous TTL to expire.
• Take one more backup in case of disaster in the switch-over to follow.
  • 172# /usr/local/lib/mysql/bin/mysqlbackupgrants ; /usr/local/lib/mysql/bin/mysqldumpexcept
• Immediately before moving the clients from the old server to the new, check that they are still synchronized.
  • 104# mysql -e 'show slave status\G' Did replication break overnight?
  • 172# pt-table-checksum --defaults-file=/etc/mysql/pt_checksum.cnf --replicate=percona.checksums --databases equipment --no-check-binlog-format --no-check-replication-filters Did the two copies of the database diverge overnight?
  • 104# mysql -e 'show slave status\G' Did pt-table-checksum break replication now?

Caution: Client visible downtime starts here. For most of the downtime, clients of all the mysql databases on the old and new servers can read, but not write the database. This section should be done quickly, but carefully. Any mistakes may cause permanent data loss and extended down time.

You will need at least 2 windows. Each of the old and new server will need its own mysql session.

• Make the old server read-only! (except for threads with SUPER or REPLICATION SLAVE privilege). This prevents clients from losing data by using the old server after replication stops.
  • 172# mysql> SET GLOBAL read_only = ON;
• Make the new server read-only! (except for threads with SUPER or REPLICATION SLAVE privilege). This prevents clients from losing data by using the new server before it is ready.
  • 104# mysql> SET GLOBAL read_only = ON;
• Show binlog position on old server.
  • 172# mysql> SHOW MASTER STATUS;
• Check that the new server is caught up (at least check: binlog file and position match above; 0 seconds behind master).
  • 104# mysql> show slave status\G
• Check that old and new servers have identical copies of the database being moved.
  • 172# pt-table-checksum --defaults-file=/etc/mysql/pt_checksum.cnf --replicate=percona.checksums --databases equipment --no-check-binlog-format --no-check-replication-filters
• Tell clients to stop using the old server and start using the new. Depending on the database(s) being moved, use the correct one of the following methods:
  • Change the server hostname in all appropriate client config.
  • Or, use infoblox to delete and recreate the appropriate CNAME pointing to the new server.
  • Or, use infoblox to delete and recreate the appropriate virtual host (extra A record) with the IP address of the new server. Fix the PTR if it is missing or incorrect.
    • Infoblox operations on A records may be performed via the Infoblox Command Line Interface. Read the instructions first; then as root on www152.cs you can su cs-inv and run: ~/infoblox-scripts/ibclispeedy -e 'conf zone "" modify host {hostname.cs.uwaterloo.ca} {new IP address}'
• At this point (if using DNS changes), some clients are using the new server and some are still using the old until their TTL expires. Both servers are still read-only.

• IMPORTANT: Stop replication on the new server! This prevents the database from being dropped on the new server in the next step.
  • 104# mysql> STOP SLAVE; RESET SLAVE ALL;
• Verify that replication has stopped on the new server! The following output should be empty.
  • 104# mysql> SHOW SLAVE STATUS\G
• On the old server, drop the database being moved! This prevents clients from losing data (or seeing incorrect old data) by accidentally using the old server. After this point, any client still trying to use the moved database on the old server will fail.
  • 172# mysql> DROP DATABASE equipment;

• On the old server, return to normal operation with the remaining databases.
  • 172# mysql> SET GLOBAL read_only = OFF;
• On the new server, return to normal operation with addition of the moved database(s).
  • 104# mysql> SET GLOBAL read_only = OFF;
• Delete the temporary config file on the new server and restart mysqld.
  • 104 rm /etc/mysql/conf.d/temp-slave.cnf
  • 104 service mysql restart

At this point, client visible downtime is over and all operations should be back to normal (assuming the 1 minute TTL has expired for all clients). Some essential clean-up work remains.

• Perform tests to ensure old and new server are working normally.
  • Are all the databases present that should be?
  • Are all applications working correctly?
  • Does the data look correct, e.g. most recent transactions in a history table.
• If telling the clients to use a different server is done via DNS changes:
  • Change TTL back to normal for the service. 5 minutes is typical.
  • If you are using the extra A record method, check that the PTR is correct.

At this point, slaves 102 and 106 probably will have replication holes in the moved database(s).

• First, you must run pt-table-checksum on the master to find and record any differences between master and slaves.
  • 104# pt-table-checksum --defaults-file=/etc/mysql/pt_checksum.cnf --replicate=percona.checksums --no-check-binlog-format --quiet
• Then, if there are diffs, run pt-table-sync to fix them.
  • 104# pt-table-sync --defaults-file=/root/.my.cnf --verbose --execute --replicate=percona.checksums h=localhost

-- FraserGunn - March 2016