Shulou(Shulou.com)05/31 Report--

Editor to share with you how to do mysql replication errors, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Mysql replication went wrong again. Colleagues are using navicat to manipulate the master, causing out of sync.

1. Show slave stauts\ G

Master_Log_File:-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: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (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

Follow the prompts to check if there is an error in the main binary log or relay log.

1. Check the primary binary log

Mysqlbinlog-- no-defaults-- start-position=588269801 mysql-bin.000027 > master.sql

two。 Check from relay log

Mysqlbinlog-- no-defaults-- start-position=85998 mysqld-relay-bin.000002 > slave.sql

If there is an error in the two log, the above command must add the-f parameter if the execution is unsuccessful.

Then find the following command in master.sql, where Unknown event is the command that cannot be recognized and executed, 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

It's easy to know the reason, just skip it.

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=588270071

3. Start slave

Of course, if there are multiple such Unknown event events, it will be troublesome to do so.

You can first execute master.sql on slave, and then find the value of the last postion in the file. It is also feasible to reset change master.

I don't know if SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N; will be effective for this situation. Why didn't I think of giving it a try at first?

The above is all the contents of the article "what to do with mysql replication errors". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.