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.
<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;
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;
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.
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.
<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
<WRAP left round help 20%> Do this in the slave </WRAP>
bzip2 -dc full-backup.sql.bz2 | mysql -h SLAVE_COMPUTER -u root -p
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';
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;