Wednesday, 7 November 2012

MySQL:Fixing Mysql Replication on RHEL/CentOS


Fixing Mysql Replication on RHEL/CentOS


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.

No comments:

Post a Comment