1 简介
Binlog日志，即binary log，是二进制日志文件，记录对数据发生或潜在发生更改的SQL语句，并以二进制形式保存在磁盘。有两个作用，一个是增量备份，另一个是主从复制，即主节点维护一个binlog日志文件，从节点从binlog中同步数据，也可以通过binlog日志来恢复数据
2 开启Binlog
2.1 查询当前 MySQL 是否支持 binlog
如下ON代表支持（MySQL5.7默认关闭，MySQL8默认开启）：
show variables like '%log_bin%';
+---------------------------------+---------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------+
| log_bin | ON |
| log_bin_basename | C:\db\mysql-8.0.17-winx64\data\binlog |
| log_bin_index | C:\db\mysql-8.0.17-winx64\data\binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------------------+
Binlog日志包括两类文件：第一个是二进制索引文件(后缀名为.index),第二个为日志文件(后缀名为.00000*)，记录数据库所有的DDL和DML(除了查询语句select)语句事件。
2.2 修改 my.cnf 文件以支持 binlog
2.2.1 Linux下可以通过以下方式查看my.cnf路径
mysql --help --verbose | grep my.cnf
2.2.2 在/etc 新建文件my.cnf并添加如下内容
- 注意添加 mysqld 组
[mysqld]
# 日志中的Serverid
server-id = 1
# 开启二进制日志(默认开启)
log_bin = /usr/local/mysql/log/mysql-bin.log
# mysql清除过期日志的时间(天)，默认值0，不自动清理，而是使用滚动循环的方式。
expire_logs_days = 3
# 设置binlog清理时间(秒) --8.0新增
binlog_expire_logs_seconds = 3
# 如果二进制日志写入的内容超出给定值，日志就会发生滚动。你不能将该变量设置为大于1GB或小于4096字节。 默认值是1GB。
max_binlog_size = 100m
# 默认是所有记录，可以配置哪些需要记录，哪些不记录
# binlog_do_db = include_database_name
# binlog_ignore_db = include_database_name
# binlog缓存大小
binlog_cache_size = 4m
# 最大binlog缓存大小
max_binlog_cache_size = 512m
# binlog的格式也有三种：STATEMENT，ROW，MIXED。mysql 5.7.7后，默认值从 MIXED 改为 ROW
binlog_format = row
# 默认值N=1，使binlog在每N次binlog写入后与硬盘同步，ps：1最慢
# sync_binlog = 1
# 关闭binlog。也可用disable-log-bin --8.0新增
skip-log-bin
- 重启 MySQL
net stop mysql
net start mysql
2.3 binlog的三种格式
2.3.1 ROW
针对行记录日志，每行修改产生一条记录。
优点：上下文信息比较全，恢复某条误操作时可以直接在日志中查找到原文信息，对于主从复制支持好。
缺点：输出非常大，如果是Alter语句将产生大量的记录
格式如下：
DELETE FROM `back`.`sys_user` WHERE `deptid`=27 AND `status`=1 AND `account`='admin' AND `name`='张三' AND `phone`='18200000000' AND `roleid`='1' AND `createtime`='2016-01-29 08:49:53' AND `sex`=2 AND `email`='sn93@qq.com' AND `birthday`='2017-05-05 00:00:00' AND `avatar`='girl.gif' AND `version`=25 AND `password`='ecfadcde9305f8891bcfe5a1e28c253e' AND `salt`='8pgby' AND `id`=1 LIMIT 1; #start 4 end 796 time 2018-10-12 17:03:19
2.3.2 STATEMENT
针对sql语句的，每条语句产生一条记录
优点：产生的日志量比较小，主从版本可以不一致
缺点：主从有些语句不能支持，像自增主键和UUID这种类型的
格式如下：
delete from `sys_role`;
2.3.3 MIX
结合了两种的优点，一般情况下都采用STATEMENT模式，对于不支持的语句采用ROW模式。
3 binlog管理命令
3.1 状态相关
3.1.1 show master logs
查看所有Binlog的日志列表。
show master logs; # 或者 show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 178 | No |
| binlog.000002 | 155 | No |
+---------------+-----------+-----------+
3.1.2 show master status
查看binlog日志状态。查看最后一个Binlog日志的编号名称，及最后一个事件结束的位置( pos )。
show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000008 | 155 | | | |
+---------------+----------+--------------+------------------+-------------------+
3.1.3 flush logs
刷新binlog日志文件，刷新之后会创建一个新的Binlog日志文件。
每当mysqld服务重启时，会自动执行刷新binlog日志命令，mysqldump备份数据时加 -F 选项也会刷新binlog日志。
3.1.4 reset master
清空所有的 binlog 日志文件
3.2 事件相关
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
3.2.1 查看第一个Binlog日志
show binlog events;
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 124 | Server ver: 8.0.17, Binlog ver: 4 |
| binlog.000001 | 124 | Previous_gtids | 1 | 155 | |
| binlog.000001 | 155 | Stop | 1 | 178 | |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
3.2.2 查看指定的Binlog日志
show binlog events in 'mysql-bin.000002';
3.2.3 从指定的位置开始，查看指定的Binlog日志
show binlog events in 'mysql-bin.000001' from 666;
3.2.4 从指定的位置开始，查看指定的Binlog日志，限制查询的条数
show binlog events in 'mysql-bin.000001' from 666 limit 2;
3.2.5 从指定的位置开始，带有偏移，查看指定的Binlog日志，限制查询的条数
show binlog events in 'mysql-bin.000001' from 666 limit 1, 2;
3.3 内容相关
mysqlbinlog用于处理二进制的日志文件，如果想要查看这些日志文件的文本内容，就需要使用mysqlbinlog工具。
3.3.1 mysqlbinlog命令的语法
mysqlbinlog [options] logfile1 logfile2 ...
3.3.2 mysqlbinlog的参数
-d, --database=name 仅显示指定数据库的转储内容。
-o, --offset=# 跳过前N行的日志条目。
-r, --result-file=name 将输入的文本格式的文件转储到指定的文件。
-s, --short-form 使用简单格式。
--set-charset=name 在转储文件的开头增加'SET NAMES character_set'语句。
--start-datetime=name 转储日志的起始时间。
--stop-datetime=name 转储日志的截止时间。
-j, --start-position=# 转储日志的起始位置。
--stop-position=# 转储日志的截止位置。
3.3.3 指令示例
3.3.3.1 查看binlog日志文件内容
mysqlbinlog mysql-bin.000002
3.3.3.2 转为文本输出
mysqlbinlog --base64-output=decode-rows -v --start-date='2014-09-16 14:00:00' --stop-date='2014-09-16 14:20:00' mysql-bin.000017 > mysqlbinlogsql_restore_2014091614.sql
-
--no-defaults 为了防止报错：mysqlbinlog: unknown variable 'default_character_set=utf8mb4'
-
--base64-output='decode-rows' 和-v一起使用， 进行base64解码
-
-d=--database 指明数据库名称
其他有很多用来限定范围的参数，比如数据库，起始时间，起始位置等等。这些参数在查找误操作的时候非常有用
3.3.3.3 恢复数据到数据库
mysqlbinlog --start-datetime="2018-04-27 20:58:18" --stop-datetime="2018-04-27 20:58:35" --database=hello /var/lib/mysql/mysql-bin.000009 | /usr/bin/mysql -uroot -p8856769abcd -v hello
3.3.4 内容结构
3.3.4.1 binlog的基本块
# at 417750
#181007 1:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Query thread_id=440109962 exec_time=0 error_code=0
SET TIMESTAMP=1538877038/*!*/;
BEGIN
# at 417750
指明的当前位置相对文件开始的偏移位置，这个在mysqlbinlog命令中可以作为–start-position的参数
#181007 1:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Query thread_id=440109962 exec_time=0 error_code=0
- 181007 1:50:38 指明时间为18年10月7号1:50:38；
- serverid 也就是你在配置文件中的配置的；
- end_log_pos 417844 这个块在417844结束；
- thread_id 执行的线程id；
- exec_time 执行时间；
- error_code 错误码。
SET TIMESTAMP=1538877038/!/；
BEGIN
具体的执行语句
3.3.4.2 完整内容
一行记录产生的日志如下所示：
# at 417750
#181010 9:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Query thread_id=440109962 exec_time=0 error_code=0
SET TIMESTAMP=1539136238/*!*/;
BEGIN
/*!*/;
# at 417844
#181010 9:50:38 server id 1630000 end_log_pos 417930 CRC32 0xce36551b Table_map: `goods`.`good_info` mapped to number 129411
# at 417930
#181010 9:50:38 server id 1630000 end_log_pos 418030 CRC32 0x5827674a Update_rows: table id 129411 flags: STMT_END_F
### UPDATE `goods`.`good_info`
### WHERE
### @1='2018:10:07' /* DATE meta=0 nullable=0 is_null=0 */
### @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @3=1 /* INT meta=0 nullable=0 is_null=0 */
### @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
### @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @6=20 /* LONGINT meta=0 nullable=0 is_null=0 */
### @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### SET
### @1='2018:10:07' /* DATE meta=0 nullable=0 is_null=0 */
### @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @3=1 /* INT meta=0 nullable=0 is_null=0 */
### @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
### @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @6=21 /* LONGINT meta=0 nullable=0 is_null=0 */
### @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 418030
#181010 9:50:38 server id 1630000 end_log_pos 418061 CRC32 0x468fb30e Xid = 212760460521
COMMIT/*!*/;
# at 418061
以SET TIMESTAMP=1539136238/!/;开始，以COMMIT/!/;结尾。我们可以根据两个at指明的位置来限定范围。
注意一条记录开始的SET TIMESTAMP之前的# at 417750和结尾的COMMIT之后的# at 418061。
恢复到数据库
4 Binlog相关变量
mysql> show variables like "%binlog%";
+------------------------------------------------+----------------------+
| Variable_name | Value |
+------------------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_encryption | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_expire_logs_seconds | 2592000 |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_rotate_encryption_master_key_at_startup | OFF |
| binlog_row_event_max_size | 8192 |
| binlog_row_image | FULL |
| binlog_row_metadata | MINIMAL |
| binlog_row_value_options | |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| innodb_api_enable_binlog | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+------------------------------------------------+----------------------+
5 数据恢复流程实例
0 数据库中创建一张表，插入一条数据，再将表删除。此时我们来恢复被删除的这条数据。
CREATE TABLE USER (
id INT,
NAME VARCHAR(32)
);
INSERT INTO USER VALUES (1, "张三");
DROP TABLE USER;
1 查看binlog日志
show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000012 | 865 | | | |
+---------------+----------+--------------+------------------+-------------------+
2 使用flush logs;创建新的日志文件，方便处理当前文件。
3 查看binlog.000012中的事件。
mysql> show binlog events in "binlog.000012";
+---------------+-----+----------------+-----------+-------------+------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+----------------+-----------+-------------+------------------------------------------------------+
| binlog.000012 | 4 | Format_desc | 1 | 124 | Server ver: 8.0.17, Binlog ver: 4 |
| binlog.000012 | 124 | Previous_gtids | 1 | 155 | |
| binlog.000012 | 155 | Anonymous_Gtid | 1 | 232 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000012 | 232 | Query | 1 | 371 | use `test`; create table user (
id int,
name varchar(32)
) /* xid=458 */ |
| binlog.000012 | 371 | Anonymous_Gtid | 1 | 450 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000012 | 450 | Query | 1 | 525 | BEGIN |
| binlog.000012 | 525 | Table_map | 1 | 581 | table_id: 342 (test.user) |
| binlog.000012 | 581 | Write_rows | 1 | 628 | table_id: 342 flags: STMT_END_F |
| binlog.000012 | 628 | Xid | 1 | 659 | COMMIT /* xid=460 */ |
| binlog.000012 | 659 | Anonymous_Gtid | 1 | 736 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000012 | 736 | Query | 1 | 865 | use `test`; DROP TABLE `user` /* generated by server
*/ /* xid=466 */ |
| binlog.000012 | 865 | Rotate | 1 | 909 | binlog.000013;pos=4 |
+---------------+-----+----------------+-----------+-------------+------------------------------------------------------+
可以看到，从659的commit到865，是删除表操作。所以我们恢复数据应是从155到659。
4 恢复数据
mysqlbinlog --start-position=155 --stop-position=659 --database=test /var/lib/mysql/binlog.000012 | /usr/bin/mysql -uroot -p8856769abcd -v test
-v 控制台输出信息
5 查看数据
select * from user;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
+------+--------+
在实际生产环境中，通常是将备份（mysqldump）与binlog结合，在备份数据的基础上使用binlog恢复剩余的数据。
评论区