MySQL5.1 the Relay log read failure error occurs during master-slave synchronization. Solution: mysql5.1relay

MySQL5.1 the Relay log read failure error occurs during master-slave synchronization. Solution: mysql5.1relay

As we all know, Replication of MySQL5.1 is poor. Every version update of MySQL shows a lot about synchronization. However, MySQL 5.1 has outstanding performance. Therefore, we cannot resist the temptation to use MySQL 5.1. Therefore, some bugs are also frequently encountered. For example:
Copy codeThe Code is as follows:
Mysql> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.118
Master_User: repl_wu
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.005121
Read_Master_Log_Pos: 64337286
Relay_Log_File: relay-bin.003995
Relay_Log_Pos: 18446697137031827760
Relay_Master_Log_File: mysql-bin.005121
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.: 1594
Last_Error: Relay log read failure: 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: 4
Relay_Log_Space: 64337901
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 1594
Last_ SQL _Error: Relay log read failure: 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.
1 row in set (0.00 sec)

From the above we can see that there is a problem with the relay daily value or the daily value on the Master.

First, if the relay day value breaks down, you only need to find the synchronization time point and then re-synchronize it so that a new relay day value can be created. If the daily value on the Master node is broken, it will be troublesome.

From experience, this is a problem with the relay daily value. Solution:

You need to find the synchronization point.

The value is: Master_Log_File: mysql-bin.005121, Relay_Master_Log_File: mysql-bin.005121 to Relay_Master_Log_File prevail, Master_Log_File for reference.

Execution time of daily values:
Copy codeThe Code is as follows: Exec_Master_Log_Pos: 4

Now you can:
Copy codeThe Code is as follows:
Mysql> stop slave;
 
Mysql> change master to Master_Log_File = 'mysql-bin.005121 ', Master_Log_Pos = 4;

Mysql> start slave;
 
Mysql> show slave status \ G;

Confirm.

Suggestion:

If you are using versions below MySQL-5.1.36, please upgrade to MySQL-5.1.40 & MySQL-5.1.37sp1 as soon as possible

How does mysql implement master-slave synchronization database backup?

1. master server:
# Master start
Log-bin = "d:/log/mysql/mysql_log_bin"
Server-id = 1
# Master end
2. slave server:
# Slave start
Log-bin = "D:/log/mysql2/log-bin.log"
Relay_log = "D:/log/mysql2/relay-log-bin"
# Slave id, different from the host id
Server-id = 2
# Host ip address, used by the slave machine to connect to the host
# Master-host = localhost
# Host port
# Master-ports = 3300
# The account you just created to copy host data from the slave machine
# Master-user = slave
# The new password for copying host data from the slave
# Master-password = 654321
# Retry Interval: 10 seconds
# Master-connect-retry = 10
# Databases to be synchronized
# Replicate-do-db = test
# Enable slave database logs for chained Replication
Log-slave-updates
# Whether the slave database is read-only. 0 indicates read/write, and 1 indicates read-only.
Read-only = 1

# Copy only a table
# Replicate-do-table = tablename
# Copy only some tables (matching characters available)
# Replicate-wild-do-table = tablename %
# Only copy a database
# Replicate-do-db = dbname
# Do not copy a table
# Replicate-ignore-table = tablename
# Do not copy some tables
# Replicate-wild-ignore-table = tablename %
# Do not copy a database
# Replicate-ignore-db = dbname
# Slave end
3. Set the MASTER server's change master statement for the slave server
Note: 1. You must create a user on the master server that can execute replication.
2. the user name can be remotely logged on to the master server.
3. Enable MySQL log-bin.
Reference: blog.163.com/..31959/

How to check the Master/Slave latency of the MySQL database?

How can I determine the Master/Slave latency of a MySQL database? This article introduces two methods for judging: 1. Seconds_Behind_Master vs 2. mk-heartbeat. Next we will introduce these methods separately. In daily work, for MySQL master-slave replication check, on the one hand, we need to ensure that the overall structure of the replication is normal, on the other hand, we need to check whether the master-slave data is consistent. For the former, we can monitor whether the replication thread is working normally and whether the master-slave latency is within the tolerable range. For the latter, we can verify whether the md5 codes of the data in the master-slave table are consistent, to ensure data consistency, you can use the mk-table-checksum tool in the Maatkit toolkit to check. Method 1: monitor the value of the Seconds_Behind_Master parameter output by the show slave status \ G command to determine whether there is a master-slave latency. There are several values: NULL-indicates that either io_thread or SQL _thread has a fault, that is, the Running status of the thread is No, rather than Yes. 0-the value is zero, which is a situation we are eager to see. It indicates that the master-slave replication is good and lag does not exist. Positive Value: The Master/Slave database has a latency. A larger number indicates that the slave database lags behind the master database. Negative value-rarely seen. I have heard from some senior DBAs. In fact, this is a BUG value. This parameter does not support negative values, that is, it should not appear. Show slave status \ G. The output result of this command is very rich and many meaningful parameters are provided for our monitoring. For example, Slave_IO_Running can be used as the monitoring item of io_thread, yes indicates that the io_thread and the master database are connected normally and can be copied. If No, the communication with the master database is abnormal. In most cases, it is caused by the master-slave network; slave_ SQL _Running: this parameter indicates whether SQL _thread is normal, specifically, whether the statement is executed successfully. If the primary key is repeated or a table does not exist. Next we will talk about the focus of today's Seconds_Behind_Master. This value is used as an indicator to judge the master-slave latency. How does it get this value? At the same time, why has it been questioned by many people? Seconds_Behind_Master compares the timestamp of the event executed by SQL _thread with the timestamp (ts) of the event copied by io_thread, and obtains such a difference. We all know that the relay-log and the bin-log of the master database have exactly the same content. When an SQL statement is recorded, the current ts will be recorded. Therefore, the reference value is from binlog, in fact, the master and slave do not need to be synchronized with NTP, that is, the master and slave clock do not need to be consistent. You will also find that, in fact, io_thread actually occurs between io_thread and SQL _thread, and io_thread is actually associated with the master database, so the problem arises, when the I/O load of the master database is large or the network is congested, io_thread cannot copy the binlog in time (without interruption or replication), while SQL _thread can keep up with the io_thread script, in this case, the value of Seconds_Behind_Master is 0, that is, we think there is no latency, but actually it is not, you know. This is why we need to criticize the use of this parameter to monitor whether the database is delayed, but this value is not always inaccurate. If the io_thread and master network are good, this value is also very valuable. Previously, the Seconds_Behind_Master parameter has a negative value. We already know that this value is the difference between the latest and new ts executed by io_thread and the ts executed by SQL _thread. The former is always greater than the latter, the only Ken is that the ts of an event has an error, which is smaller than the previous one. In this case ...... remaining full text>