基于Xtrabackup备份集来恢复某个误删除的表（drop）
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
Precedure
Step 1: Destroy the table.
Generate a backup with Xtrabackup first.
扫码关注我们
微信号：SRE实战
拒绝背锅 运筹帷幄
| 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 |
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)
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。 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 status\G 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 status\G 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 status\G 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 status\G 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 status\G 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.
更多精彩
