本文中所使用的MySQL版本：5.6.44
本文中所使用的测试数据表都是基于：INNODB引擎
mysql中的binlog日志是什么？
MySQL的binlog就是大家经常所说的Binary Log，也就是我们常说的binlog，即 二进制日志，二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录日志在日志文件中，其中还包括没每条语句所执行的时间和消耗的资源，以及相关的事务信息。
binlog记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。然后，若操作本身并没有导致数据库发生变化，那么该操作也会写入二进制日志。默认情况下二进制日志功能是没有开启的，开启二进制日志功能可以在mysql中的配置文件里配置 log-bin[=file_name] 来开启二进制日志。
binlog日志的作用：
用于MySQL主从复制
增量数据备份(不是所有数据备份，而是最近的写操作) 及 恢复误操作的数据
binlog日志在INNODB和MYISAM中的区别：
对于INNODB存储引擎的表：commit后才会写binlog，如果语句被cancel，作为事务性引擎，会回滚，不会出现更新一半，也不会记binlog。
对于MYISAM存储引擎的表：只要语法和语义没有错误，MYSQL不等执行语句执行完，就会写binlog。如果binlog_format=statement对于复制而言就很危险，因为主库可能会将它执行到一半的时候cancel（取消），然后这条语句却会在从库上完整的执行，造成主从数据不一致。
可见使用innodb表相比myisam无事务的表来说，从很多方面来看都安全很多。另外binlog可以恢复所有存储引擎的数据。
binlog日志的启动和关闭
查看是否开启binlog日志：SHOW VARIABLES LIKE '%log_bin%';
示例截图：
如果log_bin的值是OFF 表示binlog日志是关闭状态
如何开启binlog日志？
找到mysql的配置文件，windows上一般是my.ini，而在linux上一般是my.cnf。无所谓，只要你自己知道你的mysql配置文件使用的是那个就好。
在mysql配置文件中加入如下2个配置选项：
server-id=1
log-bin=mysql-bin #开启binlog日志
然后重启mysql服务后，再次使用SHOW VARIABLES LIKE '%log_bin%'; 命令查看binlog日志是否开启成功。
示例截图：
log_bin的值是 ON 表示binlog日志成功开启
log_bin_basename：表示二进制的文件保存路径
注意：一般网上的大部分开启binlog日志的答案都是mysql配置文件中加入：log-bin=mysql-bin 这一行配置项就行了，但是只使用这个可能会导致一些错误或不稳定性，所以推荐加入 server-id=1 这一行配置项。关于 server-id=1 代表什么意思，可以从本博客中的搜索框里搜索：mysql主从 然后点进去自行查看，或者自己上网查。
关于binlog日志的默认开启和关闭：额 一些集成环境貌似都是将binlog日志开启了，比如windows上的phpstudy、Visual NMP等。linux上的lnmp、宝塔等集成环境貌似也都是默认开启的~~~
查看当前二进制日志文件状态： show master status;
show master status; 可以查看master数据库当前正在使用的二进制日志及当前执行二进制日志位置。
示例截图：
对于binlog来说上图中的mysql-bin.xxx就是binlog的节点日志文件，而mysql-bin.index这个文件记录的是所有日志文件的地址
我这里使用的是lnmp环境，默认二进制日志文件路径为：/usr/local/mysql/var
示例截图如下：
注意：上图中的mysql-bin.xxxxxx等文件可以删除，但是mysql-bin.index文件不能删除！
注意：如果重新启动了mysql服务，那么使用 show master status; 查看日志文件会发现mysql会重新生成了一份新的binlog日志文件（可以自己试一下，这里就不进行截图演示了），也就是说 一个binlog日志文件记录的是 从启动（或重启）之后的时间段的日志信息，直到mysql服务停止或者重新启动为止。
清空所有的binlog日志文件：reset master;
功能说明：删除所有的binglog日志文件，并将日志索引文件（mysql-bin.index 文件）中的内容清空，重新开始所有新的日志文件。
示例截图：
再次进入/usr/local/mysql/var 目录 查看mysql的二进制日志文件
上图中可以看到之前显示的mysql-bin.000014和mysql-bin.000015两个binlog日志文件已经被清空，binlog日志文件重新开始从mysql-bin.000001开始，然后查看 mysql-bin.index 文件，会发现里面记录的日志文件地址又是从 mysql-bin.000001 这个文件开始记录了
谨慎使用该命令。。不要随便使用这个命令。。
生成一个新的binlog日志文件：mysql> flush logs; // 以后的binlog日志记录都会记录在这个新的日志文件中
查看当前binlog日志的格式：mysql> show VARIABLES like '%binlog_format%'
使用binlog日志进行数据恢复操作
前期准备工作：
先将mysql中的bin目录添加到环境变量中，为了方便我们下面操作不用写全路径。。
我这里安装的是lnmp的环境，lnmp环境中的mysql的bin目录路径为：/usr/local/mysql/bin
vi /etc/profile #在文件中加入下面2行代码，mysql中的bin目录换成你自己的 PATH=$PATH:/usr/local/mysql/bin export PATH #然后保存退出
最后：执行命令 source /etc/profile 或 执行点命令 ./profile使其修改生效，执行完可通过echo $PATH 命令查看是否添加成功。
开始数据恢复操作演示：
添加一下测试的数据操作
CREATE DATABASE bin DEFAULT CHARACTER SET utf8; use bin; CREATE TABLE `t` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; INSERT INTO t VALUES(null, 'php'); INSERT INTO t VALUES(null, 'mysql');
这个时候我们可以使用以下2中方式来查看我们的mysql中的二进制文件信息
由于我们上面执行了一遍reset master; 所以我们这里的binlog日志文件变成了 mysql-bin.000001 这个文件。
①、在终端中执行：mysqlbinlog ./mysql-bin.000001
注意：后面的 mysql-bin.000001 根据自己的实际情况来，别啥都不管一通复制。。最后发现结果不是你想要的或报错。。
示例截图：
噗。。由于文件内容实在太多。。笔记本屏幕展示不全所有信息。。所以显示的完整日志文件内容如下：
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #190928 20:38:22 server id 1 end_log_pos 120 CRC32 0xd63e04c0 Start: binlog v 4, server v 5.6.44-log created 190928 20:38:22 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' PlSPXQ8BAAAAdAAAAHgAAAABAAQANS42LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAA+VI9dEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAcAE PtY= '/*!*/; # at 120 #190928 20:38:32 server id 1 end_log_pos 238 CRC32 0xf0d50a9f Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1569674312/*!*/; SET @@session.pseudo_thread_id=7/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1075838976/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; CREATE DATABASE bin DEFAULT CHARACTER SET utf8 /*!*/; # at 238 #190928 20:38:54 server id 1 end_log_pos 590 CRC32 0x749950bb Query thread_id=7 exec_time=0 error_code=0 use `bin`/*!*/; SET TIMESTAMP=1569674334/*!*/; CREATE TABLE `t` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact /*!*/; # at 590 #190928 20:39:08 server id 1 end_log_pos 667 CRC32 0x21928d55 Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1569674348/*!*/; BEGIN /*!*/; # at 667 # at 699 #190928 20:39:08 server id 1 end_log_pos 699 CRC32 0x0944f341 Intvar SET INSERT_ID=1/*!*/; #190928 20:39:08 server id 1 end_log_pos 804 CRC32 0xe2cc779d Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1569674348/*!*/; INSERT INTO t VALUES(null, 'php') /*!*/; # at 804 #190928 20:39:08 server id 1 end_log_pos 835 CRC32 0x747f6612 Xid = 557 COMMIT/*!*/; # at 835 #190928 20:39:10 server id 1 end_log_pos 912 CRC32 0x181bc814 Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1569674350/*!*/; BEGIN /*!*/; # at 912 # at 944 #190928 20:39:10 server id 1 end_log_pos 944 CRC32 0xc75b4910 Intvar SET INSERT_ID=2/*!*/; #190928 20:39:10 server id 1 end_log_pos 1051 CRC32 0xaa340797 Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1569674350/*!*/; INSERT INTO t VALUES(null, 'mysql') /*!*/; # at 1051 #190928 20:39:10 server id 1 end_log_pos 1082 CRC32 0x1ea21d2e Xid = 558 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
通过以上二进制日志文件中的内容可以看到 上面我创建的数据库（bin）、数据表（t）、插入的2条数据 以及每一步操作的具体时间等，通通都有记录。不过这种方式不推荐使用。。因为看起来实在是不容易。。推荐下面这种第二种方式。。
②、使用show binlog events; 命令查看某个binlog二进制日志文件中的内容，也可以理解为 查看二进制日志中的事件
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
各个选项解析说明：
IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,]：偏移量(不指定就是0)
row_count：查询总条数(不指定就是所有行)
这里的查看命令为：show binlog events in 'mysql-bin.000001';
示例截图如下：
Log_name：顾名思义就是 查询的binlog日志的文件名
Pos：Pos的起始点
Event_type：事件类型
Server_id：标识是由哪台服务器执行的
End_log_pos：Pos的结束点（即：下次的Pos的起始点）
Info：执行的sql语句信息
也可以 基于开始时间/结束时间 和 日期 查看binlog日志文件中的内容，示例如下：
mysqlbinlog --start-datetime='2021-03-01 00:00:00' --stop-datetime='2021-03-01 23:59:59' -d 数据库名 ./mysql-bin.000001（二进制文件）
③、接下来做一个操作就是将t表中的所有数据进行删除（这是模拟某些不小心的同学做的操作）
mysql> delete FROM t; Query OK, 2 rows affected (0.00 sec) mysql> select * from t; Empty set (0.00 sec)
然后可以在mysql使用 show binlog events; 查看二进制日志文件信息。
注意：show binlog events; 这里没有指定'log_name'，则显示的是 第一个binlog的二进制日志文件中的内容信息，即mysql-bin.000001
或者直接指定要显示哪一个的binlog日志文件信息：show binlog events in 'mysql-bin.000001';
示例截图如下：
现在来做恢复；根据数据情况找到数据的节点位置， 在我们这里 发现节点是从590开始到1082结束
为了防止报错，还是进入mysql的binlog日志文件中执行吧，并且指定mysqlbinlog这个程序的全路径吧。
#进入保存binlog日志文件中的目录 /usr/local/mysql/var #开始将起始节点为 590 到 结束节点为 1082 这段节点之间的数据恢复到mysql中 /usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 --start-position 590 --stop-position 1082 | mysql -u root -p 然后按回车 然后输入密码即可 #/*********分割线************/ #除了以上这种直接恢复到数据库外，还可以将恢复的数据写入单个文件，然后将该文件导入到mysql中，这里只写一下相关命令，就不做截图演示了 shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql #将数据写入到一个文件 shell> mysql -u root -p -e "source /tmp/statements.sql" #将文件中的数据导入到mysql中
示例截图如下：
最后查看select * from t; 发现数据已经恢复了。
注意：我这里使用的是根据起始节点和结束节点的方式来恢复数据 除了这种方式外，也可以根据日期来恢复数据的。
删除跑路？ 不存在的。 不慌 问题不大！不是只有DBA才能帮你找回数据 自己也可以！
binlog日志恢复弊端：
思考：如果是生产环境，binlog此种数据恢复手段会有怎样的弊端？ 以下列举了大概3点弊端：
①、binlog日志记录的不单单是一个数据库的操作日志，可能对其它数据库也有记录相关的操作日志，那 如何从binlog日志中获取指定数据库的日志信息呢？
解决：mysqlbinlog工具加一个-d参数即可 -d即database的简写，使用示例如下：
-- 从mysql-bin.000001这个binlog日志文件中 截取起始节点590到结束节点1082之间的所有bin数据库操作的日志数据 并保存输出到/tmp/test.sql文件中 ps:相关参数 请根据自身的实际情况换成自己的,别啥都复制。。 /usr/local/mysql/bin/mysqlbinlog -d bin --start-position 590 --stop-position 1082 /usr/local/mysql/var/mysql-bin.000001 > /tmp/test.sql
②、需要的日志数据分布在多个binlog日志文件中，那这种跨日志文件的又该如何获（截）取想要的日志数据呢？
我们来举个栗子，看 以下场景：
起点：假如说,我们需要的日志数据的起点在mysql-bin.000001这个binlog文件中，假设起始点是6600
终点：一般都是binlog日志文件的最后一个文件，假设在mysql-bin.000010文件中，假设结束点是980
解决：像这种跨日志文件获取日志数据，就不能和上面一样直接使用起始点和结束点（起始点和结束点 也就是上面说的Pos和End_log_pos 以下就简称pos号了）进行恢复了。或者是只能使用一半的pos号，要么是前面使用pos号 后边就不能使用pos号、要么是后面使用pos号 前面就不能使用pos号。此时我们可以借助mysqlbinlog工具中的--start-datetime=时间 和 --stop-datetime=时间。这个两个参数，使用示例如下：
-- 1、从时间这个维度获取mysql-bin.000001 mysql-bin.000010两个binlog文件中符合条件的数据 /usr/local/mysql/bin/mysqlbinlog --start-datetime=时间 --stop-datetime=时间 /usr/local/mysql/var/mysql-bin.000001 mysql-bin.000010 > /tmp/test.sql -- 2、时间和pos号混合搭配使用 /usr/local/mysql/bin/mysqlbinlog --start-position --stop-datetime=时间 /usr/local/mysql/var/mysql-bin.000001 mysql-bin.000010 > /tmp/test.sql -- 注意：时间和pos号可以随意组合 可根据自身实际情况 自由搭配使用 活学活用，这里只列举了2条使用示例。。更多姿势。。等你发掘、、
③、假如说一个数据库创建了3年，期间一直在用数据库，binglog日志文件已经从mysql-bin.000001、mysql-bin.000002、mysql-bin.000003、mysql-bin.000004、mysql-bin.000005、mysql-bin.000006等等......记录了N多个binlog文件，突然间操作失误，该数据库被误删了。。这个时候又该如何使用binlog文件进行恢复？毕竟这么多binlog日志文件啊。。按照常理来说。。3年了。。数据量应该也挺多的了。。
说明：首先必须明白一点，这个恢复很难。。。第一 这个日志文件不一定能保存这么长时间！第二 即使日志能保存这么长时间 恢复起来要多痛苦有多难受！
解决：
假设：每周六做一次全备份（晚上23:00），binlog每天备份（晚上23:00）。当然了，生产环境中的数据必须有备份。。
故障点：周三 10点 核心数据库被误操作给drop掉了。
首先 拿 全备份的数据恢复到周六那个时间点。然后使用binlog日志把周六晚上23:00之后的 一直到drop操作之前的数据进行恢复，就可以了。这样，只需要恢复3-4天binlog即可。
binlog日志维护操作：
①、日志滚动
说明：所谓的日志滚动，意思就是说 生成一份新的binlog日志文件，让mysql将日志记录在新生成的binlog日志文件里面去。
手动触发机制：
mysql> flush logs; // 手动触发滚动日志（在mysql里面执行该命令，上面也有该命令的说明。）
/usr/local/mysql/bin/mysqladmin -uroot -pxxx flush-logs // 手动触发滚动日志，记得把mysqladmin工具的路径以及root账号的密码换成自己的。
自动触发机制：
mysql> select @@max_binlog_size; // 查看binlog日志文件的大小限制，默认binlog日志文件的大小限制是1GB，当binlog日志文件的大小超过了1GB，则 自动滚动一次日志。（该命令在mysql里面执行）可以根据自己的需求 设置成合理的大小。
/usr/local/mysql/bin/mysqldump -F // 使用mysqldump工具做备份的时候，可以加上 -F 选项，也可以进行自动滚动日志。
mysql数据库重新启动也会自动滚动日志
②、日志的删除
说明：默认情况下，binlog日志文件是不会被删除的。如何去删除日志呢？切记：不要使用rm命令去删除binlog日志，因为它不是数据库的一个行为，使用rm命令去删除binlog日志文件可能会造成意想不到的后果（比如说 造成日志损坏、日志不匹配、mysql主从出现异常 等......）。。
自动删除机制：
mysql> select @@expire_logs_days; // 该命令查看的是mysql清除过期日志的时间，单位是：天。 expire_logs_days参数的值 默认是0，即 表示所有binlog日志永久都不会失效，不会自动删除。想要expire_logs_days参数永久生效的话，可以修改mysql的配置文件 在[mysqld]标签下增加 expire_logs_days=值 那expire_logs_days参数的值具体应该设置多少天比较合理呢？一般都是根据你的数据库全备份周期来设置的，假如说 你7天做一次全备份，那expire_logs_days=8 就可以这样设置，也就是一个全备份周期+1天，因为一个全备份可能要持续很长时间才能完成，所以最好在延长1天，也就是说，每8天清除一次binlog日志文件。一般在生产环境中expire_logs_days的值，建议最少设置为2个全备份周期+1天。接着上面说的那个例子 expire_logs_days=15
手动删除机制：
mysql> purge binary logs to 'mysql-bin.000003'; // 删除mysql-bin.000001，mysql-bin.000002 这2个binlog日志文件。mysql-bin.000003这个binlog日志文件不会被删除。（该命名在mysql里去执行）
mysql> purge binary logs before '2008-04-02 22:46:26'; // 按照时间去删除binlog日志文件
mysql> reset master; // 删除所有的binglog日志文件，并将日志索引文件（mysql-bin.index 文件）中的内容清空，重新开始所有新的日志文件。新的日志文件的起始值从000001开始（该命名在mysql里去执行）。注意：该命令相当危险，如果在主从模式下，在主库上执行该命令，则主从必宕。该命令请谨慎使用！
简单总结：
①、使用binlog只能针对针对数据量不是很多的情况，真正的生产环境每个几个G的日志文件，不止是光靠binlog恢复的，还有更多其它的办法。（比如 每天都要将数据备份N遍等。。）
②、判断binlog日志的时间阶段与pos节点位置很重要，但是需要知道关于mysqlbinlog工具的很多参数。
③、binlog实际上是我们数据恢复时，配合备份一起恢复数据的手段。
不要输了就跑 不要疼了就抱怨 -->【华尔街】
声明：禁止任何非法用途使用，凡因违规使用而引起的任何法律纠纷，本站概不负责。
精彩评论