MySQL日志功能

日志

MySQL 的日志主要有以下几种类型，它们分别记录了数据库操作的不同层面的信息。了解它们的作用和用途，可以帮助我们更好地进行数据库的管理和调优。

1. 错误日志（Error Log）

错误日志记录了 MySQL 服务器启动、运行和关闭过程中的诊断信息。它记录了如下信息：

• 启动和关闭 MySQL 服务器时的信息
• 服务器运行时的错误和异常信息
• InnoDB 存储引擎的错误和诊断信息

默认情况下，错误日志的输出位置为数据目录下的 hostname.err 文件，其中 hostname 为服务器主机名。

2. 查询日志（General Query Log）

查询日志记录了 MySQL 服务器收到的所有客户端请求，包括连接、断开连接、查询和其他命令。通常用于审计（例如，跟踪潜在的安全问题）。

由于查询日志记录了大量信息，所以它可能会导致性能下降。因此，在生产环境中，通常不建议启用查询日志。

3. 慢查询日志（Slow Query Log）

慢查询日志记录了执行时间超过设定阈值的 SQL 查询。通过分析慢查询日志，可以找出导致数据库性能下降的慢查询，并对其进行优化。

启用慢查询日志需要设置 slow_query_log 配置项，并通过 long_query_time 配置项设定查询执行时间的阈值。

4. 二进制日志（Binary Log）

二进制日志记录了对数据库进行更改的所有 SQL 语句，如 INSERT、UPDATE 和 DELETE。它主要用于复制和数据恢复。

启用二进制日志需要设置 log_bin 配置项。注意，启用二进制日志会产生一定的性能开销。

5. 中继日志（Relay Log）

中继日志是 MySQL 复制过程中使用的一种日志，存储了从主服务器接收到的二进制日志事件。这些事件在从服务器上被执行之前，会被写入中继日志。

通常，中继日志在主从复制环境中自动启用，无需手动配置。

6. InnoDB 重做日志（InnoDB Redo Log）

InnoDB 存储引擎使用重做日志来确保事务的持久性和数据的完整性。重做日志记录了所有已提交事务对数据库所做的更改。

重做日志是循环使用的，由两个或多个固定大小的文件组成。InnoDB 重做日志的大小和数量可以通过 innodb_log_file_size 和 innodb_log_files_in_group 配置项进行设置。

错误日志

在Linux中，MySQL的错误日志记录了MySQL服务器在运行过程中发生的错误和警告消息。错误日志对于故障排除和问题分析非常重要。下面是对Linux中MySQL错误日志的详细解析：

1. 日志位置和命名：

   • 在Linux系统上，默认情况下，MySQL的错误日志位于MySQL数据目录下的hostname.err文件。
   • 数据目录的位置取决于MySQL的安装和配置，通常在/var/lib/mysql目录下。
   • 错误日志的文件名通常使用主机名（hostname）作为前缀。

2. 记录内容：

   • 错误日志记录了MySQL服务器的启动、关闭、连接和执行期间发生的错误和警告。
   • 记录的内容包括错误代码、错误描述、错误发生时间、错误来源（例如线程ID、用户）等信息。
   • 错误日志还可以包含警告信息、优化器消息、查询执行统计等其他有关MySQL服务器运行的相关信息。

3. 错误级别：

   • 错误日志根据错误的严重程度将消息分为不同的级别，如错误（ERROR）、警告（Warning）等。
   • 错误级别有助于确定错误的严重性和优先级。

4. 错误日志的配置：

   • MySQL的配置文件通常位于/etc/my.cnf或/etc/mysql/my.cnf，可以使用文本编辑器打开该文件进行配置。
   • 错误日志的配置信息存储在配置文件中的log_error选项中。
   • 可以通过编辑配置文件中的log_error行来更改错误日志的路径和文件名。

5. 日志旋转：

   • 为了控制错误日志的大小和保留历史记录，可以使用日志旋转机制来管理MySQL的错误日志。
   • 在Linux系统上，通常使用日志旋转工具（如logrotate）来处理日志文件的旋转和归档。
   • 日志旋转工具可以定期或在日志文件达到一定大小时，将当前的错误日志重命名为备份文件，并创建一个新的错误日志文件。

错误日志是MySQL服务器运行过程中非常有用的资源，可以帮助识别和解决问题。对于开发人员和管理员来说，定期查看和分析错误日志是维护MySQL服务器和保障数据的稳定性的重要任务。

配置

如果指定错误日志的路径，主要目的地的目录需要给mysql用户写的权限

在/etc/my.cnf修改

查询错误日志

查询日志

在Linux中，MySQL的查询日志（General Query Log）是一种日志类型，用于记录MySQL服务器接收到的所有客户端查询语句。查询日志对于调试和审计目的非常有用。下面是关于MySQL查询日志的详细解析：

1. 日志位置和命名：
   • 查询日志的位置和命名根据操作系统和MySQL的配置而定。
   • Linux系统上，通常位于MySQL数据目录下的hostname.log文件。
2. 记录内容：
   • 查询日志记录了客户端发送给MySQL服务器的所有查询语句。
   • 记录的内容包括查询语句本身、查询执行的时间戳、连接ID、客户端IP地址等信息。
   • 查询日志还可以包含其他有关查询执行的统计信息，如查询执行时间、扫描行数等。
3. 配置查询日志：
   • 查询日志的配置信息存储在MySQL的配置文件（如my.cnf或my.ini）中。
   • 可以通过配置文件中的general_log选项启用或禁用查询日志。
   • 可以通过配置文件中的general_log_file选项指定查询日志的路径和文件名。
4. 查询日志的影响：
   • 启用查询日志可能会对MySQL服务器的性能产生一定的影响。
   • 查询日志记录了所有的查询操作，包括SELECT、INSERT、UPDATE、DELETE等语句，因此可能会占用大量的磁盘空间。
   • 在高并发环境下，查询日志的开启可能会对磁盘IO和服务器性能产生较大的负载。
5. 注意事项：
   • 在生产环境中，启用查询日志应该谨慎，并根据实际需求进行配置。
   • 可以通过定期轮转查询日志文件、限制日志大小和定期清理旧日志等方式来控制日志文件的大小和数量。

配置

在mysql中配置

指定文件路径

general_log_file=/data/mysql/sanchuang_mysql_ge.log

默认存放在数据目录下，名字是主机名.log

临时开启

set global general_log = 1;

1是开启 0 是关闭

查看是否开启

select @@general_log;

配置文件修改

[mysqld]
socket=/data/mysql/mysql.sock
port = 3309
open_files_limit = 8192
#general log
general_log
general_log_file=/data/mysql/sanchuang_mysql_ge.log

慢日志

在Linux中，MySQL的慢查询日志（Slow Query Log）是一种日志类型，用于记录执行时间超过特定阈值的查询语句。慢查询日志对于性能优化和查询优化非常重要。下面是关于MySQL慢查询日志的详细解析：

1. 激活慢查询日志：

   • 在MySQL配置文件（如/etc/my.cnf）中，可以设置慢查询日志的相关配置参数。
   • slow_query_log：指示是否启用慢查询日志，设置为ON启用，设置为OFF禁用。
   • slow_query_log_file：指定慢查询日志文件的路径和文件名。
   • long_query_time：指定查询执行时间的阈值，超过该阈值的查询将被记录到慢查询日志中。
   • 在配置文件中进行设置后，需要重启MySQL服务才能使配置生效。

2. 记录内容：

   • 慢查询日志记录了执行时间超过阈值的查询语句，以及相关的执行统计信息。
   • 记录的内容包括查询语句、执行时间、查询时间戳、用户信息、访问的数据库和表名等。
   • 慢查询日志还可以包含查询执行的详细信息，如扫描的行数、使用的索引、执行计划等。

3. 查询优化和性能分析：

   • 慢查询日志是性能优化和查询优化的重要工具。
   • 通过分析慢查询日志，可以识别执行时间较长的查询，找到性能瓶颈和潜在的优化机会。
   • 可以使用工具（如mysqldumpslow、pt-query-digest等）来解析和分析慢查询日志，生成报告和统计数据。

4. 日志旋转：

   • 为了控制慢查询日志的大小和保留历史记录，可以使用日志旋转机制。
   • 日志旋转可以定期或在日志文件达到一定大小时，将当前的慢查询日志重命名为备份文件，并创建一个新的慢查询日志文件。
   • 旧的慢查询日志文件可以进行归档、分析和长期保存。

慢查询日志是MySQL性能调优的重要工具，可以帮助识别低效的查询和瓶颈，提供性能优化的线索。通过启用慢查询日志并对其进行分析，可以优化查询语句、索引设计和数据库结构，提升系统的性能和响应速度。

配置

查看默认设置

mysql> show variables like "%long_query%";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 10.000 |
+-----------------+----------+
1 row in set (0.00 sec)

默认的是10秒

在/etc/my.cnf配置文件进行修改

查看MySQL是否开启

mysql> show variables like "%slow_query%";
+---------------------+---------------------------------------+
| Variable_name       | Value                                 |
+---------------------+---------------------------------------+
| slow_query_log      | OFF                                   |
| slow_query_log_file | /data/mysql/zabbix-4-centos7-slow.log |
+---------------------+---------------------------------------+
2 rows in set (0.01 sec)

在配置文件里开启

[mysqld]
socket=/data/mysql/mysql.sock
port = 3309
open_files_limit = 8192
#binary log
log-bin
server-id=1
#general log
general_log
#slow query log
slow_query_log=1
long_query_time=0.01
#给慢日志设置一个时间标准，select查询耗时超过0.01秒就是算了
#快慢的标准是自己定的

二进制日志

MySQL的二进制日志（Binary Log）是MySQL数据库的核心日志之一，用于记录所有的数据库更改操作，包括数据的插入、更新和删除等操作（show、select不会被记入到binlog）。下面是对MySQL二进制日志的详细介绍：

1. 日志格式：

   • MySQL的二进制日志可以使用不同的日志格式进行记录，包括基于语句（Statement-based）的复制日志格式、基于行（Row-based）的复制日志格式和混合（Mixed）日志格式。
   • 基于语句的复制日志格式记录了执行的SQL语句，是最常见的日志格式。
   • 基于行的复制日志格式记录了每个修改的行的详细变化。
   • 混合日志格式根据具体情况自动选择使用基于语句或基于行的记录方式。

2. 数据同步和复制：

   • 二进制日志对于数据同步和复制非常重要。
   • 在主从复制架构中，主服务器将更改操作记录到二进制日志，并将二进制日志传输到从服务器进行重放和同步。
   • 从服务器读取主服务器的二进制日志，并将其应用于本地数据库，以保持数据的一致性和复制状态。

3. 数据恢复和回滚：

   • 二进制日志可以用于数据恢复和回滚操作。
   • 通过分析和重放二进制日志，可以还原数据库到特定时间点的状态，进行数据恢复。
   • 通过回滚未提交的事务，可以使用二进制日志来还原数据库到事务开始前的状态。

4. 数据库安全和审计：

   • 二进制日志可以用于数据库的安全性和审计跟踪。
   • 通过记录所有的数据库更改操作，二进制日志可以用于审计和追踪数据修改的来源。
   • 二进制日志还可以用于恢复误删除或误修改的数据，提供了额外的安全保障。

5. 日志管理和维护：

   • 二进制日志可能会占用大量磁盘空间，因此需要进行定期的日志管理和维护。
   • 可以使用PURGE BINARY LOGS语句来删除旧的二进制日志文件，以释放磁盘空间。
   • 可以通过配置expire_logs_days参数来自动清理过期的二进制日志文件。

二进制日志是MySQL数据库的重要组成部分，它提供了数据同步、数据恢复、数据库安全性和审计跟踪等关键功能。通过合理配置和管理二进制日志，可以确保数据的完整性、一致性和安全性。

配置

操作

查看二进制文件的大小

root@(none) 15:41 mysql>show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.01 sec)

默认最大容量是1G

查看当前二进制日志文件和位置的详细信息

root@(none) 15:41 mysql>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

查看所有的二进制文件和大小

SHOW BINARY LOGS 

删除二进制文件

• 删除所有的二进制日志

mysql> reset master;

刷新

flush 

恢复数据

恢复数据的时候：

• 根据时间来恢复
• 根据位置来恢复Position

刷盘

MySQL的二进制日志(binary log)记录了正在运行的MySQL实例所做的变更,这些变更可以用来重播以重新创建数据或者恢复数据。

为了确保二进制日志在实例发生崩溃的情况下不丢失,MySQL提供了刷盘(flush)的功能。

二进制日志的刷盘主要有两种方式:

1. 手动刷盘(flush logs):即手动执行FLUSH BINARY LOGS; 语句。这会立即将二进制日志从缓冲区刷新到磁盘上。

2. 自动刷盘:通过配置sync_binlog参数。

• 如果sync_binlog=0,那么MySQL不会主动将二进制日志写入磁盘,仅缓存在内存中。
• 如果sync_binlog=1,MySQL会周期性地(几秒一次)将二进制日志写入磁盘。
• 如果sync_binlog=2,MySQL会在每次事务提交后立即将二进制日志写入磁盘。

因此,sync_binlog=0性能最高,但数据最不稳定;sync_binlog>1性能最低,但数据最稳定。

一般推荐设置sync_binlog=1,既保证了较高的性能,又基本上避免了数据丢失的风险。

另外,可以通过配置innodb_flush_log_at_trx_commit参数控制InnoDB事务日志的刷盘频率,以提供更多冗余。

总的来说,刷新二进制日志到磁盘可以:

1. 在MySQL崩溃时保护二进制日志不丢失
2. 提高数据恢复能力和安全性
3. 不建议设置sync_binlog=0,否则数据会处于不稳定状态

查看默认设置

root@chenyulin123 15:37  scmysql>show variables like "sync_binlog";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.01 sec)

查看二进制文件

mysqlbinlog是MySQL数据库的一个命令行工具，用于解析和显示二进制日志文件（binary log）。二进制日志文件记录了MySQL数据库的所有写操作，包括插入、更新和删除等操作，以便实现数据备份、恢复和复制等功能。

mysqlbinlog工具的主要功能是将二进制日志文件转换为可读的文本格式，以便于用户查看和分析。它可以显示二进制日志文件中的操作语句，以及相关的元数据信息。

下面是mysqlbinlog工具的一些常用参数和功能：

1. -h, --host=：指定MySQL服务器的主机名。
2. -u, --user=：指定连接MySQL服务器所使用的用户名。
3. -p, --password[=]：指定连接MySQL服务器所使用的密码。如果未指定密码，mysqlbinlog将提示您输入密码。
4. –start-datetime=：指定要解析的二进制日志文件的起始日期和时间。
5. –stop-datetime=：指定要解析的二进制日志文件的结束日期和时间。
6. –start-position=：指定要解析的二进制日志文件的起始位置。
7. –stop-position=：指定要解析的二进制日志文件的结束位置。
8. –database=：指定要解析的二进制日志文件中的数据库名称。
9. –exclude-gtids=<gtid_set>：指定要排除的全局事务标识符（GTID）集合。
10. –verbose：显示更详细的解析信息，包括二进制日志文件的元数据。
11. –base64-output=decode-rows：以解码的形式输出Base64编码的行数据。
12. –hexdump：以十六进制格式显示二进制日志文件的内容。

使用mysqlbinlog工具可以执行以下操作：

1. 查看二进制日志文件的内容：可以使用mysqlbinlog命令并指定要解析的二进制日志文件，然后它将显示日志文件中的操作语句和元数据信息。
2. 过滤特定时间范围内的日志：使用–start-datetime和–stop-datetime参数可以指定要解析的二进制日志文件的时间范围。
3. 过滤特定位置范围内的日志：使用–start-position和–stop-position参数可以指定要解析的二进制日志文件的位置范围。
4. 解码Base64编码的行数据：使用–base64-output=decode-rows参数可以将Base64编码的行数据解码为可读的格式。
5. 显示更详细的解析信息：使用–verbose参数可以显示更多关于二进制日志文件的元数据信息。

请注意，mysqlbinlog工具需要对MySQL服务器的二进制日志文件具有读取权限，并且只能解析尚未被清理的二进制日志文件。

格式

MySQL的二进制日志（Binary Log）可以使用不同的格式进行记录，这些格式决定了日志中记录的内容和方式。MySQL支持以下几种二进制日志格式：

1. Statement-Based Replication (基于语句的复制)：

   • 在语句复制模式下，二进制日志记录了在主服务器上执行的SQL语句。
   • 日志中包含了每个执行的SQL语句，例如INSERT、UPDATE、DELETE等。
   • 通过重放二进制日志中的SQL语句，从服务器可以复制主服务器上的操作并应用到从服务器的数据库上。
   • 这种格式简单，适用于大多数情况，但某些情况下可能会导致不一致或不确定的结果。

2. Row-Based Replication (基于行的复制)：

   • 在行复制模式下，二进制日志记录了对行数据的更改。
   • 日志中包含了每个修改操作的行数据的详细变化。
   • 通过重放二进制日志中的行变化，从服务器可以准确地复制主服务器上的行数据。
   • 这种格式比基于语句的复制更安全，但会产生更多的日志数据和网络流量。

3. Mixed Replication (混合复制)：

   • 混合复制模式是上述两种模式的结合。
   • 在混合模式下，MySQL根据具体情况自动选择使用基于语句的复制或基于行的复制。
   • 大多数情况下，MySQL使用基于语句的复制来提高性能，但在某些情况下会自动切换到基于行的复制以保证数据的一致性。

每种二进制日志格式都有其特定的用途和优势，应根据实际需求和场景进行选择和配置。基于语句的复制通常适用于大多数情况，而基于行的复制提供了更准确和安全的复制。混合复制则提供了自动选择的灵活性，根据需要自动切换复制模式。可以通过配置binlog_format参数来指定所需的二进制日志格式。

redo

重做日志（redo log）

作用：

• 确保事务的持久性。
• 防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

redo的整体流程

第一步：InnoDB 会先把记录从硬盘读入内存
第二部：修改数据的内存拷贝
第三步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
第四步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式
第五步：定期将内存中修改的数据刷新到磁盘中(注意注意注意，不是从redo log file刷入磁盘，而是从内存刷入磁盘，redo log file只在崩溃恢复数据时才用)，如果数据库崩溃，则依据redo log buffer、redo log file进行重做，恢复数据,这才是redo log file的价值所在

如何实现事物的持久化（就是先写入日志，之后修改数据）

innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。

• 物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。

  什么时候产生：

• 事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。

  什么时候释放：

• ​ 当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

  对应的物理文件：

• 默认情况下，对应的物理文件位于数据库的data目录下的ib_logfile1&ib_logfile2

undo

MySQL的Undo日志主要用于事务回滚和崩溃恢复。具体来说,MySQL的Undo日志包含以下几个关键特性:

1. 存储引擎层的实现

Undo日志是在各个存储引擎内部实现的,如InnoDB, MyISAM等存储引擎会有自己的Undo日志实现。这使得UNDO日志可与具体存储引擎深度集成。

2. 日志内容

Undo日志记录的是数据修改前的值。当执行回滚或恢复时,可以利用Undo日志将数据恢复到原始状态。

3. 持续日志

Undo日志采用的是追加方式写日志,并不会重写旧的日志内容。新日志会追加到文件的末尾。

4. 事务级的

每个事务会分配独立的Undo日志空间,用于记录该事务的修改操作日志。rollback时只回滚该事务的日志。

5. 先写入日志后写入数据

每个数据修改操作会先写入Undo日志,并保存数据在日志中的位置。然后执行真正的数据更新操作。这确保了可以回滚数据变更。

6. 空间回收

长事务结束后,其Undo空间可以被回收和重用。此外,处于空闲状态的Undo段也会被逐步回收。

7. 压缩

Undo日志可以采用压缩的形式保存,减少磁盘空间占用。

8. 崩溃恢复

服务器崩溃后可以通过Undo日志做崩溃恢复,恢复到崩溃前的状态。

所以总体来说,Undo日志在保证MySQL ACID事务和恢复能力方面非常关键。

redo、undo

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-77AiU4bc-1689786330795)(D:\Snipaste\03【电脑截图软件】snipaste截图软件\Typora\mysql\日志\redo、undo.png)]

MySQL在崩溃后恢复的顺序

MySQL在崩溃后恢复时,是先使用undo日志回滚未提交事务,然后使用redo日志重做已提交事务。具体的过程是:

使用undo日志回滚未提交事务:
当MySQL实例崩溃重启后,首先会查询undo日志,找到那些还没有提交的事务。然后通过undo日志记录的还原信息,一个一个撤销未提交事务做出的变更,让相关数据回滚到修改前的状态。

使用redo日志重做已提交事务:
undo日志回滚未提交事务完成后,MySQL就只剩已提交的事务了。此时,会按照redo日志的顺序,将这些已提交事务重新执行一遍。

依次将这些事务应用到数据库,把数据rolling forward到崩溃前的最新一致状态。

数据库恢复完成:
通过上述两步,MySQL最终可以恢复到崩溃前的最新一致状态。首先使用undo日志回滚未提交事务,使当前数据是一致的;然后使用redo日志重做已提交事务,将数据rolling forward到最新的位置。

所以,总的来说,MySQL在进行崩溃恢复时,是先使用undo日志回滚未提交事务,然后再使用redo日志重做已提交事务,最终恢复到最新一致状态。

undo日志用于撤销,redo日志用于重做,两者配合使用,共同保证了MySQL实例在崩溃后能及时快速地恢复。

redo log：记录的是脏数据的变化--》buffer pool里的
作用：
	MySQL意外宕机重启也不要紧。只要在重启时解析redo log中的事务而后重做一遍。将Buffer Pool中的缓存页重作成脏页。后续再在合适的时机将该脏页刷入磁盘便可。

undo log：记录某 数据 被修改 前 的值
作用：方便回滚 rollback --》相当于做了一个快照（备份）