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

程序员文章站 2022-07-06 10:11:51

一、二进制日志（binary log）二进制日志记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。然而，若操作本身并没有导致数据库发生变化，那么该操作可能也会写入二进制日志。二、二进制日志作用恢复（recovery）：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的My...

一、二进制日志（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:二进制日志(binlog)

一、二进制日志（binary log）

二、二进制日志作用

三、二进制日志配置

1. 二进制日志路径

2. 开启二进制文件

四、使用二进制日志

1. 创建一个测试表t

2. 查看二进制日志

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

(2) 使用二进制文件

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

mysql-清除binlog日志命令

Laravel框架使用monolog_mysql实现将系统日志信息保存到mysql数据库的方法

详解如何通过Mysql的二进制日志恢复数据库数据

MySQL读取Binlog日志常见的3种错误

MySQL和Mariadb二进制日志binlog实例讲解

php下将图片以二进制存入mysql数据库中并显示的实现代码

mysql 正确清理binlog日志的两种方法

mysql 开发进阶篇系列30 数据库二进制包(安装指定路径，目录介绍)

MySQL 日志系统之 redo log 和 binlog

详解mysql数据库如何开启慢查询日志