您的位置:永利集团登录网址 > 永利集团登录网址 > 恢复误删除表黑科技之relay log大法

恢复误删除表黑科技之relay log大法

2019-10-05 09:39

 

 

 

Preface

Preface

Preface

 

 

恢复误删除表黑科技之relay log大法。 

    In my previous blogs,I've demonstrated several mothods of how to rescue a dropped table(or truncated table as well).

    Today I'm gonna test how to rescue a dropped table from binlog server based on a full Xtrabackup backup set.

    Yesterday,I've demonstratated how to rescue a droped and a truncated table based on mysqldump backup.Let's see what's different in rescuing them based on a backup set made by Xtrabackup.Binlog is also indispensable,if we want to rerieve all the incremental data of them.

  •     full mysqldump backup + binlog on master(master was normally running)
  •     full Xtrabackup backup + binlog on master(master was normally running)**
  •     full Xtrabackup backup + binlog on binlog server( binlog server had acquired binlogs before master crashed.)

 

 

 

Framework

Framework

    Les't see another way to achieve the goal more simply.

 

 

 

Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm1 192.168.56.100/3306 master CentOS 7.0 5.7.21 on row
zlm2 192.168.56.101/3306 slave CentOS 7.0 5.7.21 on row
zlm3 192.168.56.102/3306 binlog server CentOS 7.0 5.7.21 on row
Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm2 192.168.1.101/3306 master CentOS 7.0 5.7.21 on row
zlm3 192.168.1.102/3306 slave CentOS 7.0 5.7.21 on row

Procedure

 

 

 

Precedure

Precedure

***Step

 

 

  1. Destroy***

Step 1: Create binlog server.

Step 1: Destroy the table.

 

 

 

Check the table on master.

Check the position on master 

Generate a backup with Xtrabackup first.

 1 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 2 ERROR 2006 (HY000): MySQL server has gone away
 3 No connection. Trying to reconnect...
 4 Connection id:    4
 5 Current database: sysbench
 6 
 7 +--------------------+
 8 | Tables_in_sysbench |
 9 +--------------------+
10 | sbtest1            |
11 | sbtest2            |
12 | sbtest3            |
13 | sbtest4            |
14 | sbtest5            |
15 +--------------------+
16 5 rows in set (0.00 sec)
17 
18 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest5;
19 +----------+
20 | count(*) |
21 +----------+
22 |    10000 |
23 +----------+
24 1 row in set (0.01 sec)
1 zlm@192.168.56.100:3306 [sysbench]>show master status;
2 +------------------+----------+--------------+------------------+-------------------------------------------------+
3 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
4 +------------------+----------+--------------+------------------+-------------------------------------------------+
5 | mysql-bin.000098 |      363 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715693 |
6 +------------------+----------+--------------+------------------+-------------------------------------------------+
7 1 row in set (0.00 sec)
 1 [root@zlm2 07:57:40 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf -uroot -pPassw0rd /data/backup
 3 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1013306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1013306 
 4 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1013306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1013306 
 5 180727 07:57:43 innobackupex: Starting the backup operation
 6 
 7 IMPORTANT: Please check that the backup run completes successfully.
 8            At the end of a successful backup run innobackupex
 9            prints "completed OK!".
10 
11 ... //Omitted the procedure.
12 
13 180727 07:57:56 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
14 xtrabackup: The latest check point (for incremental): '10141351562'
15 xtrabackup: Stopping log copying thread.
16 .180727 07:57:56 >> log scanned up to (10141351571)
17 
18 180727 07:57:56 Executing UNLOCK TABLES
19 180727 07:57:56 All tables unlocked
20 180727 07:57:56 [00] Copying ib_buffer_pool to /data/backup/2018-07-27_07-57-43/ib_buffer_pool
21 180727 07:57:56 [00]        ...done
22 180727 07:57:56 Backup created in directory '/data/backup/2018-07-27_07-57-43/'
23 MySQL binlog position: filename 'mysql-bin.000035', position '954887', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730220'
24 180727 07:57:56 [00] Writing /data/backup/2018-07-27_07-57-43/backup-my.cnf
25 180727 07:57:56 [00]        ...done
26 180727 07:57:56 [00] Writing /data/backup/2018-07-27_07-57-43/xtrabackup_info
27 180727 07:57:56 [00]        ...done
28 xtrabackup: Transaction log of lsn (10141351562) to (10141351571) was copied.
29 180727 07:57:56 completed OK!

 

 

 

**Generate a full Xtrabackup of master.**

**Make binlog server begin to receive binlog.**

**Continue to execute dml operation.**

 1 [root@zlm2 06:28:44 /data/mysql/mysql3306/data]
 2 #innobackupex --default-file=/data/mysql/mysql3306/my.cnf --host=localhost -uroot -pPassw0rd /data/backup
 3 xtrabackup: recognized server arguments: 
 4 xtrabackup: recognized client arguments: 
 5 180731 06:29:03 innobackupex: Starting the backup operation
 6 ... //Omitted.
 7 
 8 180731 06:29:31 Executing UNLOCK TABLES
 9 180731 06:29:31 All tables unlocked
10 180731 06:29:31 [00] Copying ib_buffer_pool to /data/backup/2018-07-31_06-29-03/ib_buffer_pool
11 180731 06:29:31 [00]        ...done
12 180731 06:29:31 Backup created in directory '/data/backup/2018-07-31_06-29-03/'
13 MySQL binlog position: filename 'mysql-bin.000043', position '190', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229'
14 180731 06:29:31 [00] Writing /data/backup/2018-07-31_06-29-03/backup-my.cnf
15 180731 06:29:31 [00]        ...done
16 180731 06:29:31 [00] Writing /data/backup/2018-07-31_06-29-03/xtrabackup_info
17 180731 06:29:31 [00]        ...done
18 xtrabackup: Transaction log of lsn (10141400402) to (10141400411) was copied.
19 180731 06:29:31 completed OK!
 1 [root@zlm3 16:25:01 /data]
 2 #mysqlbinlog -R --raw -h192.168.56.100 -urepl -prepl4slave -P3306 --stop-never mysql-bin.000098 &
 3 [1] 4375
 4 mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
 5 
 6 [root@zlm3 16:26:24 /data]
 7 #ls -l
 8 total 4
 9 drwxr-xr-x 2 mysql mysql  32 Jun 10 03:41 backup
10 drwxr-xr-x 3 mysql mysql  22 Mar 18 16:05 mysql
11 -rw-r----- 1 root  root  363 Jul 29 16:26 mysql-bin.000098
 1 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 2 +--------------------+
 3 | Tables_in_sysbench |
 4 +--------------------+
 5 | sbtest1            |
 6 | sbtest2            |
 7 | sbtest3            |
 8 | sbtest4            |
 9 | sbtest5            |
10 | sbtest6            |
11 | sbtest7            |
12 +--------------------+
13 7 rows in set (0.00 sec)
14 
15 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest7;
16 +----------+
17 | count(*) |
18 +----------+
19 |    10000 |
20 +----------+
21 1 row in set (0.00 sec)
22 
23 (zlm@192.168.1.101 3306)[sysbench]>show master status;
24 +------------------+----------+--------------+------------------+------------------------------------------------+
25 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
26 +------------------+----------+--------------+------------------+------------------------------------------------+
27 | mysql-bin.000035 |   954887 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730220 |
28 +------------------+----------+--------------+------------------+------------------------------------------------+
29 1 row in set (0.00 sec)
30 
31 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
32 Query OK, 0 rows affected (0.02 sec)
33 
34 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest7 limit 10;
35 Query OK, 10 rows affected (0.00 sec)
36 
37 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest7;
38 +----------+
39 | count(*) |
40 +----------+
41 |     9990 |
42 +----------+
43 1 row in set (0.00 sec)
44 
45 (zlm@192.168.1.101 3306)[sysbench]>show master status;
46 +------------------+----------+--------------+------------------+------------------------------------------------+
47 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
48 +------------------+----------+--------------+------------------+------------------------------------------------+
49 | mysql-bin.000036 |     2338 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221 |
50 +------------------+----------+--------------+------------------+------------------------------------------------+
51 1 row in set (0.00 sec)

 

 

 

**Continue to executing some dml operations on the target table and then kill the mysqld.**

**Flush two logs on master.**

**Drop table "sbtest7" in "sysbench" database on master in a certain time(our miss operation point).**

 1 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest5 limit 2000;
 2 Query OK, 2000 rows affected (0.10 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest5 limit 3000;
 5 Query OK, 3000 rows affected (0.07 sec)
 6 
 7 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest5;
 8 +----------+
 9 | count(*) |
10 +----------+
11 |     5000 |
12 +----------+
13 1 row in set (0.00 sec)
14 
15 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest5;
16 Query OK, 0 rows affected (0.01 sec)
17 
18 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
19 Query OK, 0 rows affected (0.02 sec)
20 
21 (zlm@192.168.1.101 3306)[sysbench]>show master status;
22 +------------------+----------+--------------+------------------+------------------------------------------------+
23 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
24 +------------------+----------+--------------+------------------+------------------------------------------------+
25 | mysql-bin.000044 |      190 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730232 |
26 +------------------+----------+--------------+------------------+------------------------------------------------+
27 1 row in set (0.00 sec)
28 
29 [root@zlm2 06:38:14 ~]
30 #pkill mysqld
31 
32 [root@zlm2 06:38:18 ~]
33 #ps aux|grep mysqld
34 root      4050  0.0  0.0 112640   956 pts/0    R+   06:38   0:00 grep --color=auto mysqld
 1 zlm@192.168.56.100:3306 [sysbench]>flush logs;
 2 Query OK, 0 rows affected (0.06 sec)
 3 
 4 zlm@192.168.56.100:3306 [sysbench]>flush logs;
 5 Query OK, 0 rows affected (0.01 sec)
 6 
 7 zlm@192.168.56.100:3306 [sysbench]>show master status;
 8 +------------------+----------+--------------+------------------+-------------------------------------------------+
 9 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
10 +------------------+----------+--------------+------------------+-------------------------------------------------+
11 | mysql-bin.000100 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715693 |
12 +------------------+----------+--------------+------------------+-------------------------------------------------+
13 1 row in set (0.00 sec)
 1 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest7;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 5 +--------------------+
 6 | Tables_in_sysbench |
 7 +--------------------+
 8 | sbtest1            |
 9 | sbtest2            |
10 | sbtest3            |
11 | sbtest4            |
12 | sbtest5            |
13 | sbtest6            |
14 +--------------------+
15 6 rows in set (0.00 sec)
16 
17 (zlm@192.168.1.101 3306)[sysbench]>show master status;
18 +------------------+----------+--------------+------------------+------------------------------------------------+
19 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
20 +------------------+----------+--------------+------------------+------------------------------------------------+
21 | mysql-bin.000036 |     2523 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730222 |
22 +------------------+----------+--------------+------------------+------------------------------------------------+
23 1 row in set (0.00 sec)

 

 

 

**Scp the Xtrabackup backup to another server zlm3 with newly initialized instance**

**Check whether the newly generated binlogs are successfully transmited to binlog server.**

Step 2: Begin to restore backup set.

1 [root@zlm2 06:43:42 ~]
2 #scp -r /data/backup/2018-07-31_06-29-03/ zlm3:/data/backup
3 root@zlm3's password: 
4 xtrabackup_logfile                                                                                                 100% 2560     2.5KB/s   00:00    
5 ibdata1                                                                                                            100%  100MB  50.0MB/s   00:02    
6 plugin.ibd                                                                                                         100%   96KB  96.0KB/s   00:00    
7 servers.ibd                                                                                                        100%   96KB  96.0KB/s   00:00
8 ... //Omitted.
1 [root@zlm3 16:26:27 /data]
2 #ls -l
3 total 12
4 drwxr-xr-x 2 mysql mysql  32 Jun 10 03:41 backup
5 drwxr-xr-x 3 mysql mysql  22 Mar 18 16:05 mysql
6 -rw-r----- 1 root  root  410 Jul 29 16:27 mysql-bin.000098
7 -rw-r----- 1 root  root  241 Jul 29 16:27 mysql-bin.000099
8 -rw-r----- 1 root  root  194 Jul 29 16:27 mysql-bin.000100

 

 

 

Copy the backup set to another server(mine is zlm3) with newly initialized instance of MySQL.

**Step 2. Rescue***

Step 2: Destroy the table.

 1 [root@zlm2 08:03:12 /data/backup]
 2 #ls -l
 3 total 4
 4 drwxr-x--- 6 root root 4096 Jul 27 07:57 2018-07-27_07-57-43
 5 
 6 [root@zlm2 08:03:15 /data/backup]
 7 #tar cf innobkex_full.tar 2018-07-27_07-57-43/
 8 
 9 [root@zlm2 08:03:42 /data/backup]
10 #ls -l
11 total 187384
12 drwxr-x--- 6 root root      4096 Jul 27 07:57 2018-07-27_07-57-43
13 -rw-r--r-- 1 root root 191877120 Jul 27 08:03 innobkex_full.tar
14 
15 [root@zlm2 08:03:48 /data/backup]
16 #scp innobkex_full.tar zlm3:/data/backup
17 root@zlm3's password: 
18 innobkex_full.tar                                                                                                  100%  183MB  91.5MB/s   00:02    
19 
20 [root@zlm2 08:04:39 /data/backup]
21 #

 

 

 

Check target table on master.

Prepare to restore the table "sbtest7".

Restore the backup on zlm3.

 1 zlm@192.168.56.100:3306 [sysbench]>show tables;
 2 +--------------------+
 3 | Tables_in_sysbench |
 4 +--------------------+
 5 | sbtest1            |
 6 | sbtest2            |
 7 | sbtest3            |
 8 | sbtest4            |
 9 | sbtest5            |
10 | sbtest6            |
11 +--------------------+
12 6 rows in set (0.00 sec)
13 
14 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest6;
15 +----------+
16 | count(*) |
17 +----------+
18 |        0 |
19 +----------+
20 1 row in set (0.00 sec)
21 
22 zlm@192.168.56.100:3306 [sysbench]>insert into sbtest6 values(1,1,'a','b');
23 Query OK, 1 row affected (0.00 sec)
24 
25 zlm@192.168.56.100:3306 [sysbench]>select * from sbtest6;
26 +----+---+---+-----+
27 | id | k | c | pad |
28 +----+---+---+-----+
29 |  1 | 1 | a | b   |
30 +----+---+---+-----+
31 1 row in set (0.00 sec)
 1 [root@zlm3 08:05:13 /data/backup]
 2 #ls -l
 3 total 187380
 4 -rw-r--r-- 1 root root 191877120 Jul 27 08:04 innobkex_full.tar
 5 
 6 [root@zlm3 08:05:15 /data/backup]
 7 #tar xf innobkex_full.tar 
 8 
 9 [root@zlm3 08:05:20 /data/backup]
10 #ls -l
11 total 187384
12 drwxr-x--- 6 root root      4096 Jul 27 07:57 2018-07-27_07-57-43
13 -rw-r--r-- 1 root root 191877120 Jul 27 08:04 innobkex_full.tar
14 
15 [root@zlm3 08:06:06 /data/backup]
16 #cd 2018-07-27_07-57-43/
17 
18 [root@zlm3 08:06:22 /data/backup/2018-07-27_07-57-43]
19 #ls -l
20 total 102464
21 -rw-r----- 1 root root       495 Jul 27 07:57 backup-my.cnf
22 -rw-r----- 1 root root      8988 Jul 27 07:57 ib_buffer_pool
23 -rw-r----- 1 root root 104857600 Jul 27 07:57 ibdata1
24 drwxr-x--- 2 root root      4096 Jul 27 07:57 mysql
25 drwxr-x--- 2 root root      8192 Jul 27 07:57 performance_schema
26 drwxr-x--- 2 root root      8192 Jul 27 07:57 sys
27 drwxr-x--- 2 root root      4096 Jul 27 07:57 sysbench
28 -rw-r----- 1 root root        71 Jul 27 07:57 xtrabackup_binlog_info
29 -rw-r----- 1 root root       121 Jul 27 07:57 xtrabackup_checkpoints
30 -rw-r----- 1 root root       587 Jul 27 07:57 xtrabackup_info
31 -rw-r----- 1 root root      2560 Jul 27 07:57 xtrabackup_logfile
32 
33 [root@zlm3 08:06:23 /data/backup/2018-07-27_07-57-43]
34 #cd sysbench/
35 
36 [root@zlm3 08:06:25 /data/backup/2018-07-27_07-57-43/sysbench]
37 #ls -l
38 total 71768
39 -rw-r----- 1 root root       61 Jul 27 07:57 db.opt
40 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest1.frm
41 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest1.ibd
42 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest2.frm
43 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest2.ibd
44 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest3.frm
45 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest3.ibd
46 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest4.frm
47 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest4.ibd
48 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest5.frm
49 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest5.ibd
50 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest6.frm
51 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest6.ibd
52 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest7.frm
53 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest7.ibd
54 
55 [root@zlm3 08:06:27 /data/backup/2018-07-27_07-57-43/sysbench]
56 #mv sbtest7* ../
57 
58 [root@zlm3 08:06:38 /data/backup/2018-07-27_07-57-43/sysbench]
59 #ls -l
60 total 61516
61 -rw-r----- 1 root root       61 Jul 27 07:57 db.opt
62 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest1.frm
63 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest1.ibd
64 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest2.frm
65 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest2.ibd
66 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest3.frm
67 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest3.ibd
68 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest4.frm
69 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest4.ibd
70 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest5.frm
71 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest5.ibd
72 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest6.frm
73 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest6.ibd
74 
75 [root@zlm3 08:06:39 /data/backup/2018-07-27_07-57-43/sysbench]
76 #rm -f sbtest* //Delete all those irrelevant tables which I'm not going to rescue.It can extremly reduce the content I need.
77 
78 [root@zlm3 08:06:45 /data/backup/2018-07-27_07-57-43/sysbench]
79 #mv ../sbtest* .
80 
81 [root@zlm3 08:06:57 /data/backup/2018-07-27_07-57-43/sysbench]
82 #ls -l
83 total 10256
84 -rw-r----- 1 root root       61 Jul 27 07:57 db.opt
85 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest7.frm
86 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest7.ibd
 1 [root@zlm3 06:47:52 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --apply-log /data/backup/2018-07-31_06-29-03/
 3 ... //Omitted.
 4 
 5 [root@zlm3 06:46:39 ~]
 6 #cd /data/mysql/mysql3306/data/
 7 
 8 [root@zlm3 06:46:44 /data/mysql/mysql3306/data]
 9 #ls -l
10 total 409716
11 -rw-r----- 1 mysql mysql        56 Jul 27 11:15 auto.cnf
12 -rw-r----- 1 mysql mysql     19677 Jul 27 11:25 error.log
13 -rw-r----- 1 mysql mysql      2005 Jul 27 11:25 ib_buffer_pool
14 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:25 ibdata1
15 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:25 ib_logfile0
16 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:14 ib_logfile1
17 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:14 ib_logfile2
18 drwxr-x--- 2 mysql mysql      4096 Jul 27 11:15 mysql
19 drwxr-x--- 2 mysql mysql      8192 Jul 27 11:15 performance_schema
20 -rw-r----- 1 mysql mysql       276 Jul 27 11:18 relay-bin.000003
21 -rw-r----- 1 mysql mysql      2771 Jul 27 11:25 relay-bin.000004
22 -rw-r----- 1 mysql mysql       292 Jul 27 11:25 relay-bin.000005
23 -rw-r----- 1 mysql mysql       454 Jul 27 11:25 relay-bin.000006
24 -rw-r----- 1 mysql mysql       344 Jul 27 11:25 relay-bin.000007
25 -rw-r----- 1 mysql mysql       169 Jul 27 11:25 relay-bin-group_replication_applier.000001
26 -rw-r----- 1 mysql mysql        45 Jul 27 11:15 relay-bin-group_replication_applier.index
27 -rw-r----- 1 mysql mysql       169 Jul 27 11:25 relay-bin-group_replication_recovery.000001
28 -rw-r----- 1 mysql mysql        46 Jul 27 11:15 relay-bin-group_replication_recovery.index
29 -rw-r----- 1 mysql mysql        95 Jul 27 11:25 relay-bin.index
30 -rw-r----- 1 mysql mysql       334 Jul 27 11:25 slow.log
31 drwxr-x--- 2 mysql mysql      8192 Jul 27 11:15 sys
32 drwxr-x--- 2 mysql mysql      4096 Jul 27 11:15 sysbench
33 -rw-r----- 1 mysql mysql        24 Jul 27 11:15 xtrabackup_binlog_pos_innodb
34 -rw-r----- 1 mysql mysql       587 Jul 27 11:15 xtrabackup_info
35 -rw-r----- 1 mysql mysql         1 Jul 27 11:15 xtrabackup_master_key_id
36 
37 [root@zlm3 06:46:45 /data/mysql/mysql3306/data]
38 #rm -rf *
39 
40 [root@zlm3 06:46:50 /data/mysql/mysql3306/data]
41 #ps aux|grep mysqld
42 root      3913  0.0  0.0 112640   960 pts/1    R+   06:50   0:00 grep --color=auto mysqld
43 
44 [root@zlm3 06:51:00 ~]
45 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-31_06-29-03/
46 ... //Omitted.
47 
48 180731 06:51:36 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool
49 180731 06:51:36 [01]        ...done
50 180731 06:51:36 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info
51 180731 06:51:36 [01]        ...done
52 180731 06:51:36 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb
53 180731 06:51:36 [01]        ...done
54 180731 06:51:36 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id
55 180731 06:51:36 [01]        ...done
56 180731 06:51:36 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1
57 180731 06:51:36 [01]        ...done
58 180731 06:51:36 completed OK!
59 
60 [root@zlm3 06:50:14 /data/mysql/mysql3306/data]
61 #ls -l
62 total 421936
63 -rw-r----- 1 root root      1017 Jul 31 06:51 ib_buffer_pool
64 -rw-r----- 1 root root 104857600 Jul 31 06:51 ibdata1
65 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile0
66 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile1
67 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile2
68 -rw-r----- 1 root root  12582912 Jul 31 06:51 ibtmp1
69 drwxr-x--- 2 root root      4096 Jul 31 06:51 mysql
70 drwxr-x--- 2 root root      8192 Jul 31 06:51 performance_schema
71 drwxr-x--- 2 root root      8192 Jul 31 06:51 sys
72 drwxr-x--- 2 root root      4096 Jul 31 06:51 sysbench
73 -rw-r----- 1 root root        22 Jul 31 06:51 xtrabackup_binlog_pos_innodb
74 -rw-r----- 1 root root       600 Jul 31 06:51 xtrabackup_info
75 -rw-r----- 1 root root         1 Jul 31 06:51 xtrabackup_master_key_id
76 drwxr-x--- 2 root root       120 Jul 31 06:51 zlm
77 
78 [root@zlm3 06:53:49 /data/mysql/mysql3306/data]
79 #chown -R mysql.mysql *

 

 

 

Generate Xtrabackup backup set.

Apply the redo logfile.

永利游戏平台,**Startup the MySQL instance on zlm3.**

 1 [root@zlm1 16:32:14 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd /data/backup
 3 180729 16:32:20 innobackupex: Error: extra argument found -pPassw0rd
 4 180729 16:32:20 innobackupex: Error: extra argument found /data/backup
 5 
 6 [root@zlm1 16:32:20 ~]
 7 #innobackupex -v
 8 innobackupex version 2.4.4 Linux (x86_64) (revision id: df58cf2)
 9 
10 [root@zlm1 16:32:26 ~]
11 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
12 180729 16:32:33 innobackupex: Starting the backup operation
13 ...
14 
15 180729 16:32:53 Backup created in directory '/data/backup/2018-07-29_16-32-33'
16 MySQL binlog position: filename 'mysql-bin.000100', position '476', GTID of the last change '2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694'
17 180729 16:32:53 [00] Writing backup-my.cnf
18 180729 16:32:53 [00]        ...done
19 180729 16:32:53 [00] Writing xtrabackup_info
20 180729 16:32:53 [00]        ...done
21 xtrabackup: Transaction log of lsn (1719676169) to (1719676178) was copied.
22 180729 16:32:53 completed OK!
 1 [root@zlm3 08:06:58 /data/backup/2018-07-27_07-57-43/sysbench]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --apply-log /data/backup/2018-07-27_07-57-43/
 3 xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1013306 --redo-log-version=1 
 4 xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1013306 --redo-log-version=1 
 5 180727 08:08:41 innobackupex: Starting the apply-log operation
 6 
 7 IMPORTANT: Please check that the apply-log run completes successfully.
 8            At the end of a successful apply-log run innobackupex
 9            prints "completed OK!".
10 
11 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
12 xtrabackup: cd to /data/backup/2018-07-27_07-57-43/
13 
14 ... //Omitted.
15 
16 InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
17 InnoDB: New log files created, LSN=10141352841
18 InnoDB: Highest supported file format is Barracuda.
19 InnoDB: Log scan progressed past the checkpoint lsn 10141352972
20 InnoDB: Doing recovery: scanned up to log sequence number 10141352981 (0%)
21 InnoDB: Database was not shutdown normally!
22 InnoDB: Starting crash recovery.
23 InnoDB: xtrabackup: Last MySQL binlog file position 954219, file name mysql-bin.000035
24 InnoDB: Removed temporary tablespace data file: "ibtmp1"
25 InnoDB: Creating shared tablespace for temporary tables
26 InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
27 InnoDB: File './ibtmp1' size is now 12 MB.
28 InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
29 InnoDB: 32 non-redo rollback segment(s) are active.
30 InnoDB: Waiting for purge to start
31 InnoDB: 5.7.19 started; log sequence number 10141352981
32 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
33 InnoDB: page_cleaner: 1000ms intended loop took 18267ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
34 InnoDB: FTS optimize thread exiting.
35 InnoDB: Starting shutdown...
36 InnoDB: Shutdown completed; log sequence number 10141353000
37 180727 08:09:03 completed OK!
38 
39 [root@zlm3 08:09:03 /data/backup/2018-07-27_07-57-43/sysbench]
40 #cd ..
41 
42 [root@zlm3 08:11:14 /data/backup/2018-07-27_07-57-43]
43 #ls -l
44 total 430144
45 -rw-r----- 1 root root       495 Jul 27 07:57 backup-my.cnf
46 -rw-r----- 1 root root      8988 Jul 27 07:57 ib_buffer_pool
47 -rw-r----- 1 root root 104857600 Jul 27 08:09 ibdata1
48 -rw-r----- 1 root root 104857600 Jul 27 08:09 ib_logfile0
49 -rw-r----- 1 root root 104857600 Jul 27 08:08 ib_logfile1
50 -rw-r----- 1 root root 104857600 Jul 27 08:08 ib_logfile2
51 -rw-r----- 1 root root  12582912 Jul 27 08:09 ibtmp1
52 drwxr-x--- 2 root root      4096 Jul 27 07:57 mysql
53 drwxr-x--- 2 root root      8192 Jul 27 07:57 performance_schema
54 drwxr-x--- 2 root root      8192 Jul 27 07:57 sys
55 drwxr-x--- 2 root root        55 Jul 27 08:06 sysbench
56 -rw-r----- 1 root root        71 Jul 27 07:57 xtrabackup_binlog_info
57 -rw-r--r-- 1 root root        24 Jul 27 08:08 xtrabackup_binlog_pos_innodb
58 -rw-r----- 1 root root       121 Jul 27 08:08 xtrabackup_checkpoints
59 -rw-r----- 1 root root       587 Jul 27 07:57 xtrabackup_info
60 -rw-r----- 1 root root   8388608 Jul 27 08:08 xtrabackup_logfile
61 -rw-r--r-- 1 root root         1 Jul 27 08:08 xtrabackup_master_key_id
62 
63 [root@zlm3 08:11:15 /data/backup/2018-07-27_07-57-43]
64 #cat xtrabackup_binlog_info
65 mysql-bin.000035    954887    1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730220
66 
67 [root@zlm3 08:11:21 /data/backup/2018-07-27_07-57-43]
68 #cat xtrabackup_binlog_pos_innodb
69 mysql-bin.000035    954219
70 
71 //The result of "show master status;" on zlm2.
72 (zlm@192.168.1.101 3306)[(none)]>show master status;
73 +------------------+----------+--------------+------------------+------------------------------------------------+
74 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
75 +------------------+----------+--------------+------------------+------------------------------------------------+
76 | mysql-bin.000036 |     2523 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730222 | //The newest GTID is "uuid:1-3730222"
77 +------------------+----------+--------------+------------------+------------------------------------------------+
78 1 row in set (0.00 sec)
 1 [root@zlm3 06:53:57 /data/mysql/mysql3306/data]
 2 #sh /root/mysqld.sh
 3 
 4 [root@zlm3 06:55:16 /data/mysql/mysql3306/data]
 5 #ps aux|grep mysqld
 6 mysql     3940 20.0 17.7 1110004 180300 pts/1  Sl   06:55   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
 7 root      3975  0.0  0.0 112640   956 pts/1    R+   06:55   0:00 grep --color=auto mysqld
 8 
 9 [root@zlm3 06:55:44 /data/mysql/mysql3306/data]
10 #mysql
11 Welcome to the MySQL monitor.  Commands end with ; or g.
12 Your MySQL connection id is 3
13 Server version: 5.7.21-log MySQL Community Server (GPL)
14 
15 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
16 
17 Oracle is a registered trademark of Oracle Corporation and/or its
18 affiliates. Other names may be trademarks of their respective
19 owners.
20 
21 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
22 
23 (zlm@192.168.1.102 3306)[(none)]>select count(*) from sysbench.sbtest5;
24 +----------+
25 | count(*) |
26 +----------+
27 |    10000 |
28 +----------+
29 1 row in set (0.02 sec)

 

 

 

Insert one record of "sbtest6" and drop it.

Shutdown the instance on zlm3 and copy back the datafiles

**    The data in Xtrabackup of master has been restored on zlm3. *Notice,it doesn't contain the operations of deletion 5000 rows. Firstly,I supposed that the mysqld has crashed and it can never start again. Secondly,I don't have binlog server any more this time.Is *there any other way to restore the dropping table and guarantee the change will not lose on it?**How can we restore the data safely and simply?Surely there is.**

 1 zlm@192.168.56.100:3306 [sysbench]>insert into sbtest6 values(2,2,'c','d');
 2 Query OK, 1 row affected (0.00 sec)
 3 
 4 zlm@192.168.56.100:3306 [sysbench]>show master status;
 5 +------------------+----------+--------------+------------------+-------------------------------------------------+
 6 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
 7 +------------------+----------+--------------+------------------+-------------------------------------------------+
 8 | mysql-bin.000100 |      758 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715695 |
 9 +------------------+----------+--------------+------------------+-------------------------------------------------+
10 1 row in set (0.00 sec)
11 
12 zlm@192.168.56.100:3306 [sysbench]>drop tables sbtest6;
13 Query OK, 0 rows affected (0.02 sec)
14 
15 zlm@192.168.56.100:3306 [sysbench]>show master status;
16 +------------------+----------+--------------+------------------+-------------------------------------------------+
17 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
18 +------------------+----------+--------------+------------------+-------------------------------------------------+
19 | mysql-bin.000100 |      951 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
20 +------------------+----------+--------------+------------------+-------------------------------------------------+
21 1 row in set (0.00 sec)
 1 [root@zlm3 08:11:28 /data/backup/2018-07-27_07-57-43]
 2 #ps aux|grep mysqld
 3 mysql     5079  0.0 20.2 1110576 205684 pts/1  Sl   07:35   0:01 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
 4 root      5341  0.0  0.0 112640   956 pts/2    R+   08:16   0:00 grep --color=auto mysqld
 5 
 6 [root@zlm3 08:16:38 /data/backup/2018-07-27_07-57-43]
 7 #mysqladmin shutdown
 8 
 9 [root@zlm3 08:16:43 /data/backup/2018-07-27_07-57-43]
10 #ps aux|grep mysqld
11 root      5352  0.0  0.0 112640   960 pts/2    R+   08:16   0:00 grep --color=auto mysqld
12 
13 [root@zlm3 08:16:45 /data/backup/2018-07-27_07-57-43]
14 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-27_07-57-43/
15 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
16 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
17 180727 08:17:34 innobackupex: Starting the copy-back operation
18 
19 IMPORTANT: Please check that the copy-back run completes successfully.
20            At the end of a successful copy-back run innobackupex
21            prints "completed OK!".
22 
23 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
24 Original data directory /data/mysql/mysql3306/data is not empty! //The data directory need to be cleaned.
25 
26 [root@zlm3 08:17:34 /data/backup/2018-07-27_07-57-43]
27 #cd /data/mysql/mysql3306/data
28 
29 [root@zlm3 08:18:09 /data/mysql/mysql3306/data]
30 #ls -l
31 total 409700
32 -rw-r----- 1 mysql mysql        56 Jul 27 07:35 auto.cnf
33 -rw-r----- 1 mysql mysql     27980 Jul 27 08:16 error.log
34 -rw-r----- 1 mysql mysql       904 Jul 27 08:16 ib_buffer_pool
35 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:16 ibdata1
36 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:16 ib_logfile0
37 -rw-r----- 1 mysql mysql 104857600 Jul 27 07:35 ib_logfile1
38 -rw-r----- 1 mysql mysql 104857600 Jul 27 07:35 ib_logfile2
39 drwxr-x--- 2 mysql mysql      4096 Jul 27 07:34 mysql
40 drwxr-x--- 2 mysql mysql      8192 Jul 27 07:34 performance_schema
41 -rw-r----- 1 mysql mysql       276 Jul 27 07:59 relay-bin.000004
42 -rw-r----- 1 mysql mysql      2562 Jul 27 08:16 relay-bin.000005
43 -rw-r----- 1 mysql mysql       169 Jul 27 08:16 relay-bin-group_replication_applier.000001
44 -rw-r----- 1 mysql mysql        45 Jul 27 07:35 relay-bin-group_replication_applier.index
45 -rw-r----- 1 mysql mysql       169 Jul 27 08:16 relay-bin-group_replication_recovery.000001
46 -rw-r----- 1 mysql mysql        46 Jul 27 07:35 relay-bin-group_replication_recovery.index
47 -rw-r----- 1 mysql mysql        38 Jul 27 07:59 relay-bin.index
48 -rw-r----- 1 mysql mysql       167 Jul 27 07:35 slow.log
49 drwxr-x--- 2 mysql mysql      8192 Jul 27 07:34 sys
50 drwxr-x--- 2 mysql mysql        55 Jul 27 07:34 sysbench
51 -rw-r----- 1 mysql mysql       584 Jul 27 07:34 xtrabackup_info
52 
53 [root@zlm3 08:18:18 /data/mysql/mysql3306/data]
54 #rm -rf *
55 
56 [root@zlm3 08:18:19 /data/mysql/mysql3306/data]
57 #ls -l
58 total 0
59 
60 [root@zlm3 08:18:22 /data/mysql/mysql3306/data]
61 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-27_07-57-43/
62 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
63 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
64 180727 08:18:53 innobackupex: Starting the copy-back operation
65 
66 IMPORTANT: Please check that the copy-back run completes successfully.
67            At the end of a successful copy-back run innobackupex
68            prints "completed OK!".
69 
70 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
71 180727 08:18:53 [01] Copying ib_logfile0 to /data/mysql/mysql3306/data/ib_logfile0
72 180727 08:18:54 [01]        ...done
73 180727 08:18:55 [01] Copying ib_logfile1 to /data/mysql/mysql3306/data/ib_logfile1
74 180727 08:18:55 [01]        ...done
75 180727 08:18:59 [01] Copying ib_logfile2 to /data/mysql/mysql3306/data/ib_logfile2
76 180727 08:19:00 [01]        ...done
77 180727 08:19:04 [01] Copying ibdata1 to /data/mysql/mysql3306/data/ibdata1
78 180727 08:19:06 [01]        ...done
79 
80 ... //Omitted.
81 
82 180727 08:19:13 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool
83 180727 08:19:13 [01]        ...done
84 180727 08:19:13 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info
85 180727 08:19:13 [01]        ...done
86 180727 08:19:13 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb
87 180727 08:19:13 [01]        ...done
88 180727 08:19:13 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id
89 180727 08:19:13 [01]        ...done
90 180727 08:19:13 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1
91 180727 08:19:13 [01]        ...done
92 180727 08:19:14 completed OK!

    Even thought the mysqld process is down on master.I still can get the binlog files on it.How about change the master binlog files into relay log files and apply them on zlm3?Let's have a try.

 

 

 

Shutdown the mysqld on master(mimic the MySQL instance down).

Start the instance and check the table "sbtest7".

**Step

 1 [root@zlm1 16:56:54 /data/backup]
 2 #mysqladmin shutdown
 3 
 4 [root@zlm1 16:57:05 /data/backup]
 5 #ps aux|grep mysqld
 6 mysql     3804  0.0 20.7 1072396 210620 pts/0  Sl   15:52   0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
 7 root      4512  0.0  0.0 112640   960 pts/2    R+   16:57   0:00 grep --color=auto mysqld
 8 
 9 [root@zlm1 16:57:10 /data/backup]
10 #ps aux|grep mysqld
11 mysql     3804  0.0 20.1 1038828 204780 pts/0  Sl   15:52   0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
12 root      4518  0.0  0.0 112640   956 pts/2    R+   16:57   0:00 grep --color=auto mysqld
13 
14 [root@zlm1 16:57:15 /data/backup]
15 #mysqladmin shutdown
16 mysqladmin: connect to server at '192.168.56.100' failed
17 error: 'Can't connect to MySQL server on '192.168.56.100' (111)'
18 Check that mysqld is running on 192.168.56.100 and that the port is 3306.
19 You can check this by doing 'telnet 192.168.56.100 3306'
20 
21 [root@zlm1 16:57:23 /data/backup]
22 #ps aux|grep mysqld
23 root      4529  0.0  0.0 112640   960 pts/2    R+   16:57   0:00 grep --color=auto mysqld
 1 [root@zlm3 08:21:05 /data/mysql/mysql3306/data]
 2 #sh /root/mysqld.sh
 3 
 4 [root@zlm3 08:21:22 /data/mysql/mysql3306/data]
 5 #ps aux|grep mysqld
 6 root      5416  0.0  0.0 112640   960 pts/2    R+   08:21   0:00 grep --color=auto mysqld
 7 
 8 [root@zlm3 08:21:26 /data/mysql/mysql3306/data]
 9 #tail -f error.log 
10 2018-07-27T08:21:22.902146+01:00 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
11 2018-07-27T08:21:22.902161+01:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
12 2018-07-27T08:21:23.503758+01:00 0 [ERROR] Plugin 'InnoDB' init function returned error.
13 2018-07-27T08:21:23.503784+01:00 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
14 2018-07-27T08:21:23.503792+01:00 0 [ERROR] Failed to initialize builtin plugins.
15 2018-07-27T08:21:23.503796+01:00 0 [ERROR] Aborting
16 
17 2018-07-27T08:21:23.503801+01:00 0 [Note] Binlog end
18 2018-07-27T08:21:23.504149+01:00 0 [Note] mysqld: Shutdown complete
19 
20 ^C
21 
22 [root@zlm3 08:21:33 /data/mysql/mysql3306/data]
23 #chown -R mysql.mysql *
24 
25 [root@zlm3 08:22:05 /data/mysql/mysql3306/data]
26 #ls -l
27 total 421944
28 -rw-r----- 1 mysql mysql      1910 Jul 27 08:21 error.log
29 -rw-r----- 1 mysql mysql      8988 Jul 27 08:19 ib_buffer_pool
30 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:19 ibdata1
31 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:18 ib_logfile0
32 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:18 ib_logfile1
33 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:19 ib_logfile2
34 -rw-r----- 1 mysql mysql  12582912 Jul 27 08:19 ibtmp1
35 -rw-r----- 1 mysql mysql         0 Jul 27 08:21 innodb_status.5398
36 drwxr-x--- 2 mysql mysql      4096 Jul 27 08:19 mysql
37 drwxr-x--- 2 mysql mysql      8192 Jul 27 08:19 performance_schema
38 drwxr-x--- 2 mysql mysql      8192 Jul 27 08:19 sys
39 drwxr-x--- 2 mysql mysql        55 Jul 27 08:19 sysbench
40 -rw-r----- 1 mysql mysql        24 Jul 27 08:19 xtrabackup_binlog_pos_innodb
41 -rw-r----- 1 mysql mysql       587 Jul 27 08:19 xtrabackup_info
42 -rw-r----- 1 mysql mysql         1 Jul 27 08:19 xtrabackup_master_key_id
43 
44 [root@zlm3 08:22:12 /data/mysql/mysql3306/data]
45 #sh /root/mysqld.sh
46 
47 [root@zlm3 08:22:25 /data/mysql/mysql3306/data]
48 #ps aux|grep mysqld
49 mysql     5437  3.0 17.7 1110004 180944 pts/2  Sl   08:22   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
50 root      5470  0.0  0.0 112640   956 pts/2    R+   08:22   0:00 grep --color=auto mysqld
51 
52 (zlm@192.168.1.102 3306)[(none)]>show databases;
53 +--------------------+
54 | Database           |
55 +--------------------+
56 | information_schema |
57 | mysql              |
58 | performance_schema |
59 | sys                |
60 | sysbench           |
61 +--------------------+
62 5 rows in set (0.01 sec)
63 
64 (zlm@192.168.1.102 3306)[(none)]>use sysbench
65 Reading table information for completion of table and column names
66 You can turn off this feature to get a quicker startup with -A
67 
68 Database changed
69 (zlm@192.168.1.102 3306)[sysbench]>show tables;
70 +--------------------+
71 | Tables_in_sysbench |
72 +--------------------+
73 | sbtest7            |
74 +--------------------+
75 1 row in set (0.00 sec)
76 
77 (zlm@192.168.1.102 3306)[sysbench]>select count(*) from sbtest7;
78 +----------+
79 | count(*) |
80 +----------+
81 |    10000 |
82 +----------+
83 1 row in set (0.00 sec)
84 
85 //Compare the output of server zlm2.
86 (zlm@192.168.1.101 3306)[sysbench]>show tables;
87 +--------------------+
88 | Tables_in_sysbench |
89 +--------------------+
90 | sbtest1            |
91 | sbtest2            |
92 | sbtest3            |
93 | sbtest4            |
94 | sbtest5            |
95 | sbtest6            |
96 +--------------------+
97 7 rows in set (0.00 sec)
  1. Special technique*

 

 


Copy backup set to zlm2.

Step 3: Implement a slave with replication filter option.

 

 1 [root@zlm1 16:32:53 ~]
 2 #cd /data/backup/
 3 
 4 [root@zlm1 16:37:19 /data/backup]
 5 #ls -l
 6 total 4
 7 drwxr-x--- 7 root root 4096 Jul 29 16:32 2018-07-29_16-32-33
 8 
 9 [root@zlm1 16:37:24 /data/backup]
10 #scp -r 2018-07-29_16-32-33 zlm2:/data/backup
11 xtrabackup_logfile                                                                                                 100% 2560     2.5KB/s   00:00    
12 ibdata1                                                                                                            100%  100MB  50.0MB/s   00:02
13 ... //Omitted.

 

Make sure which binlogs we need and copy them to zlm3.

 

Execute a "change master to ... " on zlm3.

 1 [root@zlm3 07:23:49 /data/backup/2018-07-31_06-29-03]
 2 #cat xtrabackup_binlog_info
 3 mysql-bin.000043    190    1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229 //It means that the binlog we need should begin from mysql-bin.000043.
 4 
 5 [root@zlm2 06:38:09 /data/mysql/mysql3306/data]
 6 #cd ../logs
 7 
 8 [root@zlm2 07:20:00 /data/mysql/mysql3306/logs]
 9 #ls -l
10 total 64848
11 -rw-r----- 1 mysql mysql      233 Jul 23 11:21 mysql-bin.000023
12 -rw-r----- 1 mysql mysql      209 Jul 23 11:21 mysql-bin.000024
13 -rw-r----- 1 mysql mysql      233 Jul 24 11:27 mysql-bin.000025
14 -rw-r----- 1 mysql mysql      209 Jul 24 11:27 mysql-bin.000026
15 -rw-r----- 1 mysql mysql      233 Jul 25 06:12 mysql-bin.000027
16 -rw-r----- 1 mysql mysql      209 Jul 25 06:12 mysql-bin.000028
17 -rw-r----- 1 mysql mysql  5727732 Jul 25 11:33 mysql-bin.000029
18 -rw-r----- 1 mysql mysql      209 Jul 25 11:33 mysql-bin.000030
19 -rw-r----- 1 mysql mysql 58202858 Jul 26 09:12 mysql-bin.000031
20 -rw-r----- 1 mysql mysql   477279 Jul 26 09:13 mysql-bin.000032
21 -rw-r----- 1 mysql mysql      383 Jul 26 11:21 mysql-bin.000033
22 -rw-r----- 1 mysql mysql      209 Jul 26 11:21 mysql-bin.000034
23 -rw-r----- 1 mysql mysql   954930 Jul 27 07:59 mysql-bin.000035
24 -rw-r----- 1 mysql mysql     2566 Jul 27 11:25 mysql-bin.000036
25 -rw-r----- 1 mysql mysql      209 Jul 27 11:25 mysql-bin.000037
26 -rw-r----- 1 mysql mysql     1394 Jul 30 11:29 mysql-bin.000038
27 -rw-r----- 1 mysql mysql      209 Jul 30 11:29 mysql-bin.000039
28 -rw-r----- 1 mysql mysql      418 Jul 31 04:37 mysql-bin.000040
29 -rw-r----- 1 mysql mysql      233 Jul 31 04:37 mysql-bin.000041
30 -rw-r----- 1 mysql mysql      233 Jul 31 04:37 mysql-bin.000042
31 -rw-r----- 1 mysql mysql   954479 Jul 31 06:35 mysql-bin.000043
32 -rw-r----- 1 mysql mysql      209 Jul 31 06:38 mysql-bin.000044
33 -rw-r----- 1 mysql mysql      968 Jul 31 06:35 mysql-bin.index
34 
35 [root@zlm2 07:28:32 /data/mysql/mysql3306/logs]
36 #scp mysql-bin.00004{3,4} zlm3:/data/backup37 root@zlm3's password: 
38 mysql-bin.000043                                                                                                   100%  932KB 932.1KB/s   00:00    
39 mysql-bin.000044                                                                                                   100%  209     0.2KB/s   00:00

*Step 3: Rescue data.**

 1 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 2 Empty set (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show master status;
 5 +------------------+----------+--------------+------------------+------------------------------------------------+
 6 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
 7 +------------------+----------+--------------+------------------+------------------------------------------------+
 8 | mysql-bin.000003 |      190 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221 |
 9 +------------------+----------+--------------+------------------+------------------------------------------------+
10 1 row in set (0.00 sec)
11 
12 (zlm@192.168.1.102 3306)[sysbench]>change master to 
13     -> master_host='192.168.1.101',
14     -> master_port=3306,
15     -> master_user='repl',
16     -> master_password='repl4slave',
17     -> master_auto_position=1;
18 Query OK, 0 rows affected, 2 warnings (0.05 sec)

 


 

**Transfer the mysql-bin files into relay-bin files.**

 

Start slave IO_Thread.

 1 [root@zlm3 07:24:08 /data/backup/2018-07-31_06-29-03]
 2 #cd ..
 3 
 4 [root@zlm3 07:30:46 /data/backup]
 5 #ls -l
 6 total 944
 7 drwxr-x--- 7 root root   4096 Jul 31 06:48 2018-07-31_06-29-03
 8 -rw-r----- 1 root root 954479 Jul 31 07:28 mysql-bin.000043
 9 -rw-r----- 1 root root    209 Jul 31 07:28 mysql-bin.000044
10 
11 [root@zlm3 07:30:47 /data/backup]
12 #for i in $(ls mysql-bin.0*)
13 > do
14 >     ext=$(echo $i | cut -d'.' -f2);
15 >     cp $i relay-bin.$ext;
16 > done
17 
18 [root@zlm3 07:31:19 /data/backup]
19 #ls -l
20 total 1884
21 drwxr-x--- 7 root root   4096 Jul 31 06:48 2018-07-31_06-29-03
22 -rw-r----- 1 root root 954479 Jul 31 07:28 mysql-bin.000043
23 -rw-r----- 1 root root    209 Jul 31 07:28 mysql-bin.000044
24 -rw-r----- 1 root root 954479 Jul 31 07:31 relay-bin.000043
25 -rw-r----- 1 root root    209 Jul 31 07:31 relay-bin.000044 
26 
27 [root@zlm3 07:36:18 /data/backup]
28 #ls ./relay-bin.0* > relay-bin.index
29 
30 [root@zlm3 07:36:20 /data/backup]
31 #ls -l
32 total 1888
33 drwxr-x--- 7 root root   4096 Jul 31 06:48 2018-07-31_06-29-03
34 -rw-r----- 1 root root 954479 Jul 31 07:28 mysql-bin.000043
35 -rw-r----- 1 root root    209 Jul 31 07:28 mysql-bin.000044
36 -rw-r----- 1 root root 954479 Jul 31 07:31 relay-bin.000043
37 -rw-r----- 1 root root    209 Jul 31 07:31 relay-bin.000044
38 -rw-r--r-- 1 root root     38 Jul 31 07:36 relay-bin.index
39 
40 [root@zlm3 07:36:23 /data/backup]
41 #cat relay-bin.index 
42 ./relay-bin.000043
43 ./relay-bin.000044
44 
45 [root@zlm3 07:36:27 /data/backup]
46 #chown mysql.mysql relay*
47 
48 [root@zlm3 07:37:12 /data/backup]
49 #ls -l
50 total 1888
51 drwxr-x--- 7 root  root    4096 Jul 31 06:48 2018-07-31_06-29-03
52 -rw-r----- 1 root  root  954479 Jul 31 07:28 mysql-bin.000043
53 -rw-r----- 1 root  root     209 Jul 31 07:28 mysql-bin.000044
54 -rw-r----- 1 mysql mysql 954479 Jul 31 07:31 relay-bin.000043
55 -rw-r----- 1 mysql mysql    209 Jul 31 07:31 relay-bin.000044
56 -rw-r--r-- 1 mysql mysql     38 Jul 31 07:36 relay-bin.index

Prepare to restore the backup with "--apply-log" on zlm2.

 1 (zlm@192.168.1.102 3306)[sysbench]>start slave io_thread;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.101
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000036
12           Read_Master_Log_Pos: 2523
13                Relay_Log_File: relay-bin.000001
14                 Relay_Log_Pos: 4
15         Relay_Master_Log_File: 
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 0
28               Relay_Log_Space: 776
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1013306
46                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222 //The newest transaction has been retrieved.
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)

 

 1 [root@zlm2 16:38:09 /data/backup]
 2 #ls -l
 3 total 4
 4 drwxr-x--- 7 root root 4096 Jul 29 16:37 2018-07-29_16-32-33
 5 
 6 [root@zlm2 16:38:12 /data/backup]
 7 #innobackupex -v
 8 xtrabackup: recognized server arguments: --datadir=/var/lib/mysql 
 9 innobackupex version 2.4.12 Linux (x86_64) (revision id: 170eb8c)
10 
11 [root@zlm2 16:39:13 /data/backup]
12 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --apply-log /data/backup/2018-07-29_16-32-33/
13 ... //Omitted.
14 
15 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
16 InnoDB: page_cleaner: 1000ms intended loop took 8812ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
17 InnoDB: FTS optimize thread exiting.
18 InnoDB: Starting shutdown...
19 InnoDB: Shutdown completed; log sequence number 1719676456
20 180729 16:39:39 completed OK!

 

**Copy these relay-bin files to the proper directory.**

 

Change replication filter "replicate_do_table" option.

 1 [root@zlm3 07:48:10 /data/backup]
 2 #cp relay* /data/mysql/mysql3306/data
 3 
 4 [root@zlm3 07:48:27 /data/backup]
 5 #cd /data/mysql/mysql3306/data
 6 
 7 [root@zlm3 07:48:35 /data/mysql/mysql3306/data]
 8 #ls -l|grep relay
 9 -rw-r----- 1 root  root     954479 Jul 31 07:48 relay-bin.000043 //Notice,the owner and group has been changed.
10 -rw-r----- 1 root  root        209 Jul 31 07:48 relay-bin.000044
11 -rw-r----- 1 mysql mysql       150 Jul 31 06:55 relay-bin-group_replication_applier.000001
12 -rw-r----- 1 mysql mysql        45 Jul 31 06:55 relay-bin-group_replication_applier.index
13 -rw-r----- 1 mysql mysql       150 Jul 31 06:55 relay-bin-group_replication_recovery.000001
14 -rw-r----- 1 mysql mysql        46 Jul 31 06:55 relay-bin-group_replication_recovery.index
15 -rw-r--r-- 1 root  root         60 Jul 31 07:48 relay-bin.index
16 
17 [root@zlm3 07:48:40 /data/mysql/mysql3306/data]
18 #chown mysql.mysql relay*
19 
20 [root@zlm3 07:49:45 /data/mysql/mysql3306/data]
21 #ls -l|grep relay
22 -rw-r----- 1 mysql mysql    954479 Jul 31 07:48 relay-bin.000043
23 -rw-r----- 1 mysql mysql       209 Jul 31 07:48 relay-bin.000044
24 -rw-r----- 1 mysql mysql       150 Jul 31 06:55 relay-bin-group_replication_applier.000001
25 -rw-r----- 1 mysql mysql        45 Jul 31 06:55 relay-bin-group_replication_applier.index
26 -rw-r----- 1 mysql mysql       150 Jul 31 06:55 relay-bin-group_replication_recovery.000001
27 -rw-r----- 1 mysql mysql        46 Jul 31 06:55 relay-bin-group_replication_recovery.index
28 -rw-r--r-- 1 mysql mysql        60 Jul 31 07:48 relay-bin.index

Shutdown instance and copy back.

 1 (zlm@192.168.1.102 3306)[sysbench]>change replication filter replicate_do_table=(sysbench.sbteset7);
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.101
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000036
12           Read_Master_Log_Pos: 2523
13                Relay_Log_File: relay-bin.000001
14                 Relay_Log_Pos: 4
15         Relay_Master_Log_File: 
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: sysbench.sbteset7
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 0
28               Relay_Log_Space: 776
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1013306
46                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)

 

 1 [root@zlm2 16:39:39 /data/backup]
 2 #ps aux|grep mysqld
 3 mysql     3771  0.0 18.7 1071816 190784 pts/0  Sl   15:52   0:01 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
 4 root      4451  0.0  0.0 112640   956 pts/1    R+   16:43   0:00 grep --color=auto mysqld
 5 
 6 [root@zlm2 16:43:10 /data/backup]
 7 #mysqladmin shutdown
 8 
 9 [root@zlm2 16:43:15 /data/backup]
10 #ps aux|grep mysqld
11 root      4463  0.0  0.0 112640   956 pts/1    R+   16:43   0:00 grep --color=auto mysqld
12 
13 [root@zlm2 16:43:18 /data/backup]
14 #cd /data/mysql/mysql3306/data/
15 
16 [root@zlm2 16:43:36 /data/mysql/mysql3306/data]
17 #rm -rf *
18 
19 [root@zlm2 16:43:39 /data/mysql/mysql3306/data]
20 #cd ../logs
21 
22 [root@zlm2 16:43:50 /data/mysql/mysql3306/logs]
23 #rm -rf *
24 
25 [root@zlm2 16:43:53 /data/mysql/mysql3306/logs]
26 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/2018-07-29_16-32-33/
27 ... //Omitted.

 

**Restart the mysqld process.**

 

Analyze the binlog file on zlm2 to get the GTID before dropping operation.

 1 [root@zlm3 08:25:18 /data/mysql/mysql3306/data]
 2 #mysqladmin shutdown
 3 
 4 [root@zlm3 08:31:25 /data/mysql/mysql3306/data]
 5 #ps aux|grep mysqld
 6 root      4309  0.0  0.0 112640   956 pts/1    R+   08:31   0:00 grep --color=auto mysqld
 7 
 8 [root@zlm3 08:31:35 /data/mysql/mysql3306/data]
 9 #sh /root/mysqld.sh
10 
11 [root@zlm3 08:31:45 /data/mysql/mysql3306/data]
12 #ps aux|grep mysqld
13 mysql     4315 11.5 17.8 1044468 181776 pts/1  Sl   08:31   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
14 root      4348  0.0  0.0 112640   960 pts/1    R+   08:31   0:00 grep --color=auto mysqld

Restart instance and check table.

 1 [root@zlm2 08:35:06 ~]
 2 #mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.
 3 mysql-bin.000011  mysql-bin.000015  mysql-bin.000019  mysql-bin.000023  mysql-bin.000027  mysql-bin.000031  mysql-bin.000035  
 4 mysql-bin.000012  mysql-bin.000016  mysql-bin.000020  mysql-bin.000024  mysql-bin.000028  mysql-bin.000032  mysql-bin.000036  
 5 mysql-bin.000013  mysql-bin.000017  mysql-bin.000021  mysql-bin.000025  mysql-bin.000029  mysql-bin.000033  mysql-bin.index   
 6 mysql-bin.000014  mysql-bin.000018  mysql-bin.000022  mysql-bin.000026  mysql-bin.000030  mysql-bin.000034  
 7 
 8 [root@zlm2 08:35:06 ~]
 9 #mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.000036 > 36.log
10 
11 [root@zlm2 08:35:43 ~]
12 #tail -20 36.log
13 ###   @2=5039
14 ###   @3='32005756334-91141508143-50354766020-63389724388-71947328293-08845712532-56754824208-47077542818-55826128113-56514840054'
15 ###   @4='22255110541-96175917050-58709546750-68026622026-27581577227'
16 # at 2311
17 #180727  7:59:57 server id 1013306  end_log_pos 2338     Xid = 232
18 COMMIT/*!*/;
19 # at 2338
20 #180727  8:01:50 server id 1013306  end_log_pos 2399     GTID    last_committed=1    sequence_number=2    rbr_only=no
21 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222'/*!*/; //Here's the GTID we need.
22 # at 2399
23 #180727  8:01:50 server id 1013306  end_log_pos 2523     Query    thread_id=21    exec_time=0    error_code=0
24 use `sysbench`/*!*/;
25 SET TIMESTAMP=1532671310/*!*/;
26 DROP TABLE `sbtest7` /* generated by server */
27 /*!*/;
28 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
29 DELIMITER ;
30 # End of log file
31 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
32 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

 1 [root@zlm2 16:44:41 /data/mysql/mysql3306/logs]
 2 #cd ../data
 3 
 4 [root@zlm2 16:47:40 /data/mysql/mysql3306/data]
 5 #chown -R mysql.mysql *
 6 
 7 [root@zlm2 16:47:49 /data/mysql/mysql3306/data]
 8 #sh /root/mysqld.sh
 9 
10 [root@zlm2 16:47:56 /data/mysql/mysql3306/data]
11 #ps aux|grep mysqld
12 mysql     4514  3.2 17.9 1071804 182316 pts/1  Sl   16:47   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
13 root      4547  0.0  0.0 112640   960 pts/1    R+   16:48   0:00 grep --color=auto mysqld
14 
15 [root@zlm2 16:48:03 /data/mysql/mysql3306/data]
16 #mysql
17 Welcome to the MySQL monitor.  Commands end with ; or g.
18 Your MySQL connection id is 2
19 Server version: 5.7.21-log MySQL Community Server (GPL)
20 
21 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
22 
23 Oracle is a registered trademark of Oracle Corporation and/or its
24 affiliates. Other names may be trademarks of their respective
25 owners.
26 
27 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
28 
29 zlm@192.168.56.101:3306 [(none)]>use sysbench
30 Reading table information for completion of table and column names
31 You can turn off this feature to get a quicker startup with -A
32 
33 Database changed
34 zlm@192.168.56.101:3306 [sysbench]>show tables;
35 +--------------------+
36 | Tables_in_sysbench |
37 +--------------------+
38 | sbtest1            |
39 | sbtest2            |
40 | sbtest3            |
41 | sbtest4            |
42 | sbtest5            |
43 | sbtest6            |
44 +--------------------+
45 6 rows in set (0.00 sec)
46 
47 zlm@192.168.56.101:3306 [sysbench]>select * from sbtest6;
48 +----+---+---+-----+
49 | id | k | c | pad |
50 +----+---+---+-----+
51 |  1 | 1 | a | b   |
52 +----+---+---+-----+
53 1 row in set (0.00 sec)

 

**Check out the first consistent position we need.**

 

Start slave SQL_Thread with until_option clause.

1 [root@zlm3 08:34:15 /data/backup/2018-07-31_06-29-03]
2 #cat xtrabackup_binlog_info
3 mysql-bin.000043    190    1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229

*Step 4: **Implement a slave with binlog server.***

 1 (zlm@192.168.1.102 3306)[sysbench]>start slave sql_thread until sql_before_gtids='1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222';
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.101
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000036
12           Read_Master_Log_Pos: 2523
13                Relay_Log_File: relay-bin.000002
14                 Relay_Log_Pos: 398
15         Relay_Master_Log_File: mysql-bin.000036
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: sysbench.sbteset7
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 2338
28               Relay_Log_Space: 776
29               Until_Condition: SQL_BEFORE_GTIDS
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1013306
46                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)
64 
65 (zlm@192.168.1.102 3306)[sysbench]>select count(*) from sbtest7;
66 +----------+
67 | count(*) |
68 +----------+
69 |    10000 |
70 +----------+
71 1 row in set (0.00 sec)
72 
73 //Why the amount of record isn't 9990?

 

 

 

**Execute "change master to" as below.**

Start mysqld on binlog server.

Try to stop and start the slave again.

 1 (zlm@192.168.1.102 3306)[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=190,master_host='xxx';
 2 ERROR 1380 (HY000): Failed initializing relay log position: Could not find first log during relay log initialization
 3 (zlm@192.168.1.102 3306)[(none)]>show master status;
 4 +------------------+----------+--------------+------------------+------------------------------------------------+
 5 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
 6 +------------------+----------+--------------+------------------+------------------------------------------------+
 7 | mysql-bin.000004 |      206 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229 |
 8 +------------------+----------+--------------+------------------+------------------------------------------------+
 9 1 row in set (0.00 sec)
10 
11 (zlm@192.168.1.102 3306)[(none)]>reset master;
12 Query OK, 0 rows affected (0.02 sec)
13 
14 (zlm@192.168.1.102 3306)[(none)]>show master status;
15 +------------------+----------+--------------+------------------+-------------------+
16 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
17 +------------------+----------+--------------+------------------+-------------------+
18 | mysql-bin.000001 |      150 |              |                  |                   |
19 +------------------+----------+--------------+------------------+-------------------+
20 1 row in set (0.00 sec)
21 
22 (zlm@192.168.1.102 3306)[(none)]>set @@global.gtid_purged='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229';
23 Query OK, 0 rows affected (0.01 sec)
24 
25 (zlm@192.168.1.102 3306)[(none)]>show master status;
26 +------------------+----------+--------------+------------------+------------------------------------------------+
27 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
28 +------------------+----------+--------------+------------------+------------------------------------------------+
29 | mysql-bin.000002 |      150 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229 |
30 +------------------+----------+--------------+------------------+------------------------------------------------+
31 1 row in set (0.00 sec)
32 
33 (zlm@192.168.1.102 3306)[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=190,master_host='xxx';
34 ERROR 1380 (HY000): Failed initializing relay log position: Could not find first log during relay log initialization
35 
36 (zlm@192.168.1.102 3306)[(none)]>show slave statusG
37 *************************** 1. row ***************************
38                Slave_IO_State: 
39                   Master_Host: xxx
40                   Master_User: test
41                   Master_Port: 3306
42                 Connect_Retry: 60
43               Master_Log_File: 
44           Read_Master_Log_Pos: 4
45                Relay_Log_File: relay-bin.000043
46                 Relay_Log_Pos: 190
47         Relay_Master_Log_File: 
48              Slave_IO_Running: No
49             Slave_SQL_Running: No
50               Replicate_Do_DB: 
51           Replicate_Ignore_DB: 
52            Replicate_Do_Table: 
53        Replicate_Ignore_Table: 
54       Replicate_Wild_Do_Table: 
55   Replicate_Wild_Ignore_Table: 
56                    Last_Errno: 0
57                    Last_Error: 
58                  Skip_Counter: 0
59           Exec_Master_Log_Pos: 0
60               Relay_Log_Space: 0
61               Until_Condition: None
62                Until_Log_File: 
63                 Until_Log_Pos: 0
64            Master_SSL_Allowed: No
65            Master_SSL_CA_File: 
66            Master_SSL_CA_Path: 
67               Master_SSL_Cert: 
68             Master_SSL_Cipher: 
69                Master_SSL_Key: 
70         Seconds_Behind_Master: NULL
71 Master_SSL_Verify_Server_Cert: No
72                 Last_IO_Errno: 0
73                 Last_IO_Error: 
74                Last_SQL_Errno: 0
75                Last_SQL_Error: 
76   Replicate_Ignore_Server_Ids: 
77              Master_Server_Id: 0
78                   Master_UUID: 
79              Master_Info_File: mysql.slave_master_info
80                     SQL_Delay: 0
81           SQL_Remaining_Delay: NULL
82       Slave_SQL_Running_State: 
83            Master_Retry_Count: 86400
84                   Master_Bind: 
85       Last_IO_Error_Timestamp: 
86      Last_SQL_Error_Timestamp: 
87                Master_SSL_Crl: 
88            Master_SSL_Crlpath: 
89            Retrieved_Gtid_Set: //No relay logs was retrieved here.
90             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229
91                 Auto_Position: 0
92          Replicate_Rewrite_DB: 
93                  Channel_Name: 
94            Master_TLS_Version: 
95 1 row in set (0.00 sec)
 1 [root@zlm3 16:50:00 /data/mysql/mysql3306/logs]
 2 #ps aux|grep mysqld
 3 root      4405  0.0  0.0 112640   960 pts/0    R+   16:50   0:00 grep --color=auto mysqld
 4 
 5 [root@zlm3 16:50:08 /data/mysql/mysql3306/logs]
 6 #sh /root/mysqld.sh
 7 
 8 [root@zlm3 16:50:13 /data/mysql/mysql3306/logs]
 9 #ps aux|grep mysqld
10 mysql     4411 16.5 15.6 498232 159408 pts/0   Rl   16:50   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
11 root      4429  0.0  0.0 112640   960 pts/0    R+   16:50   0:00 grep --color=auto mysqld
12 
13 [root@zlm3 16:51:03 /data/mysql/mysql3306/logs]
14 #ls -l
15 total 11216
16 -rw-r----- 1 root  root      4128 Jul 29 08:42 mysql-bin.000095
17 -rw-r----- 1 root  root       241 Jul 29 08:42 mysql-bin.000096
18 -rw-r----- 1 root  root  11461562 Jul 29 09:07 mysql-bin.000097
19 -rw-r----- 1 mysql mysql      154 Jul 29 16:50 mysql-bin.000098
20 -rw-r----- 1 mysql mysql       44 Jul 29 16:50 mysql-bin.index
21 
22 [root@zlm3 16:51:04 /data/mysql/mysql3306/logs]
23 #cat mysql-bin.index
24 /data/mysql/mysql3306/logs/mysql-bin.000098
 1 (zlm@192.168.1.102 3306)[sysbench]>stop slave;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>start slave;
 5 Query OK, 0 rows affected (0.00 sec)
 6 
 7 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 8 *************************** 1. row ***************************
 9                Slave_IO_State: Waiting for master to send event
10                   Master_Host: 192.168.1.101
11                   Master_User: repl
12                   Master_Port: 3306
13                 Connect_Retry: 60
14               Master_Log_File: mysql-bin.000036
15           Read_Master_Log_Pos: 2523
16                Relay_Log_File: relay-bin.000003
17                 Relay_Log_Pos: 438
18         Relay_Master_Log_File: mysql-bin.000036
19              Slave_IO_Running: Yes
20             Slave_SQL_Running: Yes
21               Replicate_Do_DB: 
22           Replicate_Ignore_DB: 
23            Replicate_Do_Table: sysbench.sbteset7
24        Replicate_Ignore_Table: 
25       Replicate_Wild_Do_Table: 
26   Replicate_Wild_Ignore_Table: 
27                    Last_Errno: 0
28                    Last_Error: 
29                  Skip_Counter: 0
30           Exec_Master_Log_Pos: 2523
31               Relay_Log_Space: 1064
32               Until_Condition: None
33                Until_Log_File: 
34                 Until_Log_Pos: 0
35            Master_SSL_Allowed: No
36            Master_SSL_CA_File: 
37            Master_SSL_CA_Path: 
38               Master_SSL_Cert: 
39             Master_SSL_Cipher: 
40                Master_SSL_Key: 
41         Seconds_Behind_Master: 0
42 Master_SSL_Verify_Server_Cert: No
43                 Last_IO_Errno: 0
44                 Last_IO_Error: 
45                Last_SQL_Errno: 0
46                Last_SQL_Error: 
47   Replicate_Ignore_Server_Ids: 
48              Master_Server_Id: 1013306
49                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
50              Master_Info_File: mysql.slave_master_info
51                     SQL_Delay: 0
52           SQL_Remaining_Delay: NULL
53       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
54            Master_Retry_Count: 86400
55                   Master_Bind: 
56       Last_IO_Error_Timestamp: 
57      Last_SQL_Error_Timestamp: 
58                Master_SSL_Crl: 
59            Master_SSL_Crlpath: 
60            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222
61             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730222
62                 Auto_Position: 1
63          Replicate_Rewrite_DB: 
64                  Channel_Name: 
65            Master_TLS_Version: 
66 1 row in set (0.00 sec)
67 
68 (zlm@192.168.1.102 3306)[sysbench]>show tables;
69 +--------------------+
70 | Tables_in_sysbench |
71 +--------------------+
72 | sbtest7            |
73 +--------------------+
74 1 row in set (0.00 sec)
75 
76 (zlm@192.168.1.102 3306)[sysbench]>select count(*) from sbtest7;
77 +----------+
78 | count(*) |
79 +----------+
80 |    10000 |
81 +----------+
82 1 row in set (0.01 sec)
83 
84 //Even though it has executed the newest GTID "uuid:3730222",the amount of record is unchanged.

 

 

 

    I was stuck again,faint!T_T...

Disguise the binlog server is a fake master.

Try to analyze the binlog on slave zlm3.

 

 1 [root@zlm3 16:52:59 /data/mysql/mysql3306/logs]
 2 #rm -f mysql-bin.00009*
 3 [1]+  Done                    mysqlbinlog -R --raw -h192.168.56.100 -urepl -prepl4slave -P3306 --stop-never mysql-bin.000098  (wd: /data)
 4 (wd now: /data/mysql/mysql3306/logs)
 5 
 6 [root@zlm3 17:00:06 /data/mysql/mysql3306/logs]
 7 #ls -l
 8 total 4
 9 -rw-r----- 1 mysql mysql 44 Jul 29 16:50 mysql-bin.index
10 
11 [root@zlm3 17:00:27 /data/mysql/mysql3306/logs]
12 #ps aux|grep mysqlbinlog
13 root      4475  0.0  0.0 112640   960 pts/0    R+   17:00   0:00 grep --color=auto mysqlbinlog
14 
15 [root@zlm3 17:00:38 /data/mysql/mysql3306/logs]
16 #cp /data/mysql-bin* .
17 
18 [root@zlm3 17:02:48 /data/mysql/mysql3306/logs]
19 #ls -l
20 -rw-r----- 1 root  root  410 Jul 29 17:01 mysql-bin.000098
21 -rw-r----- 1 root  root  241 Jul 29 17:01 mysql-bin.000099
22 -rw-r----- 1 root  root  951 Jul 29 17:01 mysql-bin.000100
23 -rw-r----- 1 root  root  177 Jul 29 17:03 mysql-bin.index
24 
25 [root@zlm3 17:02:54 /data/mysql/mysql3306/logs]
26 #ls -1 | awk '{print i$0}' i=`pwd`'/'|grep mysql-bin.000 > mysql-bin.index
27 
28 [root@zlm3 17:03:06 /data/mysql/mysql3306/logs]
29 #cat mysql-bin.index 
30 /data/mysql/mysql3306/logs/mysql-bin.000098
31 /data/mysql/mysql3306/logs/mysql-bin.000099
32 /data/mysql/mysql3306/logs/mysql-bin.000100
33 
34 [root@zlm3 17:16:14 /data/mysql/mysql3306/logs]
35 #chown mysql.mysql *
36 
37 [root@zlm3 17:17:23 /data/mysql/mysql3306/logs]
38 #ls -l
39 total 16
40 -rw-r----- 1 mysql mysql 410 Jul 29 17:01 mysql-bin.000098
41 -rw-r----- 1 mysql mysql 241 Jul 29 17:01 mysql-bin.000099
42 -rw-r----- 1 mysql mysql 951 Jul 29 17:01 mysql-bin.000100
43 -rw-r----- 1 mysql mysql 177 Jul 29 17:03 mysql-bin.index
 1 [root@zlm3 09:06:44 /data/mysql/mysql3306/data]
 2 #cd ../logs
 3 
 4 [root@zlm3 09:06:46 /data/mysql/mysql3306/logs]
 5 #ls -l
 6 total 16
 7 -rw-r----- 1 mysql mysql  169 Jul 27 07:32 mysql-bin.000001
 8 -rw-r----- 1 mysql mysql 1473 Jul 27 08:16 mysql-bin.000002
 9 -rw-r----- 1 mysql mysql  396 Jul 27 09:02 mysql-bin.000003
10 -rw-r----- 1 mysql mysql  132 Jul 27 08:22 mysql-bin.index
11 
12 [root@zlm3 09:06:47 /data/mysql/mysql3306/logs]
13 #mysqlbinlog -v --base64-output=decode-rows mysql-bin.000003 > 3.log
14 
15 [root@zlm3 09:07:26 /data/mysql/mysql3306/logs]
16 #cat 3.log
17 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
18 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
19 DELIMITER /*!*/;
20 # at 4
21 #180727  8:22:26 server id 1023306  end_log_pos 123     Start: binlog v 4, server v 5.7.21-log created 180727  8:22:26 at startup
22 # Warning: this binlog is either in use or was not closed properly.
23 ROLLBACK/*!*/; //It shows that transactions in this binlog file have been rolled back!!!
24 # at 123
25 #180727  8:22:26 server id 1023306  end_log_pos 190     Previous-GTIDs
26 # 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730215-3730221 //The rolled back GTID is from 3730215 to 3730221.
27 # at 190
28 #180727  8:01:50 server id 1013306  end_log_pos 251     GTID    last_committed=0    sequence_number=1    rbr_only=no
29 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222'/*!*/;
30 # at 251
31 #180727  8:01:50 server id 1013306  end_log_pos 323     Query    thread_id=21    exec_time=3665    error_code=0
32 SET TIMESTAMP=1532671310/*!*/;
33 SET @@session.pseudo_thread_id=21/*!*/;
34 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
35 SET @@session.sql_mode=1436549152/*!*/;
36 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
37 /*!C utf8 *//*!*/;
38 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
39 SET @@session.lc_time_names=0/*!*/;
40 SET @@session.collation_database=DEFAULT/*!*/;
41 BEGIN
42 /*!*/;
43 # at 323
44 #180727  8:01:50 server id 1013306  end_log_pos 396     Query    thread_id=21    exec_time=3665    error_code=0
45 SET TIMESTAMP=1532671310/*!*/;
46 COMMIT
47 /*!*/;
48 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
49 DELIMITER ;
50 # End of log file
51 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
52 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Supplemented on August 1.

 

 

 

Change the server-uuid of binlog server to be equal with master.

    Okay,I didn't get my supposed consequences in the above test this time.In my opinion,the miss dropped table "sbtest7" should have been recovered to the state of "9990" instead of "10000" what really astonished me.

    After discussing with my classmate Shuaibing Zhang,I found out that the reason why I got failure above was due to not executing "reset slave all;".Therefore,it meantioned that "Could not find the first log..." when I Executed "change master to ... ".

 1 [root@zlm3 18:01:50 /data/mysql/mysql3306]
 2 #cd ../data
 3 
 4 [root@zlm3 18:01:53 /data/mysql/mysql3306/data]
 5 #cat auto.cnf
 6 [auto]
 7 server-uuid=e00ef9f5-6c4b-11e8-8389-080027de0e0e
 8 
 9 [root@zlm3 18:04:11 /data/mysql/mysql3306/data]
10 #vim auto.cnf
11 
12 [root@zlm3 18:04:26 /data/mysql/mysql3306/data]
13 #cat auto.cnf
14 [auto]
15 server-uuid=2a4b3562-2ab6-11e8-be7a-080027de0e0e

    The transaction of deleting 10 records befor dropping "sbtest7" ,together with those other transactions in mysql-bin.000035,was rolled back.Even if the slave had executed the newest GTID "uuid:3720222",the incremtental modifications on the table couldn't be rescued.

 

 

    I guess the result is due to that the redo log files in backup set didn't contain the transactions information,thus,it chose to roll back the transactions.

Execute "change master to ... "

Execute "change master to ... " on zlm2.

 

 1 (zlm@192.168.1.102 3306)[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=190,master_host='xxx';
 2 Query OK, 0 rows affected (0.01 sec)
 3 
 4 (zlm@192.168.1.102 3306)[(none)]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: 
 7                   Master_Host: xxx
 8                   Master_User: 
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: 
12           Read_Master_Log_Pos: 4
13                Relay_Log_File: relay-bin.000043
14                 Relay_Log_Pos: 190
15         Relay_Master_Log_File: 
16              Slave_IO_Running: No
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 0
28               Relay_Log_Space: 954838
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 0
46                   Master_UUID: 
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229
59                 Auto_Position: 0
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)
1 zlm@192.168.56.101:3306 [sysbench]>change master to 
2     -> master_host='192.168.56.102',
3     -> master_port=3306,
4     -> master_user='repl',
5     -> master_password='repl4slave',
6     -> master_auto_position=1;
7 Query OK, 0 rows affected, 2 warnings (0.01 sec)

 

 

Analyze the relay-bin file to find out the until postion before dropping operation.

Start IO_Thread on zlm2.

 1 [root@zlm3 04:11:50 /data/mysql/mysql3306/data]
 2 #cd /data/backup/
 3 
 4 [root@zlm3 04:12:17 /data/backup]
 5 #mysqlbinlog --base64-output=decode-rows relay-bin.000043 > 43.log
 6 
 7 [root@zlm3 04:12:47 /data/backup]
 8 #tail -20 43.log
 9 #180731  6:34:54 server id 1013306  end_log_pos 954224     Delete_rows: table id 222 flags: STMT_END_F
10 # at 954224
11 #180731  6:34:54 server id 1013306  end_log_pos 954251     Xid = 58
12 COMMIT/*!*/;
13 # at 954251
14 #180731  6:35:09 server id 1013306  end_log_pos 954312     GTID    last_committed=2    sequence_number=3    rbr_only=no
15 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730232'/*!*/;
16 # at 954312
17 #180731  6:35:09 server id 1013306  end_log_pos 954436     Query    thread_id=13    exec_time=0    error_code=0
18 use `sysbench`/*!*/;
19 SET TIMESTAMP=1533011709/*!*/;
20 DROP TABLE `sbtest5` /* generated by server */ //Here's the dropping operation.Therefore,the util position we need is "954251" which just below the "COMMIT/*!*/;"
21 /*!*/;
22 # at 954436
23 #180731  6:35:29 server id 1013306  end_log_pos 954479     Rotate to mysql-bin.000044  pos: 4
24 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
25 DELIMITER ;
26 # End of log file
27 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
28 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
 1 [root@zlm2 17:12:39 /data/backup/2018-07-29_16-32-33]
 2 #cat xtrabackup_binlog_info
 3 mysql-bin.000100    476    2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
 4 
 5 zlm@192.168.56.101:3306 [sysbench]>reset master;
 6 Query OK, 0 rows affected (0.00 sec)
 7 
 8 zlm@192.168.56.101:3306 [sysbench]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';
 9 Query OK, 0 rows affected (0.00 sec)
10 
11 zlm@192.168.56.101:3306 [sysbench]>start slave io_thread;
12 Query OK, 0 rows affected (0.00 sec)
13 
14 zlm@192.168.56.101:3306 [sysbench]>show slave statusG
15 *************************** 1. row ***************************
16                Slave_IO_State: 
17                   Master_Host: 192.168.56.102
18                   Master_User: repl
19                   Master_Port: 3306
20                 Connect_Retry: 60
21               Master_Log_File: 
22           Read_Master_Log_Pos: 4
23                Relay_Log_File: relay-bin.000001
24                 Relay_Log_Pos: 4
25         Relay_Master_Log_File: 
26              Slave_IO_Running: No
27             Slave_SQL_Running: No
28               Replicate_Do_DB: 
29           Replicate_Ignore_DB: 
30            Replicate_Do_Table: 
31        Replicate_Ignore_Table: 
32       Replicate_Wild_Do_Table: 
33   Replicate_Wild_Ignore_Table: 
34                    Last_Errno: 0
35                    Last_Error: 
36                  Skip_Counter: 0
37           Exec_Master_Log_Pos: 0
38               Relay_Log_Space: 154
39               Until_Condition: None
40                Until_Log_File: 
41                 Until_Log_Pos: 0
42            Master_SSL_Allowed: No
43            Master_SSL_CA_File: 
44            Master_SSL_CA_Path: 
45               Master_SSL_Cert: 
46             Master_SSL_Cipher: 
47                Master_SSL_Key: 
48         Seconds_Behind_Master: NULL
49 Master_SSL_Verify_Server_Cert: No
50                 Last_IO_Errno: 1236
51                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
52                Last_SQL_Errno: 0
53                Last_SQL_Error: 
54   Replicate_Ignore_Server_Ids: 
55              Master_Server_Id: 1023306
56                   Master_UUID: e00ef9f5-6c4b-11e8-8389-080027de0e0e
57              Master_Info_File: /data/mysql/mysql3306/data/master.info
58                     SQL_Delay: 0
59           SQL_Remaining_Delay: NULL
60       Slave_SQL_Running_State: 
61            Master_Retry_Count: 86400
62                   Master_Bind: 
63       Last_IO_Error_Timestamp: 180729 18:00:08
64      Last_SQL_Error_Timestamp: 
65                Master_SSL_Crl: 
66            Master_SSL_Crlpath: 
67            Retrieved_Gtid_Set: 
68             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
69                 Auto_Position: 1
70          Replicate_Rewrite_DB: 
71                  Channel_Name: 
72            Master_TLS_Version: 
73 1 row in set (0.00 sec)

 

 

Execute start slave sql_thread util clause.

    I was stucked here.Slave IO_Thread could not be started with 1236,although I had replaced the server-uuid and set the variable of gtid_purged.Only if the slave get binlog which contains the incremental data and start the slave SQL_Thread.We can get back our dropped table together with the modification on it.

 1 (zlm@192.168.1.102 3306)[(none)]>start slave sql_thread until relay_log_file='relay-bin.000043',relay_log_pos=954251;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[(none)]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: 
 7                   Master_Host: xxx
 8                   Master_User: 
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: 
12           Read_Master_Log_Pos: 4
13                Relay_Log_File: relay-bin.000043
14                 Relay_Log_Pos: 954251
15         Relay_Master_Log_File: 
16              Slave_IO_Running: No
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 954251
28               Relay_Log_Space: 954838
29               Until_Condition: Relay
30                Until_Log_File: relay-bin.000043
31                 Until_Log_Pos: 954251
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 0
46                   Master_UUID: 
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730231
59                 Auto_Position: 0
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)
64 
65 (zlm@192.168.1.102 3306)[(none)]>select count(*) from sysbench.sbtest5;
66 +----------+
67 | count(*) |
68 +----------+
69 |     5000 |
70 +----------+
71 1 row in set (0.01 sec)

 

 

Supplemented on July 31:

Summary

 

 

    The previous fault of 1236 was due to the wrong configuration of variable "gtid_purged".There were two individual gtids generated by the other masters with different uuid as bellow:

The simply description of rescuing data with relay log method is shown below:

 

The precondition is that the master has a up-to-date full backup.(Xtrabackup or mysqldump)

永利游戏平台 1

1. Copy all the binlog files to another server which has a newly initialized instance.

 

2. Restore the backup on new instance and check data is restored normally.

 

3. Execute "reset slave all;" to clear the original replication information(restored from master).**

    Therefore,the configuration command should be executed like this:

4. Reconfigure those binlog files with "relay-bin.xxxxxx" format together with relay-bin.index file.

 

*5. Copy **those relay logs** to the proper datadir and c*hange the ownership and group of them.**

1 set @@global.gtid_purged='27af30ca-6800-11e8-ad7e-080027de0e0e:1,
2 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694,
3 e00ef9f5-6c4b-11e8-8389-080027de0e0e:1';

6. Execute "change master to ... " with "relay_log_file" and "relay_log_pos".

 

7. Execute "change replication filter ... " if you're supposed to merely restore a single table.(optional)

    not merely set it like this:

8. Execute "start slave sql_thread until ... " to restore data until the position you need.(both relay_log_pos and sql_before_gtids is okay)

 

 

1 set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';

 

    Eventually,In order to make the gtid more clear,I executed "reset master" on binlog server(zlm3) and then copyed those necessary binlogs which were transfered from original master.Further more,I changed back the uuid of zlm3.Because I found that there's no need to change it at all.

    The new procedure of demonstration was shown below:

 

  1 zlm@192.168.56.101:3306 [(none)]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';
  2 Query OK, 0 rows affected (0.00 sec)
  3 
  4 zlm@192.168.56.101:3306 [(none)]>change master to master_host='192.168.56.102',master_port=3306,master_user='repl',master_password='repl4slave',master_auto_position=1;
  5 Query OK, 0 rows affected, 2 warnings (0.01 sec)
  6 
  7 zlm@192.168.56.101:3306 [(none)]>start slave io_thread;
  8 Query OK, 0 rows affected (0.00 sec)
  9 
 10 zlm@192.168.56.101:3306 [(none)]>show slave statusG
 11 *************************** 1. row ***************************
 12                Slave_IO_State: Waiting for master to send event
 13                   Master_Host: 192.168.56.102
 14                   Master_User: repl
 15                   Master_Port: 3306
 16                 Connect_Retry: 60
 17               Master_Log_File: mysql-bin.000105
 18           Read_Master_Log_Pos: 194
 19                Relay_Log_File: relay-bin.000001
 20                 Relay_Log_Pos: 4
 21         Relay_Master_Log_File: 
 22              Slave_IO_Running: Yes
 23             Slave_SQL_Running: No
 24               Replicate_Do_DB: 
 25           Replicate_Ignore_DB: 
 26            Replicate_Do_Table: 
 27        Replicate_Ignore_Table: 
 28       Replicate_Wild_Do_Table: 
 29   Replicate_Wild_Ignore_Table: 
 30                    Last_Errno: 0
 31                    Last_Error: 
 32                  Skip_Counter: 0
 33           Exec_Master_Log_Pos: 0
 34               Relay_Log_Space: 3360
 35               Until_Condition: None
 36                Until_Log_File: 
 37                 Until_Log_Pos: 0
 38            Master_SSL_Allowed: No
 39            Master_SSL_CA_File: 
 40            Master_SSL_CA_Path: 
 41               Master_SSL_Cert: 
 42             Master_SSL_Cipher: 
 43                Master_SSL_Key: 
 44         Seconds_Behind_Master: NULL
 45 Master_SSL_Verify_Server_Cert: No
 46                 Last_IO_Errno: 0
 47                 Last_IO_Error: 
 48                Last_SQL_Errno: 0
 49                Last_SQL_Error: 
 50   Replicate_Ignore_Server_Ids: 
 51              Master_Server_Id: 1023306
 52                   Master_UUID: e00ef9f5-6c4b-11e8-8389-080027de0e0e //I changed back the uuid of zlm3 to its previous value.
 53              Master_Info_File: /data/mysql/mysql3306/data/master.info
 54                     SQL_Delay: 0
 55           SQL_Remaining_Delay: NULL
 56       Slave_SQL_Running_State: 
 57            Master_Retry_Count: 86400
 58                   Master_Bind: 
 59       Last_IO_Error_Timestamp: 
 60      Last_SQL_Error_Timestamp: 
 61                Master_SSL_Crl: 
 62            Master_SSL_Crlpath: 
 63            Retrieved_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715695-12715696 //New gtid was received.
 64             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
 65                 Auto_Position: 1
 66          Replicate_Rewrite_DB: 
 67                  Channel_Name: 
 68            Master_TLS_Version: 
 69 1 row in set (0.00 sec)
 70 
 71 zlm@192.168.56.101:3306 [sysbench]>start slave sql_thread until sql_before_gtids='2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715696';
 72 Query OK, 0 rows affected (0.00 sec)
 73 
 74 zlm@192.168.56.101:3306 [sysbench]>show slave statusG
 75 *************************** 1. row ***************************
 76                Slave_IO_State: Waiting for master to send event
 77                   Master_Host: 192.168.56.102
 78                   Master_User: repl
 79                   Master_Port: 3306
 80                 Connect_Retry: 60
 81               Master_Log_File: mysql-bin.000105
 82           Read_Master_Log_Pos: 194
 83                Relay_Log_File: relay-bin.000002
 84                 Relay_Log_Pos: 696
 85         Relay_Master_Log_File: mysql-bin.000100
 86              Slave_IO_Running: Yes
 87             Slave_SQL_Running: No
 88               Replicate_Do_DB: 
 89           Replicate_Ignore_DB: 
 90            Replicate_Do_Table: 
 91        Replicate_Ignore_Table: 
 92       Replicate_Wild_Do_Table: 
 93   Replicate_Wild_Ignore_Table: 
 94                    Last_Errno: 0
 95                    Last_Error: 
 96                  Skip_Counter: 0
 97           Exec_Master_Log_Pos: 758
 98               Relay_Log_Space: 3360
 99               Until_Condition: SQL_BEFORE_GTIDS
100                Until_Log_File: 
101                 Until_Log_Pos: 0
102            Master_SSL_Allowed: No
103            Master_SSL_CA_File: 
104            Master_SSL_CA_Path: 
105               Master_SSL_Cert: 
106             Master_SSL_Cipher: 
107                Master_SSL_Key: 
108         Seconds_Behind_Master: NULL
109 Master_SSL_Verify_Server_Cert: No
110                 Last_IO_Errno: 0
111                 Last_IO_Error: 
112                Last_SQL_Errno: 0
113                Last_SQL_Error: 
114   Replicate_Ignore_Server_Ids: 
115              Master_Server_Id: 1023306
116                   Master_UUID: e00ef9f5-6c4b-11e8-8389-080027de0e0e
117              Master_Info_File: /data/mysql/mysql3306/data/master.info
118                     SQL_Delay: 0
119           SQL_Remaining_Delay: NULL
120       Slave_SQL_Running_State: 
121            Master_Retry_Count: 86400
122                   Master_Bind: 
123       Last_IO_Error_Timestamp: 
124      Last_SQL_Error_Timestamp: 
125                Master_SSL_Crl: 
126            Master_SSL_Crlpath: 
127            Retrieved_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715695-12715696
128             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715695
129                 Auto_Position: 1
130          Replicate_Rewrite_DB: 
131                  Channel_Name: 
132            Master_TLS_Version: 
133 1 row in set (0.00 sec)
134 
135 zlm@192.168.56.101:3306 [sysbench]>select * from sbtest6;
136 +----+---+---+-----+
137 | id | k | c | pad |
138 +----+---+---+-----+
139 |  1 | 1 | a | b   |
140 |  2 | 2 | c | d   |
141 +----+---+---+-----+
142 2 rows in set (0.00 sec)
143 
144 //The incremental data in "sbtest6" had been come again.It was okay this time.

  

Summary

  • Binlog server act as a master in this experiment.It can prevent data loss such as miss opeartion of dropping tables whenever the master crashes.
  • We can implement as many binlog servers as possible to continuously acquire the binlogs on different masters.
  • If the only purpose is to get back the dropping data,replication filter of "replicate_do_table" is not necessary.
  • The modification of "sever-uuid" is also neglectable operation.Each gtid contains a differten uuid is completely acceptable.It won't influence our replication except for a little bit complex in output.

 

本文由永利集团登录网址发布于永利集团登录网址,转载请注明出处:恢复误删除表黑科技之relay log大法

关键词:

  • 上一篇:没有了
  • 下一篇:没有了