mysql利用数据库日志恢复数据(MySQL通过binlog恢复数据)
mysql利用数据库日志恢复数据
MySQL通过binlog恢复数据mysql 日志文件
任何成熟软件都会有一套成熟的日志系统,当软件出现问题时,这些日志就是查询问题来源的宝库。同样,mysql也不例外,也会有一系列日志记录mysql的运行状态。
mysql主要有以下几种日志:
- 错误日志:记录mysql运行过程中的错误信息
- 一般查询日志:记录mysql正在运行的语句,包括查询、修改、更新等的每条sql
- 慢查询日志:记录查询比较耗时的SQL语句
- binlog日志:记录数据修改记录,包括创建表、数据更新等
这些日志均需要在my.cnf文件进行配置,如果不知道mysql的配置文件路径,可以使用mysql命令进行查找,
mysql --verbose --help|grep -A 1 'Default options' #该命令会罗列出my.cnf顺序查找的路径。
binlog日志
binlog就是binary log,二进制日志文件,记录所有数据库更新语句,包括表更新和记录更新,即数据操纵语言(DML),binlog主要用于数据恢复和配置主从复制等;
数据恢复:当数据库误删或者发生不可描述的事情时,可以通过binlog恢复到某个时间点的数据。主从复制:当有数据库更新之后,主库通过binlog记录并通知从库进行更新,从而保证主从数据库数据一致;
mysql按照功能分为服务层模块和存储引擎层模块,服务层负责客户端连接、SQL语句处理优化等操作,存储引擎层负责数据的存储和查询;binlog属于服务层模块的日志,即引擎无关性,所有数据引擎的数据更改都会记录binlog日志。当数据库发生崩溃时,如果使用InnoDB引擎,binlog日志还可以检验InnoDB的redo日志的commit情况。
binlog日志开启
日志开启方式:
1、添加配置
log_bin=ON log_bin_basename=/path/bin-log log_bin_index=/path/bin-log.index
2、仅仅设置log-bin参数
log-bin=/path/bin-log
当开启binlog日志之后,mysql会创建一个 log_bin_index指定的 .index 文件和多个二进制日志文件,index中按顺序记录了mysql使用的所有binlog文件。binlog日志则会以指定的名称(或默认值) 加自增的数字作为后缀,ex:bin-log.000001,当发生下述三种情况时,binlog日志便会进行重建:
文件大小达到max_binlog_size参数的值 执行 flush logs命令 重启mysql服务
binlog 日志格式
通过参数binlog_format参数的值,可以设置binlog的格式,可选值有 statement、row、mixed * statement格式:记录数据库执行的原始SQL语句 * row格式:记录具体的行的修改,这个为目前默认值 * mixed格式:因为上边两种格式各有优缺点,所以就出现了mixed格式
binlog日志查看工具:mysqlbinlog
因为binlog是二进制文件,不能像其他文件一样,直接打开查看。但mysql提供了binlog查看工具mysqlbinlog,可以解析二进制文件。当然不同格式的日志解析结果是不一样的; 1. statement格式日志,执行mysqlbinlog /path/bin-log.000001,可以直接看到原始执行的SQL语句 2. row格式日志,则可读性没有那么好,但仍可通过参数使文档更加可读 mysqlbinlog -v /path/bin-log.000001
mysqlbinlog两对非常重要的参数 1. --start-datetime --stop-datetime 解析某一个时间段内的binlog; 2. --start-position --stop-position 解析在两个position之间的binlog;
使用binlog恢复数据
使用binlog恢复数据,本质上就是通过binlog找到所有DML操作,去掉错误的SQL语句,然后重走一遍长征路,就可以将数据恢复;
线下实操
1.创建数据表并插入初始值
CREATE TABLE `users` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(8) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `users` (`id`, `name`, `age`) VALUES (null, '姓名一', 5);
2.找到上一次全量备份的数据库和binlog的position(ps:当然也可以通过时间进行恢复)。此处以目前状态作为备份的初始值,
mysqldump -uroot -p T > /path/xxx.sql; # 备份数据库 show master status; # 查看当前的position位置,此时值为154
3.插入多条记录
INSERT INTO `users` (`id`, `name`, `age`) VALUES (null, '姓名二', 13), (null, '姓名三', 14), (null, '姓名四', 15), (null, '姓名五', 16), (null, '姓名六', 17);
4.进行误操作,并且在误操作之后又插入几条数据
update users set age = 5; INSERT INTO `users` (`id`, `name`, `age`) VALUES (null, '姓名七', 16), (null, '姓名八', 18);
5.发现误操作之后,进行数据恢复,首先停止mysql对外的服务,利用备份数据恢复到上次数据;
6.通过mysqlbinlog命令对二进制文件进行分析,分析发现
误操作发生在position为706位置,且上次正常操作的结束位置在513 在1152到结尾位置有正常执行的SQL执行
7.通过mysqlbinlog命令从binlog日志中导出可执行的SQL文件,并将数据导入到mysql
mysqlbinlog --start-position=154 --stop-position=513 bin-log.000001 > /path/bak.sql; mysql -uroot -p < /path/bak.sql;
8.跳过错误的更新语句,再通过步骤7的逻辑把后续正常语句重新跑一遍,完成数据恢复工作
小结
无论什么时间,数据库发生崩溃都会令人愁眉紧锁,心烦意乱。binlog可以说是在各种情况下,数据库崩溃、数据丢失之后的一粒后悔药,本文通过线下环境,简单的对数据库进行了一次数据恢复实验,如有不对,还请指教
以上就是MySQL通过 binlog 恢复数据的详细内容,更多关于MySQL binlog 恢复数据的资料请关注开心学习网其它相关文章!
- mysql删库操作记录(mysql常用sql与命令之从入门到删库跑路)
- 阿里云mysql配置升级注意事项(详解如何在阿里云上安装mysql)
- mysql索引原理和优化(Mysql 索引该如何设计与优化)
- mysql删除数据库的命令(MySQL 线上数据库清理数据的方法)
- mysql数据库主键选择的详解(图文详解MySQL中的主键与事务)
- mysql客户端怎么运行程序(MySQL 如何连接对应的客户端进程)
- mysql字符集怎么看(mysql字符集相关总结)
- navicat for mysql连接测试失败(Navicat连接虚拟机mysql常见错误问题及解决方法)
- mysql和explain哪个好(MySQL EXPLAIN输出列的详细解释)
- mysql查看执行计划
- mysql经典问题(MySQL null的一些易错点)
- mysqlshell日常运维脚本(监控MySQL主从状态的shell脚本)
- 最新版mysql 安装教程(新手如何安装Mysql亲测有效)
- mysql的默认隔离级别(再有人问你MySQL的隔离级别是什么,就把这篇文章发给他!)
- 创建数据库入门教程mysql(MySQL数据库安装教程一学就会)
- dockermysql实例(docker 连接宿主Mysql操作)
- 红色代表什么(红色代表什么寓意)
- 蓝天代表什么(蓝天代表什么生肖)
- 今天要吃什么(今天要吃什么菜)
- 营养餐是什么(学校营养餐是什么)
- 谁说女子不如男 范冰冰演的武则天只是其一,另外两位你认识吗(谁说女子不如男)
- 杯酒人生---瓦伦丁酒杯和奥丁格啤酒(杯酒人生---瓦伦丁酒杯和奥丁格啤酒)
热门推荐
- html5菜单栏(html5写一个BUI折叠菜单插件的实现方法)
- phpstudyv8.0使用教程学习(phpStudy环境安装SSL证书教程)
- cssdiv边框外发光怎么设置(DIV+CSS实现带三角箭头的提示框)
- phpstudy 目录浏览宽度(JspStudy如何设置PHP根目录可编辑)
- css怎么样让banner不动(CSS banner图响应式居中显示的方法)
- 微信小程序开发模式(微信小程序引入Vant框架的全过程记录)
- css控制滚动条的样式
- php实现非递归快速排序(PHP实现无限极分类的两种方式示例递归和引用方式)
- linux启动jenkins(linux 下jenkins项目搭建过程centos7为例 )
- 数据推入阿里云rds(将阿里云RDS中的数据库迁移到本机自建数据库的可用过程RDS数据迁移)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9