恢复误删除表黑科技之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 gain.The experiment was finished successfully.