supermanhamuerto.com

User Tools

Site Tools

Trace:
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:32] – 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 57: Line 76:
 Restart the server in order to make sure all the connections are released and nobody can enter  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:  again in the server (except you). In windows: 
 +
 +<WRAP left round important 20%>
 +Do this in the master
 +</WRAP>
  
 <code> <code>
Line 78: 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 93: 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 102: 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 111: 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 119: 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.1510396378.txt.gz · Last modified: 2022/12/02 22:02 (external edit)