User Tools

Site Tools


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

Do this in the slave

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:

Do this in the MASTER

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:

Do this in the MASTER

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:

Do this in the master

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 |

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

Fourth step: make a full backup of your database

Do this in the master

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

Do this in the slave

 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.

Do this in the slave

Do this in the slave

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

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

On the slave, issue the following command:

Do this in the slave

change master to
master_host = '',
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',

And start the slave:

Do this in the slave

start slave;
mysql> SET GLOBAL slow_query_log=1; 
mysql> set global general_log=1;
mysql/recoveringsynchronization.txt · Last modified: 2017/11/11 17:03 by rlunaro