MySQL二进制日志 恢复数据

MySQL二进制日志

什么是Binlog？

 

二进制日志：记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中；
主要作用：复制（Master-Slave 主从同步）、恢复和审计。

 

Binlog相关变量

变量名称	变量含义	相关语句
log_bin	Binlog开关	查看变量：show variables like ‘log_bin’;
binlog_format	Binlog日志的格式	查看变量：show variables like 'binlog_format';

Binlog 日志的三种格式

格式类型	格式特性
ROW	仅保存记录被修改细节，不记录SQL语句上下文相关信息
STATEMENT	每一条会修改数据的SQL都会记录在Binlog中
MIXED	以上两种level的混合使用

 

日志恢复

方案一. 日志增量恢复数据

先导入上次备份的数据，找到备份数据后 mysqlbinlog 时间点 和 偏移量 （position） 来恢复数据

1: mysqlbinlog 导出 sql 语句来执行，时间和偏移量选择一个就行，示列如下

#通过时间点导出 数据库为xxxx 确实时间：2019-10-24 05:00:00 结束时间：2019-10-24 11:28:00cd 到 mysql/bin下面mysqlbinlog --database=数据库 --start-datetime='2019-10-24 05:00:00' --stop-datetime='2019-10-24 11:28:00'  mysql-bin.xx> /data/binlog.sql
#附docker 命令docker exec -i 容器ID mysqlbinlog --database=数据库 --start-datetime='2019-10-24 05:00:00' --stop-datetime='2019-10-24 11:28:00'  /var/lib/mysql/mysql-bin.xx> /data/binlog.sql

 

#通过 偏移量导出 数据库为xxx 起始偏移量:709272650 结束偏移量：743558867mysqlbinlog ---database=数据库 --start-position=709272650 --stop-position=743558867  mysql-bin.xx> /binlog.sql

 

2：检查导出的 binlog.sql 是否正常, 导出的sql 可能包含编码过的语句，是可以正常执行的，如下图：

SET TIMESTAMP=1571877783/*!*/;BEGIN/*!*/;# at 712883233#191024  8:43:03 server id 1  end_log_pos 712883441 	Table_map: `tzyy`.`recipe` mapped to number 4389056# at 712883441#191024  8:43:03 server id 1  end_log_pos 712883499 	Table_map: `tzyy`.`zyll_state` mapped to number 4389131# at 712883499#191024  8:43:03 server id 1  end_log_pos 712883562 	Table_map: `tzyy`.`recipe_history` mapped to number 4389083# at 712883562#191024  8:43:03 server id 1  end_log_pos 712883648 	Table_map: `tzyy`.`upload_recipe` mapped to number 4389143# at 712883648#191024  8:43:03 server id 1  end_log_pos 712883710 	Table_map: `tzyy`.`recipe_track` mapped to number 4389132# at 712883710#191024  8:43:03 server id 1  end_log_pos 712884420 	Update_rows: table id 4389056# at 712884420#191024  8:43:03 server id 1  end_log_pos 712884767 	Update_rows: table id 4389143 flags: STMT_END_F
BINLOG 'l/OwXRMBAAAA0AAAAPG8fSoAAMD4QgAAAAEABHR6eXkABnJlY2lwZQBRAw8HCg8DBwMDCg8PAw8DAwMPDw8DAwMDDw8DAwMDAwMPBwMD9vYPDw8DDw/2BAMPAwMDAwMDAwMDBwP29gP2Dw8DAw8D9gMPD/b2Aw8PDw8PSTwAWAI8ADwAPACWAJYALAE8ADwAPAAUCBQIlgAeAB4AhAM8ABQIBDwAFAUUBQoDPABaAFoAFAU8ADwAFAUUBSwBHgAeAJYALAH+AQ==l/OwXRMBAAAAOgAAACu9fSoAAAv5QgAAAAEABHR6eXkACnp5bGxfc3RhdGUACAMHAwMPAwMHArAE9g==l/OwXRMBAAAAPwAAAGq9fSoAANv4QgAAAAEABHR6eXkADnJlY2lwZV9oaXN0b3J5AAcDAwMDBw8PBFgCLAF+l/OwXRMBAAAAVgAAAMC9fSoAABf5QgAAAAEABHR6eXkADXVwbG9hZF9yZWNpcGUAEAMMAwMDDA8PDw8PDw8DDw8SlgCWAJYALAEsATwAlgBaAJYA/v8=l/OwXRMBAAAAPgAAAP69fSoAAAz5QgAAAAEABHR6eXkADHJlY2lwZV90cmFjawAIAw8PAwMHBwMElgCWAP4=l/OwXRgBAAAAxgIAAMTAfSoAAMD4QgAAAAAAUf8AAAAAwo0goikF/iWcAQAKMjkxMDI0MDAyOZPzsF1Yxw8GAOS7o+aKkwUAAACT87BdBQAAAAEAAABYxw8BNwAwAAAAAAIAAAACAAAAdnkAAAnpg5HmmKXnlJ8IODIyOTc1ODYZAOWNl+WNl+WumOWkp+mBkzc45byENTTlj7d+AwAA0AMAANMDAAABAAAAAAAOAAAAAAAAAAAAAAABAAAAAQAAAAIAAAAAAAAAAAAAAACAAAAAAAAAAAAAgAAAAAAAAAAAAAAAAACAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAgAAADwAAAAAAAAAAAAAAAAAAACAAAAAAAAAAAAAgAAAAAAAAAAAAIAAAAoAAAAAAAAAAAAAAAAAAAEAAAAAgAAAAAAAAAAAAAQAAAAAAAAAAAAAAAAAAAKMIKIpBf4lnAEACjI5MTAyNDAwMjmT87BdWMcPBgDku6PmipMFAAAAk/OwXQUAAAABAAAAWMcPATcAMAAAAAACAAAAAgAAAHZ5AAAJ6YOR5pil55SfCDgyMjk3NTg2GQDljZfljZflrpjlpKfpgZM3OOW8hDU05Y+3fgMAANADAADTAwAAAQAAAAAADgAAAAAAAAAAAAAAAQAAAAEAAAACAAAAAAAAAAAAAAAAgAAAAAAAAAAAAIAAAAAAAAAAAAAMMjYwNDk2ODI0MTIzAzU3NgQ1NzZCAQAAAIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACAAAAPAAAAAAAAAAAAAAAAAAAAIAAAAAAAAAAAACAAAAAAAAAAAAAgAAACgAAAAAAAAAAAAAAAAAAAQAAAACAAAAAAAAAAAAABAAAAAAAAAAAAAA=l/OwXRgBAAAAWwEAAB/CfSoAABf5QgAAAAEAEP9AAJtTAAAjddEWXRIAAAAAAAAAAAAAJZwBACN10RZdEgAACDgyMjk3NTg2CemDkeaYpeeUnxkA5Y2X5Y2X5a6Y5aSn6YGTNzjlvIQ1NOWPtzQA5rWZ5rGf55yB5Y+w5bee5biC6Lev5qGl5Yy65Y2X5Y2X5a6Y5aSn6YGTNzjlvIQ1NOWPtwnot6/moaXljLoAAAAAAAAAAACbUwAAI3XRFl0SAAAAAAAAAAAAACWcAQAjddEWXRIAAAwyNjA0OTY4MjQxMjMIODIyOTc1ODYJ6YOR5pil55SfGQDljZfljZflrpjlpKfpgZM3OOW8hDU05Y+3NADmtZnmsZ/nnIHlj7Dlt57luILot6/moaXljLrljZfljZflrpjlpKfpgZM3OOW8hDU05Y+3Cei3r+ahpeWMugAAAAAAAAA='/*!*/;# at 712884767#191024  8:43:03 server id 1  end_log_pos 712884794 	Xid = 475556393COMMIT/*!*/;# at 712884794

 

3：导入binglog 生成的sql 语句

mysql -uroot -p密码 数据库 <binlog.sql

 

方案二. 日志全量恢复

本方案的前提是你的二进制日志没有被删除（因为硬盘的问题，我们一般只保留了最近七天的日志）

#确定时间点或偏移量，

#时间恢复mysqlbinlog --database=数据库 --start-datetime='开始时间' --stop-datetime='结束时间'  mysql-bin.xx | mysql -u用户名 -p密码 --database=数据库
#附docker 命令docker exec -i mysql容器ID mysqlbinlog --database=数据库 --start-datetime='开始时间' --stop-datetime='结束时间'  /var/lib/mysql/mysql-bin.xx/mysql-bin.xx | docker exec -i mysql容器ID mysql -u用户名 -p密码 --database=数据库
#偏移量恢复mysqlbinlog --database=数据库 --start-position=其实偏移量 --stop-position=结束偏移量 mysql-bin.xxx | mysql -u用户名 -p密码 --database=数据库

 

总结：

方案一我们可以把备份数据导到其他地方，再把增量的二进制日志生成sql 导入，检查无误再导入正式库；

方案二是在正式库直接执行的，有极大的风险，执行前最好备份好数据。