Example docs: DB

This is example documentation for the fairytale blog post. It is for an imaginary legacy infrastructure and is not intended to be use.

• Table of contents
• Quick overview
• Current considerations
• Alerts
• Replication lag
• Possible causes
• Determining the replication problem
• Fixing broken replication
• How to
• Fail-over
• Connect to MySQL using the MySQL client
• Recover from a replication failure
• Overriding the health check
• Promote or demote a server
• How to reboot the slave
• How to reboot the master
• Check the status of the replication
• Determine which query is currently being replicated
• Skip the the current query

• Failover being manual: This is temporary while we solve a recurring replication issue that could lead to split brain.
• Topology: Was previously master-master. Currently master-slave to simplify replication while we debug what’s wrong with it. We’ll revisit this once we know more.

• High load.
  • Normal, but insufficient capacity.
  • App bug.
  • Malicious
• Broken replication.

1. Connect to the VPN.
2. Connect to MySQL using the MySQL client
3. Check the replication status and see what state Exec_Source_Log_Pos and Seconds_Behind_Source are in.

Outcomes:

• If Exec_Source_Log_Pos is increasing, and Seconds_Behind_Source is high: Only high load.
• If Exec_Source_Log_Pos is not increasing: Replication is broken.

1. Connect to the VPN.
2. Connect to MySQL using the MySQL client
3. Find out what the current query is that is breaking.
4. Skip it.

1. Connect to the VPN.
2. Force the health check to fail.
3. Wait for the connections to drain.
4. Promote the other server to master.
5. Check that the connections start coming in.
6. Reset the health check.

This is deeper than I want to go for an example. I’ve left this in here to show what the structure would look like.

This is deeper than I want to go for an example. I’ve left this in here to show what the structure would look like.

Force a failure:

1. Connect to the VPN.

2. SSH to the relevant server.

3. Run

   touch /tmp/forceFailure

4. Check that HAProxy has marked the machine as failed..

Allow the health check to operate normally:

1. Connect to the VPN.

2. SSH to the relevant server.

3. Run

   rm /tmp/forceFailure

4. Check that HAProxy has marked the machine as online..

Background: If /tmp/forceFailure exists, the health check that HAProxy uses will fail. If you are unable to SSH to the machine, you can get similar results by manipulating HAProxy.

This is deeper than I want to go for an example. I’ve left this in here to show what the structure would look like.

1. Connect to the VPN.

2. Check that there are no connections to the server..

3. SSH to the server.

4. Run

   sudo shutdown -r 0

5. Watch for the server to come back..

NOTE: If you need to do the acting slave as well, do it first, to minimise the number of times you need to drain the connections.

1. Connect to the VPN.
2. Promote the slave to master and demote the current master.
3. Check that connections have moved to the original slave..
4. Follow the slave reboot steps.
5. Promote the server back to master and demote the current master back to slave.
6. Check that connections have moved to the original slave..

1. Connect to the VPN.

2. Connect to MySQL using the MySQL client

3. Run this a few times over a few seconds:

   SHOW REPLICA STATUS\G

   • Look at Exec_Source_Log_Pos and Seconds_Behind_Source are doing. You’ll need this information for what ever instructions sent you here.

This is deeper than I want to go for an example. I’ve left this in here to show what the structure would look like.

1. Connect to the VPN.

2. Connect to MySQL using the MySQL client

3. Check the replication status and see what state Exec_Source_Log_Pos and Seconds_Behind_Source are in.

4. Run

   SET GLOBAL sql_replica_skip_counter = 2;START SLAVE;

5. Check the replication status and see what state Exec_Source_Log_Pos and Seconds_Behind_Source are in over the space of the next minutes.

At this point, Exec_Source_Log_Pos should have now increased, and Seconds_Behind_Source should start decreasing over time.