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 th ...
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 |
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