当前位置：移动技术网 > IT编程>数据库>Mysql > 数据库:mysql:二进制日志(binlog)

数据库:mysql:二进制日志(binlog)

2020年07月18日 | 移动技术网IT编程 | 我要评论

一、二进制日志（binary log）

二进制日志记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。然而，若操作本身并没有导致数据库发生变化，那么该操作可能也会写入二进制日志。

二、二进制日志作用

恢复（recovery）：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。
复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或standby）与一台MySQL数据库（一般称为master或primary）进行实时同步。
审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

三、二进制日志配置

1. 二进制日志路径

通过配置文件配置参数log-bin[=name]可以启动二进制日志。如果不指定name，则默认二进制日志文件名为主机名，后缀名为二进制日志的序列号，所在路径为数据库所在目录（datadir），

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

这里的bin_log.00001即为二进制日志文件，我们在配置文件中指定了名字，所以没有用默认的文件名。bin_log.index为二进制的索引文件，用来存储过往产生的二进制日志序号，在通常情况下，不建议手工修改这个文件。

2. 开启二进制文件

//二进制文件默认没有开启:
mysql> show variables like '%bin%';
| log_bin                                    | OFF                  |
| log_bin_basename                           |                      |
| log_bin_index                              |                      |

//配置文件加入配置项
[mysqld]
log_bin=/var/log/mysql/binlog.log
server-id=1        //这里需要注意

//保存 关闭配置文件 重启mysql
service mysql restart

//重启登录mysql查看binlog信息
mysql> show variables like '%bin%';
| log_bin                                    | ON                          |
| log_bin_basename                           | /var/log/mysql/binlog       |
| log_bin_index                              | /var/log/mysql/binlog.index |

//如果配置文件不加server-id项会报以下错
root@ubuntu:# service mysql start 
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.
mysql# journalctl -xe
[ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation

四、使用二进制日志

1. 创建一个测试表t

//选择测试库
mysql> show databases;
mysql> use learn;

//创建表
 create table t ( 
 		id INT AUTO_INCREMENT , 
 		a INT,
 	 	PRIMARY KEY (id)
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;

//插入测试数据
INSERT INTO t (id,a) VALUES (1,1);
INSERT INTO t (id,a) VALUES (2,2);

//查看数据
mysql> select * from t;
+----+------+
| id | a    |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+

2. 查看二进制日志

(1) 二进制日志相关参数

mysql> show variables like '%bin%';
+--------------------------------------------+-----------------------------+
| Variable_name                              | Value                       |
+--------------------------------------------+-----------------------------+
| bind_address                               | *                           |
| binlog_cache_size                          | 32768                       |
| binlog_checksum                            | CRC32                       |
| binlog_direct_non_transactional_updates    | OFF                         |
| binlog_error_action                        | ABORT_SERVER                |
| 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_row_image                           | FULL                        |
| 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                         |
| innodb_locks_unsafe_for_binlog             | OFF                         |
| log_bin                                    | ON                          |
| log_bin_basename                           | /var/log/mysql/binlog       |
| log_bin_index                              | /var/log/mysql/binlog.index |
| log_bin_trust_function_creators            | OFF                         |
| log_bin_use_v1_row_events                  | OFF                         |
| log_statements_unsafe_for_binlog           | ON                          |
| max_binlog_cache_size                      | 18446744073709547520        |
| max_binlog_size                            | 104857600                   |
| max_binlog_stmt_cache_size                 | 18446744073709547520        |
| sql_log_bin                                | ON                          |
| sync_binlog                                | 1                           |
+--------------------------------------------+-----------------------------+

binlog_format参数十分重要，它影响了记录二进制日志的格式。可见这里用的是ROW格式.

(2) 使用二进制文件

先看下之前的binlog状态

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |     1094 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

mysql> SHOW BINLOG EVENTS IN 'binlog.000002';
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                              |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------+
| binlog.000002 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.30-0ubuntu0.18.04.1-log, Binlog ver: 4                                                            |
| binlog.000002 |  123 | Previous_gtids |         1 |         154 |                                                                                                                   |
| binlog.000002 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                              |
| binlog.000002 |  219 | Query          |         1 |         335 | use `learn`; DROP TABLE `t` /* generated by server */                                                             |
| binlog.000002 |  335 | Anonymous_Gtid |         1 |         400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                              |
| binlog.000002 |  400 | Query          |         1 |         576 | use `learn`; create table t ( id INT AUTO_INCREMENT , a INT, PRIMARY KEY (id) )ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| binlog.000002 |  576 | Anonymous_Gtid |         1 |         641 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                              |
| binlog.000002 |  641 | Query          |         1 |         714 | BEGIN                                                                                                             |
| binlog.000002 |  714 | Table_map      |         1 |         760 | table_id: 110 (learn.t)                                                                                           |
| binlog.000002 |  760 | Write_rows     |         1 |         804 | table_id: 110 flags: STMT_END_F                                                                                   |
| binlog.000002 |  804 | Xid            |         1 |         835 | COMMIT /* xid=21 */                                                                                               |
| binlog.000002 |  835 | Anonymous_Gtid |         1 |         900 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                              |
| binlog.000002 |  900 | Query          |         1 |         973 | BEGIN                                                                                                             |
| binlog.000002 |  973 | Table_map      |         1 |        1019 | table_id: 110 (learn.t)                                                                                           |
| binlog.000002 | 1019 | Write_rows     |         1 |        1063 | table_id: 110 flags: STMT_END_F                                                                                   |
| binlog.000002 | 1063 | Xid            |         1 |        1094 | COMMIT /* xid=22 */     
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------+

操作测试表，产生相关信息

//更新记录
mysql> update t set a=4 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看binlog日志

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |     1363 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

mysql> SHOW BINLOG EVENTS IN 'binlog.000002';
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                              |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------+
| binlog.000002 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.30-0ubuntu0.18.04.1-log, Binlog ver: 4                                                            |
| binlog.000002 |  123 | Previous_gtids |         1 |         154 |                                                                                                                   |
| binlog.000002 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                              |
| binlog.000002 |  219 | Query          |         1 |         335 | use `learn`; DROP TABLE `t` /* generated by server */                                                             |
| binlog.000002 |  335 | Anonymous_Gtid |         1 |         400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                              |
| binlog.000002 |  400 | Query          |         1 |         576 | use `learn`; create table t ( id INT AUTO_INCREMENT , a INT, PRIMARY KEY (id) )ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| binlog.000002 |  576 | Anonymous_Gtid |         1 |         641 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                              |
| binlog.000002 |  641 | Query          |         1 |         714 | BEGIN                                                                                                             |
| binlog.000002 |  714 | Table_map      |         1 |         760 | table_id: 110 (learn.t)                                                                                           |
| binlog.000002 |  760 | Write_rows     |         1 |         804 | table_id: 110 flags: STMT_END_F                                                                                   |
| binlog.000002 |  804 | Xid            |         1 |         835 | COMMIT /* xid=21 */                                                                                               |
| binlog.000002 |  835 | Anonymous_Gtid |         1 |         900 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                              |
| binlog.000002 |  900 | Query          |         1 |         973 | BEGIN                                                                                                             |
| binlog.000002 |  973 | Table_map      |         1 |        1019 | table_id: 110 (learn.t)                                                                                           |
| binlog.000002 | 1019 | Write_rows     |         1 |        1063 | table_id: 110 flags: STMT_END_F                                                                                   |
| binlog.000002 | 1063 | Xid            |         1 |        1094 | COMMIT /* xid=22 */                                                                                               |
| binlog.000002 | 1094 | Anonymous_Gtid |         1 |        1159 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                              |
| binlog.000002 | 1159 | Query          |         1 |        1232 | BEGIN                                                                                                             |
| binlog.000002 | 1232 | Table_map      |         1 |        1278 | table_id: 110 (learn.t)                                                                                           |
| binlog.000002 | 1278 | Update_rows    |         1 |        1332 | table_id: 110 flags: STMT_END_F                                                                                   |
| binlog.000002 | 1332 | Xid            |         1 |        1363 | COMMIT /* xid=30 */                                                                                               |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------+

//在命令行查看binlog日志
root@ubuntu:/var/log/mysql# mysqlbinlog  -vv --start-position=1094 binlog.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200717 12:44:18 server id 1  end_log_pos 123 CRC32 0xa43eddb7 	Start: binlog v 4, server v 5.7.30-0ubuntu0.18.04.1-log created 200717 12:44:18 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
oiwRXw8BAAAAdwAAAHsAAAABAAQANS43LjMwLTB1YnVudHUwLjE4LjA0LjEtbG9nAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACiLBFfEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AbfdPqQ=
'/*!*/;
# at 1094
#200717 15:22:48 server id 1  end_log_pos 1159 CRC32 0x26d7243b 	Anonymous_GTID	last_committed=4	sequence_number=5	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1159
#200717 15:22:48 server id 1  end_log_pos 1232 CRC32 0x44b8d45f 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1594970568/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 1232
#200717 15:22:48 server id 1  end_log_pos 1278 CRC32 0xeeaf667b 	Table_map: `learn`.`t` mapped to number 110
# at 1278
#200717 15:22:48 server id 1  end_log_pos 1332 CRC32 0xf56ed89e 	Update_rows: table id 110 flags: STMT_END_F

BINLOG '
yFERXxMBAAAALgAAAP4EAAAAAG4AAAAAAAEABWxlYXJuAAF0AAIDAwACe2av7g==
yFERXx8BAAAANgAAADQFAAAAAG4AAAAAAAEAAgAC///8AQAAAAEAAAD8AQAAAAQAAACe2G71
'/*!*/;
### UPDATE `learn`.`t`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
# at 1332
#200717 15:22:48 server id 1  end_log_pos 1363 CRC32 0xcb34b81a 	Xid = 30
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

五、开启二进制文件的弊端

开启这个选项的确会影响性能，但是性能的损失十分有限。根据MySQL官方手册中的测试表明，开启二进制日志会使性能下降1%。但考虑到可以使用复制（replication）和point-in-time的恢复，这些性能损失绝对是可以且应该被接受的。
binlog_format参数设置为ROW，会加大二进制文件的大小。每次执行相关的语句，会占用用磁盘空间

本文地址：https://blog.csdn.net/wangdamingll/article/details/107405589

您可能感兴趣的文章:

如对本文有疑问，点击进行留言回复！！

MySql中流程控制函数/统计函数/分组查询用法解析

路漫漫其修远兮，吾将上下而求索，又到了周末，我继续带各位看官学习回顾mysql知识。上次说到了流程控制函数，那就从流程控制函数来继续学习吧！#五.流程控制函数#... [阅读全文]
MySQL 快速删除大量数据（千万级别）的几种实践方案详解

笔者最近工作中遇见一个性能瓶颈问题，mysql表，每天大概新增776万条记录，存储周期为7天，超过7天的数据需要在新增记录前老化。连续运行9天以后，删除一天的数... [阅读全文]
MySQL Innodb 存储结构和存储Null值用法详解

背景:表空间：innodb 所有数据都存在表空间当中（共享表空间），要是开启innodb_file_per_table，则每张表的数据会存到单独的一个表空间内（... [阅读全文]
mysql中两点之间距离的计算

需求背景最近做电商项目，要求跟根据用户当前位置显示距离用户当前位置十五公里内的商品。用户当前位置由移动端(An... [阅读全文]
牛客编程巅峰赛S1第6场——A循环右移（字符串右移）

题目描述：解题思路：这道题很简单，思路就是求出右移后的字符串，然后转换成对应的十进制数即可，我是直接使用的API... [阅读全文]
《软件工程之美》总结三：关于需求

需求分析什么是需求用户需求：是由用户提出来的，期望满足自身一定需要的要求产品需求：在分析提炼用户真实需求后，提出... [阅读全文]
-bash: !: event not found

[root@MGR-node3 ~]# mysql -uroot -p"Eb>wk?r+*8!<"-... [阅读全文]
关系型数据库与nosql数据库学习总结

数据库学习总结第一次真正写博客，以前都是将学习总结记录在笔记当中，但是慢慢看了大佬们的博客突然意识到学习的学习的... [阅读全文]
OpenResty基础

OpenResty进程管理OpenResty的进程模型基于nginx，master/worker，进程相互平等且... [阅读全文]
MySQL架构原理之MySQL体系架构

select uid,name from user where gender=1;选取--》投影--》联接策略... [阅读全文]