日志类型
MySQL分为 二进制日志
、 错误日志
、 通用查询日志
和 慢查询日志
,这也是常用的4种。MySQL 8
又新增: 中继日志
和 数据定义语句日志
。使用这些日志文件,可以查看MySQL内部发生的事情。
这6类日志分别为:
- 慢查询日志:记录所有执行时间超过long_query_time的所有查询,方便我们对查询进行优化。
- 通用查询日志:记录所有连接的起始时间和终止时间,以及连接发送给数据库服务器的所有指令,对我们复原操作的实际场景、发现问题,甚至是对数据库操作的审计都有很大的帮助。
- 错误日志:记录MySQL服务的启动、运行或停止MySQL服务时出现的问题,方便我们了解服务器的状态,从而对服务器进行维护。
- 二进制日志:记录所有更改数据的语句,可以用于主从服务器之间的数据同步,以及服务器遇到故障时数据的无损失恢复。
- 中继日志:用于主从服务器架构中,从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容,来同步主服务器上的操作。
- 数据定义语句日志:记录数据定义语句执行的元数据操作。
日志弊端
- 日志功能会
降低MySQL数据库的性能
。 - 日志会
占用大量的磁盘空间
。
一、慢查询日志
前面已了解
二、通用查询日志(general log)
通用查询日志用来
记录用户的所有操作
,包括启动
和关闭MySQL服务
、所有用户的连接开始时间
和截止时间
、发给 MySQL 数据库服务器的所有 SQL 指令
等。当我们的数据发生异常时,查看通用查询日志,还原操作时的具体场景,可以帮助我们准确定位问题。
查看当前日志状态
SHOW VARIABLES LIKE '%general%';
默认是关闭的
开启日志
永久开启
修改my.cnf或者my.ini配置文件来设置
。在[mysqld]组下加入log选项,并重启MySQL服务。如果不指定目录和文件名,通用查询日志将默认存储
在MySQL数据目录中的hostname.log文件中,hostname表示主机名
。格式如下:
[mysqld]
general_log=ON
general_log_file=[path[filename]] #日志文件所在目录路径,filename为日志文件名
临时开启
SET GLOBAL general_log=on; # 开启通用查询日志
SET GLOBAL general_log_file='path/filename'; # 设置日志文件保存位置
SET GLOBAL general_log=off; # 关闭通用查询日志
查看日志
文本形式可以直接文本编辑器打开
D:\Mysql\mysql-8.0.23-winx64\bin\mysqld, Version: 8.0.23 (MySQL Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
2022-08-27T13:35:25.367523Z 16 Query SHOW VARIABLES LIKE 'general_log%'
2022-08-27T13:35:46.287516Z 17 Query SET PROFILING = 1
2022-08-27T13:35:46.287815Z 17 Query SHOW STATUS
2022-08-27T13:35:46.293366Z 17 Query SHOW STATUS
2022-08-27T13:35:46.300092Z 17 Query SELECT * FROM students
2022-08-27T13:35:46.300700Z 18 Init DB test434
2022-08-27T13:35:46.300956Z 18 Query SELECT * FROM `test434`.`students` LIMIT 0
2022-08-27T13:35:46.301294Z 18 Init DB test434
2022-08-27T13:35:46.301615Z 18 Query SHOW COLUMNS FROM `test434`.`students`
2022-08-27T13:35:46.308183Z 17 Query SHOW STATUS
2022-08-27T13:35:46.314398Z 17 Query SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID
2022-08-27T13:35:46.315082Z 17 Query SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/0.000829*100,3), '') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=2 GROUP BY SEQ, STATE ORDER BY SEQ
三、错误日志(error log)
在MySQL数据库中,错误日志功能是 默认开启
的。而且,错误日志 无法被禁止
。
默认情况下,错误日志存储在MySQL数据库的数据文件夹下,名称默认为 mysqld.log (Linux系统)
或 hostname.err (mac系统)
。如果需要制定文件名,则需要在my.cnf或者my.ini中做如下配置:
[mysqld]
log-error=[path/[filename]] #path为日志文件所在的目录路径,filename为日志文件名
查看日志
SHOW VARIABLES LIKE 'log_err%';
进到相关路径找到相关文件,即可用文本编辑器打开
四、二进制日志(bin log)
binlog即binary log,二进制日志文件,也叫作变更日志(update log)。它记录了数据库所有执行的DDL 和 DML 等数据库更新事件的语句
,以事件方式存储
,但是不包含没有修改任何数据的语句(如数据查询语句select、show等)。
主要场景
- 数据恢复
- 数据复制
数据恢复、主备、主主、主从都要用binlog日志
查看日志默认情况
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------------+
| log_bin | ON |
| log_bin_basename | D:\Mysql\mysql-8.0.23-winx64\Data\binlog |
| log_bin_index | D:\Mysql\mysql-8.0.23-winx64\Data\binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)
- log_bin_basename:基本文件名,重启一次mysql服务会新建一个binlog
- log_bin_index:binlog文件索引,管理所有的binlog日志
- log_bin_trust_function_creators:限制存储过程,这是因为二进制日志的一个重要功能是用于主从复制,而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后,需要限制存储函数的创建、修改、调用(比如现在now()函数和主从复制时,调用的结果会不一样)
- log_bin_use_v1_row_events:弃用
参数情况
修改MySQL的 my.cnf 或 my.ini 文件可以设置二进制日志的相关参数:
[mysqld]
#启用二进制日志
log-bin=pang-bin # 基础文件名
binlog_expire_logs_seconds=600 # 文件保留时间,单位为秒
max_binlog_size=100M # 单个binlog文件大小,并不能精确把控
如果想改变日志文件的目录和名称,
linux系统新建的文件夹需要使用mysql用户
,可以对my.cnf或my.ini中的log_bin参数修改如下:
[mysqld]
log-bin="/var/lib/mysql/binlog/pang-bin"
MySQL服务
重新启动一次
,以“.000001”为后缀的文件就会增加一个,并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同;如果日志长度超过了 max_binlog_size 的上限(默认是1GB),就会创建一个新的日志文件。
查看日志
查看binlog日志列表和大小
mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000434 | 32034 | No |
| binlog.000435 | 521 | No |
| binlog.000436 | 7719 | No |
| binlog.000437 | 138018953 | No |
| binlog.000438 | 615 | No |
+---------------+-----------+-----------+
17 rows in set (0.70 sec)
所有对数据库的修改都会记录在binglog中。但binlog是二进制文件,无法直接查看,想要更直观的观测它就要借助mysqlbinlog
命令工具了。下面命令将行事件以 伪SQL的形式 表现出来
mysqlbinlog --no-defaults -v "D:\Mysql\mysql-8.0.23-winx64\Data\binlog.000450"
.....
### UPDATE `test434`.`students`
### WHERE
### @1=1
### @2='lin'
### @3='1'
### SET
### @1=1
### @2='hai'
### @3='1'
# at 446
#220827 23:08:57 server id 1 end_log_pos 477 CRC32 0x158f90d5 Xid = 176
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日志的全文内容比较多,不容易分辨查看到pos点信息
,下面介绍一种更为方便的查询命令:
mysql> show binlog events in 'binlog.000450';
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| binlog.000450 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.23, Binlog ver: 4 |
| binlog.000450 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000450 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000450 | 235 | Query | 1 | 322 | BEGIN |
| binlog.000450 | 322 | Table_map | 1 | 388 | table_id: 99 (test434.students) |
| binlog.000450 | 388 | Update_rows | 1 | 446 | table_id: 99 flags: STMT_END_F |
| binlog.000450 | 446 | Xid | 1 | 477 | COMMIT /* xid=176 */ |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
7 rows in set (0.00 sec)
这里一个update语句包含如下事件o
- Query事件负责开始一个事务(BEGIN)
- Table_map事件负责映射需要的表
- Update_rows事件负责写入数据
- Xid事件负责结束事务
使用日志恢复数据
# 增加三条数据
INSERT INTO students VALUES ('2', 'hong', 2)
INSERT INTO students VALUES ('3', 'huang', 2)
INSERT INTO students VALUES ('4', 'lu', 2)
# 更新一条数据
UPDATE students SET name = 'lan' WHERE class = 1
# 删除数据
DELETE FROM students WHERE class = 2
如果我们不小心删除了数据,这些都是已提交的事务,无法回滚,可以用binlog日志恢复到某一个事件节点
查看binlog日志,找到相关事务事件
mysql> show binlog events in 'binlog.000450';
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| binlog.000450 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.23, Binlog ver: 4 |
| binlog.000450 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000450 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000450 | 235 | Query | 1 | 322 | BEGIN |
| binlog.000450 | 322 | Table_map | 1 | 388 | table_id: 99 (test434.students) |
| binlog.000450 | 388 | Update_rows | 1 | 446 | table_id: 99 flags: STMT_END_F |
| binlog.000450 | 446 | Xid | 1 | 477 | COMMIT /* xid=176 */ |
| binlog.000450 | 477 | Anonymous_Gtid | 1 | 556 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000450 | 556 | Query | 1 | 634 | BEGIN |
| binlog.000450 | 634 | Table_map | 1 | 700 | table_id: 99 (test434.students) |
| binlog.000450 | 700 | Write_rows | 1 | 747 | table_id: 99 flags: STMT_END_F |
| binlog.000450 | 747 | Xid | 1 | 778 | COMMIT /* xid=239 */ |
| binlog.000450 | 778 | Anonymous_Gtid | 1 | 857 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000450 | 857 | Query | 1 | 935 | BEGIN |
| binlog.000450 | 935 | Table_map | 1 | 1001 | table_id: 99 (test434.students) |
| binlog.000450 | 1001 | Write_rows | 1 | 1049 | table_id: 99 flags: STMT_END_F |
| binlog.000450 | 1049 | Xid | 1 | 1080 | COMMIT /* xid=246 */ |
| binlog.000450 | 1080 | Anonymous_Gtid | 1 | 1159 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000450 | 1159 | Query | 1 | 1237 | BEGIN |
| binlog.000450 | 1237 | Table_map | 1 | 1303 | table_id: 99 (test434.students) |
| binlog.000450 | 1303 | Write_rows | 1 | 1348 | table_id: 99 flags: STMT_END_F |
| binlog.000450 | 1348 | Xid | 1 | 1379 | COMMIT /* xid=253 */ |
| binlog.000450 | 1379 | Anonymous_Gtid | 1 | 1458 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000450 | 1458 | Query | 1 | 1545 | BEGIN |
| binlog.000450 | 1545 | Table_map | 1 | 1611 | table_id: 99 (test434.students) |
| binlog.000450 | 1611 | Update_rows | 1 | 1669 | table_id: 99 flags: STMT_END_F |
| binlog.000450 | 1669 | Xid | 1 | 1700 | COMMIT /* xid=271 */ |
| binlog.000450 | 1700 | Anonymous_Gtid | 1 | 1779 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000450 | 1779 | Query | 1 | 1857 | BEGIN |
| binlog.000450 | 1857 | Table_map | 1 | 1923 | table_id: 99 (test434.students) |
| binlog.000450 | 1923 | Delete_rows | 1 | 1993 | table_id: 99 flags: STMT_END_F |
| binlog.000450 | 1993 | Xid | 1 | 2024 | COMMIT /* xid=293 */ |
+---------------+------+----------------+-----------+-------------+--------------------------------------+
32 rows in set (0.01 sec)
可以先看Event_type,三个Write_rows,一个Update_rows,一个Delete_rows,就是对应我们刚刚的更新SQL,BEGIN到COMMIT是一个完整的事件,我们基于Pos去恢复
基于Pos恢复
如果我们想恢复插入的三条数据,可以找到
| binlog.000450 | 556 | Query | 1 | 634 | BEGIN |
......
| binlog.000450 | 1379 | Anonymous_Gtid | 1 | 1458 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
mysqlbinlog --no-defaults --start-position=556 --stop-position=1379 --database=test434 D:\Mysql\mysql-8.0.23-winx64\Data\binlog.000450 | mysql -uroot -p123456 -v test434
--no-defaults # 解决字符集冲突
--start-position # 开始位置
--stop-position # 停止位置
--database # 数据库名称
D:\Mysql\mysql-8.0.23-winx64\Data\binlog.000450 # binlog日志文件
mysql -u用户名 -p密码 # mysql用户名和密码输入
恢复后查询可以看到被删除的数据
删除binlog日志
PURGE {MASTER | BINARY} LOGS TO '指定日志文件名' # 删除该指定日志文件名之前的所有文件
PURGE {MASTER | BINARY} LOGS BEFORE '指定日期' # 删除指定日期
mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000434 | 32034 | No |
| binlog.000435 | 521 | No |
| binlog.000436 | 7719 | No |
| binlog.000437 | 138018953 | No |
| binlog.000438 | 615 | No |
| binlog.000439 | 179 | No |
| binlog.000440 | 507565335 | No |
| binlog.000441 | 179 | No |
| binlog.000442 | 63289288 | No |
| binlog.000443 | 1459 | No |
| binlog.000444 | 12522 | No |
| binlog.000445 | 3493 | No |
| binlog.000446 | 179 | No |
| binlog.000447 | 1193 | No |
| binlog.000448 | 519 | No |
| binlog.000449 | 200 | No |
| binlog.000450 | 2024 | No |
+---------------+-----------+-----------+
17 rows in set (0.44 sec)
删除450之前的
mysql> purge master logs to 'binlog.000450';
Query OK, 0 rows affected (0.20 sec)
mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000450 | 2951 | No |
+---------------+-----------+-----------+
1 row in set (0.00 sec)
日期删除
# 先打开文件查看日期
mysqlbinlog --no-defaults -v "D:\Mysql\mysql-8.0.23-winx64\Data\binlog.000450"
# 然后删除
PURGE {MASTER | BINARY} LOGS BEFORE '指定日期' # 删除指定日期
其他场景
二进制日志可以通过数据库的 全量备份
和二进制日志中保存的 增量信息
,完成数据库的 无损失恢复
。但是,如果遇到数据量大、数据库和数据表很多(比如分库分表的应用)的场景,用二进制日志进行数据恢复,是很有挑战性的,因为起止位置不容易管理
。在这种情况下,一个有效的解决办法是 配置主从数据库服务器
,甚至是 一主多从
的架构,把二进制日志文件的内容通过中继日志,同步到从数据库服务器中,这样就可以有效避免数据库故障导致的数据异常等问题。
二进制日志(binlog)写入策略
binlog的写入时机也非常简单,事务执行过程中,先把日志写到 binlog cache
,事务提交的时候,再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开,无论这个事务多大,也要确保一次性写入,所以系统会给每个线程分配一个块内存作为binlog cache。
- write和fsync的时机,可以由参数 sync_binlog 控制,
默认是 0
。为0的时候,表示每次提交事务都只write
,由系统自行判断什么时候执行fsync。虽然性能得到提升,但是机器宕机,page cache里面的binglog 会丢失。 - 设置为 1 ,表示每次提交事务都会执行write和fsync,就如同redo log 刷盘流程差不多一样(redo刷盘都会存在一个后台线程默认行为)。
- 可以设置为N(N>1),表示每次提交事务都write,但累积N个事务后才fsync。
三个参数共同点都是事务提交时才write
binlog与redolog对比
- redo log 它是 物理日志 ,记录内容是“在某个数据页上做了什么修改”,属于 InnoDB 存储引擎层产生
的。 - 而 binlog 是 逻辑日志 ,记录内容是语句的原始逻辑,类似于“给 ID=2 这一行的 c 字段加 1”,属于
MySQL Server 层。 - redo log 刷盘策略不同,以基本的事务为单位,redo log在事务执行过程中可以不断写入,而binlog只有在提交事务时才写入,所以redo log与binlog的
写入时机
不一样。
redo的刷盘方式是有一个后台线程不断刷盘,即使在事务执行过程中,而binlog只会在事务提交时才会写入page cache
两阶段提交
问题产生
结果
主机和从备机数据不一致
为了解决两份日志之间的逻辑一致问题,InnoDB存储引擎使用两阶段提交方案。
写入binlog异常
redo log处于prepare阶段,检查对应的binlog日志,没有则回滚
redo log设置commit阶段发生异常
并不会回滚事务,它会执行上图框住的逻辑,虽然redo log是处于prepare阶段,但是能通过事务id找到对应的binlog日志,所以MySQL认为是完整的,就会提交事务恢复数据。
五、中继日志
中继日志只在主从服务器架构的从服务器上存在
。从服务器为了与主服务器保持一致,要从主服务器读取二进制日志的内容,并且把读取到的信息写入 本地的日志文件
中,这个从服务器本地的日志文件就叫中继日志
。然后,从服务器读取中继日志,并根据中继日志的内容对从服务器的数据进行更新,完成主从服务器的 数据同步
。搭建好主从服务器之后,中继日志默认会保存在从服务器的数据目录下。文件名的格式是: 从服务器名 -relay-bin.序号 。中继日志还有一个索引文件: 从服务器名 -relaybin.index ,用来定位当前正在使用的中继日志。