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.)
Les't see another way to achieve the goal more simply.
Procedure
Step 1. Destroy
Check the table on master.
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/backup] 41 #cat relay-bin.index 42 ./relay-bin.000043 43 ./relay-bin.000044 44 45 [root@zlm3 07:36:27 /data/backup] 46 #chown mysql.mysql relay* 47 48 [root@zlm3 07:37:12 /data/backup] 49 #ls -l 50 total 1888 51 drwxr-x--- 7 root root 4096 Jul 31 06:48 2018-07-31_06-29-03 52 -rw-r----- 1 root root 954479 Jul 31 07:28 mysql-bin.000043 53 -rw-r----- 1 root root 209 Jul 31 07:28 mysql-bin.000044 54 -rw-r----- 1 mysql mysql 954479 Jul 31 07:31 relay-bin.000043 55 -rw-r----- 1 mysql mysql 209 Jul 31 07:31 relay-bin.000044 56 -rw-r--r-- 1 mysql mysql 38 Jul 31 07:36 relay-bin.index
Copy these relay-bin files to the proper directory.
1 [root@zlm3 07:48:10 /data/backup] 2 #cp relay* /data/mysql/mysql3306/data 3 4 [root@zlm3 07:48:27 /data/backup] 5 #cd /data/mysql/mysql3306/data 6 7 [root@zlm3 07:48:35 /data/mysql/mysql3306/data] 8 #ls -l|grep relay 9 -rw-r----- 1 root root 954479 Jul 31 07:48 relay-bin.000043 //Notice,the owner and group has been changed. 10 -rw-r----- 1 root root 209 Jul 31 07:48 relay-bin.000044 11 -rw-r----- 1 mysql mysql 150 Jul 31 06:55 relay-bin-group_replication_applier.000001 12 -rw-r----- 1 mysql mysql 45 Jul 31 06:55 relay-bin-group_replication_applier.index 13 -rw-r----- 1 mysql mysql 150 Jul 31 06:55 relay-bin-group_replication_recovery.000001 14 -rw-r----- 1 mysql mysql 46 Jul 31 06:55 relay-bin-group_replication_recovery.index 15 -rw-r--r-- 1 root root 60 Jul 31 07:48 relay-bin.index 16 17 [root@zlm3 07:48:40 /data/mysql/mysql3306/data] 18 #chown mysql.mysql relay* 19 20 [root@zlm3 07:49:45 /data/mysql/mysql3306/data] 21 #ls -l|grep relay 22 -rw-r----- 1 mysql mysql 954479 Jul 31 07:48 relay-bin.000043 23 -rw-r----- 1 mysql mysql 209 Jul 31 07:48 relay-bin.000044 24 -rw-r----- 1 mysql mysql 150 Jul 31 06:55 relay-bin-group_replication_applier.000001 25 -rw-r----- 1 mysql mysql 45 Jul 31 06:55 relay-bin-group_replication_applier.index 26 -rw-r----- 1 mysql mysql 150 Jul 31 06:55 relay-bin-group_replication_recovery.000001 27 -rw-r----- 1 mysql mysql 46 Jul 31 06:55 relay-bin-group_replication_recovery.index 28 -rw-r--r-- 1 mysql mysql 60 Jul 31 07:48 relay-bin.index
Restart the mysqld process.
1 [root@zlm3 08:25:18 /data/mysql/mysql3306/data] 2 #mysqladmin shutdown 3 4 [root@zlm3 08:31:25 /data/mysql/mysql3306/data] 5 #ps aux|grep mysqld 6 root 4309 0.0 0.0 112640 956 pts/1 R+ 08:31 0:00 grep --color=auto mysqld 7 8 [root@zlm3 08:31:35 /data/mysql/mysql3306/data] 9 #sh /root/mysqld.sh 10 11 [root@zlm3 08:31:45 /data/mysql/mysql3306/data] 12 #ps aux|grep mysqld 13 mysql 4315 11.5 17.8 1044468 181776 pts/1 Sl 08:31 0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf 14 root 4348 0.0 0.0 112640 960 pts/1 R+ 08:31 0:00 grep --color=auto mysqld
Check out the first consistent position we need.
1 [root@zlm3 08:34:15 /data/backup/2018-07-31_06-29-03] 2 #cat xtrabackup_binlog_info 3 mysql-bin.000043 190 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229
Execute "change master to" as below.
1 (zlm@192.168.1.102 3306)[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=190,master_host='xxx'; 2 ERROR 1380 (HY000): Failed initializing relay log position: Could not find first log during relay log initialization 3 (zlm@192.168.1.102 3306)[(none)]>show master status; 4 +------------------+----------+--------------+------------------+------------------------------------------------+ 5 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 6 +------------------+----------+--------------+------------------+------------------------------------------------+ 7 | mysql-bin.000004 | 206 | | | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229 | 8 +------------------+----------+--------------+------------------+------------------------------------------------+ 9 1 row in set (0.00 sec) 10 11 (zlm@192.168.1.102 3306)[(none)]>reset master; 12 Query OK, 0 rows affected (0.02 sec) 13 14 (zlm@192.168.1.102 3306)[(none)]>show master status; 15 +------------------+----------+--------------+------------------+-------------------+ 16 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 17 +------------------+----------+--------------+------------------+-------------------+ 18 | mysql-bin.000001 | 150 | | | | 19 +------------------+----------+--------------+------------------+-------------------+ 20 1 row in set (0.00 sec) 21 22 (zlm@192.168.1.102 3306)[(none)]>set @@global.gtid_purged='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229'; 23 Query OK, 0 rows affected (0.01 sec) 24 25 (zlm@192.168.1.102 3306)[(none)]>show master status; 26 +------------------+----------+--------------+------------------+------------------------------------------------+ 27 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 28 +------------------+----------+--------------+------------------+------------------------------------------------+ 29 | mysql-bin.000002 | 150 | | | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229 | 30 +------------------+----------+--------------+------------------+------------------------------------------------+ 31 1 row in set (0.00 sec) 32 33 (zlm@192.168.1.102 3306)[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=190,master_host='xxx'; 34 ERROR 1380 (HY000): Failed initializing relay log position: Could not find first log during relay log initialization 35 36 (zlm@192.168.1.102 3306)[(none)]>show slave statusG 37 *************************** 1. row *************************** 38 Slave_IO_State: 39 Master_Host: xxx 40 Master_User: test 41 Master_Port: 3306 42 Connect_Retry: 60 43 Master_Log_File: 44 Read_Master_Log_Pos: 4 45 Relay_Log_File: relay-bin.000043 46 Relay_Log_Pos: 190 47 Relay_Master_Log_File: 48 Slave_IO_Running: No 49 Slave_SQL_Running: No 50 Replicate_Do_DB: 51 Replicate_Ignore_DB: 52 Replicate_Do_Table: 53 Replicate_Ignore_Table: 54 Replicate_Wild_Do_Table: 55 Replicate_Wild_Ignore_Table: 56 Last_Errno: 0 57 Last_Error: 58 Skip_Counter: 0 59 Exec_Master_Log_Pos: 0 60 Relay_Log_Space: 0 61 Until_Condition: None 62 Until_Log_File: 63 Until_Log_Pos: 0 64 Master_SSL_Allowed: No 65 Master_SSL_CA_File: 66 Master_SSL_CA_Path: 67 Master_SSL_Cert: 68 Master_SSL_Cipher: 69 Master_SSL_Key: 70 Seconds_Behind_Master: NULL 71 Master_SSL_Verify_Server_Cert: No 72 Last_IO_Errno: 0 73 Last_IO_Error: 74 Last_SQL_Errno: 0 75 Last_SQL_Error: 76 Replicate_Ignore_Server_Ids: 77 Master_Server_Id: 0 78 Master_UUID: 79 Master_Info_File: mysql.slave_master_info 80 SQL_Delay: 0 81 SQL_Remaining_Delay: NULL 82 Slave_SQL_Running_State: 83 Master_Retry_Count: 86400 84 Master_Bind: 85 Last_IO_Error_Timestamp: 86 Last_SQL_Error_Timestamp: 87 Master_SSL_Crl: 88 Master_SSL_Crlpath: 89 Retrieved_Gtid_Set: //No relay logs was retrieved here. 90 Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229 91 Auto_Position: 0 92 Replicate_Rewrite_DB: 93 Channel_Name: 94 Master_TLS_Version: 95 1 row in set (0.00 sec)
I was stuck again,faint!T_T...
Supplemented on August 1.
After discussing with my classmate Shuaibing Zhang,I found out that the reason why I got failure above was due to not executing "reset slave all;".Therefore,it meantioned that "Could not find the first log..." when I Executed "change master to ... ".
Execute "change master to ... "
1 (zlm@192.168.1.102 3306)[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=190,master_host='xxx'; 2 Query OK, 0 rows affected (0.01 sec) 3 4 (zlm@192.168.1.102 3306)[(none)]>show slave statusG 5 *************************** 1. row *************************** 6 Slave_IO_State: 7 Master_Host: xxx 8 Master_User: 9 Master_Port: 3306 10 Connect_Retry: 60 11 Master_Log_File: 12 Read_Master_Log_Pos: 4 13 Relay_Log_File: relay-bin.000043 14 Relay_Log_Pos: 190 15 Relay_Master_Log_File: 16 Slave_IO_Running: No 17 Slave_SQL_Running: No 18 Replicate_Do_DB: 19 Replicate_Ignore_DB: 20 Replicate_Do_Table: 21 Replicate_Ignore_Table: 22 Replicate_Wild_Do_Table: 23 Replicate_Wild_Ignore_Table: 24 Last_Errno: 0 25 Last_Error: 26 Skip_Counter: 0 27 Exec_Master_Log_Pos: 0 28 Relay_Log_Space: 954838 29 Until_Condition: None 30 Until_Log_File: 31 Until_Log_Pos: 0 32 Master_SSL_Allowed: No 33 Master_SSL_CA_File: 34 Master_SSL_CA_Path: 35 Master_SSL_Cert: 36 Master_SSL_Cipher: 37 Master_SSL_Key: 38 Seconds_Behind_Master: NULL 39 Master_SSL_Verify_Server_Cert: No 40 Last_IO_Errno: 0 41 Last_IO_Error: 42 Last_SQL_Errno: 0 43 Last_SQL_Error: 44 Replicate_Ignore_Server_Ids: 45 Master_Server_Id: 0 46 Master_UUID: 47 Master_Info_File: mysql.slave_master_info 48 SQL_Delay: 0 49 SQL_Remaining_Delay: NULL 50 Slave_SQL_Running_State: 51 Master_Retry_Count: 86400 52 Master_Bind: 53 Last_IO_Error_Timestamp: 54 Last_SQL_Error_Timestamp: 55 Master_SSL_Crl: 56 Master_SSL_Crlpath: 57 Retrieved_Gtid_Set: 58 Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229 59 Auto_Position: 0 60 Replicate_Rewrite_DB: 61 Channel_Name: 62 Master_TLS_Version: 63 1 row in set (0.00 sec)
Analyze the relay-bin file to find out the until postion before dropping operation.
1 [root@zlm3 04:11:50 /data/mysql/mysql3306/data] 2 #cd /data/backup/ 3 4 [root@zlm3 04:12:17 /data/backup] 5 #mysqlbinlog --base64-output=decode-rows relay-bin.000043 > 43.log 6 7 [root@zlm3 04:12:47 /data/backup] 8 #tail -20 43.log 9 #180731 6:34:54 server id 1013306 end_log_pos 954224 Delete_rows: table id 222 flags: STMT_END_F 10 # at 954224 11 #180731 6:34:54 server id 1013306 end_log_pos 954251 Xid = 58 12 COMMIT/*!*/; 13 # at 954251 14 #180731 6:35:09 server id 1013306 end_log_pos 954312 GTID last_committed=2 sequence_number=3 rbr_only=no 15 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730232'/*!*/; 16 # at 954312 17 #180731 6:35:09 server id 1013306 end_log_pos 954436 Query thread_id=13 exec_time=0 error_code=0 18 use `sysbench`/*!*/; 19 SET TIMESTAMP=1533011709/*!*/; 20 DROP TABLE `sbtest5` /* generated by server */ //Here's the dropping operation.Therefore,the util position we need is "954251" which just below the "COMMIT/*!*/;" 21 /*!*/; 22 # at 954436 23 #180731 6:35:29 server id 1013306 end_log_pos 954479 Rotate to mysql-bin.000044 pos: 4 24 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; 25 DELIMITER ; 26 # End of log file 27 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 28 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Execute start slave sql_thread util clause.
1 (zlm@192.168.1.102 3306)[(none)]>start slave sql_thread until relay_log_file='relay-bin.000043',relay_log_pos=954251; 2 Query OK, 0 rows affected (0.00 sec) 3 4 (zlm@192.168.1.102 3306)[(none)]>show slave statusG 5 *************************** 1. row *************************** 6 Slave_IO_State: 7 Master_Host: xxx 8 Master_User: 9 Master_Port: 3306 10 Connect_Retry: 60 11 Master_Log_File: 12 Read_Master_Log_Pos: 4 13 Relay_Log_File: relay-bin.000043 14 Relay_Log_Pos: 954251 15 Relay_Master_Log_File: 16 Slave_IO_Running: No 17 Slave_SQL_Running: No 18 Replicate_Do_DB: 19 Replicate_Ignore_DB: 20 Replicate_Do_Table: 21 Replicate_Ignore_Table: 22 Replicate_Wild_Do_Table: 23 Replicate_Wild_Ignore_Table: 24 Last_Errno: 0 25 Last_Error: 26 Skip_Counter: 0 27 Exec_Master_Log_Pos: 954251 28 Relay_Log_Space: 954838 29 Until_Condition: Relay 30 Until_Log_File: relay-bin.000043 31 Until_Log_Pos: 954251 32 Master_SSL_Allowed: No 33 Master_SSL_CA_File: 34 Master_SSL_CA_Path: 35 Master_SSL_Cert: 36 Master_SSL_Cipher: 37 Master_SSL_Key: 38 Seconds_Behind_Master: NULL 39 Master_SSL_Verify_Server_Cert: No 40 Last_IO_Errno: 0 41 Last_IO_Error: 42 Last_SQL_Errno: 0 43 Last_SQL_Error: 44 Replicate_Ignore_Server_Ids: 45 Master_Server_Id: 0 46 Master_UUID: 47 Master_Info_File: mysql.slave_master_info 48 SQL_Delay: 0 49 SQL_Remaining_Delay: NULL 50 Slave_SQL_Running_State: 51 Master_Retry_Count: 86400 52 Master_Bind: 53 Last_IO_Error_Timestamp: 54 Last_SQL_Error_Timestamp: 55 Master_SSL_Crl: 56 Master_SSL_Crlpath: 57 Retrieved_Gtid_Set: 58 Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730231 59 Auto_Position: 0 60 Replicate_Rewrite_DB: 61 Channel_Name: 62 Master_TLS_Version: 63 1 row in set (0.00 sec) 64 65 (zlm@192.168.1.102 3306)[(none)]>select count(*) from sysbench.sbtest5; 66 +----------+ 67 | count(*) | 68 +----------+ 69 | 5000 | 70 +----------+ 71 1 row in set (0.01 sec)
Summary
The simply description of rescuing data with relay log method is shown below:
The precondition is that the master has a up-to-date full backup.(Xtrabackup or mysqldump)
1. Copy all the binlog files to another server which has a newly initialized instance.
2. Restore the backup on new instance and check data is restored normally.
3. Execute "reset slave all;" to clear the original replication information(restored from master).
4. Reconfigure those binlog files with "relay-bin.xxxxxx" format together with relay-bin.index file.
5. Copy those relay logs to the proper datadir and change the ownership and group of them.
6. Execute "change master to ... " with "relay_log_file" and "relay_log_pos".
7. Execute "change replication filter ... " if you're supposed to merely restore a single table.(optional)
8. Execute "start slave sql_thread until ... " to restore data until the position you need.(both relay_log_pos and sql_before_gtids is okay)