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

This article introduces the "MySQL master-slave synchronization problem and delay from the library" what is the "flashback" knowledge, in the actual case of the operation process, many people will encounter such a dilemma, then let the editor lead you to learn how to deal with these situations! I hope you can read it carefully and be able to achieve something!

Background: a by-product of toss MySQL-5.7.9; all drills and operations are based on 5.7.9, which should be no different from 5.6.2x

Phenomenon of the problem: Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g.after a power or disk failure when sync_binlog! = 1. The master may or may not have rolled back transactions that were already replica'

Cause of problem: error while start slave from library

Problem analysis:

Master-slave synchronization still depends on logs. The reason for this problem is that the information in the slave_master_info of the slave database is inconsistent with the state of the master database. When slave_IO_thread relies on the slave_master_info information of the slave database to "continue" dump binlog on the master database, the data can not be found.

The solution to the problem:

When 1.change master, specify a definite log_file and log_pos, do not make it easy to use auto_position

two。 Make sure that the current master-slave data is completely consistent (the master database is in read-only state or completely shut down), reset master on slave and master, clean up all logs, and then start a new synchronization with auto_position

Method 1 is more commonly used. After all, there is almost no chance of stopping the library.

PS: in multi-source replication, if you prompt ERROR 3079 (HY000): Multiple channels exist on the slave. Please provide channel name as an argument. Need to use reset slave all to clear the information of multiple channel

-extension of the problem-

When it comes to the slave library, you can use a separate delay to achieve flashback. If there is a problem, if there is less data involved, you can directly parse the binlog to recover (ROW)

If there is a lot of data involved (for example, update http://blog.itpub.net/29510932/viewspace-1962834/ without where), it will take a lot of time to rebuild the backup database by backup.

Extend: if there is a delay from the library, how to perform "flashback" / data recovery?

Analyze & capture:

The main premise is that the delay slave database has not yet executed the wrong statement, so there is correct data on the delay slave library, so you can stop the slave_SQL_thread immediately, then control the slave_SQL_thread execution to a specific pos, and then restore it.

Exercise:

Construct a table for testing

Click (here) to collapse or open

CREATE TABLE `student` (

`sid` bigint (20) NOT NULL

`sname` varchar (10) DEFAULT NULL

`col1` int (11) DEFAULT NULL

`col2` bigint (20) NOT NULL

`time`datetime NOT NULL DEFAULT '2015-11-11 00 purl 0000'

PRIMARY KEY (`sid`)

KEY `idx_c1_ c2` (`col1`, `col2`)

KEY `col1` (`col1`, `col2`, `sid`)

KEY `idx_ time` (`time`)

KEY `idx_ sname` (`sname`)

KEY `idx_ col2` (`col2`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Data:

Click (here) to collapse or open

INSERT INTO `student` VALUES (10pics 2015-11-11 00 purge 01'), (10001-11-01 0010-01'), (10002-- 1000: 00-01'), (10003-11-09-00-01'), (10004-11-11-12-00-01'), (10004-11-12-0015-11-12-0014-01'). (2015-11-02 00 purl 01'), (10006-11-02 00-012-02), (10006-11-07-00-00), (10007-- 11-02-00), (10007-11-02-02), (10006-11-02-02), (10006-11-07-00), (10007-11-02), (10006-11-02-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02), (10006-11-02-02), (1000

Simulate deferred slave libraries by closing a slave SQL_thread, (pull binlog from the master library, but the contents of these binlog are not being reproduced from the slave library, similar to keeping the state that it was X minutes ago)

Delay the test data from the library:

Operate on the main library, and the result after the correct statement is executed:

Wrong statement-- update without where

If the problem is found in time, and these statements are not repeated in the slave library, then stop the SQL_thread of the slave library in time.

It can be changed into a situation similar to the test environment. Looking at the status of the slave library, you can see that the new binlog has been received, but these operations are not repeated from the library.

Go back to the main library and use mysqlbinlog to parse the log

You can see that the transaction of 535 is the problematic transaction, so you can specify this transaction as the termination transaction from the library.

Enter START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS = 'dfe44b6e-940a-11e5-89a6-005056a94f05pur535' at the command line

Specify that SQL_THREAD executes transactions before 535, and then SQL_THREAD stops automatically. Take a look at slave's status.

You can see that after slave finished executing 533 and 534, it did not execute 535, and then stopped. Looking at the table data from the slave library, you can find that the data is just before the wrong statement.

Then it's easier to export the table data and restore it on the main library.

-extra nagging-

In reality, the situation is always much more complicated. if the business is busy, if other businesses are operating on this table after this error statement, if you only do the above steps, there will be a loss of transactions / business operations. in this case, you should be prepared to toss this delay from the library.

New problem & requirement: although the wrong statement has been executed, there are still business-related SQL generated in the following, and they are all very important and cannot be lost.

The solution:

Until the previous step, just let the delay stop before the wrong transaction from the library. In order not to lose the subsequent operation, you need to generate an empty transaction on the delay from the library, skip the problematic 535 transaction, and then remove the delay from the library.

When you catch up with the main library, the data in this table will be the complete data.

"MySQL master-slave synchronization problem and delay slave library" what is flashback "content is introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.