恢复误删除表黑科技之relay log大法（续）
Preface
I've stuck twice in my previous experiments in backing up dropped tables.I am still not sure that why I got failure yesterday.Therefore,I decide to do that again with the same steps on another environment.Let's see the details.
Procedure
1. All the operations on master zlm1.
1 //Check data at first. 2 zlm@192.168.56.100:3306 [sysbench]>show tables; 3 +--------------------+ 4 | Tables_in_sysbench | 5 +--------------------+ 6 | sbtest1 | 7 | sbtest2 | 8 | sbtest3 | 9 | sbtest4 | 10 | sbtest5 | 11 +--------------------+ 12 5 rows in set (0.00 sec) 13 14 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest5; 15 +----------+ 16 | count(*) | 17 +----------+ 18 | 10000 | 19 +----------+ 20 1 row in set (0.00 sec) 21 22 zlm@192.168.56.100:3306 [sysbench]>show binary logs; 23 +------------------+-----------+ 24 | Log_name | File_size | 25 +------------------+-----------+ 26 | mysql-bin.000091 | 1288 | 27 | mysql-bin.000092 | 217 | 28 | mysql-bin.000093 | 241 | 29 | mysql-bin.000094 | 217 | 30 | mysql-bin.000095 | 4128 | 31 | mysql-bin.000096 | 241 | 32 | mysql-bin.000097 | 11461585 | 33 | mysql-bin.000098 | 410 | 34 | mysql-bin.000099 | 241 | 35 | mysql-bin.000100 | 974 | 36 | mysql-bin.000101 | 217 | 37 | mysql-bin.000102 | 217 | 38 | mysql-bin.000103 | 194 | 39 +------------------+-----------+ 40 13 rows in set (0.01 sec) 41 42 zlm@192.168.56.100:3306 [sysbench]>show master status; 43 +------------------+----------+--------------+------------------+-------------------------------------------------+ 44 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 45 +------------------+----------+--------------+------------------+-------------------------------------------------+ 46 | mysql-bin.000103 | 194 | | | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 | 47 +------------------+----------+--------------+------------------+-------------------------------------------------+ 48 1 row in set (0.00 sec) 49 50 //Generate a Xtrabackup then send it to the slave zlm2. 51 [root@zlm1 16:27:18 /data/backup] 52 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup 53 54 [root@zlm1 16:34:09 /data/backup] 55 #scp -r 2018-07-31_16-31-46/ zlm2:/data/backup/ 56 57 //Continue to do some operations(DML). 58 zlm@192.168.56.100:3306 [sysbench]>delete from sbtest5 limit 2000; 59 Query OK, 2000 rows affected (0.19 sec) 60 61 zlm@192.168.56.100:3306 [sysbench]>delete from sbtest5 limit 3000; 62 Query OK, 3000 rows affected (0.04 sec) 63 64 //Drop the table. 65 zlm@192.168.56.100:3306 [sysbench]>drop table sbtest5; 66 Query OK, 0 rows affected (0.04 sec) 67 68 zlm@192.168.56.100:3306 [sysbench]>flush logs; 69 Query OK, 0 rows affected (0.05 sec) 70 71 zlm@192.168.56.100:3306 [sysbench]>show master status; 72 +------------------+----------+--------------+------------------+-------------------------------------------------+ 73 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 74 +------------------+----------+--------------+------------------+-------------------------------------------------+ 75 | mysql-bin.000104 | 194 | | | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715699 | 76 +------------------+----------+--------------+------------------+-------------------------------------------------+ 77 1 row in set (0.00 sec) 78 79 //Shutdown the master. 80 zlm@192.168.56.100:3306 [sysbench]>exit 81 Bye 82 83 [root@zlm1 16:36:01 ~] 84 #mysqladmin shutdown 85 86 //Send the binlogs which contain the operations above to slave zlm2 either. 87 [root@zlm1 16:36:10 ~] 88 #cd /data/mysql/mysql3306/logs 89 90 [root@zlm1 16:41:45 /data/mysql/mysql3306/logs] 91 #ls -l 92 total 12188 93 -rw-r----- 1 mysql mysql 1288 Jul 22 11:27 mysql-bin.000091 94 -rw-r----- 1 mysql mysql 217 Jul 22 11:28 mysql-bin.000092 95 -rw-r----- 1 mysql mysql 241 Jul 25 19:18 mysql-bin.000093 96 -rw-r----- 1 mysql mysql 217 Jul 25 19:18 mysql-bin.000094 97 -rw-r----- 1 mysql mysql 4128 Jul 29 08:42 mysql-bin.000095 98 -rw-r----- 1 mysql mysql 241 Jul 29 08:42 mysql-bin.000096 99 -rw-r----- 1 mysql mysql 11461585 Jul 29 09:52 mysql-bin.000097 100 -rw-r----- 1 mysql mysql 410 Jul 29 16:27 mysql-bin.000098 101 -rw-r----- 1 mysql mysql 241 Jul 29 16:27 mysql-bin.000099 102 -rw-r----- 1 mysql mysql 974 Jul 29 16:57 mysql-bin.000100 103 -rw-r----- 1 mysql mysql 217 Jul 30 17:01 mysql-bin.000101 104 -rw-r----- 1 mysql mysql 217 Jul 30 18:12 mysql-bin.000102 105 -rw-r----- 1 mysql mysql 954995 Jul 31 16:35 mysql-bin.000103 106 -rw-r----- 1 mysql mysql 217 Jul 31 16:36 mysql-bin.000104 107 -rw-r----- 1 mysql mysql 616 Jul 31 16:35 mysql-bin.index 108 109 [root@zlm1 16:42:01 /data/mysql/mysql3306/logs] 110 #scp mysql-bin.00010{3,4} zlm2:/data/backup 111 mysql-bin.000103 100% 933KB 932.6KB/s 00:00 112 mysql-bin.000104 100% 217 0.2KB/s 00:00
2. Operations on slave zlm2.
1 //Prepare to restore the backup of Xtrabackup. 2 [root@zlm2 16:37:11 /data/backup] 3 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --apply-log /data/backup/2018-07-31_16-31-46/ 4 5 [root@zlm2 16:36:42 /data/mysql/mysql3306/data] 6 #ls -l 7 total 409732 8 -rw-r----- 1 mysql mysql 56 Jul 30 17:25 auto.cnf 9 -rw-r----- 1 mysql mysql 31332 Jul 30 21:08 error.log 10 -rw-r----- 1 mysql mysql 871 Jul 30 21:08 ib_buffer_pool 11 -rw-r----- 1 mysql mysql 104857600 Jul 30 21:08 ibdata1 12 -rw-r----- 1 mysql mysql 104857600 Jul 30 21:08 ib_logfile0 13 -rw-r----- 1 mysql mysql 104857600 Jul 30 17:22 ib_logfile1 14 -rw-r----- 1 mysql mysql 104857600 Jul 30 17:22 ib_logfile2 15 -rw-r----- 1 mysql mysql 131 Jul 30 21:08 master.info 16 drwxr-x--- 2 mysql mysql 4096 Jul 30 17:22 mrbs 17 drwxr-x--- 2 mysql mysql 4096 Jul 30 17:22 mysql 18 drwxr-x--- 2 mysql mysql 8192 Jul 30 17:23 performance_schema 19 -rw-r----- 1 mysql mysql 201 Jul 30 20:08 relay-bin.000001 20 -rw-r----- 1 mysql mysql 936 Jul 30 20:08 relay-bin.000002 21 -rw-r----- 1 mysql mysql 454 Jul 30 20:08 relay-bin.000003 22 -rw-r----- 1 mysql mysql 454 Jul 30 20:08 relay-bin.000004 23 -rw-r----- 1 mysql mysql 454 Jul 30 20:08 relay-bin.000005 24 -rw-r----- 1 mysql mysql 454 Jul 30 20:08 relay-bin.000006 25 -rw-r----- 1 mysql mysql 430 Jul 30 21:08 relay-bin.000007 26 -rw-r----- 1 mysql mysql 133 Jul 30 20:11 relay-bin.index 27 -rw-r----- 1 mysql mysql 53 Jul 30 21:08 relay-log.info 28 -rw-r----- 1 mysql mysql 346 Jul 30 18:17 slow.log 29 drwxr-x--- 2 mysql mysql 8192 Jul 30 17:22 sys 30 drwxr-x--- 2 mysql mysql 4096 Jul 30 17:23 sysbench 31 -rw-r----- 1 mysql mysql 21 Jul 30 17:23 xtrabackup_binlog_pos_innodb 32 -rw-r----- 1 mysql mysql 595 Jul 30 17:23 xtrabackup_info 33 -rw-r----- 1 mysql mysql 1 Jul 30 17:23 xtrabackup_master_key_id 34 35 [root@zlm2 16:36:41 /data/mysql/mysql3306/data] 36 #ps aux|grep mysqld 37 root 3900 0.0 0.0 112640 960 pts/1 R+ 16:38 0:00 grep --color=auto mysqld 38 39 [root@zlm2 16:36:44 /data/mysql/mysql3306/data] 40 #rm -rf * 41 42 //Copy back the datafiles. 43 [root@zlm2 16:38:31 /data/backup] 44 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/2018-07-31_16-31-46/ 45 46 [root@zlm2 16:38:48 /data/mysql/mysql3306/data] 47 #ls -l 48 total 421940 49 -rw-r----- 1 root root 784 Jul 31 16:39 ib_buffer_pool 50 -rw-r----- 1 root root 104857600 Jul 31 16:39 ibdata1 51 -rw-r----- 1 root root 104857600 Jul 31 16:39 ib_logfile0 52 -rw-r----- 1 root root 104857600 Jul 31 16:39 ib_logfile1 53 -rw-r----- 1 root root 104857600 Jul 31 16:39 ib_logfile2 54 -rw-r----- 1 root root 12582912 Jul 31 16:39 ibtmp1 55 drwxr-x--- 2 root root 4096 Jul 31 16:39 mrbs 56 drwxr-x--- 2 root root 4096 Jul 31 16:39 mysql 57 drwxr-x--- 2 root root 8192 Jul 31 16:39 performance_schema 58 drwxr-x--- 2 root root 8192 Jul 31 16:39 sys 59 drwxr-x--- 2 root root 4096 Jul 31 16:39 sysbench 60 -rw-r----- 1 root root 21 Jul 31 16:39 xtrabackup_binlog_pos_innodb 61 -rw-r----- 1 root root 595 Jul 31 16:39 xtrabackup_info 62 -rw-r----- 1 root root 1 Jul 31 16:39 xtrabackup_master_key_id 63 64 [root@zlm2 16:40:26 /data/mysql/mysql3306/data] 65 #sh /root/mysqld.sh 66 67 [root@zlm2 16:40:33 /data/mysql/mysql3306/data] 68 #mysql 69 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.101' (111) 70 71 [root@zlm2 16:40:37 /data/mysql/mysql3306/data] 72 #chown -R mysql.mysql * 73 74 [root@zlm2 16:41:00 /data/mysql/mysql3306/data] 75 #sh /root/mysqld.sh 76 77 [root@zlm2 16:41:02 /data/mysql/mysql3306/data] 78 #mysql 79 Welcome to the MySQL monitor. Commands end with ; or \g. 80 Your MySQL connection id is 2 81 Server version: 5.7.21-log MySQL Community Server (GPL) 82 83 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 84 85 Oracle is a registered trademark of Oracle Corporation and/or its 86 affiliates. Other names may be trademarks of their respective 87 owners. 88 89 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 90 91 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5; 92 +----------+ 93 | count(*) | 94 +----------+ 95 | 10000 | 96 +----------+ 97 1 row in set (0.03 sec) 98 99 //Continue to restore the incremental data with sql_thread. 100 [root@zlm2 16:39:55 /data/backup] 101 #ls -l 102 total 944 103 drwxr-x--- 7 root root 4096 Jul 31 16:38 2018-07-31_16-31-46 104 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103 105 -rw-r----- 1 root root 217 Jul 31 16:42 mysql-bin.000104 106 107 [root@zlm2 16:43:19 /data/backup] 108 #for i in $(ls mysql-bin.0*) 109 > do 110 > ext=$(echo $i | cut -d'.' -f2); 111 > cp $i relay-bin.$ext; 112 > done 113 114 [root@zlm2 16:45:20 /data/backup] 115 #ls -l 116 total 1884 117 drwxr-x--- 7 root root 4096 Jul 31 16:38 2018-07-31_16-31-46 118 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103 119 -rw-r----- 1 root root 217 Jul 31 16:42 mysql-bin.000104 120 -rw-r----- 1 root root 954995 Jul 31 16:45 relay-bin.000103 121 -rw-r----- 1 root root 217 Jul 31 16:45 relay-bin.000104 122 123 [root@zlm2 16:45:23 /data/backup] 124 #ls ./relay-bin.0* > relay-bin.index 125 126 [root@zlm2 16:45:31 /data/backup] 127 #ls -l 128 total 1888 129 drwxr-x--- 7 root root 4096 Jul 31 16:38 2018-07-31_16-31-46 130 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103 131 -rw-r----- 1 root root 217 Jul 31 16:42 mysql-bin.000104 132 -rw-r----- 1 root root 954995 Jul 31 16:45 relay-bin.000103 133 -rw-r----- 1 root root 217 Jul 31 16:45 relay-bin.000104 134 -rw-r--r-- 1 root root 38 Jul 31 16:45 relay-bin.index 135 136 [root@zlm2 16:45:33 /data/backup] 137 #cat relay-bin.index 138 ./relay-bin.000103 139 ./relay-bin.000104 140 141 [root@zlm2 16:45:37 /data/backup] 142 #chown mysql.mysql relay* 143 144 [root@zlm2 16:45:51 /data/backup] 145 #ls -l 146 total 1888 147 drwxr-x--- 7 root root 4096 Jul 31 16:38 2018-07-31_16-31-46 148 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103 149 -rw-r----- 1 root root 217 Jul 31 16:42 mysql-bin.000104 150 -rw-r----- 1 mysql mysql 954995 Jul 31 16:45 relay-bin.000103 151 -rw-r----- 1 mysql mysql 217 Jul 31 16:45 relay-bin.000104 152 -rw-r--r-- 1 mysql mysql 38 Jul 31 16:45 relay-bin.index 153 154 [root@zlm2 16:45:52 /data/backup] 155 #cp relay* /data/mysql/mysql3306/data 156 157 [root@zlm2 16:46:08 /data/backup] 158 #cd /data/mysql/mysql3306/data 159 160 [root@zlm2 16:46:25 /data/mysql/mysql3306/data] 161 #ls -l 162 total 422908 163 -rw-r----- 1 mysql mysql 56 Jul 31 16:41 auto.cnf 164 -rw-r----- 1 mysql mysql 5989 Jul 31 16:46 error.log 165 -rw-r----- 1 mysql mysql 784 Jul 31 16:39 ib_buffer_pool 166 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ibdata1 167 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ib_logfile0 168 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile1 169 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile2 170 -rw-r----- 1 mysql mysql 12582912 Jul 31 16:41 ibtmp1 171 -rw-r----- 1 mysql mysql 0 Jul 31 16:40 innodb_status.3949 172 -rw-r----- 1 mysql mysql 3835 Jul 31 16:46 innodb_status.3979 173 drwxr-x--- 2 mysql mysql 4096 Jul 31 16:39 mrbs 174 drwxr-x--- 2 mysql mysql 4096 Jul 31 16:39 mysql 175 -rw-r----- 1 mysql mysql 5 Jul 31 16:41 mysql.pid 176 drwxr-x--- 2 mysql mysql 8192 Jul 31 16:39 performance_schema 177 -rw-r----- 1 root root 954995 Jul 31 16:46 relay-bin.000103 178 -rw-r----- 1 root root 217 Jul 31 16:46 relay-bin.000104 179 -rw-r--r-- 1 root root 38 Jul 31 16:46 relay-bin.index 180 -rw-r----- 1 mysql mysql 173 Jul 31 16:41 slow.log 181 drwxr-x--- 2 mysql mysql 8192 Jul 31 16:39 sys 182 drwxr-x--- 2 mysql mysql 4096 Jul 31 16:39 sysbench 183 -rw-r----- 1 mysql mysql 21 Jul 31 16:39 xtrabackup_binlog_pos_innodb 184 -rw-r----- 1 mysql mysql 595 Jul 31 16:39 xtrabackup_info 185 -rw-r----- 1 mysql mysql 1 Jul 31 16:39 xtrabackup_master_key_id 186 187 [root@zlm2 16:46:26 /data/mysql/mysql3306/data] 188 #chown mysql.mysql relay* 189 190 [root@zlm2 16:46:37 /data/mysql/mysql3306/data] 191 #ls -l 192 total 422908 193 -rw-r----- 1 mysql mysql 56 Jul 31 16:41 auto.cnf 194 -rw-r----- 1 mysql mysql 6137 Jul 31 16:52 error.log 195 -rw-r----- 1 mysql mysql 784 Jul 31 16:39 ib_buffer_pool 196 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ibdata1 197 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ib_logfile0 198 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile1 199 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile2 200 -rw-r----- 1 mysql mysql 12582912 Jul 31 16:41 ibtmp1 201 -rw-r----- 1 mysql mysql 0 Jul 31 16:40 innodb_status.3949 202 -rw-r----- 1 mysql mysql 3835 Jul 31 16:53 innodb_status.3979 203 drwxr-x--- 2 mysql mysql 4096 Jul 31 16:39 mrbs 204 drwxr-x--- 2 mysql mysql 4096 Jul 31 16:39 mysql 205 -rw-r----- 1 mysql mysql 5 Jul 31 16:41 mysql.pid 206 drwxr-x--- 2 mysql mysql 8192 Jul 31 16:39 performance_schema 207 -rw-r----- 1 mysql mysql 954995 Jul 31 16:46 relay-bin.000103 208 -rw-r----- 1 mysql mysql 217 Jul 31 16:46 relay-bin.000104 209 -rw-r--r-- 1 mysql mysql 38 Jul 31 16:46 relay-bin.index 210 -rw-r----- 1 mysql mysql 173 Jul 31 16:41 slow.log 211 drwxr-x--- 2 mysql mysql 8192 Jul 31 16:39 sys 212 drwxr-x--- 2 mysql mysql 4096 Jul 31 16:39 sysbench 213 -rw-r----- 1 mysql mysql 21 Jul 31 16:39 xtrabackup_binlog_pos_innodb 214 -rw-r----- 1 mysql mysql 595 Jul 31 16:39 xtrabackup_info 215 -rw-r----- 1 mysql mysql 1 Jul 31 16:39 xtrabackup_master_key_id 216 217 zlm@192.168.56.101:3306 [(none)]>show master status; 218 +------------------+----------+--------------+------------------+-------------------------------------------------+ 219 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 220 +------------------+----------+--------------+------------------+-------------------------------------------------+ 221 | mysql-bin.000002 | 194 | | | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 | 222 +------------------+----------+--------------+------------------+-------------------------------------------------+ 223 1 row in set (0.00 sec) 224 225 zlm@192.168.56.101:3306 [(none)]>change master to relay_log_file='relay-bin.000103',relay_log_pos=194,master_host='xxx'; 226 Query OK, 0 rows affected (0.01 sec) 227 228 zlm@192.168.56.101:3306 [(none)]>show slave status\G 229 *************************** 1. row *************************** 230 Slave_IO_State: 231 Master_Host: xxx 232 Master_User: 233 Master_Port: 3306 234 Connect_Retry: 60 235 Master_Log_File: 236 Read_Master_Log_Pos: 4 237 Relay_Log_File: relay-bin.000103 238 Relay_Log_Pos: 194 239 Relay_Master_Log_File: 240 Slave_IO_Running: No 241 Slave_SQL_Running: No 242 Replicate_Do_DB: 243 Replicate_Ignore_DB: 244 Replicate_Do_Table: 245 Replicate_Ignore_Table: 246 Replicate_Wild_Do_Table: 247 Replicate_Wild_Ignore_Table: 248 Last_Errno: 0 249 Last_Error: 250 Skip_Counter: 0 251 Exec_Master_Log_Pos: 0 //No data here yet. 252 Relay_Log_Space: 955366 253 Until_Condition: None 254 Until_Log_File: 255 Until_Log_Pos: 0 256 Master_SSL_Allowed: No 257 Master_SSL_CA_File: 258 Master_SSL_CA_Path: 259 Master_SSL_Cert: 260 Master_SSL_Cipher: 261 Master_SSL_Key: 262 Seconds_Behind_Master: NULL 263 Master_SSL_Verify_Server_Cert: No 264 Last_IO_Errno: 0 265 Last_IO_Error: 266 Last_SQL_Errno: 0 267 Last_SQL_Error: 268 Replicate_Ignore_Server_Ids: 269 Master_Server_Id: 0 270 Master_UUID: 271 Master_Info_File: /data/mysql/mysql3306/data/master.info 272 SQL_Delay: 0 273 SQL_Remaining_Delay: NULL 274 Slave_SQL_Running_State: 275 Master_Retry_Count: 86400 276 Master_Bind: 277 Last_IO_Error_Timestamp: 278 Last_SQL_Error_Timestamp: 279 Master_SSL_Crl: 280 Master_SSL_Crlpath: 281 Retrieved_Gtid_Set: //No data here. 282 Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 283 Auto_Position: 0 284 Replicate_Rewrite_DB: 285 Channel_Name: 286 Master_TLS_Version: 287 1 row in set (0.00 sec) 288 289 [root@zlm2 16:59:42 /data/backup] 290 #ls -l 291 total 1888 292 drwxr-x--- 7 root root 4096 Jul 31 16:38 2018-07-31_16-31-46 293 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103 294 -rw-r----- 1 root root 217 Jul 31 16:42 mysql-bin.000104 295 -rw-r----- 1 mysql mysql 954995 Jul 31 16:45 relay-bin.000103 296 -rw-r----- 1 mysql mysql 217 Jul 31 16:45 relay-bin.000104 297 -rw-r--r-- 1 mysql mysql 38 Jul 31 16:45 relay-bin.index 298 299 //Find out the end position to set util clause. 300 [root@zlm2 16:59:43 /data/backup] 301 #mysqlbinlog --base64-output=decode-rows relay-bin.000103 > 103.log 302 303 [root@zlm2 17:01:22 /data/backup] 304 #tail -20 103.log 305 #180731 16:35:17 server id 1003306 end_log_pos 954724 CRC32 0xe3e63622 Delete_rows: table id 114 flags: STMT_END_F 306 # at 954724 307 #180731 16:35:17 server id 1003306 end_log_pos 954755 CRC32 0x84cf5136 Xid = 31 308 COMMIT/*!*/; 309 # at 954755 //This position is which I need to set until to. 310 #180731 16:35:29 server id 1003306 end_log_pos 954820 CRC32 0xef03ef84 GTID last_committed=2 sequence_number=3 rbr_only=no 311 SET @@SESSION.GTID_NEXT= '2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715699'/*!*/; 312 # at 954820 313 #180731 16:35:29 server id 1003306 end_log_pos 954948 CRC32 0x0309b10f Query thread_id=2 exec_time=0 error_code=0 314 use `sysbench`/*!*/; 315 SET TIMESTAMP=1533047729/*!*/; 316 DROP TABLE `sbtest5` /* generated by server */ 317 /*!*/; 318 # at 954948 319 #180731 16:35:37 server id 1003306 end_log_pos 954995 CRC32 0xc1ca182a Rotate to mysql-bin.000104 pos: 4 320 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; 321 DELIMITER ; 322 # End of log file 323 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 324 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 325 326 zlm@192.168.56.101:3306 [(none)]>start slave sql_thread until relay_log_file='relay-bin.000103',relay_log_pos=954755; 327 Query OK, 0 rows affected (0.00 sec) 328 329 zlm@192.168.56.101:3306 [(none)]>show slave status\G 330 *************************** 1. row *************************** 331 Slave_IO_State: 332 Master_Host: xxx 333 Master_User: 334 Master_Port: 3306 335 Connect_Retry: 60 336 Master_Log_File: 337 Read_Master_Log_Pos: 4 338 Relay_Log_File: relay-bin.000103 339 Relay_Log_Pos: 954755 340 Relay_Master_Log_File: 341 Slave_IO_Running: No 342 Slave_SQL_Running: No 343 Replicate_Do_DB: 344 Replicate_Ignore_DB: 345 Replicate_Do_Table: 346 Replicate_Ignore_Table: 347 Replicate_Wild_Do_Table: 348 Replicate_Wild_Ignore_Table: 349 Last_Errno: 0 350 Last_Error: 351 Skip_Counter: 0 352 Exec_Master_Log_Pos: 954755 //This is the terminal relay log position I set just now. 353 Relay_Log_Space: 955366 354 Until_Condition: Relay 355 Until_Log_File: relay-bin.000103 356 Until_Log_Pos: 954755 357 Master_SSL_Allowed: No 358 Master_SSL_CA_File: 359 Master_SSL_CA_Path: 360 Master_SSL_Cert: 361 Master_SSL_Cipher: 362 Master_SSL_Key: 363 Seconds_Behind_Master: NULL 364 Master_SSL_Verify_Server_Cert: No 365 Last_IO_Errno: 0 366 Last_IO_Error: 367 Last_SQL_Errno: 0 368 Last_SQL_Error: 369 Replicate_Ignore_Server_Ids: 370 Master_Server_Id: 0 371 Master_UUID: 372 Master_Info_File: /data/mysql/mysql3306/data/master.info 373 SQL_Delay: 0 374 SQL_Remaining_Delay: NULL 375 Slave_SQL_Running_State: 376 Master_Retry_Count: 86400 377 Master_Bind: 378 Last_IO_Error_Timestamp: 379 Last_SQL_Error_Timestamp: 380 Master_SSL_Crl: 381 Master_SSL_Crlpath: 382 Retrieved_Gtid_Set: 383 Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715698 384 Auto_Position: 0 385 Replicate_Rewrite_DB: 386 Channel_Name: 387 Master_TLS_Version: 388 1 row in set (0.00 sec) 389 390 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5; 391 +----------+ 392 | count(*) | 393 +----------+ 394 | 5000 | 395 +----------+ 396 1 row in set (0.00 sec)
3. The supplemented experiment.
1 //Shudown the instance. 2 [root@zlm2 18:27:23 /data/mysql/mysql3306/data] 3 #ps aux|grep mysqld 4 mysql 3979 0.0 20.0 1072196 204196 pts/1 Sl 16:41 0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf 5 root 4265 0.0 0.0 112640 960 pts/0 R+ 18:27 0:00 grep --color=auto mysqld 6 7 [root@zlm2 18:27:28 /data/mysql/mysql3306/data] 8 #mysqladmin shutdown 9 10 [root@zlm2 18:27:42 /data/mysql/mysql3306/data] 11 12 //Copy back the data one more time(no need to executed with option "--apply-log" first this time). 13 [root@zlm2 18:28:13 /data/backup] 14 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/2018-07-31_16-31-46/ 15 16 //Copy the relay logs to datadir directory again. 17 [root@zlm2 18:29:22 /data/backup] 18 #ls -l 19 total 1904 20 -rw-r--r-- 1 root root 16082 Jul 31 17:01 103.log 21 drwxr-x--- 7 root root 4096 Jul 31 16:38 2018-07-31_16-31-46 22 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103 23 -rw-r----- 1 root root 217 Jul 31 16:42 mysql-bin.000104 24 -rw-r----- 1 mysql mysql 954995 Jul 31 16:45 relay-bin.000103 25 -rw-r----- 1 mysql mysql 217 Jul 31 16:45 relay-bin.000104 26 -rw-r--r-- 1 mysql mysql 38 Jul 31 16:45 relay-bin.index 27 28 [root@zlm2 18:29:45 /data/backup] 29 #cp relay* /data/mysql/mysql3306/data 30 31 [root@zlm2 18:30:25 /data/mysql/mysql3306/data] 32 #ls -l 33 total 422884 34 -rw-r----- 1 root root 784 Jul 31 18:29 ib_buffer_pool 35 -rw-r----- 1 root root 104857600 Jul 31 18:29 ibdata1 36 -rw-r----- 1 root root 104857600 Jul 31 18:28 ib_logfile0 37 -rw-r----- 1 root root 104857600 Jul 31 18:28 ib_logfile1 38 -rw-r----- 1 root root 104857600 Jul 31 18:28 ib_logfile2 39 -rw-r----- 1 root root 12582912 Jul 31 18:29 ibtmp1 40 drwxr-x--- 2 root root 4096 Jul 31 18:29 mrbs 41 drwxr-x--- 2 root root 4096 Jul 31 18:29 mysql 42 drwxr-x--- 2 root root 8192 Jul 31 18:29 performance_schema 43 -rw-r----- 1 root root 954995 Jul 31 18:30 relay-bin.000103 44 -rw-r----- 1 root root 217 Jul 31 18:30 relay-bin.000104 45 -rw-r--r-- 1 root root 38 Jul 31 18:30 relay-bin.index 46 drwxr-x--- 2 root root 8192 Jul 31 18:29 sys 47 drwxr-x--- 2 root root 4096 Jul 31 18:29 sysbench 48 -rw-r----- 1 root root 21 Jul 31 18:29 xtrabackup_binlog_pos_innodb 49 -rw-r----- 1 root root 595 Jul 31 18:29 xtrabackup_info 50 -rw-r----- 1 root root 1 Jul 31 18:29 xtrabackup_master_key_id 51 52 [root@zlm2 18:31:11 /data/mysql/mysql3306/data] 53 #chown -R mysql.mysql * 54 55 [root@zlm2 18:31:19 /data/mysql/mysql3306/data] 56 #sh /root/mysqld.sh 57 58 [root@zlm2 18:31:23 /data/mysql/mysql3306/data] 59 #mysql 60 Welcome to the MySQL monitor. Commands end with ; or \g. 61 Your MySQL connection id is 2 62 Server version: 5.7.21-log MySQL Community Server (GPL) 63 64 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 65 66 Oracle is a registered trademark of Oracle Corporation and/or its 67 affiliates. Other names may be trademarks of their respective 68 owners. 69 70 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 71 72 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5; 73 +----------+ 74 | count(*) | 75 +----------+ 76 | 10000 | 77 +----------+ 78 1 row in set (0.04 sec) 79 80 zlm@192.168.56.101:3306 [(none)]>show master status; 81 +------------------+----------+--------------+------------------+-------------------------------------------------+ 82 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 83 +------------------+----------+--------------+------------------+-------------------------------------------------+ 84 | mysql-bin.000003 | 210 | | | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715698 | 85 +------------------+----------+--------------+------------------+-------------------------------------------------+ 86 1 row in set (0.00 sec) 87 88 zlm@192.168.56.101:3306 [(none)]>reset master; 89 Query OK, 0 rows affected (0.03 sec) 90 91 zlm@192.168.56.101:3306 [(none)]>show master status; 92 +------------------+----------+--------------+------------------+-------------------+ 93 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 94 +------------------+----------+--------------+------------------+-------------------+ 95 | mysql-bin.000001 | 154 | | | | 96 +------------------+----------+--------------+------------------+-------------------+ 97 1 row in set (0.00 sec) 98 99 zlm@192.168.56.101:3306 [(none)]>show slave status\G 100 Empty set (0.00 sec) 101 102 zlm@192.168.56.101:3306 [(none)]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696'; 103 Query OK, 0 rows affected (0.00 sec) 104 105 zlm@192.168.56.101:3306 [(none)]>show master status; 106 +------------------+----------+--------------+------------------+-------------------------------------------------+ 107 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 108 +------------------+----------+--------------+------------------+-------------------------------------------------+ 109 | mysql-bin.000001 | 154 | | | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 | 110 +------------------+----------+--------------+------------------+-------------------------------------------------+ 111 1 row in set (0.00 sec) 112 113 zlm@192.168.56.101:3306 [(none)]>change master to relay_log_file='relay-bin.000103',relay_log_pos=194,master_host='xxx'; 114 Query OK, 0 rows affected (0.01 sec) 115 116 zlm@192.168.56.101:3306 [(none)]>show slave status\G 117 *************************** 1. row *************************** 118 Slave_IO_State: 119 Master_Host: xxx 120 Master_User: 121 Master_Port: 3306 122 Connect_Retry: 60 123 Master_Log_File: 124 Read_Master_Log_Pos: 4 125 Relay_Log_File: relay-bin.000103 126 Relay_Log_Pos: 194 127 Relay_Master_Log_File: 128 Slave_IO_Running: No 129 Slave_SQL_Running: No 130 Replicate_Do_DB: 131 Replicate_Ignore_DB: 132 Replicate_Do_Table: 133 Replicate_Ignore_Table: 134 Replicate_Wild_Do_Table: 135 Replicate_Wild_Ignore_Table: 136 Last_Errno: 0 137 Last_Error: 138 Skip_Counter: 0 139 Exec_Master_Log_Pos: 0 140 Relay_Log_Space: 955366 141 Until_Condition: None 142 Until_Log_File: 143 Until_Log_Pos: 0 144 Master_SSL_Allowed: No 145 Master_SSL_CA_File: 146 Master_SSL_CA_Path: 147 Master_SSL_Cert: 148 Master_SSL_Cipher: 149 Master_SSL_Key: 150 Seconds_Behind_Master: NULL 151 Master_SSL_Verify_Server_Cert: No 152 Last_IO_Errno: 0 153 Last_IO_Error: 154 Last_SQL_Errno: 0 155 Last_SQL_Error: 156 Replicate_Ignore_Server_Ids: 157 Master_Server_Id: 0 158 Master_UUID: 159 Master_Info_File: /data/mysql/mysql3306/data/master.info 160 SQL_Delay: 0 161 SQL_Remaining_Delay: NULL 162 Slave_SQL_Running_State: 163 Master_Retry_Count: 86400 164 Master_Bind: 165 Last_IO_Error_Timestamp: 166 Last_SQL_Error_Timestamp: 167 Master_SSL_Crl: 168 Master_SSL_Crlpath: 169 Retrieved_Gtid_Set: 170 Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 171 Auto_Position: 0 172 Replicate_Rewrite_DB: 173 Channel_Name: 174 Master_TLS_Version: 175 1 row in set (0.00 sec) 176 177 zlm@192.168.56.101:3306 [(none)]>start slave sql_thread until sql_before_gtids='2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715699'; 178 Query OK, 0 rows affected (0.51 sec) 179 180 zlm@192.168.56.101:3306 [(none)]>show slave status\G 181 *************************** 1. row *************************** 182 Slave_IO_State: 183 Master_Host: xxx 184 Master_User: 185 Master_Port: 3306 186 Connect_Retry: 60 187 Master_Log_File: 188 Read_Master_Log_Pos: 4 189 Relay_Log_File: relay-bin.000103 190 Relay_Log_Pos: 954755 191 Relay_Master_Log_File: 192 Slave_IO_Running: No 193 Slave_SQL_Running: No 194 Replicate_Do_DB: 195 Replicate_Ignore_DB: 196 Replicate_Do_Table: 197 Replicate_Ignore_Table: 198 Replicate_Wild_Do_Table: 199 Replicate_Wild_Ignore_Table: 200 Last_Errno: 0 201 Last_Error: 202 Skip_Counter: 0 203 Exec_Master_Log_Pos: 954755 204 Relay_Log_Space: 955366 205 Until_Condition: SQL_BEFORE_GTIDS //This time we use this option to start slave sql_thread. 206 Until_Log_File: 207 Until_Log_Pos: 0 208 Master_SSL_Allowed: No 209 Master_SSL_CA_File: 210 Master_SSL_CA_Path: 211 Master_SSL_Cert: 212 Master_SSL_Cipher: 213 Master_SSL_Key: 214 Seconds_Behind_Master: NULL 215 Master_SSL_Verify_Server_Cert: No 216 Last_IO_Errno: 0 217 Last_IO_Error: 218 Last_SQL_Errno: 0 219 Last_SQL_Error: 220 Replicate_Ignore_Server_Ids: 221 Master_Server_Id: 0 222 Master_UUID: 223 Master_Info_File: /data/mysql/mysql3306/data/master.info 224 SQL_Delay: 0 225 SQL_Remaining_Delay: NULL 226 Slave_SQL_Running_State: 227 Master_Retry_Count: 86400 228 Master_Bind: 229 Last_IO_Error_Timestamp: 230 Last_SQL_Error_Timestamp: 231 Master_SSL_Crl: 232 Master_SSL_Crlpath: 233 Retrieved_Gtid_Set: 234 Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715698 235 Auto_Position: 0 236 Replicate_Rewrite_DB: 237 Channel_Name: 238 Master_TLS_Version: 239 1 row in set (0.00 sec) 240 241 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5; 242 +----------+ 243 | count(*) | 244 +----------+ 245 | 5000 | 246 +----------+ 247 1 row in set (0.00 sec)
Ultimately,the incremental data comes back again.The experiment was finished successfully.
版权声明：本文为博主原创文章，如需转载请保留此声明及博客链接，谢谢！
博客地址： http://www.cnblogs.com/aaron8219 & http://blog.csdn.net/aaron8219
博客地址： http://www.cnblogs.com/aaron8219 & http://blog.csdn.net/aaron8219
