MySQL【日志管理】

最新推荐文章于 2024-08-26 14:45:45 发布

散落在麦田的星

最新推荐文章于 2024-08-26 14:45:45 发布

阅读量685

点赞数 15

分类专栏： Linux 文章标签： mysql adb 数据库 linux 云计算

本文链接：https://blog.csdn.net/qq_43606536/article/details/140655884

版权

Linux 专栏收录该内容

68 篇文章 0 订阅

订阅专栏

日志，就是一种将行为动作记录到一个地方，这个地方可以是文件，文本等可存储的载体。Mysql日志就是记录整个mysql从启动、运行、到结束的整个生命周期下的行为。

官网链接：MySQL :: MySQL 5.7 Reference Manual :: 5.4 MySQL Server Logs

MySQL 5.7日志分类

错误日志：

默认开启，无法关闭。错误日志包含mysqld启动和关闭时间的记录。它还包含诊断消息，如服务器启动和关闭期间以及服务器运行时发生的错误、警告和注释。

通用查询日志：

主要是记录所有关于服务器的活动，包括所有客户端的连接和所有执行的SQL语句，mysql的通用日志是关闭的，如果需要设置可以通过 general_log 选项开启通用日志。通用查询日志记录了大量的信息，它可能会对数据库服务器的性能产生显著影响，特别在高负载的生产环境中。而且还会占用大量磁盘空间和磁盘 I/O 问题，所以一般调试的时候才会开启通用日志。

二进制日志(bin log)：【5.7默认关闭；8.0默认开启】

一种特殊类型的日志文件，它记录了所有修改数据库内容【增、删、改】的操作，比如 INSERT、UPDATE 和 DELETE 语句，以及数据定义语言（DDL）语句，如 CREATE TABLE、ALTER TABLE 等。MySQL 的二进制日志并不是由纯粹的0和1序列组成的简单文件。实际上，它是一种特殊的二进制格式文件，这种格式旨在高效地存储和记录数据库更改操作。这种专门为 MySQL 优化的格式，既保证了存储和传输的高效性，也便于使用相应的工具进行内容查看和分析。例如mysqlbinlog这个工具就可以查看二进制文件的内容。MySQL的二进制日志不像通用日志、慢查询日志一样耗费性能，二进制日志对性能的影响通常较小。一般在主从复制的架构中才需要开启二进制日志。

中继日志(Relay log)：

在主从复制环境中使用的一种特殊日志文件，可以理解为二进制日志换一个名字，在主从复制环境中，中继日志是存放在从服务器上的。【在 MySQL 的主从复制环境中，中继日志（Relay Log）的作用是暂存从主服务器接收的二进制日志（Binary Log）事件，并在从服务器上执行这些事件以实现数据同步。一旦这些事件在从服务器上成功执行并且数据同步完成，相应的中继日志文件就不再需要了，因此会被自动删除。】这是从服务器自动进行的，以确保不会因为长时间累积大量的中继日志而占用过多的磁盘空间。

慢查询日志：

记录了执行时间超过特定阈值的所有查询，可以根据这些信息监控和诊断数据库性能问题。一般情况下是需要调试时候才开启慢查询日志，如果长期开启可能会对数据库性能产生一定的影响，且在大型或高负载的环境中日志文件可能会快速增长，占用过多磁盘空间。

重做日志（redo log）：

InnoDB存储引擎特有的日志系统，用于保证事务的持久性和恢复能力。重做日志也是以二进制格式存储存储的，并且如果使用的引擎是innodb，默认是开启了重做日志。

回滚日志（undo log）：

mysql中比较重要的事务日志之一，顾名思义，undo log是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。

注意：

日志功能会降低MVSOL数据库的性能。例如，在查询非常频繁的MSOL数据库系统中，如果开启了通用查询日志和慢查询日志，MySQL数据库会花费很多时间记录日志。I

同时日志会占用大量的磁盘空间。对于用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间设置比数据库文件需要的存储空间还要大。所以不必要的日志文件尽量就不开启。

1.错误日志：

查看错误日志文件路径

1.my.cnf文件中：指定mysqld记录的错误日志文件:log-error=错误文件绝对路径
log-error=/var/log/mysqld.log

2.mysql中查看：
show variables like 'log_error';

2.通用查询日志（了解）

通用查询日志可以帮助我们了解操作发生的具体时间和操作的细节，对找出异常发生的原因极其关键。

#mysql中查看：
show variables like '%general%';

# 在配置文件中修改：
vim /etc/my.cnf
# 加入：
general_log=ON                               # ON表示开启 OFF表示关闭
general_log_file=/var/log/mysql/general.log  #通用日志的存放位置和文件名

# 创建目录和日志文件
mkdir /var/log/mysql
touch /var/log/mysql/general.log
# 修改权限和属主属组
chmod -R 755  /var/log/mysql
chown -R mysql.mysql /var/log/mysql
# 重启服务：
systemctl restart mysqld

# 再次查看通用日志
show variables like '%general%';

#注意：当配置文件无法生效时，删除‘/var/log/mysql/general.log’文件，重启

3.二进制日志【重要】：增删改

在MySQL 5.7中二进制(bin log)默认是关闭状态，MySQL 8.0默认开启状态，可以通过添加my.cnf配置参数开启二进制日志。binlog可以说是MySQL中比较重要的日志了，在日常开发及运维过程中，经常会遇到。

binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的 DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。

binlog主要应用场景：

一是用于数据恢复
二是用于数据复制

# 查看二进制文件是否开启：
select @@global.log_bin;
+------------------+
| @@global.log_bin |
+------------------+
|                0 |  # 0未开启
+------------------+

# 或者：
show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

开启二进制日志：

1.打开配置文件
vim /etc/my.cnf

2.添加：
log-bin=/var/log/mysql/binlog   #开启二进制日志，binlog为二进制日志文件前缀
server-id=1                     #AB复制的时候使用，为了防止相互复制，会设置一个ID，来标识谁产生的日志

3.重启：
systemctl restart mysqld

4.查看：新建的文件夹需要更改为mysql属主和属组，且需要具有755权限
ll /var/log/mysql/

查看二进制日志：

当MySQL创建二进制日志文件时，先创建一个以“binlog”为名称、以“.index”为后缀的文件，再创建一个以“binlog”为名称、以“.000001”为后缀的文件。 MySQL服务重新启动一次，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同；

如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件。查看当前的二进制日志文件列表及大小。指令如下：

 show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       154 |
+---------------+-----------+

# 在命令终端：输入：
mysqlbinlog -v /var/log/mysql/binlog.000001

# 或者：
mysqlbinlog  -v --base64-output=decode-rows "/var/log/mysql/binlog.000001"

二进制日志三种格式：

# 查看二进制日志格式
show variables like 'binlog_format';

STATEMENT 格式（语句模式，出现在 MySQL 5.1 之前）：

在这种格式下，binlog 记录的是执行的 SQL 语句的文本。

优点：日志文件通常较小，复制效率较高。

缺点：在某些情况下，由于数据库环境的差异（如表结构、字符集等），在从服务器上重放这些 SQL 语句可能会导致不一致的结果。例如，获取当前时间的函数或存储过程等，可能会导致数据不一致。

# 打开配置文件
vim /etc/my.cnf
# 添加：
[mysqld]
binlog_format = STATEMENT

ROW 格式（行模式，诞生于 MySQL 5.1）：

在这种格式下，binlog 记录的是每一行数据更改的具体内容。

优点：能够精确地记录数据的变化，避免了 STATEMENT 格式中的环境依赖问题，提供了更强的一致性保证。

缺点：日志文件可能会比 STATEMENT 格式大，因为记录了每一行的详细变化。此外，ROW 格式的日志在进行大量数据更新时可能会导致更高的 I/O 开销。

# 打开配置文件
vim /etc/my.cnf
# 添加：
[mysqld]
binlog_format = ROW

MIXED 格式（混合模式）：【了解】

在这种格式下，binlog 可以根据具体的 SQL 语句和操作自动选择使用 STATEMENT 或 ROW 格式。

优点：结合了 STATEMENT 和 ROW 格式的优点，能够在保证一致性的同时尽可能地优化日志大小和复制性能。

缺点：由于混合使用了两种格式，可能需要更复杂的管理和监控。在某些特定情况下，MIXED 格式可能无法达到最优的性能或一致性。

# 打开配置文件
vim /etc/my.cnf
# 添加：
[mysqld]
binlog_format = MIXED

在Mysql运行时修改：

修改二进制日志格式需要具有超级用户权限（SUPER 权限），并且只对新的会话生效，已有会话不受影响。

SET GLOBAL binlog_format = 'STATEMENT';

MySQL 5.7二进制日常管理事项

- 每次重启MySQL实例，都会重新生成新的二进制文件

- 执行flush logs，会重新生成新的二进制日志文件

- 执行reset master，会删除所有的binlog日志文件。危险操作

- 可以通过 purge binary logs to 'binlog.000002' 删除binlog.000002之前的二进制文件。

- 可以通过set sql_log_bin=0关闭当前会话的执行操作记录至二进制日志文件，需要具有 SUPER 权限。在复制环境中使用 sql_log_bin = 0 时要小心，以避免主从数据不一致。

# 查看sql_log_bin状态信息
SHOW VARIABLES LIKE 'SQL_LOG_BIN';

4.慢查询日志：

慢查询日志（Slow Query Log）是 MySQL 中用于记录执行时间超过指定阈值的 SQL 查询的日志文件。慢查询日志对于诊断和优化数据库性能非常有用。

启动慢查询日志：

# 修改MySQL配置文件
vim /etc/my.cnf
# 写入：
slow_query_log = 1                                     # 启用慢查询日志
slow_query_log_file = /var/log/mysql/slow_query.log    # 慢查询日志文件路径
long_query_time = 3                                    # 查询超过2秒的SQL语句将被记录

#重启：
systemctl restart mysqld

查看慢查询日志参数：

SHOW VARIABLES LIKE '%slow_query%';

验证：

# 测试SQL语句
select sleep(6);

# 查看慢查询日志文件
cat /var/log/mysql/slow_query.log

5.中继日志【重要】

中继日志（Relay Log）是从服务器（Slave）用于记录从主服务器（Master）接收到的二进制日志事件的日志文件。这些事件在从服务器上被重新执行以保持主从数据的一致性。中继日志的主要作用是存储从主服务器复制过来的二进制日志事件。在复制过程中，从服务器上的 I/O 线程从主服务器读取二进制日志事件，并将其写入中继日志文件。从服务器上的 SQL 线程读取中继日志，并执行其中的事件，从而更新从服务器上的数据。

扩展：

UNIX Socket连接方式其实不是一个网络协议，所以只能在MySQL客户端和数据库实例在同一台服务器上的情况下使用。本地进程间通信的一种方式

# 通过socket方式登录
# 查看sock的存放路径
cat /etc/my.cnf | grep sock
socket=/var/lib/mysql/mysql.sock
#
mysql -uroot -p'QianFeng@123!' -S /var/lib/mysql/mysql.sock