-
Mysql其他日志
日志类型
MySQL分为
二进制日志、错误日志、通用查询日志和慢查询日志，这也是常用的4种。MySQL 8又新增：中继日志和数据定义语句日志。使用这些日志文件，可以查看MySQL内部发生的事情。
这6类日志分别为：- 慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
- 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
- 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。
- 二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
- 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
- 数据定义语句日志：记录数据定义语句执行的元数据操作。
日志弊端
- 日志功能会
降低MySQL数据库的性能。 - 日志会
占用大量的磁盘空间。
一、慢查询日志
前面已了解
二、通用查询日志（general log）
通用查询日志用来
记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。查看当前日志状态
SHOW VARIABLES LIKE '%general%';- 1
默认是关闭的
开启日志
永久开启
修改my.cnf或者my.ini配置文件来设置。在[mysqld]组下加入log选项，并重启MySQL服务。如果不指定目录和文件名，通用查询日志将默认存储在MySQL数据目录中的hostname.log文件中，hostname表示主机名。格式如下：[mysqld] general_log=ON general_log_file=[path[filename]] #日志文件所在目录路径，filename为日志文件名- 1
- 2
- 3
临时开启
SET GLOBAL general_log=on; # 开启通用查询日志 SET GLOBAL general_log_file='path/filename'; # 设置日志文件保存位置 SET GLOBAL general_log=off; # 关闭通用查询日志- 1
- 2
- 3
- 4
查看日志
文本形式可以直接文本编辑器打开
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
三、错误日志(error log)
在MySQL数据库中，错误日志功能是
默认开启的。而且，错误日志无法被禁止。默认情况下，错误日志存储在MySQL数据库的数据文件夹下，名称默认为
mysqld.log （Linux系统）或hostname.err （mac系统）。如果需要制定文件名，则需要在my.cnf或者my.ini中做如下配置：[mysqld] log-error=[path/[filename]] #path为日志文件所在的目录路径，filename为日志文件名- 1
- 2
查看日志
SHOW VARIABLES LIKE 'log_err%';- 1
进到相关路径找到相关文件，即可用文本编辑器打开
四、二进制日志(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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 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文件大小，并不能精确把控- 1
- 2
- 3
- 4
- 5
如果想改变日志文件的目录和名称，
linux系统新建的文件夹需要使用mysql用户，可以对my.cnf或my.ini中的log_bin参数修改如下：[mysqld] log-bin="/var/lib/mysql/binlog/pang-bin"- 1
- 2
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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
所有对数据库的修改都会记录在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*/;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
上面这种办法读取出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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
这里一个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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
如果我们不小心删除了数据，这些都是已提交的事务，无法回滚，可以用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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
可以先看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' |- 1
- 2
- 3
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- 1
--no-defaults # 解决字符集冲突 --start-position # 开始位置 --stop-position # 停止位置 --database # 数据库名称 D:\Mysql\mysql-8.0.23-winx64\Data\binlog.000450 # binlog日志文件 mysql -u用户名 -p密码 # mysql用户名和密码输入- 1
- 2
- 3
- 4
- 5
- 6
恢复后查询可以看到被删除的数据删除binlog日志
PURGE {MASTER | BINARY} LOGS TO '指定日志文件名' # 删除该指定日志文件名之前的所有文件 PURGE {MASTER | BINARY} LOGS BEFORE '指定日期' # 删除指定日期- 1
- 2
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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
删除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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
日期删除
# 先打开文件查看日期 mysqlbinlog --no-defaults -v "D:\Mysql\mysql-8.0.23-winx64\Data\binlog.000450" # 然后删除 PURGE {MASTER | BINARY} LOGS BEFORE '指定日期' # 删除指定日期- 1
- 2
- 3
- 4
其他场景
二进制日志可以通过数据库的
全量备份和二进制日志中保存的增量信息，完成数据库的无损失恢复。但是，如果遇到数据量大、数据库和数据表很多（比如分库分表的应用）的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止位置不容易管理。在这种情况下，一个有效的解决办法是配置主从数据库服务器，甚至是一主多从的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常等问题。二进制日志(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。
三个参数共同点都是事务提交时才writebinlog与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 ，用来定位当前正在使用的中继日志。 -
相关阅读:
中序遍历迭代算法（非递归算法）
prisma使用mongodb副本集群报错引发的一些列问题
题目 1063: 二级C语言-统计字符
JAVA中JPanel类方法汇总
负载均衡nginxSLB开通管理流程
园区网典型配置案例
Rome链分析
c++入门99题11-20
单元测试与自测
python绘制直方图
- 原文地址：https://blog.csdn.net/pmc0_0/article/details/126562599
