【MySQL】MySQL之Binlog[亲测有效]
Hi，大家好，我是编程小6，很荣幸遇见你，我把这些年在开发过程中遇到的问题或想法写出来，今天说一说【MySQL】MySQL之Binlog[亲测有效],希望能够帮助你!!!。
![【MySQL】MySQL之Binlog[亲测有效]_https://bianchenghao6.com/blog__第1张](./wp-content/uploads/2023/03/2023032613475569-150x93.jpg "【MySQL】MySQL之Binlog[亲测有效]__第1张_编程好6博客")
Binlog格式介绍
MySQL binlog日志有三种格式，分别为：
- Statement
- MiXED
- ROW
查看数据库使用的是什么模式：
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
三种模式的区别：
- Statement：每一条会修改数据的SQL都会记录在binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
缺点：由于记录的只是执行SQL语句，SQL中包含now()、last_insert_id()、user-defined functions(用户自定义行数)会出现数据不一致性问题。
- Row（默认）：不记录SQL语句上下文相关信息，仅保存哪条记录被修改。一条SQL操作影响的记录会被全部记录下来，比如一条SQL语句更新了三行记录，在二进制日志中会记录被修改的这三条记录的前项（before image）和后项（after image）。
优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row level的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。
- Mixedlevel：是以上两种模式的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。
探究两种模式生成的日志文件的区别
准备数据：
mysql> create table t_user(id int, name varchar(20));
mysql> insert into t_user values(1, "A");
mysql> insert into t_user values(2, "B");
mysql> insert into t_user values(3, "C");
在row模式下，执行：
delete from t_user where id in(1,2,3);
生成的日志文件的格式如下：
# at 1132
#220123 19:40:15 server id 1403311 end_log_pos 1197 CRC32 0xf5d9d4a7 Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=yes original_committed_timestamp=0 immediate_commit_timestamp=0 transaction_length=0
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=0 (1970-01-01 08:00:00.000000 CST)
# immediate_commit_timestamp=0 (1970-01-01 08:00:00.000000 CST)
/*!80001 SET @@session.original_commit_timestamp=0*//*!*/;
/*!80014 SET @@session.original_server_version=0*//*!*/;
/*!80014 SET @@session.immediate_server_version=0*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1197
#220123 19:40:15 server id 1403311 end_log_pos 1270 CRC32 0x730453b9 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1642938015/*!*/;
BEGIN
/*!*/;
# at 1270
#220123 19:40:15 server id 1403311 end_log_pos 1323 CRC32 0x84dd1ff4 Table_map: `order`.`t_user` mapped to number 108
# at 1323
#220123 19:40:15 server id 1403311 end_log_pos 1379 CRC32 0x33c0bf18 Delete_rows: table id 108 flags: STMT_END_F
BINLOG ' nz7tYROvaRUANQAAACsFAAAAAGwAAAAAAAEABW9yZGVyAAZ0X3VzZXIAAgMPAhQAA/Qf3YQ= nz7tYSCvaRUAOAAAAGMFAAAAAGwAAAAAAAEAAgAC//wBAAAAAUH8AgAAAAFC/AMAAAABQxi/wDM= '/*!*/;
### DELETE FROM `order`.`t_user`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='A' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### DELETE FROM `order`.`t_user`
### WHERE
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='B' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### DELETE FROM `order`.`t_user`
### WHERE
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='C' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
# at 1379
#220123 19:40:15 server id 1403311 end_log_pos 1410 CRC32 0x170c6b1b Xid = 17
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMTER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
修改模式需要修改my.cnf文件，并重启mysql：
log-bin=mysql-bin -- 开启binlog
binlog-format=STATEMENT -- 模式为statement
在statement模式下，执行：
delete from t_user where id in(1,2,3);
生成的日志文件的格式如下：
# at 1012
#220123 19:47:39 server id 1403311 end_log_pos 1077 CRC32 0x98482e67 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no original_committed_timestamp=0 immediate_commit_timestamp=0 transaction_length=0
# original_commit_timestamp=0 (1970-01-01 08:00:00.000000 CST)
# immediate_commit_timestamp=0 (1970-01-01 08:00:00.000000 CST)
/*!80001 SET @@session.original_commit_timestamp=0*//*!*/;
/*!80014 SET @@session.original_server_version=0*//*!*/;
/*!80014 SET @@session.immediate_server_version=0*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1077
#220123 19:47:39 server id 1403311 end_log_pos 1158 CRC32 0x8ea551a3 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1642938459/*!*/;
BEGIN
/*!*/;
# at 1158
#220123 19:47:39 server id 1403311 end_log_pos 1271 CRC32 0xbb8530a3 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1642938459/*!*/;
delete from t_user where id in(1,2,3)
/*!*/;
# at 1271
#220123 19:47:39 server id 1403311 end_log_pos 1302 CRC32 0x160bff36 Xid = 13
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*/;
Binlog文件结构
MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。
Binlog文件中Log event结构如下图所示：
![【MySQL】MySQL之Binlog[亲测有效]_https://bianchenghao6.com/blog__第2张](https://img-blog.csdnimg.cn/img_convert/894b06aa28ca55e1be04157a786c46b3.png "【MySQL】MySQL之Binlog[亲测有效]__第2张_编程好6博客")
Binlog写入机制
binlog写入过程大概分为以下三步：
-
根据记录模式和操作触发event事件生成log event（事件触发执行机制）
-
将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区，Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。
-
事务在提交阶段会将产生的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能力。
上一篇
已是最后文章
下一篇
已是最新文章
最新文章
