mysql 历史数据 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;

• 定时任务配置
• 数据库备份（每天凌晨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文件）