MySQL relaylog + SQL_Thread incremental recovery binlog

1. Set the executed gtid number of 3308 instances as the gtid number at the end of the full backup on that day

View the name of the binlog file that ends when the xtrabackup is fully backed up on that day, the pos location point of the binlog, and the Gtid number that ends when the full backup is completed:

[root@mgr01 backup]# cat /data/backup/db_3306_20190808/xtrabackup_info |grep binlog_pos
binlog_pos = filename 'mysql-bin.000003', position '29571', GTID of the last change 'bde7b592-b966-11e9-8c64-000c294f3e61:1-10296'

Use the xtrabackup tool to restore a full backup of the day to a new mysql 3308 instance:

 innobackupex  --apply-log   /data/backup/db_3306_20190808/
 190808 10:31:56 completed OK!
innobackupex --defaults-file=/data/mysql/mysql3308/my3308.cnf --copy-back /data/backup/db_3306_20190808/
 190808 10:41:38 completed OK!

Grant mysql user permissions to data directory. /data for new instance 3308:

 chown -R mysql.mysql  /data/mysql/mysql3308/data 

Start mysql successfully:

 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf   &

Since both mysql 3306 and 3308 are gtid enabled, restoring full backup data to 3308 instances does not produce the same Gtid number as the actual end of xtrabackup full backup after starting 3308 instances, so after restoring full backup to 3308,
Start and log in to the 3308 instance, reset master empties the Gtid on the current 3308, and then sets the global gtid_purged='bde7b592-b966-11e9-8c64-000c294f3e61:1-10296'; let the Gtid number of the 3308 instance execute until the end of the full backup

(root@'mgr01':mysql3308.sock)[(none)]>reset master;
Query OK, 0 rows affected (0.04 sec)
(root@'mgr01':mysql3308.sock)[(none)]>show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

(root@'mgr01':mysql3308.sock)[(none)]>set global gtid_purged='bde7b592-b966-11e9-8c64-000c294f3e61:1-10296';
Query OK, 0 rows affected (0.06 sec)

(root@'mgr01':mysql3308.sock)[(none)]>show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: bde7b592-b966-11e9-8c64-000c294f3e61:1-10296

(root@'mgr01':mysql3308.sock)[(none)]>show global variables like "%Gtid%";
+----------------------------------+----------------------------------------------+
| Variable_name                    | Value                                        |
+----------------------------------+----------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                           |
| enforce_gtid_consistency         | ON                                           |
| gtid_executed                    | bde7b592-b966-11e9-8c64-000c294f3e61:1-10296 |
| gtid_executed_compression_period | 1000                                         |
| gtid_mode                        | ON                                           |
| gtid_owned                       |                                              |
| gtid_purged                      | bde7b592-b966-11e9-8c64-000c294f3e61:1-10296 |
| session_track_gtids              | OFF                                          |
+----------------------------------+----------------------------------------------+

2. Start MySQL relaylog + SQL_Thread incremental recovery binlog demonstration

Operation on 3308 instance slave:
The 2.1 change command is designed to tell MySQL that it is a slave instance:

Casual change master to master_host="192.168.1.10";

The change command tells MySQL that it is a slave instance, because IO_Thread is not needed, host, password, user, and so on can be filled in at will.
And through this step, the relay.info file is generated.

CHANGE MASTER TO MASTER_HOST="192.168.1.10";

show slave status\G as long as 
Auto_Position: 0 That's OK

2.2 Closing 3308 instances will disguise the incremental binlog file as relaylog:

cp 3306binglog Log file to mysql3308 Data directory data lower

[root@mgr01 data]# cp /data/mysql/mysql3306/binlog/* /data/mysql/mysql3308/data/

rm -rf  /data/mysql/mysql3308/data/mysql-bin.index
cd /data/mysql/mysql3308/data/
rename mysql-bin relay-bin mysql-bin.*

And give mysql permission for the disguised relay-log file

2.3. Delete the relay.info file and modify the relay-log.index file:

Remove the original relay-log.info file below/data/mysql/mysql3308/data/
Edit the mgr01-relay-bin.index file and add the name of the relay log file to it to tell SQL_Thread what relaylog s need to be executed.
[root@mgr01 data]# cat mgr01-relay-bin.index 
./mgr01-relay-bin.000001
./relay-bin.000003
./relay-bin.000004
./relay-bin.000005
./relay-bin.000006

2.4. Tell 3308 slave from which filename and pos location point to start execution of the sql_thread incremental relaylog file:

[root@mgr01 backup]# cat /data/backup/db_3306_20190808/xtrabackup_info |grep binlog_pos
binlog_pos = filename 'mysql-bin.000003', position '29571', GTID of the last change 'bde7b592-b966-11e9-8c64-000c294f3e61:1-10296'
CHANGE MASTER TO   RELAY_LOG_FILE='relay-bin.000003',   RELAY_LOG_POS=29571;

This option tells SQL_Thread from which relay log file and pos location point (that is, the binlog file name and pos location point where the full backup ended that day on 3306 instance) to start relay log file recovery to slave 3308 instance
That is, after restoring the full backup data to 3308, the next step is to restore the data to the slave 3308 instance using the disguised relay log file (that is, the binlog file name and pos location point where the full backup ended that day on 3306 instance)+sql_thread s thread

START SLAVE   SQL_THREAD    UNTIL    MASTER_LOG_FILE = 'mysql-bin.000005', MASTER_LOG_POS =15018; ##The Gtid here is the pos location point of the last binlog file before drop table test1_event
 //Or:
 START SLAVE   SQL_THREAD  UNTIL   SQL_BEFORE_GTIDS='bde7b592-b966-11e9-8c64-000c294f3e61:10445'  ##The Gtid here is the last Gtid before drop table test1_event

3. Summary of the Ways of Recovery

Advantage:
Breakpoint recovery is possible, and breakpoint recovery is possible when progress is artificially controlled, such as stop slave, or when errors are encountered.
Good performance, speeding up recovery with a large number of binlog s.
In some versions, multithreaded replication can be used to speed up increments and recover faster.
Disadvantages:
mysqld needs to be closed.
Manual execution is more complex than mysqlbinlog.

4. To resolve doubts as follows:

Is the structure of binlog and relay-log consistent as a whole??
A: Overall, binlog and relay-log are structurally consistent
Is there a relationship between the filename of Q2 binlog and the filename of relay-log?
A: There is no necessary relationship
Is it possible for Q3 to manually change Binlog to Relay-log?
A: Yes
What is the record information related to Q4 Relay-log?

5. Summary of the incremental recovery process using SQL_thread s:

1. master_auto_position=1 cannot be used
2. Let mysql know that he is a Slave first
3. Turn off mysql and build relay-log
4. Using change master to relay_log_file=...,
relay_log_pos=...;
5.START SLAVE SQL_THREAD UNTIL MASTER_LOG_FILE='xxx',MASTER_LOG_POS=xxxxx
Or START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS='x x x--x x-x';

START SLAVE   SQL_THREAD    UNTIL    MASTER_LOG_FILE = 'mysql-bin.000005', MASTER_LOG_POS =15018; ##The Gtid here is the pos location point of the last binlog file before drop table test1_event
 //Or:
 START SLAVE   SQL_THREAD  UNTIL   SQL_BEFORE_GTIDS='bde7b592-b966-11e9-8c64-000c294f3e61:10445'  ##The Gtid here is the last Gtid before drop table test1_event

Reference blog address:
The following recovery methods refer to the documentation:
http://blog.itpub.net/29773961/viewspace-2143726/

Keywords: Operation & Maintenance MySQL mysqlbinlog

Added by Amal on Sun, 18 Aug 2019 08:23:00 +0300