Preface In my previous blogs,I've demonstrated several mothods of how to rescue a dropped table(or truncated table as well). full mysqldump backup + b ...
Preface In my previous blogs,I've demonstrated several mothods of how to rescue a dropped table(or truncated table as well).
- full mysqldump backup + binlog on master(master was normally running)
- full Xtrabackup backup + binlog on master(master was normally running)
- full Xtrabackup backup + binlog on binlog server( binlog server had acquired binlogs before master crashed.)
1 (zlm@192.168.1.101 3306)[sysbench]>show tables; 2 ERROR 2006 (HY000): MySQL server has gone away 3 No connection. Trying to reconnect... 4 Connection id: 4 5 Current database: sysbench 6 7 +--------------------+ 8 | Tables_in_sysbench | 9 +--------------------+ 10 | sbtest1 | 11 | sbtest2 | 12 | sbtest3 | 13 | sbtest4 | 14 | sbtest5 | 15 +--------------------+ 16 5 rows in set (0.00 sec) 17 18 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest5; 19 +----------+ 20 | count(*) | 21 +----------+ 22 | 10000 | 23 +----------+ 24 1 row in set (0.01 sec)
Generate a full Xtrabackup of master.
1 [root@zlm2 06:28:44 /data/mysql/mysql3306/data] 2 #innobackupex --default-file=/data/mysql/mysql3306/my.cnf --host=localhost -uroot -pPassw0rd /data/backup 3 xtrabackup: recognized server arguments: 4 xtrabackup: recognized client arguments: 5 180731 06:29:03 innobackupex: Starting the backup operation 6 ... //Omitted. 7 8 180731 06:29:31 Executing UNLOCK TABLES 9 180731 06:29:31 All tables unlocked 10 180731 06:29:31 [00] Copying ib_buffer_pool to /data/backup/2018-07-31_06-29-03/ib_buffer_pool 11 180731 06:29:31 [00] ...done 12 180731 06:29:31 Backup created in directory '/data/backup/2018-07-31_06-29-03/' 13 MySQL binlog position: filename 'mysql-bin.000043', position '190', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229' 14 180731 06:29:31 [00] Writing /data/backup/2018-07-31_06-29-03/backup-my.cnf 15 180731 06:29:31 [00] ...done 16 180731 06:29:31 [00] Writing /data/backup/2018-07-31_06-29-03/xtrabackup_info 17 180731 06:29:31 [00] ...done 18 xtrabackup: Transaction log of lsn (10141400402) to (10141400411) was copied. 19 180731 06:29:31 completed OK!
Continue to executing some dml operations on the target table and then kill the mysqld.
1 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest5 limit 2000; 2 Query OK, 2000 rows affected (0.10 sec) 3 4 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest5 limit 3000; 5 Query OK, 3000 rows affected (0.07 sec) 6 7 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest5; 8 +----------+ 9 | count(*) | 10 +----------+ 11 | 5000 | 12 +----------+ 13 1 row in set (0.00 sec) 14 15 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest5; 16 Query OK, 0 rows affected (0.01 sec) 17 18 (zlm@192.168.1.101 3306)[sysbench]>flush logs; 19 Query OK, 0 rows affected (0.02 sec) 20 21 (zlm@192.168.1.101 3306)[sysbench]>show master status; 22 +------------------+----------+--------------+------------------+------------------------------------------------+ 23 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 24 +------------------+----------+--------------+------------------+------------------------------------------------+ 25 | mysql-bin.000044 | 190 | | | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730232 | 26 +------------------+----------+--------------+------------------+------------------------------------------------+ 27 1 row in set (0.00 sec) 28 29 [root@zlm2 06:38:14 ~] 30 #pkill mysqld 31 32 [root@zlm2 06:38:18 ~] 33 #ps aux|grep mysqld 34 root 4050 0.0 0.0 112640 956 pts/0 R+ 06:38 0:00 grep --color=auto mysqld
Scp the Xtrabackup backup to another server zlm3 with newly initialized instance
1 [root@zlm2 06:43:42 ~] 2 #scp -r /data/backup/2018-07-31_06-29-03/ zlm3:/data/backup 3 root@zlm3's password: 4 xtrabackup_logfile 100% 2560 2.5KB/s 00:00 5 ibdata1 100% 100MB 50.0MB/s 00:02 6 plugin.ibd 100% 96KB 96.0KB/s 00:00 7 servers.ibd 100% 96KB 96.0KB/s 00:00 8 ... //Omitted.
Step 2. Rescue
Restore the backup on zlm3.
1 [root@zlm3 06:47:52 ~] 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --apply-log /data/backup/2018-07-31_06-29-03/ 3 ... //Omitted. 4 5 [root@zlm3 06:46:39 ~] 6 #cd /data/mysql/mysql3306/data/ 7 8 [root@zlm3 06:46:44 /data/mysql/mysql3306/data] 9 #ls -l 10 total 409716 11 -rw-r----- 1 mysql mysql 56 Jul 27 11:15 auto.cnf 12 -rw-r----- 1 mysql mysql 19677 Jul 27 11:25 error.log 13 -rw-r----- 1 mysql mysql 2005 Jul 27 11:25 ib_buffer_pool 14 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:25 ibdata1 15 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:25 ib_logfile0 16 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:14 ib_logfile1 17 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:14 ib_logfile2 18 drwxr-x--- 2 mysql mysql 4096 Jul 27 11:15 mysql 19 drwxr-x--- 2 mysql mysql 8192 Jul 27 11:15 performance_schema 20 -rw-r----- 1 mysql mysql 276 Jul 27 11:18 relay-bin.000003 21 -rw-r----- 1 mysql mysql 2771 Jul 27 11:25 relay-bin.000004 22 -rw-r----- 1 mysql mysql 292 Jul 27 11:25 relay-bin.000005 23 -rw-r----- 1 mysql mysql 454 Jul 27 11:25 relay-bin.000006 24 -rw-r----- 1 mysql mysql 344 Jul 27 11:25 relay-bin.000007 25 -rw-r----- 1 mysql mysql 169 Jul 27 11:25 relay-bin-group_replication_applier.000001 26 -rw-r----- 1 mysql mysql 45 Jul 27 11:15 relay-bin-group_replication_applier.index 27 -rw-r----- 1 mysql mysql 169 Jul 27 11:25 relay-bin-group_replication_recovery.000001 28 -rw-r----- 1 mysql mysql 46 Jul 27 11:15 relay-bin-group_replication_recovery.index 29 -rw-r----- 1 mysql mysql 95 Jul 27 11:25 relay-bin.index 30 -rw-r----- 1 mysql mysql 334 Jul 27 11:25 slow.log 31 drwxr-x--- 2 mysql mysql 8192 Jul 27 11:15 sys 32 drwxr-x--- 2 mysql mysql 4096 Jul 27 11:15 sysbench 33 -rw-r----- 1 mysql mysql 24 Jul 27 11:15 xtrabackup_binlog_pos_innodb 34 -rw-r----- 1 mysql mysql 587 Jul 27 11:15 xtrabackup_info 35 -rw-r----- 1 mysql mysql 1 Jul 27 11:15 xtrabackup_master_key_id 36 37 [root@zlm3 06:46:45 /data/mysql/mysql3306/data] 38 #rm -rf * 39 40 [root@zlm3 06:46:50 /data/mysql/mysql3306/data] 41 #ps aux|grep mysqld 42 root 3913 0.0 0.0 112640 960 pts/1 R+ 06:50 0:00 grep --color=auto mysqld 43 44 [root@zlm3 06:51:00 ~] 45 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-31_06-29-03/ 46 ... //Omitted. 47 48 180731 06:51:36 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool 49 180731 06:51:36 [01] ...done 50 180731 06:51:36 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info 51 180731 06:51:36 [01] ...done 52 180731 06:51:36 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb 53 180731 06:51:36 [01] ...done 54 180731 06:51:36 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id 55 180731 06:51:36 [01] ...done 56 180731 06:51:36 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1 57 180731 06:51:36 [01] ...done 58 180731 06:51:36 completed OK! 59 60 [root@zlm3 06:50:14 /data/mysql/mysql3306/data] 61 #ls -l 62 total 421936 63 -rw-r----- 1 root root 1017 Jul 31 06:51 ib_buffer_pool 64 -rw-r----- 1 root root 104857600 Jul 31 06:51 ibdata1 65 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile0 66 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile1 67 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile2 68 -rw-r----- 1 root root 12582912 Jul 31 06:51 ibtmp1 69 drwxr-x--- 2 root root 4096 Jul 31 06:51 mysql 70 drwxr-x--- 2 root root 8192 Jul 31 06:51 performance_schema 71 drwxr-x--- 2 root root 8192 Jul 31 06:51 sys 72 drwxr-x--- 2 root root 4096 Jul 31 06:51 sysbench 73 -rw-r----- 1 root root 22 Jul 31 06:51 xtrabackup_binlog_pos_innodb 74 -rw-r----- 1 root root 600 Jul 31 06:51 xtrabackup_info 75 -rw-r----- 1 root root 1 Jul 31 06:51 xtrabackup_master_key_id 76 drwxr-x--- 2 root root 120 Jul 31 06:51 zlm 77 78 [root@zlm3 06:53:49 /data/mysql/mysql3306/data] 79 #chown -R mysql.mysql *
Startup the MySQL instance on zlm3.
1 [root@zlm3 06:53:57 /data/mysql/mysql3306/data] 2 #sh /root/mysqld.sh 3 4 [root@zlm3 06:55:16 /data/mysql/mysql3306/data] 5 #ps aux|grep mysqld 6 mysql 3940 20.0 17.7 1110004 180300 pts/1 Sl 06:55 0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf 7 root 3975 0.0 0.0 112640 956 pts/1 R+ 06:55 0:00 grep --color=auto mysqld 8 9 [root@zlm3 06:55:44 /data/mysql/mysql3306/data] 10 #mysql 11 Welcome to the MySQL monitor. Commands end with ; or \g. 12 Your MySQL connection id is 3 13 Server version: 5.7.21-log MySQL Community Server (GPL) 14 15 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 16 17 Oracle is a registered trademark of Oracle Corporation and/or its 18 affiliates. Other names may be trademarks of their respective 19 owners. 20 21 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 22 23 (zlm@192.168.1.102 3306)[(none)]>select count(*) from sysbench.sbtest5; 24 +----------+ 25 | count(*) | 26 +----------+ 27 | 10000 | 28 +----------+ 29 1 row in set (0.02 sec)
The data in Xtrabackup of master has been restored on zlm3. Notice,it doesn't contain the operations of deletion 5000 rows. Firstly,I supposed that the mysqld has crashed and it can never start again. Secondly,I don't have binlog server any more this time.Is there any other way to restore the dropping table and guarantee the change will not lose on it?How can we restore the data safely and simply?Surely there is. Even thought the mysqld process is down on master.I still can get the binlog files on it.How about change the master binlog files into relay log files and apply them on zlm3?Let's have a try. Step 3. Special technique
Make sure which binlogs we need and copy them to zlm3.
1 [root@zlm3 07:23:49 /data/backup/2018-07-31_06-29-03] 2 #cat xtrabackup_binlog_info 3 mysql-bin.000043 190 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229 //It means that the binlog we need should begin from mysql-bin.000043. 4 5 [root@zlm2 06:38:09 /data/mysql/mysql3306/data] 6 #cd ../logs 7 8 [root@zlm2 07:20:00 /data/mysql/mysql3306/logs] 9 #ls -l 10 total 64848 11 -rw-r----- 1 mysql mysql 233 Jul 23 11:21 mysql-bin.000023 12 -rw-r----- 1 mysql mysql 209 Jul 23 11:21 mysql-bin.000024 13 -rw-r----- 1 mysql mysql 233 Jul 24 11:27 mysql-bin.000025 14 -rw-r----- 1 mysql mysql 209 Jul 24 11:27 mysql-bin.000026 15 -rw-r----- 1 mysql mysql 233 Jul 25 06:12 mysql-bin.000027 16 -rw-r----- 1 mysql mysql 209 Jul 25 06:12 mysql-bin.000028 17 -rw-r----- 1 mysql mysql 5727732 Jul 25 11:33 mysql-bin.000029 18 -rw-r----- 1 mysql mysql 209 Jul 25 11:33 mysql-bin.000030 19 -rw-r----- 1 mysql mysql 58202858 Jul 26 09:12 mysql-bin.000031 20 -rw-r----- 1 mysql mysql 477279 Jul 26 09:13 mysql-bin.000032 21 -rw-r----- 1 mysql mysql 383 Jul 26 11:21 mysql-bin.000033 22 -rw-r----- 1 mysql mysql 209 Jul 26 11:21 mysql-bin.000034 23 -rw-r----- 1 mysql mysql 954930 Jul 27 07:59 mysql-bin.000035 24 -rw-r----- 1 mysql mysql 2566 Jul 27 11:25 mysql-bin.000036 25 -rw-r----- 1 mysql mysql 209 Jul 27 11:25 mysql-bin.000037 26 -rw-r----- 1 mysql mysql 1394 Jul 30 11:29 mysql-bin.000038 27 -rw-r----- 1 mysql mysql 209 Jul 30 11:29 mysql-bin.000039 28 -rw-r----- 1 mysql mysql 418 Jul 31 04:37 mysql-bin.000040 29 -rw-r----- 1 mysql mysql 233 Jul 31 04:37 mysql-bin.000041 30 -rw-r----- 1 mysql mysql 233 Jul 31 04:37 mysql-bin.000042 31 -rw-r----- 1 mysql mysql 954479 Jul 31 06:35 mysql-bin.000043 32 -rw-r----- 1 mysql mysql 209 Jul 31 06:38 mysql-bin.000044 33 -rw-r----- 1 mysql mysql 968 Jul 31 06:35 mysql-bin.index 34 35 [root@zlm2 07:28:32 /data/mysql/mysql3306/logs] 36 #scp mysql-bin.00004{3,4} zlm3:/data/backup37 root@zlm3's password: 38 mysql-bin.000043 100% 932KB 932.1KB/s 00:00 39 mysql-bin.000044 100% 209 0.2KB/s 00:00
Transfer the mysql-bin files into relay-bin files.
1 [root@zlm3 07:24:08 /data/backup/2018-07-31_06-29-03] 2 #cd .. 3 4 [root@zlm3 07:30:46 /data/backup] 5 #ls -l 6 total 944 7 drwxr-x--- 7 root root 4096 Jul 31 06:48 2018-07-31_06-29-03 8 -rw-r----- 1 root root 954479 Jul 31 07:28 mysql-bin.000043 9 -rw-r----- 1 root root 209 Jul 31 07:28 mysql-bin.000044 10 11 [root@zlm3 07:30:47 /data/backup] 12 #for i in $(ls mysql-bin.0*) 13 > do 14 > ext=$(echo $i | cut -d'.' -f2); 15 > cp $i relay-bin.$ext; 16 > done 17 18 [root@zlm3 07:31:19 /data/backup] 19 #ls -l 20 total 1884 21 drwxr-x--- 7 root root 4096 Jul 31 06:48 2018-07-31_06-29-03 22 -rw-r----- 1 root root 954479 Jul 31 07:28 mysql-bin.000043 23 -rw-r----- 1 root root 209 Jul 31 07:28 mysql-bin.000044 24 -rw-r----- 1 root root 954479 Jul 31 07:31 relay-bin.000043 25 -rw-r----- 1 root root 209 Jul 31 07:31 relay-bin.000044 26 27 [root@zlm3 07:36:18 /data/backup] 28 #ls ./relay-bin.0* > relay-bin.index 29 30 [root@zlm3 07:36:20 /data/backup] 31 #ls -l 32 total 1888 33 drwxr-x--- 7 root root 4096 Jul 31 06:48 2018-07-31_06-29-03 34 -rw-r----- 1 root root 954479 Jul 31 07:28 mysql-bin.000043 35 -rw-r----- 1 root root 209 Jul 31 07:28 mysql-bin.000044 36 -rw-r----- 1 root root 954479 Jul 31 07:31 relay-bin.000043 37 -rw-r----- 1 root root 209 Jul 31 07:31 relay-bin.000044 38 -rw-r--r-- 1 root root 38 Jul 31 07:36 relay-bin.index 39 40 [root@zlm3 07:36:23 /data/b