Mysql之通过Binlog日志实现数据恢复

一、binlog日志

• 1.binlog日志介绍

  1）binlog也叫归档日志，它属于mysql的server层，所以所有储引擎都可以使用该日志。
  2）binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
  3）binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

• 2.常用命令

• 查看所有binlog日志列表

  mysql> show master logs;
  

• 查看最新日志编号名称

  mysql> show master status;
  

• 重置(清空)所有binlog日志

  mysql> reset master;
  

• 刷新日志

  mysql> flush logs;
  

• 查看binlog日志详情（方法一）

  mysqlbinlog --no-defaults   mysql-bin-master.000001
  

  

• 查看binlog日志详情（方法二）

  mysql>show binlog events in 'mysql-bin-master.000001'\G;
  

  

• 定时任务配置
  https://www.cnblogs.com/sxdcgaq8080/p/10730633.html

• 数据库备份（每天凌晨1点）

  crontab -e
  

• 编辑页面加上如下脚本

  0 1 * * * /data/mysql/mysqlbak.sh > /dev/null 2>&1 &
  

• mysqlbak.sh内容如下（记得授权一下脚本）

  /usr/local/mysql/bin/mysqldump -uroot -p -B -F -R -x --master-data=2 hibernate|gzip >/data/mysql/hibernate_$(date +%F).sql.gz
  

  参数说明：
  -B：指定数据库
  -F：刷新日志
  -R：备份存储过程等
  -x：锁表
  –master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

• 查看定时任务日志

  tail -f -n 200 /var/log/cron
  

  

二、数据恢复：

• 1.找到binlog日志删除数据库的位置

  mysql>show binlog events in 'mysql-bin-master.000001'\G;
  

  

• 2.找到备份文件，并恢复到备份截止时期状态。

  mysql>gzip -d hibernate_2020-03-25.sql.gz
  mysql> mysql -uroot -p -v < hibernate_2020-03-25.sql
  

  

• 3.从binlog日志恢复备份截止时间到删库的前一刻的数据
  恢复命令的语法格式：
  mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

  常用参数选项解释：
  –start-position=875 起始pos点
  –stop-position=954 结束pos点
  –start-datetime=“2016-9-25 22:01:08” 起始时间点
  –stop-datetime=“2019-9-25 22:09:46” 结束时间点
  –database=hibernate 指定只恢复hibernate 数据库(一台主机上往往有多个数据库，只限本地log日志)

  不常用选项：
  -u --user=name 连接到远程主机的用户名
  -p --password[=name] 连接到远程主机的密码
  -h --host=name 从远程主机上获取binlog日志
  –read-from-remote-server 从某个MySQL服务器上读取binlog日志

• 恢复到指定位置

   /usr/local/mysql/bin/mysqlbinlog --stop-position=885 --database=hibernate /data/mysql-bin-master.000004 | /usr/local/mysql/bin/mysql -uroot -p123456 -v hibernate
  

• 恢复到指定截止时间

  /usr/local/mysql/bin/mysqlbinlog --no-defaults  --stop-datetime="2020-03-25 15:39:13" --database=hibernate /data/mysql-bin-master.000004 | /usr/local/mysql/bin/mysql -uroot -p123456 -v hibernate
  

• 总结：

  所谓数据恢复，主要两个步骤：
  1）恢复备份数据（通过备份文件）
  2）恢复备份截止时间到删库期间新增的数据（通过binlog文件）