mysql二进制恢复_MySQL 二进制文件恢复

最新推荐文章于 2023-10-29 00:50:49 发布

weixin_39683734

最新推荐文章于 2023-10-29 00:50:49 发布

阅读量103

点赞数

文章标签： mysql二进制恢复

本文链接：https://blog.csdn.net/weixin_39683734/article/details/113147782

版权

1 mysql> show variables like 'autocommit';2 +---------------+-------+

3 | Variable_name | Value |

4 +---------------+-------+

5 | autocommit | ON |

6 +---------------+-------+

7 1 row in set (0.00sec)8

9 mysql> set autocommit=0;10 Query OK, 0 rows affected (0.00sec)11

12 mysql> show variables like 'autocommit';13 +---------------+-------+

14 | Variable_name | Value |

15 +---------------+-------+

16 | autocommit | OFF |

17 +---------------+-------+

18 1 row in set (0.00sec)19

21 mysql>show master status;22 +------------------+----------+--------------+------------------+-------------------+

24 +------------------+----------+--------------+------------------+-------------------+

25 | mysql-bin.000034 | 120 | | | |

26 +------------------+----------+--------------+------------------+-------------------+

27 1 row in set (0.00sec)28

30 #第二个31 mysql> create databaseluna;32 Query OK, 1 row affected (0.00sec)33

34 mysql>show master status;35 +------------------+----------+--------------+------------------+-------------------+

37 +------------------+----------+--------------+------------------+-------------------+

38 | mysql-bin.000034 | 214 | | | |

39 +------------------+----------+--------------+------------------+-------------------+

40 1 row in set (0.00sec)41

42 mysql> useluna;43 Databasechanged44 mysql> create table t1(id int);45 Query OK, 0 rows affected (0.08sec)46

47 mysql>show master status;48 +------------------+----------+--------------+------------------+-------------------+

50 +------------------+----------+--------------+------------------+-------------------+

51 | mysql-bin.000034 | 311 | | | |

52 +------------------+----------+--------------+------------------+-------------------+

53 1 row in set (0.00sec)54

56 mysql> insert into t1 values(1);57 Query OK, 1 row affected (0.00sec)58

59 mysql>show master status;60 +------------------+----------+--------------+------------------+-------------------+

62 +------------------+----------+--------------+------------------+-------------------+

63 | mysql-bin.000034 | 311 | | | |

64 +------------------+----------+--------------+------------------+-------------------+

65 1 row in set (0.00sec)66

67 mysql> commit;68 Query OK, 0 rows affected (0.33sec)69

70 mysql>show master status;71 +------------------+----------+--------------+------------------+-------------------+

73 +------------------+----------+--------------+------------------+-------------------+

74 | mysql-bin.000034 | 499 | | | |

75 +------------------+----------+--------------+------------------+-------------------+

76 1 row in set (0.00sec)77

78 #update

79 mysql> update t1 set id=11 where id=1;80 Query OK, 1 row affected (0.00sec)81 Rows matched: 1 Changed: 1 Warnings: 0

83 mysql>show master status;84 +------------------+----------+--------------+------------------+-------------------+

86 +------------------+----------+--------------+------------------+-------------------+

87 | mysql-bin.000034 | 857 | | | |

88 +------------------+----------+--------------+------------------+-------------------+

89 1 row in set (0.00sec)90

91 mysql>show master status;92 +------------------+----------+--------------+------------------+-------------------+

94 +------------------+----------+--------------+------------------+-------------------+

95 | mysql-bin.000034 | 1051 | | | |

96 +------------------+----------+--------------+------------------+-------------------+

97 1 row in set (0.00sec)98

100 #delete

101 mysql> delete from t1 where id=2;102 Query OK, 1 row affected (0.00sec)103

104 mysql>show master status;105 +------------------+----------+--------------+------------------+-------------------+

107 +------------------+----------+--------------+------------------+-------------------+

108 | mysql-bin.000034 | 1051 | | | |

109 +------------------+----------+--------------+------------------+-------------------+

110 1 row in set (0.00sec)111

112 mysql> commit;113 Query OK, 0 rows affected (0.01sec)114

115 mysql>show master status;116 +------------------+----------+--------------+------------------+-------------------+

118 +------------------+----------+--------------+------------------+-------------------+

119 | mysql-bin.000034 | 1239 | | | |

120 +------------------+----------+--------------+------------------+-------------------+

121 1 row in set (0.00sec)122

123

124 #drop

125 mysql> select * fromt1;126 +------+

127 | id |

128 +------+

129 | 11 |

130 | 3 |

131 | 4 |

132 +------+

133 3 rows in set (0.00sec)134

135 mysql> update t1 set id=44 where id=4;136 Query OK, 1 row affected (0.00sec)137 Rows matched: 1 Changed: 1 Warnings: 0

138

139 mysql> drop tablet1;140 Query OK, 0 rows affected (0.04sec)141

142 mysql> drop databaseluna;143 Query OK, 0 rows affected (0.08sec)144

145 mysql>show master status;146 +------------------+----------+--------------+------------------+-------------------+

148 +------------------+----------+--------------+------------------+-------------------+

149 | mysql-bin.000034 | 1633 | | | |

150 +------------------+----------+--------------+------------------+-------------------+

151 1 row in set (0.00sec)152

153

154

155

156 #工具查看157 mysql> show binlog events in 'mysql-bin.000034';158

159

160 #在命令行查看161

162 [root@db01-sa mysql]# mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/mysql-bin.000034

163

164

165

166 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;167 /*!40019 SET @@session.max_insert_delayed_threads=0*/;168 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;169 DELIMITER /*!*/;170 # at 4

171 #180627 17:49:07 server id 6 end_log_pos 120 CRC32 0x75f5723b Start: binlog v 4, server v 5.6.38-log created 180627 17:49:07at startup172 # Warning: this binlog is either in use or was notclosed properly.173 ROLLBACK/*!*/;174 # at 120

175 #180627 18:21:12 server id 6 end_log_pos 214 CRC32 0x0a1b14fc Query thread_id=2 exec_time=0 error_code=0

176 SET TIMESTAMP=1530094872/*!*/;177 SET @@session.pseudo_thread_id=2/*!*/;178 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;179 SET @@session.sql_mode=1075838976/*!*/;180 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;181 /*!\C utf8*//*!*/;182 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;183 SET @@session.lc_time_names=0/*!*/;184 SET @@session.collation_database=DEFAULT/*!*/;185 create databaseluna186 /*!*/;187 # at 214

188 #180627 18:22:16 server id 6 end_log_pos 311 CRC32 0x9fe876dc Query thread_id=2 exec_time=0 error_code=0

189 use `luna`/*!*/;190 SET TIMESTAMP=1530094936/*!*/;191 create table t1(id int)192 /*!*/;193 # at 311

194 #180627 18:23:02 server id 6 end_log_pos 383 CRC32 0xa66c8e7d Query thread_id=2 exec_time=0 error_code=0

195 SET TIMESTAMP=1530094982/*!*/;196 BEGIN

197 /*!*/;198 # at 383

199 #180627 18:23:02 server id 6 end_log_pos 428 CRC32 0xf0097518 Table_map: `luna`.`t1` mapped to number 70

200 # at 428

201 #180627 18:23:02 server id 6 end_log_pos 468 CRC32 0x2b1aa647 Write_rows: table id 70flags: STMT_END_F202 ### INSERT INTO`luna`.`t1`203 ### SET

204 ### @1=1 /*INT meta=0 nullable=1 is_null=0*/

205 # at 468

206 #180627 18:23:28 server id 6 end_log_pos 499 CRC32 0x62719421 Xid = 37

207 COMMIT/*!*/;208 # at 499

209 #180627 18:25:06 server id 6 end_log_pos 571 CRC32 0x84c9efe6 Query thread_id=2 exec_time=0 error_code=0

210 SET TIMESTAMP=1530095106/*!*/;211 BEGIN

212 /*!*/;213 # at 571

214 #180627 18:25:06 server id 6 end_log_pos 616 CRC32 0x74d151e2 Table_map: `luna`.`t1` mapped to number 70

215 # at 616

216 #180627 18:25:06 server id 6 end_log_pos 656 CRC32 0xd10d7120 Write_rows: table id 70flags: STMT_END_F217 ### INSERT INTO`luna`.`t1`218 ### SET

219 ### @1=2 /*INT meta=0 nullable=1 is_null=0*/

220 # at 656

221 #180627 18:27:16 server id 6 end_log_pos 701 CRC32 0x7895e39d Table_map: `luna`.`t1` mapped to number 70

222 # at 701

223 #180627 18:27:16 server id 6 end_log_pos 741 CRC32 0x5acc32fe Write_rows: table id 70flags: STMT_END_F224 ### INSERT INTO`luna`.`t1`225 ### SET

226 ### @1=3 /*INT meta=0 nullable=1 is_null=0*/

227 # at 741

228 #180627 18:27:30 server id 6 end_log_pos 786 CRC32 0xb4ed9f5a Table_map: `luna`.`t1` mapped to number 70

229 # at 786

230 #180627 18:27:30 server id 6 end_log_pos 826 CRC32 0x819e8db3 Write_rows: table id 70flags: STMT_END_F231 ### INSERT INTO`luna`.`t1`232 ### SET

233 ### @1=4 /*INT meta=0 nullable=1 is_null=0*/

234 # at 826

235 #180627 18:27:58 server id 6 end_log_pos 857 CRC32 0x452e1f31 Xid = 41

236 COMMIT/*!*/;237 # at 857

238 #180627 18:29:56 server id 6 end_log_pos 929 CRC32 0x5e68dff7 Query thread_id=2 exec_time=0 error_code=0

239 SET TIMESTAMP=1530095396/*!*/;240 BEGIN

241 /*!*/;242 # at 929

243 #180627 18:29:56 server id 6 end_log_pos 974 CRC32 0xf4e4e2bf Table_map: `luna`.`t1` mapped to number 70

244 # at 974

245 #180627 18:29:56 server id 6 end_log_pos 1020 CRC32 0x2d809738 Update_rows: table id 70flags: STMT_END_F246 ### UPDATE`luna`.`t1`247 ### WHERE

248 ### @1=1 /*INT meta=0 nullable=1 is_null=0*/

249 ### SET

250 ### @1=11 /*INT meta=0 nullable=1 is_null=0*/

251 # at 1020

252 #180627 18:30:25 server id 6 end_log_pos 1051 CRC32 0x08029580 Xid = 52

253 COMMIT/*!*/;254 # at 1051

255 #180627 18:31:25 server id 6 end_log_pos 1123 CRC32 0xcf1b6980 Query thread_id=2 exec_time=0 error_code=0

256 SET TIMESTAMP=1530095485/*!*/;257 BEGIN

258 /*!*/;259 # at 1123

260 #180627 18:31:25 server id 6 end_log_pos 1168 CRC32 0x7729069b Table_map: `luna`.`t1` mapped to number 70

261 # at 1168

262 #180627 18:31:25 server id 6 end_log_pos 1208 CRC32 0x04cb5496 Delete_rows: table id 70flags: STMT_END_F263 ### DELETE FROM`luna`.`t1`264 ### WHERE

265 ### @1=2 /*INT meta=0 nullable=1 is_null=0*/

266 # at 1208

267 #180627 18:31:36 server id 6 end_log_pos 1239 CRC32 0x27093f44 Xid = 56

268 COMMIT/*!*/;269 # at 1239

270 #180627 18:33:45 server id 6 end_log_pos 1311 CRC32 0x8be80fc2 Query thread_id=2 exec_time=0 error_code=0

271 SET TIMESTAMP=1530095625/*!*/;272 BEGIN

273 /*!*/;274 # at 1311

275 #180627 18:33:45 server id 6 end_log_pos 1356 CRC32 0x77578bf1 Table_map: `luna`.`t1` mapped to number 70

276 # at 1356

277 #180627 18:33:45 server id 6 end_log_pos 1402 CRC32 0x9c7bf8df Update_rows: table id 70flags: STMT_END_F278 ### UPDATE`luna`.`t1`279 ### WHERE

280 ### @1=4 /*INT meta=0 nullable=1 is_null=0*/

281 ### SET

282 ### @1=44 /*INT meta=0 nullable=1 is_null=0*/

283 # at 1402

284 #180627 18:34:17 server id 6 end_log_pos 1433 CRC32 0x354e0150 Xid = 60

285 COMMIT/*!*/;286 # at 1433

287 #180627 18:34:17 server id 6 end_log_pos 1548 CRC32 0x52b3dc50 Query thread_id=2 exec_time=0 error_code=0

288 SET TIMESTAMP=1530095657/*!*/;289 DROP TABLE `t1` /*generated by server*/

290 /*!*/;291 # at 1548

292 #180627 18:34:26 server id 6 end_log_pos 1633 CRC32 0x2e40af97 Query thread_id=2 exec_time=0 error_code=0

293 SET TIMESTAMP=1530095666/*!*/;294 drop databaseluna295 /*!*/;296 DELIMITER ;297 # End of log file

298 ROLLBACK /*added by mysqlbinlog*/;299 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;300 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;301

302

303

304 #截取305 [root@db01-sa mysql]# mysqlbinlog --start-position=120 --stop-position=857 /data/mysql/mysql-bin.000034 >/tmp/luna2.sql

306 WARNING: The range of printed events ends with a row event or a table map event that does nothave the STMT_END_F flag307 set. This might be because the last statement was not fully written to the log, orbecause you are using a308 --stop-position or --stop-datetime that refers to an event in the middle of a statement. The event(s) from

309 the partial statement have not been written tooutput.310

311 警告:打印事件的范围以行或表映射事件结束,没有设置了STMT_END_F标志。这可能是因为过去的声明没有完全写入日志,或者因为您使312 用的是——停止位置,stop-datetime指一个事件在一份声明中。部分语句中的事件尚未写入输出。313

314 #产生以上报错就是因为截取的时候一定要注意begin begin是一个语句的开始一定要从begin之前开始截断315 之前的语句mysqlbinlog --start-position=120 --stop-position=974 /data/mysql/mysql-bin.000034 >/tmp/luna.sql 当中的974要改成857

316

317 mysql> set sql_log_bin=0;318 Query OK, 0 rows affected (0.00sec)319

320 mysql> source /tmp/luna.sql;321 Query OK, 0 rows affected (0.00sec)322

323 Query OK, 0 rows affected, 1 warning (0.00sec)324

325 Query OK, 0 rows affected (0.00sec)326

327 Query OK, 0 rows affected (0.00sec)328

329 Query OK, 0 rows affected (0.00sec)330

331 Query OK, 0 rows affected (0.00sec)332

333 Query OK, 0 rows affected (0.00sec)334

335 Query OK, 0 rows affected (0.00sec)336

337 Query OK, 0 rows affected (0.00sec)338

339 Query OK, 0 rows affected (0.00sec)340

341 Charset changed342 Query OK, 0 rows affected (0.00sec)343

344 Query OK, 0 rows affected (0.00sec)345

346 Query OK, 0 rows affected (0.00sec)347

348 Query OK, 0 rows affected (0.00sec)349

350 Query OK, 1 row affected (0.00sec)351

352 Databasechanged353 Query OK, 0 rows affected (0.00sec)354

355 Query OK, 0 rows affected (0.36sec)356

357 Query OK, 0 rows affected (0.00sec)358

359 Query OK, 0 rows affected (0.00sec)360

361 Query OK, 0 rows affected (0.01sec)362

363 Query OK, 0 rows affected (0.00sec)364

365 Query OK, 0 rows affected (0.01sec)366

367 Query OK, 0 rows affected (0.00sec)368

369 Query OK, 0 rows affected (0.00sec)370

371 Query OK, 0 rows affected (0.03sec)372

373 Query OK, 0 rows affected (0.00sec)374

375 Query OK, 0 rows affected (0.01sec)376

377 Query OK, 0 rows affected (0.00sec)378

379 Query OK, 0 rows affected (0.00sec)380

381 Query OK, 0 rows affected (0.00sec)382

383 Query OK, 0 rows affected (0.00sec)384

385 Query OK, 0 rows affected (0.00sec)386

387 mysql> show database;388 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1

389 mysql>show databases;390 +--------------------+

391 | Database |

392 +--------------------+

393 | information_schema |

394 | binlog |

395 | luna |

396 | mysql |

397 | nod01 |

398 | oldboy |

399 | performance_schema |

400 | test |

401 | world |

402 +--------------------+

403 9 rows in set (0.00sec)404

405 mysql> useluna406 Databasechanged407 mysql>

408 mysql>

409 mysql> show table;410 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

411 mysql>show tables;412 +----------------+

413 | Tables_in_luna |

414 +----------------+

415 | t1 |

416 +----------------+

417 1 row in set (0.00sec)418

419 mysql> select * fromt1;420 +------+

421 | id |

422 +------+

423 | 1 |

424 | 2 |

425 | 3 |

426 | 4 |

427 +------+

428 4 rows in set (0.00sec)429

430 mysql>show master status;431 +------------------+----------+--------------+------------------+-------------------+

433 +------------------+----------+--------------+------------------+-------------------+

434 | mysql-bin.000034 | 1633 | | | |

435 +------------------+----------+--------------+------------------+-------------------+

436 1 row in set (0.00 sec)

weixin_39683734

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql二进制恢复_MySQL 二进制文件恢复

1 mysql> show variables like 'autocommit';2 +---------------+-------+3 | Variable_name | Value |4 +---------------+-------+5 | autocommit | ON |6 +---------------+-------+7 1 row in set (0.00...
复制链接

扫一扫