Fixing Mysql Replication on RHEL/CentOS

Mysql Oh crap, replication between my media wiki server in datacenter 1 and in datacenter 2 has for some reason stopped working.

Logging into the slave server,  I was able to verify this was in fact by typing the following and looking for the two SLAVE specific lines.

mysql> show slave status \G

Slave_IO_Running: No
Slave_SQL_Running: No

So I stopped the slave with the following command. Note that this does not stop the database, rather it just stops the slave from attempting to replicate

mysql> stop slave;

Then go onto the master and get the current log file and log file position. This information is used later to sync the slave with the master.

mysql> show master status \g

mysql-bin.000004 | 90171568

Now freeze the master.

mysql> flush tables with read lock; (dont close this window)

Then in a new console on the master and dump out the database.

mysqldump –all-databases –lock-all-tables >dbdump.db

scp the dump file to the slave server and import it.

mysql < dbdump.db

Then set the master configuration on the slave server using the command below from within the database.

mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;

Then start the slave.

mysql> start slave;

Then check the slave status again and look for Yes in the two Slave lines.

mysql> show slave status \G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

In my case I was able to verify that all changes from my master wiki replicated to the slave wiki.

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s