MySQL中的Binlog

向右看齐 2023-01-17 09:30 108阅读 0赞

# Binlog文件结构 #

MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log  
event。不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row  
event、Xid event等。binlog文件的内容就是各种Log event的集合。

Binlog文件中Log event结构如下图所示：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70]

# Binlog写入机制 #

binlog写入过程大概分为以下三步：

1.  根据记录模式和操作触发event事件生成log event（事件触发执行机制）
2.  将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区  
    ，Log Event保存在一个binlog\_cache\_mngr数据结构中，在该结构中有两个缓冲区，一个是  
    stmt\_cache，用于存放不支持事务的信息；另一个是trx\_cache，用于存放支持事务的信息。
3.  事务在提交阶段会将产生的log event写入到外部binlog文件中。  
    不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在  
    binlog文件中是连续的，中间不会插入其他事务的log event。

# Binlog文件操作 #

## Binlog状态查看 ##

默认不开启binlog：

## 开启Binlog功能 ##

尝试直接设置参数log\_bin：

mysql> set log_bin="ON"
    ;
    ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

需要修改my.cnf或my.ini配置文件，在\[mysqld\]下面增加如下内容后重启  
：

server-id=12345
    binlog-format=ROW
    log-bin=mysqlbinlog

## binlog相关参数 ##

binlog文件名及其路径的配置：

## mysql命令行查看binlog文件 ##

查看目前有哪些binlog文件：

查看目前使用的是哪个binlog文件：

查看binlog文件中记录的事件：

mysql> show binlog events;
    +--------------------+-----+----------------+-----------+-------------+---------------------------------------+
    | Log_name           | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
    +--------------------+-----+----------------+-----------+-------------+---------------------------------------+
    | mysqlbinlog.000001 |   4 | Format_desc    |     12345 |         123 | Server ver: 5.7.31-log, Binlog ver: 4 |
    | mysqlbinlog.000001 | 123 | Previous_gtids |     12345 |         154 |                                       |
    | mysqlbinlog.000001 | 154 | Stop           |     12345 |         177 |                                       |
    +--------------------+-----+----------------+-----------+-------------+---------------------------------------+
    
    mysql> show binlog events in 'mysqlbinlog.000002';
    +--------------------+-----+----------------+-----------+-------------+---------------------------------------+
    | Log_name           | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
    +--------------------+-----+----------------+-----------+-------------+---------------------------------------+
    | mysqlbinlog.000002 |   4 | Format_desc    |     12345 |         123 | Server ver: 5.7.31-log, Binlog ver: 4 |
    | mysqlbinlog.000002 | 123 | Previous_gtids |     12345 |         154 |                                       |
    +--------------------+-----+----------------+-----------+-------------+---------------------------------------+

## 系统命令行查看binlog文件 ##

# mysqlbinlog /usr/local/mysql/data/mysqlbinlog.000002
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
    DELIMITER /*!*/;
    # at 4
    #210425 9:06:38 server id 12345 end_log_pos 123 CRC32 0xa6c68f5b Start: binlog v 4, server v 5.7.31-log created 210425 9:06:38 at startup
    # Warning: this binlog is either in use or was not closed properly.
    ROLLBACK/*!*/;
    BINLOG ' XmmFYA85MAAAdwAAAHsAAAABAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABeaYVgEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AVuPxqY= '/*!*/;
    # at 123
    #210425 9:06:38 server id 12345 end_log_pos 154 CRC32 0x645b0a39 Previous-GTIDs
    # [empty]
    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*/;

## 使用binlog恢复数据 ##

下面演示一个删除数据库再到恢复数据库的过程：

mysql> create database yj;
    
    mysql> use yj;
    
    mysql> create table dept(id int primary key, name varchar(20));
    
    mysql> insert into dept values(1, 'tom');
    
    mysql> drop database yj;
    
    mysql> show binlog events in 'mysqlbinlog.000002';
    +--------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
    | Log_name           | Pos  | Event_type     | Server_id | End_log_pos | Info                                                              |
    +--------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
    | mysqlbinlog.000002 |    4 | Format_desc    |     12345 |         123 | Server ver: 5.7.31-log, Binlog ver: 4                             |
    | mysqlbinlog.000002 |  123 | Previous_gtids |     12345 |         154 |                                                                   |
    | mysqlbinlog.000002 |  154 | Anonymous_Gtid |     12345 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
    | mysqlbinlog.000002 |  219 | Query          |     12345 |         307 | create database yj                                                |
    | mysqlbinlog.000002 |  307 | Anonymous_Gtid |     12345 |         372 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
    | mysqlbinlog.000002 |  372 | Query          |     12345 |         497 | use `yj`; create table dept(id int primary key, name varchar(20)) |
    | mysqlbinlog.000002 |  497 | Anonymous_Gtid |     12345 |         562 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
    | mysqlbinlog.000002 |  562 | Query          |     12345 |         632 | BEGIN                                                             |
    | mysqlbinlog.000002 |  632 | Table_map      |     12345 |         680 | table_id: 108 (yj.dept)                                           |
    | mysqlbinlog.000002 |  680 | Write_rows     |     12345 |         724 | table_id: 108 flags: STMT_END_F                                   |
    | mysqlbinlog.000002 |  724 | Xid            |     12345 |         755 | COMMIT /* xid=20 */                                               |
    | mysqlbinlog.000002 |  755 | Anonymous_Gtid |     12345 |         820 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
    | mysqlbinlog.000002 |  820 | Query          |     12345 |         906 | drop database yj                                                  |
    | mysqlbinlog.000002 |  906 | Anonymous_Gtid |     12345 |         971 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
    | mysqlbinlog.000002 |  971 | Query          |     12345 |        1059 | create database yj                                                |
    | mysqlbinlog.000002 | 1059 | Anonymous_Gtid |     12345 |        1124 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
    | mysqlbinlog.000002 | 1124 | Query          |     12345 |        1249 | use `yj`; create table dept(id int primary key, name varchar(20)) |
    | mysqlbinlog.000002 | 1249 | Anonymous_Gtid |     12345 |        1314 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
    | mysqlbinlog.000002 | 1314 | Query          |     12345 |        1384 | BEGIN                                                             |
    | mysqlbinlog.000002 | 1384 | Table_map      |     12345 |        1432 | table_id: 110 (yj.dept)                                           |
    | mysqlbinlog.000002 | 1432 | Write_rows     |     12345 |        1476 | table_id: 110 flags: STMT_END_F                                   |
    | mysqlbinlog.000002 | 1476 | Xid            |     12345 |        1507 | COMMIT /* xid=59 */                                               |
    | mysqlbinlog.000002 | 1507 | Anonymous_Gtid |     12345 |        1572 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
    | mysqlbinlog.000002 | 1572 | Query          |     12345 |        1658 | drop database yj                                                  |
    | mysqlbinlog.000002 | 1658 | Anonymous_Gtid |     12345 |        1723 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
    | mysqlbinlog.000002 | 1723 | Query          |     12345 |        1811 | create database yj                                                |
    | mysqlbinlog.000002 | 1811 | Anonymous_Gtid |     12345 |        1876 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
    | mysqlbinlog.000002 | 1876 | Query          |     12345 |        2001 | use `yj`; create table dept(id int primary key, name varchar(20)) |
    | mysqlbinlog.000002 | 2001 | Anonymous_Gtid |     12345 |        2066 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
    | mysqlbinlog.000002 | 2066 | Query          |     12345 |        2136 | BEGIN                                                             |
    | mysqlbinlog.000002 | 2136 | Table_map      |     12345 |        2184 | table_id: 111 (yj.dept)                                           |
    | mysqlbinlog.000002 | 2184 | Write_rows     |     12345 |        2228 | table_id: 111 flags: STMT_END_F                                   |
    | mysqlbinlog.000002 | 2228 | Xid            |     12345 |        2259 | COMMIT /* xid=100 */                                              |
    | mysqlbinlog.000002 | 2259 | Anonymous_Gtid |     12345 |        2324 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
    | mysqlbinlog.000002 | 2324 | Query          |     12345 |        2410 | drop database yj                                                  |
    +--------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+

恢复数据库，命令行操作，从上面的binlog中找到要恢复数据的起始位置和结束位置：

# mysqlbinlog --start-position=154 --stop-position=1507 /usr/local/mysql/data/mysqlbinlog.000002 | mysql -uroot -p

查询数据库是否已恢复：

mysql> use yj;
    
    mysql> select * from dept;
    +----+------+
    | id | name |
    +----+------+
    |  1 | tom  |
    +----+------+

还可以根据时间段来恢复数据：

# mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stopdatetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p

mysqldump：定期全部备份数据库数据。

mysqlbinlog可以做增量备份和恢复操作。

## 删除Binlog文件 ##

purge binary logs to 'mysqlbinlog.000001'; // 删除指定文件
    purge binary logs before '2020-04-28 00:00:00'; // 删除指定时间之前的文件
    reset master; // 清除所有文件

可以通过设置expire\_logs\_days参数来启动自动清理功能。默认值为0表示没启用。设置为1  
表示超出1天binlog文件会自动删除掉。

mysql> show variables like '%expire_logs_days%';
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | expire_logs_days | 0     |
    +------------------+-------+

# Redo Log和Binlog区别 #

*  Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制  
    文件记录。
 *  Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。
 *  Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不  
    会覆盖使用。
 *  Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢  
    复使用。Binlog没有自动crash-safe能力。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70]: /images/20221021/564c1b3a1174447d86af18ed57bb9046.png