MySQL_通过Binlog日志恢复数据库
1、MySQL Binlog简介
MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。
2、Binlog日志使用场景
1、MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
2、数据恢复：通过使用 mysqlbinlog工具来使恢复数据
3、查看Binlog日志状态
常用的Binlog操作命令
-- 是否启用binlog日志 show variables like 'log_bin'; -- 查看binlog的目录 show variables like '%log_bin%'; -- mysql数据存储目录 show variables like'%dir%'; -- 查看详细的日志配置信息 show global variables like '%log%'; -- 查看当前服务器使用的biglog文件及大小 show binary logs; -- 查看 binlog 内容 show binlog events; -- 查看具体一个binlog文件的内容 （in 后面为binlog的文件名） show binlog events in 'master.000003'; -- 设置binlog文件保存事件，过期删除，单位天 set global expire_log_days=3; -- 删除当前的binlog文件 reset master; -- 删除slave的中继日志 reset slave; -- 删除指定日期前的日志索引中binlog日志文件 purge master logs before '2019-03-09 14:00:00'; -- 删除指定日志文件 purge master logs to 'master.000003';
查看情况： binlog日志开启状态为ON
4、开启Binlog日志(未开启)
通过配置
/etc/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf配置文件的log-bin选项：
##配置binlog日志路径以及日志文件名前缀、生成的日志格式为：bin-log.000001 log-bin=/var/lib/mysql/bin-log ##注意5.7以及更高版本需要配置本项、自定义，保证唯一性 server-id=1 ##binlog格式，有3种statement,row,mixed，区别的话，请自行搜索 binlog-format=mixed ##表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定 sync-binlog=1
解析：binlog格式，有3种statement、row、mixed① STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave 中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
② ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
③ MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
5、重启生效配置(未开启)
MySQL重启
# 使用 service 重启 service mysqld restart # 使用 mysqld 脚本重启 /etc/inint.d/mysqld restart
使用
[root@plmu8 ~]# service mysqld restart Redirecting to /bin/systemctl restart mysqld.service [root@plmu8 ~]#
6、查看Binlog文件内容
-- 查看Binlog文件内容 show binlog events in 'bin-log.000001';
7、操作测试
新建一个数据库，进行一系列操作
flush logs; -- 刷新（创建日志文件） create table test( id int auto_increment not null primary key, val int, data varchar(20) ); insert into test(val, data) values (10, 'wu'); insert into test(val, data) values (20, 'yang'); insert into test(val, data) values (20, 'ping'); flush logs; -- 刷新（创建日志文件） insert into test(val, data) values (40, 'hao'); insert into test(val, data) values (50, 'iteblog'); delete from test where id between 4 and 5; insert into test(val, data) values (60, 'iteblog1'); flush logs; -- 刷新（创建日志文件） insert into test(val, data) values (70, 'ping123'); insert into test(val, data) values (80, 'ping163'); drop table test; flush logs;
linux中生成3个文件
查看文件内容
8、通过Binlog指定位置恢复数据
操作解析
/usr/bin/mysqlbinlog --start-position=573 --stop-position=718 --database=hello /var/lib/mysql/bin-log-1.000001 | /usr/bin/mysql -uroot -p123456zy -v hello # /usr/bin/mysqlbinlog 为binlog命令 # --database=hello 指定数据库为hello的日志 # /var/lib/mysql/bin-log-1.000001 为binlog日志 # | 为管道符 # /usr/bin/mysql -uroot -p123456zy -v hello 连接mysql、并指定需要恢复的数据库 -v 为显示详细信息 # --start-position=573 开始位置 # --stop-position=718 结束位置
通过指定位置恢复数据思路：
确定对应的数据库日志（即你已经删除的数据日志）
确定起始位置、终止位置和要执行的操作（如：并不想将delete/drop的操作恢复到数据库，只需要insert）
在文件内容中，我们可以看到
日志bin-log.000004：DELETE的操作的起始位置是956，终止位置是1071;
日志bin-log.000005：DROP的操作的起始位置是881，终止位置是998;
那么我们只要把日志文件的开头到起始位置的操作，然后再从终止位置到末尾的操作，我们就可以把数据给恢复回来，而不会DELETE数据
/usr/bin/mysqlbinlog --database=work /var/lib/mysql/bin-log.000003 | mysql -uroot -p123456 /usr/bin/mysqlbinlog --start-position=956 --database=work /var/lib/mysql/bin-log.000004 | mysql -uroot -p123456 /usr/bin/mysqlbinlog --stop-position=1071 --database=work /var/lib/mysql/bin-log.000004 | mysql -uroot -p123456 /usr/bin/mysqlbinlog --stop-position=881 --database=work /var/lib/mysql/bin-log.000005 | mysql -uroot -p123456
执行结果（WARNING中提示 在命令行界面使用密码是不安全的 ）：
[root@plmu8 ~]# /usr/bin/mysqlbinlog --database=work /var/lib/mysql/bin-log.000003 | mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead. [root@plmu8 ~]# /usr/bin/mysqlbinlog --start-position=956 --database=work /var/lib/mysql/bin-log.000004 | mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. [root@plmu8 ~]# /usr/bin/mysqlbinlog --stop-position=1071 --database=work /var/lib/mysql/bin-log.000004 | mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead. [root@plmu8 ~]# /usr/bin/mysqlbinlog --stop-position=881 --database=work /var/lib/mysql/bin-log.000005 | mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead. [root@plmu8 ~]#
查看数据
恢复成功Success！！！
9、通过指定时间恢复数据
操作解析
/usr/bin/mysqlbinlog --start-datetime="2021-06-27 20:58:18" --stop-datetime="2021-06-27 20:58:35" --database=hello /var/lib/mysql/bin-log-1.000001 | /usr/bin/mysql -uroot -p123456zy -v hello # --start-datetime="2021-06-27 20:58:18" 开始时间 # --stop-datetime="2021-06-27 20:58:35" 结束时间 查看时间信息 /usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/bin-log-1.000001
操作类同
最后，总结几点：
1）本案例适用于人为SQL语句造成的误操作或者没有主从复制等的热备情况宕机时的修复
2）恢复条件为mysql要开启binlog日志功能，并且要全备和增量的所有数据
3）恢复时建议对外停止更新，即禁止更新数据库
4）先恢复全量，然后把全备时刻点以后的增量日志，按顺序恢复成SQL文件，然后把文件中有问题的SQL语句删除（也可通过时间和位置点），再恢复到数据库
