MySQL通过Binlog日志恢复数据库

一、MySQL Binlog 简介

MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

二、Binlog日志的两个最重要的使用场景：

1、MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
2、数据恢复：通过使用 mysqlbinlog工具来使恢复数据

三、开启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日志状态

mysql命令行：

show variables like '%log_bin%';

binlog日志开启状态为ON

 

 

 

五、查看Binlog文件内容

mysql命令行：

show binlog events in 'bin-log-1.000001'; 

 

 

 

 

六、通过Binlog恢复数据

linux命令行：

/usr/bin/mysqlbinlog --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 为显示详细信息

 

七、通过指定位置恢复数据

linux命令行：

/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

--start-position=573 开始位置

--stop-position=718  结束位置

查看位置信息：

mysql命令行：

show binlog events in 'bin-log-1.000001'; 

 

 

 

 

八、通过指定时间恢复数据

linux命令行：

/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" 结束时间

查看时间信息

linux命令行：

/usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/bin-log-1.000001