Mysql – How to actually use thesql slave as soon the master is failover or got burnt

disaster-recoveryMySQLreplication

I have MySQL master-slave replication that works fine; I googled the whole net and MySQL site to find the standard procedure to make use of the replication but found nothing. It is as if admins are happy to have replication on, but when the time comes to perform a disaster recovery no initial plan has been put into practice and publicly shared.

What i want to know is how to make the slave machine replace the master one in case the later got damaged, burnt, etc. I guess the slave machine should take the network address of the master but what else should be done? For instance, changing MySQL user permissions or running some commands! Please include references if any.

Best Answer

For a DR solution you most likely want a semi-manual process. That is, you need to decide the disaster validates a full DR failover and it's not just a small network blipp and you're stuck with days of fail-back work.

To switch a MySQL slave to a master you just issue a few commands in mysql, Mysql details can be found here.

I'd strongly suggest scripting the whole process (and test it!) and add whatever other commands you may want to run on the box to do the DR failover (restart the web tier or whatever else is needed). We use func on a management server to run failover commands all the tiers of our applications.

When referring to disaster recovery I'm talking about site failure. For a cluster failover things should probably be automatic and much simpler.

Related Solutions

Mysql – Sporadic crash of master-slave MySQL replication process

Double check that your character sets on the databases and connections are what you expect them to be - your replication choking on a statement with a non-ASCII character is immediately suspicious.

Look at the master's binary log to see how the statement it logged is different from the statement that the replica is trying to execute - that will offer clues to the problem.

Sadly, you should also make sure that the versions of the server for both the master and slave are the same. I recently encountered a weird instance where we had bad SQL trying to update a non-NULL numeric column to NULL. In the version that the master was running, MySQL silently converted it to 0, but in the version that the replica was running, it was an error.

Also, instead of doing CHANGE MASTER to fix the replica, you should be doing

STOP SLAVE;
[correct and execute whatever statement is the problem]
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

http://dev.mysql.com/doc/refman/5.1/en/set-global-sql-slave-skip-counter.html

MySQL Incremental backups with Master-Slave replication

Well a packed question, let me try to answer all the issues you raised there.

Binary backups vs dumps

Right now you're using mysqldump to do full backups of your database, that's a good practice indeed but it can get a bit endearing when dealing with huge MySQL installations. Binary backups are good point in time backups, but you need two parts, one full binary backup (which needs to be consistent) and the binary diff logs. Of course the time for recovery for one an the other is drastically different too, it all depends as said on the size of your database and your turnover times.

How flushing the binlogs will affect replication

It won't affect replication if you flush the logs always after they've been transmitted to the slave, otherwise you'll run into some nasty issues due to the slave not being able to request the exact block of the binlog that it needs

Best Answer

Related Solutions

Mysql – Sporadic crash of master-slave MySQL replication process

MySQL Incremental backups with Master-Slave replication

Related Topic