Table of Contents

Recovering a lost synchronization in mysql

I have a replicated mysql database and recently I've discovered that it has lost synchronization with its slave.

I've decided to recover it by the hard way: deleting all the information in the slave and starting with a fresh copy of the master.

Zero step: stop the slave

<WRAP left round info 20%> Do this in the slave </WRAP>

mysql> stop slave;
-- disable the slow query log is necessary because 
-- in the event of a full restore, the logging tables 
-- are dropped
mysql> SET GLOBAL slow_query_log=0; 
mysql> set global general_log=0;

First step: change password for all clients

Because this database supports various clients and its very probable that some can change something, I've decided to change all the passwords, but making a backup before. To make a backup of your actual passwords, just issue this command and copy all the results of the table:

<WRAP left round important 20%> Do this in the MASTER </WRAP>

mysql> select host, user, password from mysql.user;
+------+------------+-------------------------------------------+
| host | user       | password                                  |
+------+------------+-------------------------------------------+
| %    | root       | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| %    | bugtracker | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| %    | hg4        | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| %    | mc01       | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| %    | repl       | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| %    | sonar      | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
+------+------------+-------------------------------------------+
6 rows in set (0.04 sec)

Then, change the password for all your users to a new password: this will ban everyone from the server:

<WRAP left round important 20%> Do this in the MASTER </WRAP>

SET PASSWORD FOR root = password('yyyyyy');
SET PASSWORD FOR bugtracker = password('yyyyyy');
SET PASSWORD FOR hg4 = password('yyyyyy');
SET PASSWORD FOR mc01 = password('yyyyyy');
SET PASSWORD FOR repl = password('yyyyyy');
SET PASSWORD FOR sonar = password('yyyyyy');
flush privileges;

After all the process, you can recover the old passwords by issuing the commands:

update mysql.user set password = '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' where user = 'root';
....
flush privileges;

Second step: reboot mysql server

Restart the server in order to make sure all the connections are released and nobody can enter again in the server (except you). In windows:

<WRAP left round important 20%> Do this in the master </WRAP>

C:\>net stop mysql
The MySQL service is stopping.
C:\>net start mysql
The MySQL service is starting.

Third step: take note of the last position of the master database

show master status
Name             |Value
-----------------|----------------
File             |mysql-bin.000098
Position         |222409
Binlog_Do_DB     |
Binlog_Ignore_DB |
Executed_Gtid_Set|

Annotate these “File” and “position”: it will be needed futher.

Fourth step: make a full backup of your database

<WRAP left round important 20%> Do this in the master </WRAP>

C:\>mysqldump --add-drop-database --add-drop-table --add-drop-trigger --all-databases -h localhost -u root -p | bzip2 -c > full-backup.sql.bz2

It is not a bad idea to double-check the copy process by calculating the md5sum:

md5sum full-backup.sql.bz2 > full-backup.sql.bz2.md5

Fifth step: recover in the slave database

<WRAP left round help 20%> Do this in the slave </WRAP>

 bzip2 -dc full-backup.sql.bz2 | mysql -h SLAVE_COMPUTER -u root -p 

Sixth step: set the password for the repl user

For the replication it is a common place to have a user for this task. In my case is the repl user. Because I've changed the password in the first steps, I have to set the original password back now.

<WRAP left round info 20%> Do this in the slave </WRAP> <WRAP left round important 20%> Do this in the slave </WRAP>

update mysql.user set password = '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' where user = 'repl';

Sixth step: issue a change master to command in the slave

https://dev.mysql.com/doc/refman/5.1/en/change-master-to.html

On the slave, issue the following command:

<WRAP left round info 20%> Do this in the slave </WRAP>

change master to
master_host = 'MASTERHOST.example.com',
master_user = 'repl',
master_password = 'put-the-password-here',
master_port = 3306,
master_log_file = 'you have to guess the log file name that goes here from the slave',
master_log_pos = ENTER THE POSITION YOU ANNOTATE IN THE LAST COMMAND;  

And start the slave:

<WRAP left round info 20%> Do this in the slave </WRAP>

start slave;
mysql> SET GLOBAL slow_query_log=1; 
mysql> set global general_log=1;