User Tools

Site Tools


mysql:recoveringsynchronization

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
mysql:recoveringsynchronization [2017/11/11 11:43] rlunaromysql:recoveringsynchronization [2022/12/02 22:02] (current) – external edit 127.0.0.1
Line 6: Line 6:
 I've decided to recover it by //the hard way//: deleting all the information in the slave and  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.  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 ===== ===== First step: change password for all clients =====
Line 13: Line 28:
 actual passwords, just issue this command and copy all the results of the table: actual passwords, just issue this command and copy all the results of the table:
  
-<WRAP center round important 60%>+<WRAP left round important 20%>
 Do this in the MASTER Do this in the MASTER
 </WRAP> </WRAP>
Line 33: Line 48:
  
 Then, change the password for all your users to a new password: this will  Then, change the password for all your users to a new password: this will 
-ban everyone from the server: +ban everyone from the server: 
 + 
 +<WRAP left round important 20%> 
 +Do this in the MASTER 
 +</WRAP>
  
 <code> <code>
Line 58: Line 77:
 again in the server (except you). In windows:  again in the server (except you). In windows: 
  
-<WRAP center round important 60%>+<WRAP left round important 20%>
 Do this in the master Do this in the master
 </WRAP> </WRAP>
Line 82: Line 101:
 </code> </code>
  
-Annotate these "File" and "position": it will be neede futher. +Annotate these "File" and "position": it will be needed futher. 
  
 ==== Fourth step: make a full backup of your database ==== ==== Fourth step: make a full backup of your database ====
 +
 +
 +<WRAP left round important 20%>
 +Do this in the master
 +</WRAP>
  
 <code> <code>
-C:\>mysqldump --add-drop-database --add-drop-table --addrop-trigger --all-databases -h localhost -u root -p | bzip2 -c > full-backup.sql.bz2+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> </code>
  
Line 97: Line 121:
  
 ==== Fifth step: recover in the slave database ==== ==== Fifth step: recover in the slave database ====
 +
 +<WRAP left round help 20%>
 +Do this in the slave
 +</WRAP>
  
 <code> <code>
Line 106: Line 134:
 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.  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> <code>
 update mysql.user set password = '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' where user = 'repl'; update mysql.user set password = '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' where user = 'repl';
Line 115: Line 149:
  
 On the slave, issue the following command:  On the slave, issue the following command: 
 +
 +<WRAP left round info 20%>
 +Do this in the slave
 +</WRAP>
  
 <code> <code>
Line 123: Line 161:
 master_port = 3306, master_port = 3306,
 master_log_file = 'you have to guess the log file name that goes here from the slave', master_log_file = 'you have to guess the log file name that goes here from the slave',
-master_log_pos = 4;  -- everybody put 4 here, I don't know why +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> </code>
  
  
mysql/recoveringsynchronization.1510396985.txt.gz · Last modified: 2022/12/02 22:02 (external edit)