恢复误删除表黑科技之relay log大法（续）
2018-08-02 05:57:39来源：博客园 阅读 ()
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.
标签：
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有
上一篇：SQL中Truncate的用法
下一篇：数据库：外键是什么
相关文章
- MYSQL怎么删除匿名用户 2019-09-23
- mysql手动删除BINLOG 2019-09-23
- 让你提高效率的 Linux 技巧 2019-09-04
- mysql如何删除重复记录语句 2019-08-23
- PHP操作redis实现的分页列表，新增，删除功能封装类与用法示 2019-07-24
IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash
热门词条
最新资讯
热门关注
热门标签