MySQL常用日志
2022/05/15
MySQL
> 视频教程：[尚硅谷_MySQL](http://www.atguigu.com/download_detail.shtml?v=349) 官方文档：[5.4 MySQL Server Logs](https://dev.mysql.com/doc/refman/8.0/en/server-logs.html) # 简介 > 分类 日志类型 | 写入日志的信息 --- | --- 二进制日志 | 更改数据的语句（也用于复制） 慢查询日志 | 执行时间超过 `long_query_time` 的查询 通用查询日志 | 记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令 错误日志 | 启动、运行或停止 `MySQL` 服务时遇到的问题 中继日志 | 从复制源服务器接收的数据更改 数据定义语句日志 | 记录数据定义语句执行的元数据操作 > 弊端 - 降低 `MySQL` 数据库的性能 。 - 占用磁盘空间 。 # 慢查询日志 详见：[MySQL性能分析工具（慢查询日志与EXPLAIN关键字）](https://maxqiu.com/article/detail/137) # 通用查询日志 ## 简介 通用查询日志用来记录用户的所有操作，包括启动和关闭 `MySQL` 服务、所有用户的连接开始时间和截止时间、发给 `MySQL` 数据库服务器的所有 `SQL` 指令等。当数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助准确定位问题。 ## 查询状态 默认情况下，是关闭的。建议找问题时再开启 - 是否开启：`SHOW VARIABLES LIKE 'general_log';` - 文件位置：`SHOW VARIABLES LIKE 'general_log_file';` ## 开启 > 临时（仅可全局开启） - 开启：`SET GLOBAL general_log = 'ON';` - 文件：`SET GLOBAL general_log_file = 'xxx'` > 永久 修改 `MySQL` 配置文件，在 `mysqld` 下面添加如下配置，修改完成后重启服务器 ``` [mysqld] # 开启通用查询日志（默认关闭，不建议开启） general_log=ON # 通用查询日志位置（默认 datadir 目录下） general_log_file=C:\development\mysql\log\log-general.log ``` ## 关闭 > 临时（仅可全局开启） ``` SET GLOBAL general_log = 'OFF'; ``` > 永久 修改MySQL配置文件，去除开启的配置，或者设置为 `OFF` ，修改完成后重启服务器 ``` [mysqld] # 关闭慢查询日志 general_log=OFF ``` ## 刷新日志 1. 重命名或删除源文件 2. 以下方式三选一 1. 执行以下命令并输入密码重新生成文件（刷新全部日志）：`mysqladmin -uroot -p flush-logs` 2. 重启服务器 3. 当前会话内执行脚本 1. 仅刷新通用日志：`FLUSH GENERAL LOGS` 2. 刷新全部日志：`FLUSH LOGS` # 错误日志 ## 简介 在 `MySQL` 数据库中，错误日志功能是 **默认开启** 的。而且，错误日志 **无法关闭** 。 ## 查询配置 文件位置：`SHOW VARIABLES LIKE 'log_error';` ## 修改位置 > 临时 不支持临时修改，仅可以在配置文件中修改 > 永久 ``` [mysqld] # 错误日志存放文件（默认 datadir 目录下） log_error=C:\development\mysql\log\log_error.log ``` ## 刷新日志 1. 重命名或删除源文件 2. 以下方式三选一 1. 执行以下命令并输入密码重新生成文件（刷新全部日志）：`mysqladmin -uroot -p flush-logs` 2. 重启服务器 3. 当前会话内执行脚本 1. 仅刷新错误日志：`FLUSH ERROR LOGS` 2. 刷新全部日志：`FLUSH LOGS` # 二进制日志 ## 简介 `binlog` 是 `MySQL` 中比较重要的日志，在日常开发及运维过程中，经常会遇到。 `binlog` 即 `binary log` ，二进制日志文件，也叫作变更日志（ `update log` ）。它记录了数据库所有执行的 `DDL` 和 `DML` 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句 `select` 、 `show` 等）。 `binlog` 主要应用场景： - **数据恢复** - **数据复制** ## 配置 ### 查询配置 - 全局会话是否开启：`SHOW VARIABLES LIKE 'log_bin';` - 当前会话是否开启：`SHOW VARIABLES LIKE 'sql_log_bin';` - 文件名称：`SHOW VARIABLES LIKE 'log_bin_basename';` - 文件索引：`SHOW VARIABLES LIKE 'log_bin_index';` - 文件格式：`SHOW VARIABLES LIKE 'binlog_format';` - 文件最大大小（单位：`B`，默认：`1GB`）：`SHOW VARIABLES LIKE 'max_binlog_size';` - 文件过期时间（单位：秒，默认：`30天`）：`SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'` ### 开启 该配置不建议临时修改，应该在安装 `MySQL` 时配置好 修改 `MySQL` 配置文件，在 `mysqld` 下面添加如下配置，修改完成后重启服务器 ``` [mysqld] # 文件位置（默认 datadir 目录下，不建议和数据文件放在同一目录或磁盘） log_bin=C:\development\mysql\log\log-bin # 文件格式 binlog_format=MIXED # 单文件最大大小（默认1GB） max_binlog_size=1GB # 文件过期时间（默认30天） binlog_expire_logs_seconds=2592000 ``` ### 关闭 > 临时（仅支持当前会话） ``` SET SESSION sql_log_bin = off; ``` > 永久 修改 `MySQL` 配置文件，在 `mysqld` 下面添加如下配置，并删除 `log_bin` 配置，修改完成后重启服务器 ``` skip-log-bin ``` ## 日志格式 - `STATEMENT模式` ：基于 `SQL` 语句的复制 `(statement-based replication, SBR)`<br>每一条会修改数据的sql语句会记录到 `binlog` 中。这是默认的 `binlog` 格式。<br>`binlog_format=STATEMENT` - 优点： - 历史悠久，技术成熟 - 不需要记录每一行的变化，减少了 `binlog` 日志量，节约了 `IO` ，提高性能。 - `binlog` 中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况 - `binlog` 可以用于实时的还原，而不仅仅用于复制 - 主从版本可以不一样，从服务器版本可以比主服务器版本高 - 缺点： - 不是所有的 `UPDATE` 语句都能被复制，尤其是包含不确定操作的时候 - 使用以下函数的语句也无法被复制： `LOAD_FILE()` 、 `UUID()` 、 `USER()` 、 `FOUND_ROWS()` 、 `SYSDATE()` （除非启动时启用了 `--sysdate-is-now` 选项） - `INSERT ... SELECT` 会产生比 `RBR` 更多的行级锁 - 复制需要进行全表扫描（ `WHERE` 语句中没有使用到索引）的 `UPDATE` 时，需要比 `RBR` 请求更多的行级锁 - 对于有 `AUTO_INCREMENT` 字段的 `InnoDB` 表而言， `INSERT` 语句会阻塞其他 `INSERT` 语句 - 对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 `RBR` 模式下，只会对那个发生变化的记录产生影响 - 执行复杂语句如果出错的话，会消耗更多资源 - 数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错 - `ROW模式` ：基于行的复制 `(row-based replication, RBR)`<br>`5.1.5` 版本的 `MySQL` 才开始支持，不记录每条 `sql` 语句的上下文信息，仅记录哪条数据被修改了，修改成什么样了。<br>`binlog_format=ROW` - 优点： - 任何情况都可以被复制，这对复制来说是最安全可靠的。（比如：不会出现某些特定情况下的存储过程、函数、触发器的调用和触发无法被正确复制的问题） - 多数情况下，从服务器上的表如果有主键的话，复制就会快了很多 - 复制以下几种语句时的行锁更少：`INSERT ... SELECT` 、包含 `AUTO_INCREMENT` 字段的 `INSERT` 、没有附带条件或者并没有修改很多记录的 `UPDATE` 或 `DELETE` 语句 - 执行 `INSERT` ， `UPDATE` ， `DELETE` 语句时锁更少 - 从服务器上采用多线程来执行复制成为可能 - 缺点： - `binlog` 大了很多 - 复杂的回滚时 `binlog` 中会包含大量的数据 - 主服务器上执行 `UPDATE` 语句时，所有发生变化的记录都会写到 `binlog` 中，而 `SBR` 只会写一次，这会导致频繁发生 `binlog` 的并发写问题 - 无法从 `binlog` 中看到都复制了些什么语句 - `MIXED模式`：混合模式复制 `(mixed-based replication, MBR)`<br>从 `5.1.8` 版本开始，`MySQL` 提供了 `Mixed` 格式，实际上就是 `Statement` 与 `Row` 的结合。<br>`binlog_format=MIXED` - 在 `Mixed` 模式下，一般的语句修改使用 `statment` 格式保存 `binlog` 。如一些函数，`statement` 无法完成主从复制的操作，则采用 `row` 格式保存 `binlog` - `MySQL` 会根据执行的每一条具体的 `sql` 语句来区分对待记录的日志形式，也就是在 `Statement` 和 `Row` 之间选择一种。 ## 查看文件 ### 文件命名规则 `MySQL` 创建二进制日志文件时，先创建一个以 `filename` 为名称、以 `.index` 为后缀的文件，再创建一个以 `filename` 为名称、以 `.000001` 为后缀的文件。 `MySQL` 服务重新启动一次，或者超过了 `max_binlog_size` 的上线，以 `.000001` 为后缀的文件就会增加一个，并且后缀名按 `1` 递增。 ### 查看当前的二进制日志文件列表及大小 ``` mysql> SHOW BINARY LOGS; +----------------+-----------+-----------+ | Log_name | File_size | Encrypted | +----------------+-----------+-----------+ | log-bin.000001 | 180 | No | | log-bin.000002 | 180 | No | +----------------+-----------+-----------+ 7 rows in set (0.00 sec) ``` ### `mysqlbinlog` 工具查看文件详细 常用参数如下 - `--help`：查看帮助 - `-v`：显示伪 `SQL` - `--base64-output=DECODE-ROWS`：解密文件 注：该命令在命令行下执行 ``` C:\Windows\System32>mysqlbinlog -v --base64-output=DECODE-ROWS C:\development\mysql\log\log-bin.000001 # The proper term is pseudo_replica_mode, but we use this compatibility alias # to make the statement usable on server versions 8.0.24 and older. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #220515 13:24:38 server id 1 end_log_pos 126 CRC32 0x95b8d1bb Start: binlog v 4, server v 8.0.28 created 220515 13:24:38 at startup ROLLBACK/*!*/; # at 126 #220515 13:24:38 server id 1 end_log_pos 157 CRC32 0xfd8236f2 Previous-GTIDs # [empty] # at 157 #220515 13:24:58 server id 1 end_log_pos 236 CRC32 0x7f4ba40a Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1652592298452586 immediate_commit_timestamp=1652592298452586 transaction_length=333 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1652592298452586 (2022-05-15 13:24:58.452586 中国标准时间) # immediate_commit_timestamp=1652592298452586 (2022-05-15 13:24:58.452586 中国标准时间) /*!80001 SET @@session.original_commit_timestamp=1652592298452586*//*!*/; /*!80014 SET @@session.original_server_version=80028*//*!*/; /*!80014 SET @@session.immediate_server_version=80028*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 236 #220515 13:24:58 server id 1 end_log_pos 325 CRC32 0x9751e61b Query thread_id=8 exec_time=0 error_code=0 SET TIMESTAMP=1652592298/*!*/; SET @@session.pseudo_thread_id=8/*!*/; 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=45/*!*/; SET @@session.time_zone='SYSTEM'/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; BEGIN /*!*/; # at 325 #220515 13:24:58 server id 1 end_log_pos 400 CRC32 0x70d5d527 Table_map: `atguigudb1`.`student_info` mapped to number 81 # at 400 #220515 13:24:58 server id 1 end_log_pos 459 CRC32 0x117cd56c Write_rows: table id 81 flags: STMT_END_F ### INSERT INTO `atguigudb1`.`student_info` ### SET ### @1=2 ### @2=2 ### @3='2' ### @4=2 ### @5=2 ### @6='2022-05-15 13:24:58' # at 459 #220515 13:24:58 server id 1 end_log_pos 490 CRC32 0x357f024e Xid = 5 COMMIT/*!*/; # at 490 #220515 13:25:14 server id 1 end_log_pos 535 CRC32 0xce54888b Rotate to log-bin.000002 pos: 4 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*/; ``` ### `SHOW BINLOG EVENTS` 工具查看文件详细 命令格式如下： `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` 连接内执行 ``` mysql> SHOW BINLOG EVENTS; +----------------+-----+----------------+-----------+-------------+----------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +----------------+-----+----------------+-----------+-------------+----------------------------------------+ | log-bin.000001 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.28, Binlog ver: 4 | | log-bin.000001 | 126 | Previous_gtids | 1 | 157 | | | log-bin.000001 | 157 | Anonymous_Gtid | 1 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | log-bin.000001 | 236 | Query | 1 | 325 | BEGIN | | log-bin.000001 | 325 | Table_map | 1 | 400 | table_id: 81 (atguigudb1.student_info) | | log-bin.000001 | 400 | Write_rows | 1 | 459 | table_id: 81 flags: STMT_END_F | | log-bin.000001 | 459 | Xid | 1 | 490 | COMMIT /* xid=5 */ | | log-bin.000001 | 490 | Rotate | 1 | 535 | log-bin.000002;pos=4 | +----------------+-----+----------------+-----------+-------------+----------------------------------------+ 8 rows in set (0.00 sec) ``` ## 清理日志文件 `MySQL` 二进制文件可以配置自动删除，同时 `MySQL` 也提供了安全的手动删除二进制文件的方法。 > 语法：（ `MASTER` 和 `BINARY` 意思相同） - `PURGE {MASTER | BINARY} LOGS` ：删除指定部分的二进制日志文件 - `PURGE {MASTER | BINARY} LOGS TO '指定日志文件名'` ：指定文件名之前 - `PURGE {MASTER | BINARY} LOGS BEFORE '指定日期'` ： 指定日期之前 - `RESET MASTER` ：删除所有的二进制日志文件，并重新计算序号 > 举例 - 删除指定序号之前的日志：`PURGE MASTER LOGS TO 'log-bin.000002';` - 删除指定日期之前的日志（不包含当前正常使用的文件）：`PURGE MASTER LOGS BEFORE '20220516';` # 中继日志 ## 简介 中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入 本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。 搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。 文件名的格式是：`从服务器名-relay-bin.序号`。中继日志还有一个索引文件：`从服务器名-relaybin.index` ，用来定位当前正在使用的中继日志。 ## 查看文件 同二进制日志文件的查看 ## 修改位置 该配置不建议临时修改，应该在安装 `MySQL` 时配置好 修改 `MySQL` 配置文件，在 `mysqld` 下面添加如下配置，修改完成后重启服务器 ``` [mysqld] # 文件位置（默认 datadir 目录下，不建议和数据文件放在同一目录或磁盘） relay_log=C:\development\mysql\log\log-relay ```
5