MySQL master-Slave synchronization common exception and recovery method

MySQL master-Slave synchronization common exception and recovery method

1. General exception only need to skip one step to recover

>slave stop;

>set GLOBAL sql_slave_skip_counter = 1;

>slave start;

2. The last Bin-log log of the main library is restored when the power failure causes the master/slave to be unable to synchronize

On the main library server, Mysqlbinlog mysql-bin.xxxx > Binxxxx.txt

Tail-n 100000 binxxxx.txt > Tail-binxxxx.txt

Vim Tail-binxxxx.txt Open a tail-binxxxx.txt file to find the last Postion value

Then on the From library, change the host to the corresponding correct value

>slave stop;

>change Master to master_host= ' IP ', master_user= ' username ', master_password= ' password ', master_log_file= ' Mysql-bin.xxxx ', master_log_pos=xxxx;

>slave start;

>show slave status\g;

3. The primary key conflict, the existence of the table and other error codes such as 1062,1032,1060, can be specified in the MySQL master configuration file

Skipping this type of exception and continuing with the next SQL synchronization can also avoid many of the exception interrupts of master-slave synchronization

[Mysqld]

Slave-skip-errors = 1062,1032,1060

MySQL master-Slave synchronization common exception and recovery method