Today, when testing MySQL sync, check the database to find a MySQL slave not synchronized with the host to view the status of slave:
Mysql> Show Slave Status\g
Slave_io_running:yes
Slave_sql_running:no
last_errno:1062
....
Seconds_behind_master:null
Reason:
1. The program may be written on the slave
2. It may also be the slave machine after the transaction rollback caused.
Solution I:
1. First stop slave service: Slave stop
2. View host status on the primary server:
Record the values for file and position.
Mysql> Show master status;
+------------------+-----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+-----------+--------------+------------------+
| mysql-bin.000020 | 135617781 | | |
+------------------+-----------+--------------+------------------+
1 row in Set (0.00 sec)
3. Perform a manual synchronization on the slave server:
Copy Code code as follows:
Mysql> Change Master to
> master_host= ' master_ip ',
> master_user= ' user ',
> master_password= ' pwd ',
> master_port=3307,
> master_log_file= ' mysql-bin.000020 ',
> master_log_pos=135617781;
1 row in Set (0.00 sec)
mysql> slave start;
1 row in Set (0.00 sec)
View slave status Discovery again:
Slave_io_running:yes
Slave_sql_running:yes
...
seconds_behind_master:0
Solution II:
mysql> slave stop;
Mysql> set GLOBAL sql_slave_skip_counter=1;
mysql> slave start;
Own experience: Method one is mandatory from a certain point of synchronization, there will be a part of the data loss is not synchronized, subsequent primary server delete record synchronization will also have some error messages, will not affect the use. Method two does not necessarily have an effect.
