Another example of bitsCN.com error occurred during mysql replication.
Today, an error occurred while replicating mysql. my colleague was using navicat to operate the primary database, causing non-synchronization.
1. show slave stauts/G
Master_Log_File: mysql-bin.000027
Read_Master_Log_Pos: 604734247
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 85998
Relay_Master_Log_File: mysql-bin.000027
Slave_IO_Running: Yes
Slave_ SQL _Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error: cocould not parse relay log event entry. the possible reasons are: the master's binary log is upted (you can check this by running 'mysqlbinlog' on the binary log ), the slave's relay log is already upted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. if you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'show SLAVE status' on this slave.
Skip_Counter: 0
Exec_Master_Log_Pos: 588269801
Relay_Log_Space: 16550444
Check whether the primary binary log or relay log has an error as prompted.
1. check the primary binary log
Mysqlbinlog -- no-defaults -- start-position = 588269801 mysql-bin.000027> master. SQL
2. check the slave relay log
Mysqlbinlog -- no-defaults -- start-position = 85998 mysqld-relay-bin.000002> slave. SQL
If two logs contain errors, the-f parameter must be added if the preceding command fails to be executed.
Find the following command in master. SQL, where the Unknown event is a command that cannot be recognized for execution, and the relay_log is stuck here.
# At 588269801
#110816 14:45:50 server id 1 end_log_pos 588269874 Query thread_id = 7249444 exec_time = 0 error_code = 0
Set timestamp = 1313477150 /*! */;
BEGIN
/*! */;
# At 588269874
#110816 14:45:50 server id 1 end_log_pos 588269941
# Unknown event
# At 588269941
#110816 14:45:50 server id 1 end_log_pos 588270071
# Unknown event
# At 588270071
#110816 14:45:50 server id 1 end_log_pos 588270145 Query thread_id = 7249444 exec_time = 0 error_code = 0
Set timestamp = 1313477150 /*! */;
COMMIT
If you know the reason, skip this step.
1. stop slave;
2. change master to master_host = '192. 168.1.13 ', master_user = 'Slave', master_password = 'slavepasswd', MASTER_LOG_FILE = 'MySQL-bin.000027 ', MASTER_LOG_POS = 192;
3. start slave;
Of course, if there are multiple such Unknown event events, it will be very troublesome to do so.
You can execute master. SQL on slave first, find the value of postion in the last file, and re-set change master.
I don't know whether set global SQL _SLAVE_SKIP_COUNTER = N is effective in this case. Why didn't I try it at the beginning?
BitsCN.com