MySQL-利用binlog恢复数据
这一篇文章里，我们来记录使用mysql-binlog来恢复数据的整个过程
一、前期准备
1、建库建表
创建数据库blog以及下面的article文章表
1 create table if not exists `article` ( 2 `id` int(11) not null auto_increment, 3 `title` varchar(128) not null COMMENT '文章标题', 4 `content` text not null COMMENT '文章内容', 5 `status` int(11) not null default '1' COMMENT '文章状态 0-下架 1-上架', 6 `create_time` int(11) COMMENT '创建时间', 7 `update_time` int(11) default '0' COMMENT '修改时间', 8 primary key (`id`) 9 ) engine = InnoDB default CHARSET = utf8mb4 COMMENT = '文章详情表';
插入数据：
1 insert into article(`title`,`content`,`create_time`)
2 values ('Yii2小部件详解','测试1',unix_timestamp(now())),
3 ('查询构建器','测试2',unix_timestamp(now())),
4 ('使用 Gii 生成代码','测试3',unix_timestamp(now()));
现在看下数据表中的数据：
2. 备份完整的数据库
假设现在是凌晨2点，定时任务开始执行一次完整的数据库备份
将blog数据库备份到 /root/bak.blog.sql 文件中，执行以下命令：
mysqldump -uroot -p123456 -F -B blog >/root/bak.blog.sql
注意该命令中使用了 -F 选项，当备份工作刚开始时系统会刷新log日志，产生新的binlog日志来记录备份之后的数据库“增删改”操作，查看一下：
备份完成后，陆续执行更新article表中数据的命令：
update blog.article set content='测试1更改' where id=1;
update blog.article set content='测试2更改' where id=2;
update blog.article set content='测试3更改' where id=3;
再过了一会儿，莫名地执行了一条悲催的SQL语句，整个数据库都没了
drop database blog;
这下怎么办，删库跑路，还是冷静分析，一个人默默地承担所有。。。
先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)
show master logs;
二、恢复数据
1. 备份最后一个日志文件：
执行下面命令：
ll /www/server/data | grep mysql-bin
cp -v /www/server/data/mysql-bin.000027 /root/
2. 执行刷新日志索引操作
重新开始新的binlog日志记录文件，这个时候mysql-bin.000027 这个文件不会再有后续写入了(便于我们分析原因及查找pos点)，以后所有数据库操作都会写入到下一个日志文件；
flush logs;
show master status;
3. 读取binlog日志，分析问题
show binlog events in 'mysql-bin.000027' limit 100
通过分析，造成数据库破坏的pos点区间是介于 1567--1723 之间，只要恢复到1567前就可以啦
4. 将之前凌晨2点备份的数据恢复
/www/server/mysql/bin/mysql -uroot -p123456 -v < /root/bak.blog.sql
5. 从binlog日志恢复数据
此pos结束点介于导入数据与更新之间，这样可以恢复到更改"content='测试1更改'"之前的“导入测试数据”
/www/server/mysql/bin/mysqlbinlog --stop-position=646 --database=blog /www/server/data/mysql-bin.000026 | /www/server/mysql/bin/mysql -uroot -p123456 -v blog
说明：这里实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径
6. 指定pos点区间恢复(部分恢复)
6.1 更新 content='测试1更改' 这条数据
日志区间是Pos[725] --> End_log_pos[922]，按事务区间是：Pos[646] --> End_log_pos[953]；
1) 方式一：单独恢复 content='测试1更改' 这步操作，可这样： /www/server/mysql/bin/mysqlbinlog --start-position=725 --stop-position=922 --database=blog /www/server/data/mysql-bin.000027 | /www/server/mysql/bin/mysql -uroot -p123456 -v blog 2) 方式二：也可以按事务区间单独恢复，如下： /www/server/mysql/bin/mysqlbinlog --start-position=646 --stop-position=953 --database=blog /www/server/data/mysql-bin.000027 | /www/server/mysql/bin/mysql -uroot -p123456 -v blog
6.2 更新 content='测试2更改' 这条数据
日志区间是Pos[1032] --> End_log_pos[1229]，按事务区间是：Pos[953] --> End_log_pos[1260]；
1) 方式一：单独恢复 content='测试2更改' 这步操作，可这样： /www/server/mysql/bin/mysqlbinlog --start-position=1032 --stop-position=1229 --database=blog /www/server/data/mysql-bin.000027 | /www/server/mysql/bin/mysql -uroot -p123456 -v blog 2) 方式二：也可以按事务区间单独恢复，如下： /www/server/mysql/bin/mysqlbinlog --start-position=953 --stop-position=1260 --database=blog /www/server/data/mysql-bin.000027 | /www/server/mysql/bin/mysql -uroot -p123456 -v blog
6.3 更新 content='测试3更改' 这条数据
日志区间是Pos[1339] --> End_log_pos[1536]，按事务区间是：Pos[1260] --> End_log_pos[1567]；
1) 方式一：单独恢复 content='测试1更改' 这步操作，可这样： /www/server/mysql/bin/mysqlbinlog --start-position=1339 --stop-position=1536 --database=blog /www/server/data/mysql-bin.000027 | /www/server/mysql/bin/mysql -uroot -p123456 -v blog 2) 方式二：也可以按事务区间单独恢复，如下： /www/server/mysql/bin/mysqlbinlog --start-position=1260 --stop-position=1567 --database=blog /www/server/data/mysql-bin.000027 | /www/server/mysql/bin/mysql -uroot -p123456 -v blog
恢复完成，我们再看下blog中article表中的数据：
我们看到：所谓的数据恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已
标签：bin,binlog,www,--,恢复,MySQL,server,blog,mysql From： https://www.cnblogs.com/hld123/p/17115848.html