基于Xtrabackup备份集来恢复某个误删除的表

 

Preface

 

    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.

 

Framework

 

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

 

Precedure

 

Step 1: Destroy the table.

 

Generate a backup with Xtrabackup first.

 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!

 

**Continue to execute dml operation.**

 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)

 

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

 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)

 

Step 2: Begin to restore backup set.

 

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

 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 #

 

Prepare to restore the table "sbtest7".

 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

 

Apply the redo logfile.

 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)

 

Shutdown the instance on zlm3 and copy back the datafiles

 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!

 

Start the instance and check the table "sbtest7".

 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)

 

Step 3: Implement a slave with replication filter option.

 

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

 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)

 

Start slave IO_Thread.

 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)

 

Change replication filter "replicate_do_table" option.

 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)

 

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

 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*/;

 

Start slave SQL_Thread with until_option clause.

 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?

 

Try to stop and start the slave again.

 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.

 

Try to analyze the binlog on slave zlm3.

 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*/;

 

    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.

    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.

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:基于Xtrabackup备份集来恢复某个误删除的表

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。