• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

MySQL中的Binlog

互联网 diligentman 2周前 (04-30) 7次浏览

Binlog文件结构

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

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

MySQL中的Binlog

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:

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF    |
+---------------+-------+
1 row in set (0.00 sec)

开启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> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name                   | Value                                   |
+---------------------------------+-----------------------------------------+
| log_bin                         | ON                                      |
| log_bin_basename                | /usr/local/mysql/data/mysqlbinlog       |
| log_bin_index                   | /usr/local/mysql/data/mysqlbinlog.index |
| log_bin_trust_function_creators | OFF                                     |
| log_bin_use_v1_row_events       | OFF                                     |
| sql_log_bin                     | ON                                      |
+---------------------------------+-----------------------------------------+
6 rows in set (0.00 sec)

mysql命令行查看binlog文件

查看目前有哪些binlog文件:

mysql> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mysqlbinlog.000001 |       177 |
| mysqlbinlog.000002 |       154 |
+--------------------+-----------+
2 rows in set (0.00 sec)

mysql> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mysqlbinlog.000001 |       177 |
| mysqlbinlog.000002 |       154 |
+--------------------+-----------+
2 rows in set (0.00 sec)

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

mysql> show master status
;
+--------------------+----------+--------------+------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| mysqlbinlog.000002 |      154 |              |                  |                   |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

查看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能力。


程序员灯塔
转载请注明原文链接:MySQL中的Binlog
喜欢 (0)