Mysql – Automatic MySQL Replication Fail-over Strategy

MySQLmysql-5.0mysql-5.1mysql-5.5replication

I want to know that if replication is down due to some reason as if master is down then what strategy should be followed so that the replication is up instantly without manually intervening the process , i.e if replication is down when no DBA is available then how the automatic fail-over will take place.

Best Answer

Believe it or not, I just answered a question like this 5 days ago: MySQL Replication and High Availability

From that answer I provided, I advise against automatic failover with pure MySQL Replication unless your specifically script it to check for replication lag or totally unavailability of the old master (STONITH)

As an alternative, just setup a DRBD pair and run mysql on the DRBD Primary using HeartBeat to detect the DRBD Primary's unavailability.

Once a real failover happens, there must be manually intervention to setup the failover in the other direction.

Related Solutions

Mysql – Problem with MySQL Master/Slave reboot when in replication

Step 01) STOP SLAVE; on all Slaves
Step 02) service mysql stop on the Master
Step 03) Reboot Master
Step 04) For each Slave
- Reboot each Slave
- If /etc/my.cnf has skip-slave-start, then run START SLAVE;
Step 05) Check that each Slave is fully running

Run the following on the command line

SLAVETHREADCOUNT=`mysql -u... -p... -AN -e"SHOW PROCESSLIST" | grep -c "system user"`
echo ${SLAVETHREADCOUNT}

If SLAVETHREADCOUNT is 2, replication is fully operational.

Give it a Try !!!

Mysql – Restoring MySQL database with db dump and binlogs

They are two things that can be good for you in this instance

GOOD THING #1 : You can convert the two MyISAM tables to InnoDB
GOOD THING #2 : The two MyISAM tables are never changing

If either one of these good things apply, then I have good news for you

STEP01) Zap All Binary Logs on the Master (OPTIONAL)

Run this command on the Master:

mysql> RESET MASTER;

If this part scares you, you could either skip it or make a copy of the binary logs before doing it

STEP02) Create the mysqldump as follows:

echo "STOP SLAVE;" > MySQLSlaveReload.sql
mysqldump -v --master-data=1 --single-transaction --routines ....  >> MySQLSlaveReload.sql
echo "START SLAVE;" >> MySQLSlaveReload.sql

STEP03) Move the `MySQLSlaveReload.sql` to the Slave

STEP04) Load `MySQLSlaveReload.sql` on the Slave

On the Slave, load the script as follows:

mysql -u... -p... -A < MySQLSlaveReload.sql

The script will stop the slave, load the data, and start the slave. What about the log file and position? Before you do STEP02, look at line 22 of MySQLSlaveReload.sql

head -22 MySQLSlaveReload.sql | tail -1

The mysqldump option --master-data=1 recorded the log file and position as of the start of the mysqldump on line 22.