====== 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>

<code>
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;
</code>

===== 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>

<code>
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)
</code>

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>

<code>
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;
</code>

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

<code>
update mysql.user set password = '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' where user = 'root';
....
flush privileges;
</code>

==== 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>

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

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

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

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>

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

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

<code>
md5sum full-backup.sql.bz2 > full-backup.sql.bz2.md5
</code>

==== Fifth step: recover in the slave database ====

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

<code>
 bzip2 -dc full-backup.sql.bz2 | mysql -h SLAVE_COMPUTER -u root -p 
</code>

==== 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>
<code>
update mysql.user set password = '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' where user = 'repl';
</code>

==== 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>

<code>
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;  
</code>

And start the slave: 

<WRAP left round info 20%>
Do this in the slave
</WRAP>
<code>
start slave;
mysql> SET GLOBAL slow_query_log=1; 
mysql> set global general_log=1;
</code>