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

This article mainly shows you "mysql from the library server downmachine error Could not parse relay log event entry how to do", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "mysql from the library server downmachine error Could not parse relay log event entry how to do" this article.

Environment introduction:

Recently, there is always something wrong with the website, because the play service is always running and dying, so the manager tried to run play on my mysql servers (because the resources of these two servers are very free), but unexpectedly, after only half a day, the server ran out of 128GB of memory, the server could not be used properly, any command was reported wrong, unable to allocate memory, reboot was not allowed, I had to go to the computer room and force it off. No, no, no.

I have one or two here, the mysql copied by the master:

192.10.0.143

192.10.0.144

Vip:192.10.0.145 is mapped through keepalived, and vip is currently on 144s.

The restart is 143.

After startup, the mysql service is started successfully, but the master-slave status error is reported. The status of the sql process is NO, as follows:

MariaDB [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.144

Master_User: info_syncer

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000823

Read_Master_Log_Pos: 60049919

Relay_Log_File: mysql-relay-bin.000047

Relay_Log_Pos: 268334387

Relay_Master_Log_File: mysql-bin.000822

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: 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: 268334100

Relay_Log_Space: 535066509

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: 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.

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

1 row in set (0.00 sec)

ERROR: No query specified

Reason for reporting an error:

As can be seen from the red characters above, due to the abnormal shutdown of the slave library, the binary log of the received master library crashed, which led to the corruption of the relay log of the slave library and the inability of the sql process to read it, resulting in the sql process status of the slave library being: NO.

Problem solving:

MariaDB [(none)] > stop slave

Query OK, 0 rows affected (0.00 sec)

Solution 1:

Found, the first line records the name of the currently executing log-relay file

Find the next file of the file

To view the file using mysqlbinlog, there is information such as Rotate to log-bin.000004 pos: 4 on the line # 98, which is where slave is currently stopped, or

Reassign the synchronization location on the slave and re-execute:

Change master to

Master_host='1.1.1.1'

Master_user='repl'

Master_password='111111'

Master_port=3306

Master_log_file='log-bin.000004'

Master_log_pos=4

Then start slave

Start slave

Solution 2:

After stop slave, re-reset slave

Check the slave status and it is normal.

What exactly did reset slave do?

RESET SLAVE

The official explanation is as follows

1) RESET SLAVE makes the slave forget its replication position in the master's binary log. This statement is meant to be used for a clean start: It clears the master info and relay log info repositories, deletes all the relay log files, and starts a new relay log file. It also resets to 0 the replication delay specified with the MASTER_DELAY option to CHANGE MASTER TO. To use RESET SLAVE, the slave replication threads must be stopped (use STOP SLAVE if necessary).

2) RESET SLAVE does not change any replication connection parameters such as master host, master port, master user, or master password, which are retained in memory. This means that START SLAVE can be issued without requiring a CHANGE MASTER TO statement following

Reset slave

In fact, it simply deletes the master.info and relay-log.info files, deletes all the relay log, and then regenerates a new relay log, even though there is still a SQL in the relay log that has not been apply by the SQL thread.

But RESET SLAVE has a problem. Although it deletes the above files, the change master information in memory is not deleted. At this time, you can execute start slave directly, but because you delete master.info and relay-log.info, it will accept the main binlog from scratch and apply it. Note: when we talk about starting from scratch here, we mean that when reset, the binlog of the Lord is being accepted, and the binlog is re-accepted. If SQL thread executes stop slave and reset slave in the process of copying temporary tables, the replicated temporary tables will be deleted.

Digression: what did reset master do?

1. Reset master will delete all binlog files recorded in the log index file, and the starting value for creating a new log file starts at 000001.

2. Reset master cannot be used in a master library that has any slave running master-slave relationships. Because the reset master command is not supported when slave is running, reset master records the binlog of master from 000001, and the master log recorded by slave is the latest binlog of the master library when reset master, and the slave library will report an error with the specified binlog file that cannot be found.

Continue to solve the problem:

When the master and slave status is normal, check the alarm log and find that the error is reported

Alarm log error: table crashed, repaire is required

ERROR] log.logs: 1 client is using or hasn't closed the table properly

170310 11:54:14 [ERROR] mysqld: Table'. / log/oprlogs' is marked as crashed and should be repaired

170310 11:54:14 [Warning] Checking table:'. / log/oprlogs'

170310 11:54:14 [ERROR] log.oprlogs: 1 client is using or hasn't closed the table properly

170310 11:54:14 [ERROR] log.oprlogs: Size of datafile is: 1656831165 Should be: 1656830670

170310 11:54:47 [ERROR] log.oprlogs: Found 495 deleted space. Should be 0

170310 11:54:47 [ERROR] log.oprlogs: Found 15 deleted blocks Should be: 0

170310 11:54:47 [ERROR] log.oprlogs: Found 50207005 key parts. Should be: 50206990

170310 11:54:47 [ERROR] mysqld: Table'. / log/history' is marked as crashed and should be repaired

170310 11:54:47 [Warning] Checking table:'. / log/history'

170310 11:54:47 [ERROR] log.history: 1 client is using or hasn't closed the table properly

Direct repair table table_name is fine. To repair the tables marked as crashed that appear in the log

MariaDB [(none)] > repair table log.logs

Here's how to fix table: organize it from the network.

In most cases, database corruption only means that the index file is corrupted, and the real data is rarely corrupted. The repair of most forms of database corruption is fairly simple.

Like the previous check, there are three ways to fix it.

The method described below is valid only for tables in MyISAM format. Other types of damage need to be recovered from the backup.

Mysql service must be in running state.

2, command mysqlcheck (the mysql service can be running).

3, command myisamchk (the mysql service must be stopped, or the table being operated on is inactive).

When repairing the table, you'd better make a backup first. So you need twice as much hard disk space as the original table. Please make sure that your hard disk space is not used up before you repair it.

1 > repair with "repair table" mode

Syntax: repair table table name [option]

The options are as follows:

QUICK is the fastest when the data table has not been modified.

When EXTENDED tries to recover each data row, it will generate some junk data rows, which can be used in desperation.

USE_FRM is used when the .MYI file is missing or the header is corrupted. Use the definition of .frm to rebuild the index

In most cases, the problem can be solved simply by using "repair table tablename" without adding options. But this doesn't work when the .MYI file is missing or the header is corrupted, for example:

Mysql > REPAIR TABLE mytable

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | sports_results.mytable | repair | error | Can't find file: 'mytable.MYI' (errno: 2) | |

+-+

When the index file is missing or its header is corrupted when the repair fails, in order to use the relevant definition file to fix it, you need to use the USE_FRM option. For example:

Mysql > REPAIR TABLE mytable USE_FRM

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | sports_results.mytable | repair | warning | Number of rows changed from 0 to 2 |

| | sports_results.mytable | repair | status | OK | |

+-+

We can see the output message "ok" of the Msg_test table item, and the table name has successfully repaired the damaged table.

2 > use the mysql built-in command mysqlcheck to repair

When the mysql service is running, it can also be fixed with the mysql built-in command mysqlcheck.

Syntax: mysqlcheck-r database name table name-uuser-ppass

% mysqlcheck-r sports_results mytable-uuser-ppass

Sports_results.mytable OK

Multiple tables can be repaired at once with mysqlcheck. Just list the corresponding table name after the database name (separated by a space). Or without the table name after the database name, all tables in the database will be repaired, for example:

% mysqlcheck-r sports_results mytable events-uuser-ppass

Sports_results.mytable OK

Sports_results.events OK

% mysqlcheck-r sports_results-uuser-ppass

Sports_results.mytable OK

Sports_results.events OK

3 > repair with myisamchk

In this way, the mysql service must be stopped, or the table being operated on is inactive (the option skip-external-locking is not used). Be sure to define the path under the path of the related .MYI file or define its path yourself.

Syntax: myisamchk [option] [table name]

Here are the options and descriptions

-backup,-B back up the relevant tables before repair

-correct-checksum corrective checksum

-data-file-length=#,-D # specifies the maximum length of the data file when rebuilding the table

-extend-check,-e attempts to recover each data row, which will generate some junk data rows, which can be used in desperation.

-force,-f overwrite a .TMD file with the same file name.

Keys-used=#,-k # specifies the keys used to speed up processing, with each binary bit representing an key. The first key is 0

-recover,-r is the most commonly used option through which most of the damage can be repaired. If your memory is large enough, you can increase the value of the parameter sort_buffer_size to speed up recovery. However, this approach does not work when it comes to tables where unique keys are not unique due to corruption.

-safe-recover-o is the most thorough repair method, but it is slower than the-r method and is usually used after the-r repair fails. In this way, all rows are read out and the index is rebuilt on a behavioral basis. Its hard disk space requirement is slightly smaller than the-r mode because it does not create a classification cache. You can increase the value of key_buffer_size to speed up the repair.

-sort-recover,-n mysql uses other classes to classify the index, although the result is that temporary files can be very large

-character-sets-dir=... A directory that contains character set settings

-set-character-set=name defines a new character set for the index

-tmpdir=path,-t if you don't want to use the value of the environment variable TMPDIR, you can customize the location of the temporary file.

-quick,-Q the fastest repair method, used when the data file has not been modified, when there are multiple keys, the second-Q will modify the data file

-unpack,-u unlock the files packaged by myisampack

An example of myisamchk application

% myisamchk-r mytable

-recovering (with keycache) MyISAM-table 'mytable.MYI'

An extension out of the question.

REPAIR TABLE `table_ name` repair table

OPTIMIZE TABLE `table_ name` optimization table

The REPAIR TABLE statement is written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is used.

REPAIR TABLE is used to repair corrupted tables.

OPTIMIZE TABLE is used to reclaim idle database space. When rows of data on a table are deleted, the disk space occupied is not immediately reclaimed. After using the OPTIMIZE TABLE command, these spaces are reclaimed and rows on disk are rearranged (note: on disk, not database). Most of the time, you don't need to run OPTIMIZE TABLE, you just need to perform table optimization operations on a regular basis (weekly or monthly) after batch deletion of rows, only for those specific tables.

Log location and log number from the new chang

Whether it is the master-slave replication of the new building, or

Lock the data before copying, and then obtain the relevant log information (FILE & POSITION):

Mysql > FLUSH TABLES WITH READ LOCK; lock table. When new master and slave are added, take care of consistency

MariaDB [log] > SHOW MASTER STATUS

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000825 | 287366341 | | mysql,information_schema |

+-+

1 row in set (0.00 sec)

Next, when copying the data file, you can copy it directly if it is a MyISAM table type; if it is an InnoDB table type, be sure to stop the MySQL service before copying, otherwise the copy file may not be available. Copy the copied data file directly to the data directory of the slave server.

Finally, you need to specify the log information again:

Mysql > CHANGE MASTER TO

MASTER_HOST= ""

MASTER_USER= ""

MASTER_PASSWORD= ""

MASTER_LOG_FILE= ""

MASTER_LOG_POS=

Copying data files directly on the primary server is fast, but you need to lock the table or stop the service, which will affect the online service. If you already have a slave server, you can use the old slave server as the master to clone the new slave server:

First query the log information on the old slave server:

Mysql > SHOW SLAVE STATUS

What we need is one of the Relay_Master_Log_File & Exec_Master_Log_Pos.

Then get the data from the old slave server according to the previous method, you can first stop slave, then copy the data and restore it on the new slave server.

Then set the log information on the new slave server:

Mysql > CHANGE MASTER TO

MASTER_HOST= ""

MASTER_USER= ""

MASTER_PASSWORD= ""

MASTER_LOG_FILE= ""

MASTER_LOG_POS=

Either way, finally remember to start replication on the slave server and check to see if it is working properly:

Mysql > START SLAVE

Mysql > SHOW SLAVE STATUS

Note:

MariaDB [(none)] > show slave status\ G

The Master_Log_File and Read_Master_Log_Pos shown only correspond to the master

File number and location of show master status\ G;

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.143

Master_User: info_syncer

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000019

Read_Master_Log_Pos: 1038

The above is all the contents of this article "what to do if mysql reports an error from the library server downmachine Could not parse relay log event entry". 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.