Automated, Non-Stop MySQL Operations and Failover

There are failure scenarios that might cause unscheduled downtime, and some of them are really difficult to solve quickly. One typical example is master crash. Suppose you run single master and multiple slaves. If the master crashes, you need to pick one of the slaves, promote it to the new master, and let other slaves start replication from the new master. This is not trivial. Even though you could identify the latest slave, other slaves might have not received all relay logs, which will cause consistency problems. How do you make them in sync? Is it ok for you to run with data inconsistency risks? In this session, I will talk about how to automate master failover. I’ll explain how to script an automated master failover program that do the followings.

• Picking the new master (the lastest one)
• Making all slaves consistent(to the new master) within short time
• Restarting replication

This will be helpful for many people who do not want to spend much money for standby servers (i.e. DRBD) but also want to achieve short-time failover.

Another interesting failure scenario is a slave crash. Since updating master.info, relay-log.info and InnoDB log files is not atomic each other, data consistency might be broken when a slave crashes. In other words, replication thread might stop on restart due to “duplicate key error”. Do you recover slaves by restoring a full backup file? This is not fun. MySQL development team and communities have worked a lot for this area. I’ll talk an approach to check consistencies between relay logs and InnoDB log file, and recovering consistency without full data restore.

Non-stop scheduled maintenance is also an interesting topic. Maintenance tasks such as online schema changes, sharding, aggregating shards, migration are not trivial, but third-party tools such as oak-online-alter-table/OSC, Spider, etc have made things much easier. I’ll talk about how to achieve non-stop sharding operations.