MySQL通过binlog恢复数据的办法介绍(代码示例)
通过理解 binlog 日志的相干配置,简略把握通过 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 语句,然后重走一遍长征路,就可以将数据恢复;
线下实操:
新建数据表并插入初始值
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);
寻到上一次全量备份的数据库和binlog的position(ps:固然也可以通过工夫进行恢复)。此处以当前状态作为备份的初始值,
mysqldump -uroot -p T > /path/xxx.sql; # 备份数据库 show master status; # 查看目前的position位置,此时值为154
插入多笔记录
INSERT INTO `users` (`id`, `name`, `age`) VALUES (null, '姓名二', 13), (null, '姓名三', 14), (null, '姓名四', 15), (null, '姓名五', 16), (null, '姓名六', 17);
进行误操纵,而且在误操纵之后又插入几条数据
update users set age = 5; INSERT INTO `users` (`id`, `name`, `age`) VALUES (null, '姓名七', 16), (null, '姓名八', 18);
发明误操纵之后,进行数据恢复,第一休止 mysql 对外的办事,应用备份数据恢复到上次数据;
通过 mysqlbinlog 下令对二进制文件进行剖析,剖析发明
误操纵产生在position为706位置,且上次正常操纵的完毕位置在513 在1152到结尾位置有正常施行的SQL施行
通过 mysqlbinlog 下令从 binlog 日志中导出可施行的 SQL 文件,并将数据导入到mysql
mysqlbinlog --start-position=154 --stop-position=513 bin-log.000001 > /path/bak.sql; mysql -uroot -p < /path/bak.sql;
跳过差错的更新语句,再通过步骤7的逻辑把后续正常语句从新跑一遍,完成数据恢复工作
小结
不管什么工夫,数据库产生解体都会令人愁眉紧锁,心烦虑乱。binlog可以说是在各种状况下,数据库解体、数据遗失之后的一粒懊悔药,本文通过线下环境,简略的对数据库进行了一次数据恢复实验,若有不合错误,还请指教
本篇文章到这里就全部完毕了,对于MySQL的更多见识大家可以关注 百分百源码网 的MySQL教程栏目!!!
以上就是MySQL通过binlog恢复数据的办法介绍(代码示例)的细致内容,更多请关注 百分百源码网 其它相干文章!