MySQL 5.7 attempts to speed up recovery based on replication thread Sql_thread

1. MySQL Data Recovery Common approach

There are generally three ways to recover from MySQL:

1. Officially recommended based on fully prepared +binlog, it is common practice to restore the most recent full-time, then through Mysqlbiinlog--start-position--stop-position binlog.000xxx | Mysql-uroot-p Xxx-s database restores to the target databases

2. Based on master-slave synchronous recovery of data, it is common practice to restore the most recent full-time, and then restore the instance to do slave mount to the existing master above, through the start slave sql_thread until Master_log_pos Revert to a POS before the failure.

Now try the third recovery method, and restore the data to slave via the binlog above the original main library.

Processing ideas:

Because the nature of Relaylog and Binlog is actually the same, it is possible to use MySQL's own sql_thread to increment Binlog

1) Reinitialize an instance to restore the full amount of backup files.
2) Find the first Binlog file of the position, and all the remaining binlog.
3) disguise the Binlog as Relaylog and restore through the SQL thread increment.

Application Scenarios:

1. The most recent full-standby fault location is far away, and the recovery time is too slow in either of these ways

2. Dual-master keepalived cluster, because Keepalived does not have a log completion mechanism like MHA, the failure is likely to have data loss, in case of synchronization has serious replication delay failover to slave, so that the data is inconsistent, need to do log completion

2. Experimental steps

1. Established based on master-slave synchronization (here The experiment is based on the traditional POS, in fact Gtid is also feasible)

M1:

[Email Protected]:mysql3307.sock [(none)]>select * from restore.t1;+----+------+| ID | C1   |+----+------+|  1 | 1    | |  2 | 3    | |  3 | 2    | |  4 | 3    | |  5 | 6    | |  6 | 7    | |  7 | 9    | | 10 | NULL | | 11 |   |+----+------+9 rows in Set (0.00 sec)

M2: (slave)

[Email Protected]:mysql3307.sock [(none)]>select * from restore.t1;+----+------+| ID | C1   |+----+------+|  1 | 1    | |  2 | 3    | |  3 | 2    | |  4 | 3    | |  5 | 6    | |  6 | 7    | |  7 | 9    | | 10 | NULL | | 11 |   |+----+------+9 rows in Set (0.00 sec)

　　

[Email protected]:mysql3307.sock [restore]>show slave status\g*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos              T:M1 Master_user:repl master_port:3307 connect_retry:60                master_log_file:3307-binlog.000002 read_master_log_pos:154 relay_log_file:m2-relay-bin.000004 relay_log_pos:371 relay_master_log_file:3307-binlog.000002Slave_io_running:yes Slave_sql_running:yes              Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table : replicate_wild_do_table:replicate_wild_ignore_table:last_errno:0 last_er ror:skip_counter:0 exec_master_log_pos:154 relay_log_space:624 Unt           Il_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:0master_ssl_verify_server_cert:no last_io_errno:0 last_io_error:last_sql_errno:0 last_sql_error:replicate_ignore_server_id s:master_server_id:13307 master_uuid:afeab8d6-b871-11e7-9b2a-005056b643b3 Mas Ter_info_file:/data/mysQl/3307/data/master.info sql_delay:0 sql_remaining_delay:null slave_sql_running_state: Slave have read all relay log;      Waiting for more updates master_retry_count:86400 Master_bind:last_io_error_timestamp: Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set : executed_gtid_set:auto_position:0 Replicate_rewrite_db:channel_n Ame:master_tls_version:1 row in Set (0.00 sec)

Record the Relay-log information at this time slave

[Email protected] data]# more M2-relay-bin.index/m2-relay-bin.000003./m2-relay-bin.000004[[email protected] data]# More Relay-log.info 7./m2-relay-bin.0000043713307-binlog.000002154001

2. Analog data is not synchronized using Sysbench

[Email protected] logs]# mysqladmin Create Sbtest

[Email protected] sysbench]# sysbench--db-driver=mysql--mysql-host=m1--mysql-port=3307--mysql-user=sbtest-- mysql-password= ' sbtest '/usr/share/sysbench/oltp_common.lua--tables=4--table-size=100000--threads=2--time=60-- report-interval=10 Prepare

Stop synchronizing at the slave end when importing data from the main library, inconsistent manufacturing data

[Email protected]:mysql3307.sock [Mysql]>stop slave

3. When the Sysbench is finished, view the data of the master library and the slave data

Main Library:

[Email Protected]:mysql3307.sock [Sbtest]>select count (1) from sbtest1;+----------+| Count (1) |+----------+|   100000 |+----------+1 row in Set (0.05 sec) [e-mail Protected]:mysql3307.sock [Sbtest]>select count (1) from sbtest2;+--- -------+| Count (1) |+----------+|   100000 |+----------+1 row in Set (0.05 sec) [e-mail Protected]:mysql3307.sock [Sbtest]>select count (1) from sbtest3;+--- -------+| Count (1) |+----------+|   100000 |+----------+1 row in Set (0.05 sec) [e-mail Protected]:mysql3307.sock [Sbtest]>select count (1) from sbtest4;+--- -------+| Count (1) |+----------+|   100000 |+----------+1 row in Set (0.05 sec)

Slave End:

[Email Protected]:mysql3307.sock [Sbtest]>select count (1) from sbtest4;+----------+| Count (1) |+----------+|    67550 |+----------+1 row in Set (0.06 sec) [e-mail Protected]:mysql3307.sock [Sbtest]>select count (1) from sbtest3;+---- ------+| Count (1) |+----------+|    70252 |+----------+1 row in Set (0.04 sec)

You can see that the master never synchronizes.

4. Check the status of slave at this point:

[Email Protected]:mysql3307.sock [(none)]>show slave status\g*************************** 1. Row *************************** Slave_IO_State:Master_Host:m1 Master_us Er:repl master_port:3307 connect_retry:60 master_log_file:3307-binlog.000 002read_master_log_pos:76364214relay_log_file:m2-relay-bin.000004 relay_log_pos:64490301 relay_master_log_file:3307-binlog.00000 2 Slave_io_running:no Slave_sql_running:no Replicate_do_db:replicate_igno Re_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignor e_table:last_errno:0 last_error:skip_counter:0exec_master_log_pos:64490084              relay_log_space:76364861 Until_condition:none Until_log_file:u               ntil_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:NULLMaster                _ssl_verify_server_cert:no last_io_errno:0 last_io_error:last_sql_errno:0 last_sql_error:replicate_ignore_server_ids:master_server_id:0 Master_uui                    D:AFEAB8D6-B871-11E7-9B2A-005056B643B3 Master_info_file:/data/mysql/3307/data/master.info                  sql_delay:0 Sql_remaining_delay:null slave_sql_running_state:master_retry_count:86400            MASTER_BIND:LAST_IO_ERROR_TIMESTAMP:LAST_SQL_ERROR_TIMESTAMP:MASTER_SSL_CRL: Master_ssl_crlpath: retrieved_gtid_set:executed_gtid_set:auto_position:0 Replicate_rewrite _db:channel_name:master_tls_version:1 row in Set (0.00 sec)

Because the local relay log is not completed, in order to ensure the accuracy of the experiment, we first let the local Relaylog execute, start slave sql_thread

Check again:

1. Row *************************** Slave_IO_State:Master_Host:m1 Master_us Er:repl master_port:3307 connect_retry:60 master_log_file:3307-binlog.000  002 read_master_log_pos:76364214 relay_log_file:m2-relay-bin.000005 Relay_log_pos:              4 relay_master_log_file:3307-binlog.000002 Slave_io_running:no Slave_sql_running:yes       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: skip_counter:0 exec_master_log_pos:76364214 relay_log_space:154 Un Til_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:NULLMaster_SSL_Verify_Server_Cert:No Last_ io_errno:0 last_io_error:last_sql_errno:0 Last_sql_error:replicate_ignor             e_server_ids:master_server_id:0 master_uuid:afeab8d6-b871-11e7-9b2a-005056b643b3       Master_info_file:/data/mysql/3307/data/master.info sql_delay:0 sql_remaining_delay:null Slave_sql_running_state:slave have read all relay log;      Waiting for more updates master_retry_count:86400 Master_bind:last_io_error_timestamp: Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set : executed_gtid_set:auto_position:0 Replicate_rEwrite_db:channel_name:master_tls_version:1 row in Set (0.00 sec) 

The local relaylog has been fully executed and the latest relay log information is logged:

[Email protected] data]# more relay-log.info 7./m2-relay-bin.00000543307-binlog.00000276364214001001

The above information is very important, indicating that from the library execution to the main library 000002 of Binlog 76364214 This location, we will copy the main library binlog to simulate relaylog, and from this location to start recovery

5. Copy the Binlog to the target side and simulate the relay log

Shut down from the library before copying and modify CNF (Skip-slave-start) so that slave does not restart automatically after copying

[Email protected] data]# lltotal 185248-rw-r-----1 root root 461 Oct 17:14 3307-binlog.000001-rw-r-----1 root root 76364609 Oct 17:14 3307-b Inlog.000002-rw-r-----1 root root 203 Oct 17:14 3307-binlog.000003-rw-r-----1 root root 419 Oct 24 17:1 4 3307-binlog.000004-rw-r-----1 root root 164 Oct 17:14 3307-binlog.index-rw-r-----1 mysql mysql, Oct 15:08 auto.cnf-rw-r-----1 mysql mysql 4720 Oct 17:14 ib_buffer_pool-rw-r- ----1 mysql mysql 12582912 Oct 17:14 ibdata1-rw-r-----1 mysql mysql 50331648 Oct 17:14 ib_logfile0-rw-r-----1 Mys QL MySQL 50331648 Oct 17:11 ib_logfile1-rw-r-----1 mysql mysql 177 Oct 17:14 m2-relay-bin.000005-rw-r-----1 m Ysql MySQL 17:11 m2-relay-bin.index-rw-r-----1 mysql mysql 122 Oct 17:14 master.infodrwxr-x---2     MySQL mysql 4096 Oct 15:07 mysql-rw-------1 root root 0 Oct 15:08 nohup.outdrwxr-x---2 mysql mysql 4096 Oct 15:07 performance_schema-rw-r-----1 mysql mysql (Oct) 17:14 relay-log.infodrwxr-x---2 mysql mysq L 4096 Oct 15:07 restoredrwxr-x---2 mysql mysql 4096 Oct 16:47 sbtestdrwxr-x---2 mysql mysql 12288 Oct 15:07 sys-rw-r-----1 mysql mysql 15:07 xtrabackup_binlog_pos_innodb-rw-r-----1 mysql mysql 577 O CT 15:07 xtrabackup_iNfo 

renamed to relay log

[[email protected] data]# CP 3307-binlog.000001 Relay.000001[[email protected] data]# CP 3307-binlog.000002 relay.000002 [[email protected] data]# CP 3307-binlog.000003 Relay.000003[[email protected] data]# CP 3307-binlog.000004 relay.000004

Change permission Properties

[Email protected] data]# chown Mysql.mysql-r *

Modify the relay log index file to allow the system to recognize

[email protected] data]# cat m2-relay-bin.index./relay.000001./relay.000002./relay.000003./relay.000004

Modify the relay log info file to tell the system where to start copying

[email protected] data]# cat Relay-log.info7./relay.000002763642143307-binlog.00000276364214 001001

Finally, the sql_thread process starts to recover quickly.

Start slave Sql_thread

6. Check that the data is consistent

Slave

[Email Protected]:mysql3307.sock [Sbtest]>select count (1) from sbtest4;+----------+| Count (1) |+----------+|   100000 |+----------+1 row in Set (0.05 sec) [e-mail Protected]:mysql3307.sock [Sbtest]>select count (1) from sbtest3;+--- -------+| Count (1) |+----------+|   100000 |+----------+1 row in Set (0.05 sec)

You can see that slave has recovered all the missing data.

MySQL 5.7 Attempts to speed up recovery based on replication thread Sql_thread