mysql:recoveringsynchronization
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
mysql:recoveringsynchronization [2015/06/04 13:23] – [Third step: make a full backup of your database] rlunaro | mysql:recoveringsynchronization [2017/11/11 16:57] – [Sixth step: issue a change master to command in the slave] rlunaro | ||
---|---|---|---|
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 | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | 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 ===== | ===== First step: change password for all clients ===== | ||
Line 12: | Line 27: | ||
I've decided to change all the passwords, but making a backup before. To make a backup of your | 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: | actual passwords, just issue this command and copy all the results of the table: | ||
+ | |||
+ | <WRAP left round important 20%> | ||
+ | Do this in the MASTER | ||
+ | </ | ||
< | < | ||
Line 29: | 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 | ||
+ | </ | ||
< | < | ||
Line 53: | 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 | ||
+ | </ | ||
< | < | ||
Line 61: | Line 88: | ||
</ | </ | ||
- | ==== Third step: make a full backup | + | ==== Third step: take note of the last position of the master |
< | < | ||
- | C: | + | show master status |
+ | Name | ||
+ | -----------------|---------------- | ||
+ | File | ||
+ | Position | ||
+ | Binlog_Do_DB | ||
+ | Binlog_Ignore_DB | | ||
+ | Executed_Gtid_Set| | ||
+ | </ | ||
+ | |||
+ | Annotate these " | ||
+ | |||
+ | ==== Fourth step: make a full backup of your database ==== | ||
+ | |||
+ | |||
+ | <WRAP left round important 20%> | ||
+ | Do this in the master | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | C: | ||
</ | </ | ||
Line 73: | Line 120: | ||
</ | </ | ||
- | ==== Fourth | + | ==== Fifth step: recover in the slave database ==== |
+ | |||
+ | <WRAP left round help 20%> | ||
+ | Do this in the slave | ||
+ | </ | ||
< | < | ||
Line 79: | Line 130: | ||
</ | </ | ||
- | ==== Fifth step: set the password for the repl user ==== | + | ==== 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 '' | For the replication it is a common place to have a user for this task. In my case is the '' | ||
+ | <WRAP left round info 20%> | ||
+ | Do this in the slave | ||
+ | </ | ||
+ | <WRAP left round important 20%> | ||
+ | Do this in the slave | ||
+ | </ | ||
< | < | ||
update mysql.user set password = ' | update mysql.user set password = ' | ||
Line 92: | 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 | ||
+ | </ | ||
< | < | ||
Line 100: | 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; | + | master_log_pos = 4; |
+ | mysql> SET GLOBAL slow_query_log=1; | ||
+ | mysql> set global general_log=1; | ||
</ | </ | ||
mysql/recoveringsynchronization.txt · Last modified: 2022/12/02 22:02 by 127.0.0.1