MySQL 高级篇 -- 数据库日志

最新推荐文章于 2024-08-28 13:21:36 发布

L小Ray想有腮

最新推荐文章于 2024-08-28 13:21:36 发布

阅读量1.1k

点赞数 2

分类专栏： MySQL 文章标签：数据库

本文链接：https://blog.csdn.net/weixin_42480750/article/details/123938111

版权

MySQL 专栏收录该内容

28 篇文章 11 订阅

订阅专栏

文章目录

一、简介
二、通用查询日志
三、错误日志
四、二进制日志（bin log）
五、中继日志（relay log）
六、日志管理
- 6.1 日志清理
- 6.2 日志滚动

一、简介

日志类型	说明
慢查询日志	记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
事务日志	包括 redo 日志和 undo 日志。redo log 称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性；undo log 称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。
通用查询日志	记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
错误日志	记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。
二进制日志	记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
中继日志	用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
数据定义语句日志	记录数据定义语句执行的元数据操作。

二、通用查询日志

变量

mysql> show variables like '%general%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | OFF                             | # 通用查询日志处于关闭状态
| general_log_file | /var/lib/mysql/5157698acdb3.log | # 通用查询日志文件的名称
+------------------+---------------------------------+
2 rows in set (0.01 sec)

mysql> set global general_log = on;
Query OK, 0 rows affected (0.11 sec)

mysql>

日志内容

root@5157698acdb3:/var/lib/mysql# more 5157698acdb3.log 
/usr/sbin/mysqld, Version: 8.0.27 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
2022-04-03T08:12:30.884376Z	   59 Query	show variables like '%general%'
2022-04-03T08:14:49.776896Z	   59 Query	SELECT DATABASE()
2022-04-03T08:14:49.777140Z	   59 Init DB	test
2022-04-03T08:14:52.839826Z	   59 Query	show tables
2022-04-03T08:14:58.829493Z	   59 Query	select * from student
2022-04-03T08:15:19.392348Z	   59 Query	insert into student values(2, '', '')
2022-04-03T08:15:37.909894Z	   59 Query	update student set name = '李四' where id = 2
2022-04-03T08:15:50.862257Z	   59 Query	delete from student where name = '李四'
root@5157698acdb3:/var/lib/mysql#

三、错误日志

在MySQL数据库中，错误日志功能是 默认开启 的。而且，错误日志 无法被禁止。

变量

Linux 默认文件位置为 /var/log/mysqld.log，但容器的话会打印到控制台由 Dokcer 进行收集并通过 docker logs container-name 来查看。

mysql> show variables like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| binlog_error_action        | ABORT_SERVER                           |
| log_error                  | stderr                                 |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
5 rows in set (0.00 sec)

mysql>

日志内容

root@simwor:~# docker logs mysql33060 --tail=15
2022-03-28T01:15:10.545649Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-03-28T01:15:11.784511Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-03-28T01:15:32.150263Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-03-28T01:15:32.151975Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-03-28T01:15:32.169335Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-03-28T01:15:32.169524Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-03-28T01:15:32.185872Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2022-03-28T01:15:32.326567Z 6 [Warning] [MY-013360] [Server] 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead
2022-03-28T01:15:32.326744Z 6 [Warning] [MY-013360] [Server] 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead
2022-03-28T01:15:32.326809Z 6 [Warning] [MY-013360] [Server] 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead
2022-03-28T01:15:32.329870Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.27'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
2022-03-28T01:15:32.331423Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
mbind: Operation not permitted
mbind: Operation not permitted
mbind: Operation not permitted
root@simwor:~#

四、二进制日志（bin log）

4.1 简介

说明

binlog 即 binary log，二进制日志文件，也叫作变更日志（update log）。
它记录了数据库所有执行的 DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。
它以 事件形式 记录并保存在 二进制文件 中，通过这些信息可以再现数据更新操作的全过程。

应用场景

数据恢复：如果 MySQL 数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器分拣做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
数据复制：由于日志的延续性和时效性，master 把它的二进制日志传递给 slave 来达到 master-slave 数据一致的目的。
数据库的数据备份、主备、主主、主从都离不开 binlog，需要依靠 binlog 来同步数据，保证数据一致性。

在这里插入图片描述

对比 redo log

redo log 它是 物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生的。
而 binlog 是 逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于 MySQL Server 层。
两者虽都属于持久化的保证，但是侧重点不同：redo log 让 InnoDB 存储引擎拥有了崩溃恢复的能力；binlog 保证了 MySQL 集群架构的数据一致性。

参数

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.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>

参数	说明
log_bin_basename	日志的基本文件名，后面会追加标识来表示每一个文件。
log_bin_index	日志文件的索引文件，这个文件管理了所有的 binlog 文件的目录。
log_bin_trust_function_creators	限制存储过程及存储函数。二进制日志的一个重要功能是用于主从复制，而存储函数如 NOW() 有可能导致主从的数据不一致。
log_bin_use_v1_row_events	弃用。

binlog 记录格式

记录格式	模式	说明
statement	SBR	语句模式记录日志，做什么命令，记录什么命令（可读性强，日志量相对较少，但可能会出现记录不准确的情况例如插入字段时使用now()函数）
row	RBR	行模式，记录数据行的变化（记录更加准确，高可用环境中的新特性要依赖于RBR）
mixed	MBR	混合模式，由MySQL自行选择

4.2 查看日志

查看正在使用的二进制文件

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

4.2.1 show binlog events

语法：mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

参数	说明
IN ‘log_name’	指定要查询的binlog文件名（不指定就是第一个binlog文件）
FROM pos	指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
LIMIT [offset]	偏移量(不指定就是0)
row_count	查询总条数（不指定就是所有行）

实例

构建环境

mysql> create database binlog;
Query OK, 1 row affected (0.00 sec)

mysql> use binlog;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert t1 values(1);
Query OK, 1 row affected (0.06 sec)

mysql>

未提交DML语句查看事件

mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                               |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------+
| mysql-bin.000004 |   4 | Format_desc    |         6 |         125 | Server ver: 8.0.20, Binlog ver: 4                  |
| mysql-bin.000004 | 125 | Previous_gtids |         6 |         156 |                                                    |
| mysql-bin.000004 | 156 | Anonymous_Gtid |         6 |         233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| mysql-bin.000004 | 233 | Query          |         6 |         347 | create database binlog /* xid=15 */                |
| mysql-bin.000004 | 347 | Anonymous_Gtid |         6 |         424 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| mysql-bin.000004 | 424 | Query          |         6 |         539 | use `binlog`; create table t1(id int) /* xid=20 */ |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------+
6 rows in set (0.00 sec)

mysql>

提交DML语句后查看

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                               |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------+
| mysql-bin.000004 |   4 | Format_desc    |         6 |         125 | Server ver: 8.0.20, Binlog ver: 4                  |
| mysql-bin.000004 | 125 | Previous_gtids |         6 |         156 |                                                    |
| mysql-bin.000004 | 156 | Anonymous_Gtid |         6 |         233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| mysql-bin.000004 | 233 | Query          |         6 |         347 | create database binlog /* xid=15 */                |
| mysql-bin.000004 | 347 | Anonymous_Gtid |         6 |         424 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| mysql-bin.000004 | 424 | Query          |         6 |         539 | use `binlog`; create table t1(id int) /* xid=20 */ |
| mysql-bin.000004 | 539 | Anonymous_Gtid |         6 |         618 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| mysql-bin.000004 | 618 | Query          |         6 |         695 | BEGIN                                              |
| mysql-bin.000004 | 695 | Table_map      |         6 |         745 | table_id: 89 (binlog.t1)                           |
| mysql-bin.000004 | 745 | Write_rows     |         6 |         785 | table_id: 89 flags: STMT_END_F                     |
| mysql-bin.000004 | 785 | Xid            |         6 |         816 | COMMIT /* xid=21 */                                |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------+
11 rows in set (0.00 sec)

mysql>

4.2.2 mysqlbinlog

查看

root@5157698acdb3:/var/lib/mysql# mysqlbinlog -v binlog.000008 | tail -15
tldJYiABAAAAMAAAAPwQAAAAAJQAAAAAAAEAAgAD/wACAAAABuadjuWbmwAnVW+c
'/*!*/;
### DELETE FROM `test`.`student`
### WHERE
###   @1=2
###   @2='李四'
###   @3=''
# at 4348
#220403  8:15:50 server id 1  end_log_pos 4379 CRC32 0x77b5dc8b 	Xid = 256
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*/;
root@5157698acdb3:/var/lib/mysql#

匹配事件 745 - 785

选项 ‘-d’ 可以指定过滤某个库的日志

[root@mysql01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/binlog/mysql-bin.000004 | sed -n "/at 745/,/at 785/p"
# at 745
#200924 13:53:18 server id 6  end_log_pos 785 CRC32 0x9ed03402 	Write_rows: table id 89 flags: STMT_END_F
### INSERT INTO `binlog`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 785
[root@mysql01 ~]#

截取二进制事件 233 - 347

[root@mysql01 ~]# mysqlbinlog --start-position=233 --stop-position=347 /data/mysql/binlog/mysql-bin.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 156
#200924 13:50:41 server id 6  end_log_pos 125 CRC32 0xc637d02c 	Start: binlog v 4, server v 8.0.20 created 200924 13:50:41
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
sTNsXw8GAAAAeQAAAH0AAAABAAQAOC4wLjIwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBLNA3xg==
'/*!*/;
# at 233
#200924 13:52:53 server id 6  end_log_pos 347 CRC32 0x74cb0e8a 	Query	thread_id=9	exec_time=0	error_code=0	Xid = 15
SET TIMESTAMP=1600926773/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database binlog
/*!*/;
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*/;
[root@mysql01 ~]#

4.3 使用日志恢复数据

模拟数据

mysql> create database test2;
Query OK, 1 row affected (0.01 sec)

mysql> use test2
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>

模拟故障

mysql> drop database test2;
Query OK, 1 row affected (0.01 sec)

mysql>

查看日志

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     1653 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000004';
+------------------+------+----------------+-----------+-------------+----------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                               |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------+
| mysql-bin.000004 |    4 | Format_desc    |         6 |         125 | Server ver: 8.0.20, Binlog ver: 4                  |
| mysql-bin.000004 |  125 | Previous_gtids |         6 |         156 |                                                    |
| mysql-bin.000004 |  156 | Anonymous_Gtid |         6 |         233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| mysql-bin.000004 |  233 | Query          |         6 |         347 | create database binlog /* xid=15 */                |
| mysql-bin.000004 |  347 | Anonymous_Gtid |         6 |         424 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| mysql-bin.000004 |  424 | Query          |         6 |         539 | use `binlog`; create table t1(id int) /* xid=20 */ |
| mysql-bin.000004 |  539 | Anonymous_Gtid |         6 |         618 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| mysql-bin.000004 |  618 | Query          |         6 |         695 | BEGIN                                              |
| mysql-bin.000004 |  695 | Table_map      |         6 |         745 | table_id: 89 (binlog.t1)                           |
| mysql-bin.000004 |  745 | Write_rows     |         6 |         785 | table_id: 89 flags: STMT_END_F                     |
| mysql-bin.000004 |  785 | Xid            |         6 |         816 | COMMIT /* xid=21 */                                |
| mysql-bin.000004 |  816 | Anonymous_Gtid |         6 |         893 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| mysql-bin.000004 |  893 | Query          |         6 |        1004 | create database test2 /* xid=27 */                 |
| mysql-bin.000004 | 1004 | Anonymous_Gtid |         6 |        1081 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| mysql-bin.000004 | 1081 | Query          |         6 |        1194 | use `test2`; create table t1(id int) /* xid=32 */  |
| mysql-bin.000004 | 1194 | Anonymous_Gtid |         6 |        1273 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| mysql-bin.000004 | 1273 | Query          |         6 |        1349 | BEGIN                                              |
| mysql-bin.000004 | 1349 | Table_map      |         6 |        1398 | table_id: 90 (test2.t1)                            |
| mysql-bin.000004 | 1398 | Write_rows     |         6 |        1438 | table_id: 90 flags: STMT_END_F                     |
| mysql-bin.000004 | 1438 | Xid            |         6 |        1469 | COMMIT /* xid=33 */                                |
| mysql-bin.000004 | 1469 | Anonymous_Gtid |         6 |        1546 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| mysql-bin.000004 | 1546 | Query          |         6 |        1653 | drop database test2 /* xid=35 */                   |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------+
22 rows in set (0.00 sec)

mysql>

截取日志（893:create database - drop database:1546）

[root@mysql01 ~]# mysqlbinlog --start-position=893 --stop-position=1546 /data/mysql/binlog/mysql-bin.000004 > /tmp/restore.sql
[root@mysql01 ~]#

恢复数据（恢复数据时可以临时关闭日志）

mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> set @@sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/restore.sql
Query OK, 0 rows affected (0.00 sec)
....
Charset changed
....
Database changed
....
mysql> select * from test2.t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> set @@sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql>

4.4 写入机制

binlog的写入时机也非常简单，事务执行过程中，先把日志写到 binlog cache ，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

在这里插入图片描述

write和fsync的时机，可以由参数 sync_binlog 控制，默认是 1 。

mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql>

参数值	说明
0	每次提交事务都只 write，由系统自行判断什么时候执行fsync。
1	示每次提交事务都会执行fsync，就如同 redo log 刷盘流程一样。
N(N>1)	每次提交事务都write，但累积N个事务后才fsync。

sync_binlog = 0

在这里插入图片描述

sync_binlog = 2

在这里插入图片描述

4.5 两阶段提交

问题引出

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。

在这里插入图片描述

redo log与binlog两份日志之间的逻辑不一致，会出现什么问题？ 以 update 语句为例，假设 id = 2 的记录，字段 c 值是 0，把字段 c 值更新成 1；假设执行过程中写完 redo log 日之后，binlog 日志写期间发生了异常，会出现什么情况呢？

在这里插入图片描述

由于 binlog 没写完就异常，这时候 binlog 里面没有对应的修改记录。因此，之后用 binlog 日志恢复数据时，就会少一次更新，恢复出来的这一行 c 值为 0，而原库因为 redo log 日志恢复，这一行值 c 值是 1，最终数据不一致。

在这里插入图片描述

问题解决

为了解决两份日志之间的逻辑一致性问题，InnoDB 存储引擎使用两阶段提交方案。原理很简单，将 redo log 的写入拆成了两个步骤 prepare 和 commit。

在这里插入图片描述

使用两阶段提交后，写入 binlog 时发生异常也不会有影响，因为 MySQL 根据 redo log 日志恢复数据时，发现 redo log 还处于 prepare 阶段，并且没有对应 binlog 日志，就会回滚该事务。

在这里插入图片描述

redo log 设置 commit 阶段发生异常，如果能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。

在这里插入图片描述

总结

会话发起COMMIT动作
存储引擎层开启 [Prepare] 状态：在对应的 Redo 日志记录上打上 Prepare 标记
写入binlog 并执行 fsync(刷盘)
在 redo 日志记录上打上 COMMIT 标记表示记录提交完成

4.6 GTID

简介

缩写：Global Transaction Identification
对于 DML，每一个事务，都会生成一个 GTID 号
对于 DDL 和 DCL，一个语句（event）就是一个事务，就会有一个 GTID 号
GTID 是一个自增长的数据，从1开始
GTID 组成 - server_uuid:GTID

[root@mysql01 ~]# cat /data/mysql/data/auto.cnf 
[auto]
server-uuid=95a35387-fa2c-11ea-8932-000c29b46d5f
[root@mysql01 ~]#

开启GTID

默认

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | OFF       |
| gtid_executed                    |           |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | OFF       |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
9 rows in set (0.00 sec)

mysql>

永久开启

[root@mysql01 ~]# sed '/log_bin/a gtid_mode=on' /etc/my.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
autocommit=0
log_error=./mysql01.err
log_bin=/data/mysql/binlog/mysql-bin
gtid_mode=on
[mysql]
socket=/tmp/mysql.sock
[root@mysql01 ~]# sed -i '/log_bin/a gtid_mode=on' /etc/my.cnf 
[root@mysql01 ~]# sed -i '/gtid_mode/a enforce_gtid_consistency=true' /etc/my.cnf 
[root@mysql01 ~]# systemctl restart mysqld
[root@mysql01 ~]# mysql -uroot -p
....
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed                    |           |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
9 rows in set (0.01 sec)

mysql>

GTID 模式恢复数据

幂等性:如果拿有GTID的日志去恢复时，检查当前系统中是否有相同GTID号，有相同的就自动跳过

模拟数据

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> create database gtid;
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000005 |      341 |              |                  | 95a35387-fa2c-11ea-8932-000c29b46d5f:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> use gtid;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 |      529 |              |                  | 95a35387-fa2c-11ea-8932-000c29b46d5f:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> insert t1 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 |      529 |              |                  | 95a35387-fa2c-11ea-8932-000c29b46d5f:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 |      802 |              |                  | 95a35387-fa2c-11ea-8932-000c29b46d5f:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql>

模拟故障

mysql> drop database gtid;
Query OK, 1 row affected (0.02 sec)

mysql>

截取GTID日志

mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000005 |   4 | Format_desc    |         6 |         125 | Server ver: 8.0.20, Binlog ver: 4                                 |
| mysql-bin.000005 | 125 | Previous_gtids |         6 |         156 |                                                                   |
| mysql-bin.000005 | 156 | Gtid           |         6 |         233 | SET @@SESSION.GTID_NEXT= '95a35387-fa2c-11ea-8932-000c29b46d5f:1' |
| mysql-bin.000005 | 233 | Query          |         6 |         341 | create database gtid /* xid=5 */                                  |
| mysql-bin.000005 | 341 | Gtid           |         6 |         418 | SET @@SESSION.GTID_NEXT= '95a35387-fa2c-11ea-8932-000c29b46d5f:2' |
| mysql-bin.000005 | 418 | Query          |         6 |         529 | use `gtid`; create table t1(id int) /* xid=11 */                  |
| mysql-bin.000005 | 529 | Gtid           |         6 |         608 | SET @@SESSION.GTID_NEXT= '95a35387-fa2c-11ea-8932-000c29b46d5f:3' |
| mysql-bin.000005 | 608 | Query          |         6 |         683 | BEGIN                                                             |
| mysql-bin.000005 | 683 | Table_map      |         6 |         731 | table_id: 89 (gtid.t1)                                            |
| mysql-bin.000005 | 731 | Write_rows     |         6 |         771 | table_id: 89 flags: STMT_END_F                                    |
| mysql-bin.000005 | 771 | Xid            |         6 |         802 | COMMIT /* xid=15 */                                               |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
11 rows in set (0.00 sec)

mysql>

[root@mysql01 ~]# mysqlbinlog --include-gtids='95a35387-fa2c-11ea-8932-000c29b46d5f:1-3' /data/mysql/binlog/mysql-bin.000005 > /tmp/gtid.sql
[root@mysql01 ~]#

恢复GTID日志

mysql> set @@sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/gtid.sql
....
ERROR 1049 (42000): Unknown database 'gtid'
....
mysql> set @@sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql>

报错

因为幂等性的检查，1-3事务已经做过了

重新导出GTID日志，跳过原有的GTID信息 --skip-gtids

[root@mysql01 ~]# mysqlbinlog --skip-gtids --include-gtids='95a35387-fa2c-11ea-8932-000c29b46d5f:1-3' /data/mysql/binlog/mysql-bin.000005 > /tmp/gtid2.sql
[root@mysql01 ~]#

恢复数据

mysql> set @@sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/gtid2.sql
....
Database changed
....
mysql> set @@sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from gtid.t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

五、中继日志（relay log）

简介

中继日志只在主从服务器架构的从服务器上存在。
从服务器为了与主服务器保持一致，要从主服务器取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。
然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。

在这里插入图片描述

参数

mysql> show variables like '%relay_log%';
+---------------------------+---------------------------------------------+
| Variable_name             | Value                                       |
+---------------------------+---------------------------------------------+
| max_relay_log_size        | 0                                           |
| relay_log                 | 5157698acdb3-relay-bin                      |
| relay_log_basename        | /var/lib/mysql/5157698acdb3-relay-bin       |
| relay_log_index           | /var/lib/mysql/5157698acdb3-relay-bin.index |
| relay_log_info_file       | relay-log.info                              |
| relay_log_info_repository | TABLE                                       |
| relay_log_purge           | ON                                          |
| relay_log_recovery        | OFF                                         |
| relay_log_space_limit     | 0                                           |
| sync_relay_log            | 10000                                       |
| sync_relay_log_info       | 10000                                       |
+---------------------------+---------------------------------------------+
11 rows in set (0.00 sec)

mysql>

六、日志管理

6.1 日志清理

定时

一般设置两个备份周期加一天，例如每周一次备份，则设置为15 = 7 x 2 + 1

mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  0 |
+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

手工

mysql> help purge;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   PURGE BINARY LOGS
   PURGE MASTER LOGS

mysql> help purge binary logs;
....
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

mysql>

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       179 | No        |
| mysql-bin.000002 |       179 | No        |
| mysql-bin.000003 |       203 | No        |
| mysql-bin.000004 |      1676 | No        |
| mysql-bin.000005 |      1006 | No        |
| mysql-bin.000006 |       219 | No        |
| mysql-bin.000007 |       196 | No        |
+------------------+-----------+-----------+
7 rows in set (0.01 sec)

mysql> purge binary lomysql-bin.000003';
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000003 |       203 | No        |
| mysql-bin.000004 |      1676 | No        |
| mysql-bin.000005 |      1006 | No        |
| mysql-bin.000006 |       219 | No        |
| mysql-bin.000007 |       196 | No        |
+------------------+-----------+-----------+
5 rows in set (0.00 sec)

mysql>

[root@mysql01 ~]# ll /data/mysql/binlog/ --time-style=full-iso
total 24
-rw-r-----. 1 mysql mysql  203 2020-09-24 13:50:41.249949928 +0800 mysql-bin.000003
-rw-r-----. 1 mysql mysql 1676 2020-09-24 15:02:38.237774262 +0800 mysql-bin.000004
-rw-r-----. 1 mysql mysql 1006 2020-09-24 15:50:46.312656741 +0800 mysql-bin.000005
-rw-r-----. 1 mysql mysql  219 2020-09-24 15:56:16.500643305 +0800 mysql-bin.000006
-rw-r-----. 1 mysql mysql  196 2020-09-24 15:56:18.390643228 +0800 mysql-bin.000007
-rw-r-----. 1 mysql mysql  180 2020-09-24 16:28:45.934563979 +0800 mysql-bin.index
[root@mysql01 ~]# mysql -uroot -p
....
mysql> purge binary logs before '2020-09-24 15:56:16';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@mysql01 ~]# ll /data/mysql/binlog/ --time-style=full-iso
total 12
-rw-r-----. 1 mysql mysql 219 2020-09-24 15:56:16.500643305 +0800 mysql-bin.000006
-rw-r-----. 1 mysql mysql 196 2020-09-24 15:56:18.390643228 +0800 mysql-bin.000007
-rw-r-----. 1 mysql mysql  72 2020-09-24 16:34:11.906550715 +0800 mysql-bin.index
[root@mysql01 ~]#

6.2 日志滚动

场景一：手动刷新日志

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000006 |       219 | No        |
| mysql-bin.000007 |       196 | No        |
+------------------+-----------+-----------+
2 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000006 |       219 | No        |
| mysql-bin.000007 |       243 | No        |
| mysql-bin.000008 |       196 | No        |
+------------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql>

场景二：重启服务

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000006 |       219 | No        |
| mysql-bin.000007 |       243 | No        |
| mysql-bin.000008 |       196 | No        |
+------------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> quit
Bye
[root@mysql01 ~]# systemctl restart mysqld
[root@mysql01 ~]# mysql -uroot -p
....
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000006 |       219 | No        |
| mysql-bin.000007 |       243 | No        |
| mysql-bin.000008 |       219 | No        |
| mysql-bin.000009 |       196 | No        |
+------------------+-----------+-----------+
4 rows in set (0.00 sec)

mysql>

场景三：达到日志文件大小的最大限制

mysql> select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
|        1073741824 |
+-------------------+
1 row in set (0.00 sec)

mysql>