Bit Matrix

I have noticed strange behavior with MySQL version 5 and replication. I manage several MySQL database servers. The setup is simple, one master and two replication slaves acting as backups. The master server version is 5.0.22-log and the two slaves are 5.0.51a and 5.0.45.

When the replication gets interrupted (either via a clean server shutdown or a forced one), replication refuses to restart citing the following error:

ERROR 1201: Could not initialize master info structure, more error messages can be found in the MySQL error log

The master.info file is present in the data directory and has all the required information in it but MySQL refuses to use it. Searching on the net did not yield anything definitive.

The simplest way to restart replication from this point is to re-enter the master.info data manually. Do the following:

1. STOP SLAVE
2. RESET SLAVE
3. CHANGE MASTER TO MASTER_HOST=’your_master’, MASTER_USER=’your_login’, MASTER_PASSWORD=’your_password’, MASTER_LOG_FILE=’log_file’, MASTER_LOG_POS=log_position
4. START SLAVE

At this point you want to execute ‘SHOW SLAVE STATUS’ and check to see if everything is running. Most times MySQL will stop since it tried to replay a duplicate record from the logs. We need to tell MySQL to skip that record and continue:

1. SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1
2. START SLAVE

That should get replication up and running again.

What is interesting to note is that, under the same circumstances, my MySQL 4 servers had no problems continuing replication. In any case, don’t forget to run regular backups of your database.

Databases, MySQL, Technical database, MySQL, replication, Technical