MySQL二进制日志（Binary Log）

二进制日志主要记录数据库的更新事件，比如创建数据表、更新表中的数据、数据更新所花费的时长等信息。通过这些信息，我们可以再现数据更新操作的全过程。

使用案例
1.查看二进制文件：查看二进制日志主要有 3 种情况：
查看当前正在写入的二进制日志；查看当前写入的日志文件和位置；

mysql> SHOW MASTER STATUS;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| GJTECH-PC-bin.000011 | 2207 | | | |
+----------------------+----------+--------------+------------------+-------------------+   -- 当前正在写入的二进制日志是“GJTECH-PC-bin.000011”，当前的位置是2207。
1 row in set (0.00 sec)

查看所有的二进制日志；

SHOW BINARY LOGS;

查看二进制日志中的所有数据更新事件；

SHOW BINLOG EVENTS IN 二进制文件名;

刷新二进制文件：关闭服务器正在写入的二进制日志文件，并重新打开一个新文件，文件名的后缀在现有的基础上加 1。

FLUSH BINARY LOGS;

用 mysqlbinlog 工具进行数据恢复：

mysqlbinlog –start-positon=xxx –end-position=yyy 二进制文件名 | mysql -u 用户 -p

执行二进制日志中从位置 xxx 开始，到 yyy 截止的所有数据更新操作。这里的截止位置也可以不写，意思是从位置 xxx 开始，执行二进制文件中的所有数据更新操作。

删除二进制文件：

mysql> RESET MASTER;
Query OK, 0 rows affected (0.20 sec)
mysql> SHOW BINARY LOGS;
+----------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------+-----------+-----------+
| GJTECH-PC-bin.000001 | 156 | No |
+----------------------+-----------+-----------+
1 row in set (0.00 sec)

通过 SQL 语句，删除比指定二进制日志文件编号小的所有二进制日志文件：

mysql> PURGE MASTER LOGS TO 'GJTECH-PC-bin.000005';
Query OK, 0 rows affected (0.02 sec)

业务场景
利用日志，完成无损失的数据恢复和主从服务器之间的数据同步。

假设数据库 demo 中有一个商品信息表（demo.goodsmaster），先对数据库 demo 做了一个全量备份。所谓的全量备份，就是指对数据库中存储的全部数据进行备份。备份完成之后，又在商品信息表中插入了新数据。这个时候，数据库 demo 出现异常，数据全部丢失。现在咱们需要把所有的数据，包括备份前的数据和备份之后新插入的数据都恢复回来。介绍下具体的操作步骤：

第一步，做数据库备份
使用 MySQL 的数据备份工具 mysqldump（在安装的mysql的bin目录下进行执行）：

SQL：mysqldump -u 用户 -p 密码 数据库 > 备份文件

示例：在指定文件下执行命令

H:\>mysqldump -u root -p demo > mybackup.sql
Enter password: *****

第二步，用“FLUSH BINARY LOGS;”语句刷新一下日志。

mysql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.06 sec)

第三步，给商品信息表插入一条新的数据记录“笔”
假设这个时候，系统突然宕机，数据库无法启动，为了使系统恢复正常，我们重启机器，重新创建数据库，并且需要进行数据恢复。

第四步，准备从备份文件恢复数据:
每当服务器启动、刷新二进制日志或者二进制日志大小超过系统变量 max_binlog_size 指定的值时，系统就会生成一个新的二进制日志文件;查看一下机器上的二进制日志文件，以确定哪个文件是我们正在使用的

mysql> SHOW BINARY LOGS;
+----------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------+-----------+-----------+
| GJTECH-PC-bin.000005 | 179 | No |
| GJTECH-PC-bin.000006 | 113316452 | No |
| GJTECH-PC-bin.000007 | 12125 | No |
| GJTECH-PC-bin.000008 | 1544 | No |
| GJTECH-PC-bin.000009 | 207 | No |
| GJTECH-PC-bin.000010 | 1758 | No |
| GJTECH-PC-bin.000011 | 2207 | No |
| GJTECH-PC-bin.000012 | 462 | No |
+----------------------+-----------+-----------+
12 rows in set (0.01 sec)

然后刷新一下，重新生成日志文件，避免在操作的时候又写入：

mysql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.08 sec)

删除并重建一个数据库 demo，演示一下如何使用二进制日志恢复数据。

mysql> DROP DATABASE demo;
Query OK, 1 row affected (0.07 sec)
mysql> CREATE DATABASE demo;
Query OK, 1 row affected (0.01 sec)

第五步，从备份恢复数据：

mysql -u 用户 -p 密码 数据库名称 < 备份文件

示例：

H:\>mysql -u root -p demo<mybackup.sql
Enter password: *****

第六步，从保存增量信息的二进制日志文件“GJTECH-PC-bin.000012”中恢复增量数据

通过查看二进制日志中的事件，会发现，写入第二条记录的时候，MySQL 使用了一个事务操作，起始位置是 306，截止位置是 462。这样，就可以用 mysqlbinlog 工具进行数据恢复了。日志名称是“C:\ProgramData\MySQL\MySQL Server 8.0\Data\GJTECH-PC-bin.000012”，读取日志的起始位置是 306。

H:\>mysqlbinlog --start-position=306 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\GJTECH-PC-bin.000012" | mysql -u root -p
Enter password: *****

然后这样数据就恢复完毕了。

未经允许不得转载：任鹏个人博客 » MySQL二进制日志（Binary Log）