欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

恢复误删除表黑科技之relay log大法(续)

程序员文章站 2022-06-26 08:54:20
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 deci ......
 
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.