mysql备份恢复drop表_基于mysqldump备份集来恢复某个误操作的表（drop,truncate）

1 //Fetch the gtid_purged infomation from mysqldump backup.

2 [root@zlm3 09:39:19 /data/backup]3 #grep "SET @@GLOBAL.GTID_PURGED"db3306_20180726.sql4 SET @@GLOBAL.GTID_PURGED='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210';5

6 (root@localhost mysql3306.sock)[sysbench]>reset master;7 Query OK, 0 rows affected (0.01sec)8

9 (root@localhost mysql3306.sock)[sysbench]>reset slave;10 Query OK, 0 rows affected (0.02sec)11

12 (root@localhost mysql3306.sock)[sysbench]>show slave status\G13 *************************** 1. row ***************************

14 Slave_IO_State:15 Master_Host: 192.168.1.101

16 Master_User: repl17 Master_Port: 3306

18 Connect_Retry: 60

19 Master_Log_File:20 Read_Master_Log_Pos: 4

21 Relay_Log_File: relay-bin.000001

22 Relay_Log_Pos: 4

23 Relay_Master_Log_File:24 Slave_IO_Running: No25 Slave_SQL_Running: No26 Replicate_Do_DB:27 Replicate_Ignore_DB:28 Replicate_Do_Table:29 Replicate_Ignore_Table:30 Replicate_Wild_Do_Table:31 Replicate_Wild_Ignore_Table:32 Last_Errno: 0

33 Last_Error:34 Skip_Counter: 0

35 Exec_Master_Log_Pos: 0

36 Relay_Log_Space: 169

37 Until_Condition: None38 Until_Log_File:39 Until_Log_Pos: 0

40 Master_SSL_Allowed: No41 Master_SSL_CA_File:42 Master_SSL_CA_Path:43 Master_SSL_Cert:44 Master_SSL_Cipher:45 Master_SSL_Key:46 Seconds_Behind_Master: NULL47 Master_SSL_Verify_Server_Cert: No48 Last_IO_Errno: 0

49 Last_IO_Error:50 Last_SQL_Errno: 0

51 Last_SQL_Error:52 Replicate_Ignore_Server_Ids:53 Master_Server_Id: 0

54 Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e55 Master_Info_File: mysql.slave_master_info56 SQL_Delay: 0

57 SQL_Remaining_Delay: NULL58 Slave_SQL_Running_State:59 Master_Retry_Count: 86400

60 Master_Bind:61 Last_IO_Error_Timestamp:62 Last_SQL_Error_Timestamp:63 Master_SSL_Crl:64 Master_SSL_Crlpath:65 Retrieved_Gtid_Set:66 Executed_Gtid_Set:67 Auto_Position: 1

68 Replicate_Rewrite_DB:69 Channel_Name:70 Master_TLS_Version:71 1 row in set (0.00sec)72

73 //Set gtid_purged variable.

74 (root@localhost mysql3306.sock)[sysbench]>SET @@GLOBAL.GTID_PURGED='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210';75 Query OK, 0 rows affected (0.00sec)76

77 (root@localhost mysql3306.sock)[sysbench]>show slave status\G78 *************************** 1. row ***************************

79 Slave_IO_State:80 Master_Host: 192.168.1.101

81 Master_User: repl82 Master_Port: 3306

83 Connect_Retry: 60

84 Master_Log_File:85 Read_Master_Log_Pos: 4

86 Relay_Log_File: relay-bin.000001

87 Relay_Log_Pos: 4

88 Relay_Master_Log_File:89 Slave_IO_Running: No90 Slave_SQL_Running: No91 Replicate_Do_DB:92 Replicate_Ignore_DB:93 Replicate_Do_Table:94 Replicate_Ignore_Table:95 Replicate_Wild_Do_Table:96 Replicate_Wild_Ignore_Table:97 Last_Errno: 0

98 Last_Error:99 Skip_Counter: 0

100 Exec_Master_Log_Pos: 0

101 Relay_Log_Space: 169

102 Until_Condition: None103 Until_Log_File:104 Until_Log_Pos: 0

105 Master_SSL_Allowed: No106 Master_SSL_CA_File:107 Master_SSL_CA_Path:108 Master_SSL_Cert:109 Master_SSL_Cipher:110 Master_SSL_Key:111 Seconds_Behind_Master: NULL112 Master_SSL_Verify_Server_Cert: No113 Last_IO_Errno: 0

114 Last_IO_Error:115 Last_SQL_Errno: 0

116 Last_SQL_Error:117 Replicate_Ignore_Server_Ids:118 Master_Server_Id: 0

119 Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e120 Master_Info_File: mysql.slave_master_info121 SQL_Delay: 0

122 SQL_Remaining_Delay: NULL123 Slave_SQL_Running_State:124 Master_Retry_Count: 86400

125 Master_Bind:126 Last_IO_Error_Timestamp:127 Last_SQL_Error_Timestamp:128 Master_SSL_Crl:129 Master_SSL_Crlpath:130 Retrieved_Gtid_Set:131 Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210 //After set @@global.gtid_purged operation,Executed_Gitd_Set will contain it.

132 Auto_Position: 1

133 Replicate_Rewrite_DB:134 Channel_Name:135 Master_TLS_Version:136 1 row in set (0.00sec)137

138 //Start IO Thread.

139 (root@localhost mysql3306.sock)[sysbench]>start slave io_thread;140 Query OK, 0 rows affected (0.01sec)141

142 (root@localhost mysql3306.sock)[sysbench]>show slave status\G143 *************************** 1. row ***************************

144 Slave_IO_State: Waiting formaster to send event145 Master_Host: 192.168.1.101

146 Master_User: repl147 Master_Port: 3306

148 Connect_Retry: 60

149 Master_Log_File: mysql-bin.000033 //The newly binlog has been pulled to local server.

150 Read_Master_Log_Pos: 190

151 Relay_Log_File: relay-bin.000001

152 Relay_Log_Pos: 4

153 Relay_Master_Log_File:154 Slave_IO_Running: Yes //The IO Thread working normally.

155 Slave_SQL_Running: No156 Replicate_Do_DB:157 Replicate_Ignore_DB:158 Replicate_Do_Table:159 Replicate_Ignore_Table:160 Replicate_Wild_Do_Table:161 Replicate_Wild_Ignore_Table:162 Last_Errno: 0

163 Last_Error:164 Skip_Counter: 0

165 Exec_Master_Log_Pos: 0

166 Relay_Log_Space: 1433264

167 Until_Condition: None168 Until_Log_File:169 Until_Log_Pos: 0

170 Master_SSL_Allowed: No171 Master_SSL_CA_File:172 Master_SSL_CA_Path:173 Master_SSL_Cert:174 Master_SSL_Cipher:175 Master_SSL_Key:176 Seconds_Behind_Master: NULL177 Master_SSL_Verify_Server_Cert: No178 Last_IO_Errno: 0

179 Last_IO_Error:180 Last_SQL_Errno: 0

181 Last_SQL_Error:182 Replicate_Ignore_Server_Ids:183 Master_Server_Id: 1013306

184 Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e185 Master_Info_File: mysql.slave_master_info186 SQL_Delay: 0

187 SQL_Remaining_Delay: NULL188 Slave_SQL_Running_State:189 Master_Retry_Count: 86400

190 Master_Bind:191 Last_IO_Error_Timestamp:192 Last_SQL_Error_Timestamp:193 Master_SSL_Crl:194 Master_SSL_Crlpath:195 Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730211-3730214 //The newest gtid information has been got(3730211-3730214).

196 Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210

197 Auto_Position: 1

198 Replicate_Rewrite_DB:199 Channel_Name:200 Master_TLS_Version:201 1 row in set (0.00sec)202

203 //Specify the replication filter only for table "sbtest9".

204 (root@localhost mysql3306.sock)[sysbench]>CHANGE REPLICATION FILTER REPLICATE_DO_TABLE =(sysbench.sbtest9);205 Query OK, 0 rows affected (0.00sec)206

207 //Analyze the binlog on master to find out the right postion of gtid_set.

208 [root@zlm2 10:20:28 ~]209 #mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.000033 > 33.log210

211 [root@zlm2 10:20:36 ~]212 #cat 33.log213 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;214 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;215 DELIMITER /*!*/;216 # at 4

217 #180726 9:13:04 server id 1013306 end_log_pos 123 Start: binlog v 4, server v 5.7.21-log created 180726 9:13:04

218 # Warning: this binlog is either inuse or was not closed properly.219 # at 123

220 #180726 9:13:04 server id 1013306 end_log_pos 190 Previous-GTIDs221 # 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730213

222 # at 190

223 #180726 10:11:52 server id 1013306 end_log_pos 251 GTID last_committed=0 sequence_number=1 rbr_only=no224 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730214'/*!*/;225 # at 251

226 #180726 10:11:52 server id 1013306 end_log_pos 340 Query thread_id=16 exec_time=0 error_code=0

227 use `sysbench`/*!*/;228 SET TIMESTAMP=1532592712/*!*/;229 SET @@session.pseudo_thread_id=16/*!*/;230 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;231 SET @@session.sql_mode=1436549152/*!*/;232 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;233 /*!\C utf8*//*!*/;234 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;235 SET @@session.lc_time_names=0/*!*/;236 SET @@session.collation_database=DEFAULT/*!*/;237 truncate table sbtest9 //Here's the truncate operation,we are supposed the sql_thread just stop before this operation.

238 /*!*/;239 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /*added by mysqlbinlog*/ /*!*/;240 DELIMITER ;241 # End of log file

242 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;243 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;244

245 //Start SQL Thread using until clause.

246 (root@localhost mysql3306.sock)[sysbench]>start slave sql_thread until SQL_BEFORE_GTIDS='1b7181ee-6eaf-11e8-998e-080027de0e0e:3730214';247 Query OK, 0 rows affected (0.00sec)248

249 (root@localhost mysql3306.sock)[sysbench]>show slave status\G250 *************************** 1. row ***************************

251 Slave_IO_State: Waiting formaster to send event252 Master_Host: 192.168.1.101

253 Master_User: repl254 Master_Port: 3306

255 Connect_Retry: 60

256 Master_Log_File: mysql-bin.000033

257 Read_Master_Log_Pos: 340

258 Relay_Log_File: relay-bin.000007

259 Relay_Log_Pos: 395

260 Relay_Master_Log_File: mysql-bin.000033

261 Slave_IO_Running: Yes262 Slave_SQL_Running: No263 Replicate_Do_DB:264 Replicate_Ignore_DB:265 Replicate_Do_Table: sysbench.sbtest9 //Here's the "do table" option of replication filter.

266 Replicate_Ignore_Table:267 Replicate_Wild_Do_Table:268 Replicate_Wild_Ignore_Table:269 Last_Errno: 0

270 Last_Error:271 Skip_Counter: 0

272 Exec_Master_Log_Pos: 190

273 Relay_Log_Space: 821

274 Until_Condition: SQL_BEFORE_GTIDS //Here's the option of until condition of start slave clause.

275 Until_Log_File:276 Until_Log_Pos: 0

277 Master_SSL_Allowed: No278 Master_SSL_CA_File:279 Master_SSL_CA_Path:280 Master_SSL_Cert:281 Master_SSL_Cipher:282 Master_SSL_Key:283 Seconds_Behind_Master: NULL284 Master_SSL_Verify_Server_Cert: No285 Last_IO_Errno: 0

286 Last_IO_Error:287 Last_SQL_Errno: 0

288 Last_SQL_Error:289 Replicate_Ignore_Server_Ids:290 Master_Server_Id: 1013306

291 Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e292 Master_Info_File: mysql.slave_master_info293 SQL_Delay: 0

294 SQL_Remaining_Delay: NULL295 Slave_SQL_Running_State:296 Master_Retry_Count: 86400

297 Master_Bind:298 Last_IO_Error_Timestamp:299 Last_SQL_Error_Timestamp:300 Master_SSL_Crl:301 Master_SSL_Crlpath:302 Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730211-3730214

303 Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730213

304 Auto_Position: 1

305 Replicate_Rewrite_DB:306 Channel_Name:307 Master_TLS_Version:308 1 row in set (0.00sec)309

310 //Check the contents of rescued table.

311 (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest9;312 +----------+

313 | count(*) |

314 +----------+

315 | 2500 | //This is the correct number of records before we truncate the table on master.

316 +----------+

317 1 row in set (0.00sec)318

319 //Likewise,we can copy the rescued table back to master in a proper certain time by transportable tablespace tech(I'm not going to demonstrate here).