​引言

MySQL数据库日志是一种记录数据库活动的机制。它可以记录执行的查询、更新、删除、插入等操作以及其他服务器相关的事件。MySQL数据库日志对于数据库管理者来说非常重要,以下是MySQL数据库日志的几个重要性方面:

  1. 调试和故障排除:当MySQL数据库发生故障时,数据库日志是调试和故障排除的关键工具之一。通过分析数据库日志,管理员可以确定发生故障的时间和原因,从而更快地解决问题。
  2. 性能优化:数据库日志不仅记录了数据库发生过的事件,还提供了有关这些事件的详细信息,如执行时间,查询执行计划以及查询所使用的索引等。通过分析数据库日志,管理员可以识别查询性能瓶颈并进行进一步的调整,以提高数据库性能。
  3. 安全审计:数据库日志是安全审计的重要部分。通过数据库日志,管理员可以跟踪谁访问了数据库、何时访问以及执行了哪些操作。这对于保护敏感信息以及满足法规要求非常重要。
  4. 数据恢复:在某些情况下,数据丢失或损坏是无法避免的。在这种情况下,数据库日志可以帮助管理员通过回放日志操作来恢复数据库以前的状态。
  5. 数据库备份:数据库日志可以用来确定在某个时间点之后更改了哪些数据。这对于增量备份或差异备份非常重要,因为只需要备份从上次备份以来更改的部分就好了。

综上所述,MySQL数据库日志对于数据库管理者来说非常重要。管理员可以使用数据库日志来调试和故障排除、性能优化、安全审计、数据恢复以及数据库备份。

MySQL数据库中的日志 

  1. redo log(重做日志)
  2. undo log(回滚日志)
  3. bin log(二进制日志)
  4. error log(错误日志)
  5. slow query log(慢查询日志)
  6. general query log(一般查询日志)
  7. relay log(中继日志)

redo log(重做日志)

  • redo log属于MySQL存储引擎InnoDB的事务日志
  • MySQL的数据是存放在磁盘中的,每次读写数据都需做磁盘IO操作,如果并发场景下性能就会很差。为此MySQL提供了一个优化手段,引入缓存Buffer Pool。这个缓存中包含了磁盘中部分数据页(page)的映射,以此来缓解数据库的磁盘压力。
  • 当从数据库读数据时,首先从缓存中读取,如果缓存中没有,则从磁盘读取后放入缓存;当向数据库写入数据时,先向缓存写入,此时缓存中的数据页数据变更,这个数据页称为脏页Buffer Pool中修改完数据后会按照设定的更新策略,定期刷到磁盘中,这个过程称为刷脏页

开启方式

在MySQL中,redo log是用于保证事务的持久性和原子性的一种机制。默认情况下,MySQL会自动开启redo log,并进行管理。然而,你也可以通过修改配置文件来调整redo log的相关参数。

下面是一些关于redo log的常用配置参数:

  1. innodb_log_file_size:设置每个redo log文件的大小,默认值为 48MB。可以根据实际需求调整该参数的值。
  2. innodb_log_files_in_group:设置每组redo log文件的数量,默认值为 2。通常不需要修改此参数,除非你有特殊需求。

如果你希望修改redo log的大小或数量,可以按照以下步骤进行操作:

  1. 打开MySQL的配置文件(如my.cnf)。
  2. 在[mysqld]模块下添加或修改以下参数:
innodb_log_file_size = 新的文件大小
innodb_log_files_in_group = 新的文件数量

确保新的文件大小和文件数量合理,并与已有的redo log文件兼容。

  1. 保存并退出配置文件。
  2. 停止MySQL服务。
sudo systemctl stop mysql

  1. 移动或删除旧的redo log文件。在MySQL的数据目录中,通常有以 "ib_logfile" 开头的文件,这些就是redo log文件。移动或删除这些文件,确保MySQL能够使用新的参数创建新的redo log文件。
  2. 启动MySQL服务。
sudo systemctl start mysql

现在,MySQL将使用新的redo log参数进行运行。请注意,在修改redo log参数之后,可能需要重新执行备份和恢复操作,以确保数据的一致性和完整性。

管理命令

   redo log命令管理  ==>  展开 / 收起

undo log(回滚日志)

  • undo log也是属于MySQL存储引擎InnoDB的事务日志。
  • undo log属于逻辑日志,如其名主要起到回滚的作用,它是保证事务原子性的关键。记录的是数据修改前的状态,在数据修改的流程中,同时会记录一条与当前操作相反的逻辑日志到undo log中。
  • 我们举个栗子:假如更新ID=1记录的name字段,name原始数据为小富,现改name为程序员内点事
  • 事务执行update X set name = 程序员内点事 where id =1语句时,先会在undo log中记录一条相反逻辑的update X set name = 小富 where id =1记录,这样当某些原因导致服务异常事务失败,就可以借助undo log将数据回滚到事务执行前的状态,保证事务的完整性。

开启方式

在MySQL中,undo log是用于支持事务的回滚和 MVCC(多版本并发控制)实现的重要组件。默认情况下,MySQL会自动开启undo log,并进行管理。不需要额外的配置来启用undo log。

然而,你可以通过调整特定配置参数来优化undo log的性能和管理。下面是一些与undo log相关的常用配置参数:

  1. innodb_undo_log_truncate:当事务提交后,是否立即截断(truncate)undo log。默认值为ON,表示立即截断。如果设置为OFF,MySQL将在后台线程中定期截断undo log。
  2. innodb_undo_logs:指定undo log的数量,默认值为128。这个参数决定了可以并行执行的事务数量。增加undo log的数量可以提高并发性能,但也会增加系统资源的消耗。
  3. innodb_undo_tablespaces:指定undo log的表空间数量,默认值为0(自动确定)。可以根据数据库的负载和需求来调整此参数。

如果你希望修改以上参数或其他与undo log相关的参数,可以按照以下步骤进行操作:

  1. 打开MySQL的配置文件(如my.cnf)。
  2. 在[mysqld]模块下添加或修改需要调整的参数。
  3. 保存并退出配置文件。
  4. 重启MySQL服务。
sudo systemctl restart mysql

这样,MySQL将使用新的配置参数来管理undo log。请注意,修改undo log的参数可能会对数据库的性能和资源消耗产生影响。在进行任何更改之前,请确保了解其潜在的影响,并进行充分的测试和评估。

管理命令

   undo log命令管理  ==>  展开 / 收起

bin log(二进制日志)

  • bin log是一种数据库Server层(和存储引擎无关),以二进制形式存储在磁盘中的逻辑日志。bin log记录了数据库所有的DDL和DML操作(不包括SELECT 和 SHOW)
  • bin log也叫做归档日志,因为他不像redo log一样循环写擦除之前的记录,而是会一直记录日志。如果单个日志文件大小超过 max_binlog_size,就会创建新的文件继续写入。
  • bin log日志的内容格式其实就是执行SQL命令的反向逻辑,(和undo log有点类似)。
  • 一般来说,开启bin log都会给日志文件设置过期时间(expire_logs_days变量,单位是天)

开启方式

在MySQL中,开启二进制日志(binlog)功能可以实现数据备份、主从复制以及恢复等功能。下面是在MySQL中开启binlog日志的步骤:

  1. 修改my.cnf配置文件,找到[mysqld]模块,在其中加入如下配置:
server-id = 1
log-bin = /var/lib/mysql/mysql-bin

其中,server-id为服务器唯一标识,可以设置为任何非0数字,需要确保与其他服务器不重复。log-bin表示binlog日志存放的路径和文件名,可以根据实际需求修改。

  1. 保存并退出配置文件,重启MySQL服务,使其读取新的配置。可以使用以下命令重启MySQL服务:
sudo systemctl restart mysql

  1. 验证binlog是否已经开启。可以在MySQL客户端输入以下命令:
SHOW MASTER STATUS;

如果输出了类似于 "mysql-bin.000001" 和 "x" 的信息,则说明binlog已经开启成功,其中 "mysql-bin.000001" 为当前的binlog文件名,"x" 为binlog日志中最后一个事件的位置。

注意:开启binlog会影响MySQL服务器的性能,因此建议在生产环境中进行仔细测试再决定是否开启。

管理命令

   bin log命令管理  ==>  展开 / 收起

relay log(中继日志)

relay log日志文件和bin log日志文件格式相同,从上边的MySQL主从复制流程可以看到,relay log起到一个中转作用,slave先从主库master中读取二进制日志数据,写入从库本地,后续再一户由SQL线程读取解析relay log成对应的SQL命令。

开启方式

在MySQL中,Relay Log是用于复制和同步主从服务器之间的数据更新操作的日志文件。它记录了从主服务器接收到的binlog事件。默认情况下,当你启用MySQL的复制功能时,Relay Log会自动开启。以下是启用Relay Log的一般步骤:

  1. 首先,确保你已经启用了MySQL的复制功能,并且主服务器和从服务器已经建立了正确的复制关系。
  2. 在主服务器上修改配置文件(如my.cnf或my.ini),找到并编辑[mysqld]模块。
  3. 添加或修改以下参数以启用Relay Log:
relay_log = relay-log-name
relay_log_index = relay-log-index-name

其中,relay-log-name和relay-log-index-name是存储Relay Log日志文件和索引文件的路径和名称。你可以根据自己的需求指定它们的具体值。

示例:

relay_log = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index

  1. 保存并关闭配置文件。
  2. 在从服务器上停止MySQL服务。
sudo systemctl stop mysql

  1. 删除从服务器上的旧的Relay Log文件和索引文件(如果存在)。
sudo rm /var/lib/mysql/relay-bin*

  1. 启动从服务器上的MySQL服务。
sudo systemctl start mysql

现在,从服务器将开始使用新配置的Relay Log来接收和处理来自主服务器的binlog事件。请注意,在设置Relay Log之前,请确保主从服务器之间的复制已经正确配置,并在进行修改之前进行备份。

同时,如果你使用的是MySQL 8.0版本或更高版本,并且启用了基于GTID(全局事务标识)的复制,你可以设置以下参数来控制Relay Log的保留和自动清理:

relay_log_purge = ON
relay_log_recovery = ON

这些参数将在复制过程中自动管理Relay Log的清理和恢复操作。

管理命令

   relay log命令管理  ==>  展开 / 收起

error log(错误日志)

  • 错误日志主要记录MySQL服务器每次启动和停止的时间以及诊断和出错信息。
  • 错误日志中记录的并非全部错误信息,也包括MySQL如何启动InnoDB的表空间文件、如何初始化自己的存储引擎,初始化buffer pool等,这些也记录在错误日志中。

开启方式

在MySQL中,Error Log是一种记录MySQL服务器运行过程中产生的错误和警告信息的日志文件。通过启用Error Log,你可以方便地记录和跟踪MySQL服务器活动中出现的各种问题,有助于诊断和解决系统故障。以下是启用Error Log的一般步骤:

  1. 找到MySQL的配置文件(如my.cnf或my.ini)并打开它。
  2. 找到[mysqld]模块,并添加或修改以下参数:
log_error = /path/to/error.log

其中,/path/to/error.log是存储Error Log的目录和文件名称。你可以根据需要修改它们的名称和路径。

示例:

log_error = /var/log/mysql/error.log

  1. 添加或修改以下参数来限制Error Log文件的大小和数量:
log_error_verbosity = 2
log_error_suppression_list = "1270"
max_error_count = 1000

  • log_error_verbosity = 2: 指定Error Log记录的详细程度,值为2时记录错误和警告信息。
  • log_error_suppression_list = "1270": 指定一个逗号分隔的错误号列表,在Error Log中删除这些错误的记录。例如,这里指定了"1270",表示忽略称为“1364 Field 'xx' doesn't have a default value”的错误。
  • max_error_count = 1000: 指定Error Log文件最大记录的条目数,默认值是1000。
  1. 保存并关闭配置文件。
  2. 重启MySQL服务以使配置生效。通过以下命令重启服务:
sudo systemctl restart mysql

  1. 现在,当MySQL服务器运行时产生错误和警告信息时,这些信息将被写入指定的Error Log文件中。

请注意,在生产环境中,开启Error Log可以帮助你及时发现和解决系统异常,但也需要定期查看和维护Error Log文件,以确保其不会因为过大或其他问题而占用过多磁盘空间或导致其他性能问题。同时,请保护好Error Log文件,防止未授权的用户访问敏感信息。

管理命令

   error log命令管理  ==>  展开 / 收起

slow query log(慢查询日志)

  • 慢查询日志(slow query log): 用来记录在 MySQL 中执行时间超过指定时间的查询语句,在 SQL 优化过程中会经常使用到。通过慢查询日志,我们可以查找出哪些查询语句的执行效率低,耗时严重。
  • 出于性能方面的考虑,一般只有在排查慢SQL、调试参数时才会开启,默认情况下,慢查询日志功能是关闭的。

开启方式

在MySQL中,Slow Query Log(慢查询日志)可以记录执行时间超过设定阈值的SQL查询语句。启用Slow Query Log可以帮助你发现并优化执行时间较长的查询,从而提高数据库性能。以下是启用Slow Query Log的一般步骤:

  1. 找到MySQL的配置文件(如my.cnf或my.ini)并打开它。
  2. 找到[mysqld]模块,并添加或修改以下参数:
slow_query_log = 1
slow_query_log_file = /path/to/slow-query.log
long_query_time = X

其中:

  • slow_query_log参数用于启用/禁用Slow Query Log,将其设置为1表示启用,设置为0表示禁用。
  • slow_query_log_file参数用于指定Slow Query Log文件的路径和文件名。
  • long_query_time参数用于设定执行时间超过多少秒的查询被认为是慢查询。X请根据自己的需求来设置,例如设置为2表示执行时间超过2秒的查询会被记录在Slow Query Log中。

示例:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2

  1. 保存并关闭配置文件。
  2. 重启MySQL服务以使配置生效。可以通过以下命令重启服务:
sudo systemctl restart mysql

  1. 现在,当执行时间超过设定阈值的查询语句被执行时,它们的详细信息将被记录在指定的Slow Query Log文件中。

注意事项:

  • 启用Slow Query Log会对系统性能产生一定影响,因此在生产环境中应谨慎使用,并根据需要进行定期的日志文件维护和清理。
  • 为了获得更准确的慢查询日志,你可以使用其他参数进行进一步设置,如min_examined_row_limit(最小扫描行数限制)等。
  • Slow Query Log记录的是执行时间超过阈值的查询语句,因此该日志文件可能包含敏感信息。请妥善保护好该文件,防止未授权的访问。

通过启用Slow Query Log,你可以在数据库中识别和优化执行慢查询的操作,从而提高数据库的性能和效率。

管理命令

   slow query log命令管理  ==>  展开 / 收起

general query log(一般查询日志)

  • 一般查询日志(general query log):用来记录用户的所有操作,包括客户端何时连接了服务器、客户端发送的所有SQL以及其他事件,比如 MySQL 服务启动和关闭等等。MySQL服务器会按照它接收到语句的先后顺序写入日志文件。
  • 由于一般查询日志记录的内容过于详细,开启后 Log 文件的体量会非常庞大,所以出于对性能的考虑,默认情况下,该日志功能是关闭的,通常会在排查故障需获得详细日志的时候才会临时开启。

开启方式

在MySQL中,General Query Log(通用查询日志)可以记录所有经过MySQL服务器的查询语句,包括登录和注销等操作。启用General Query Log可以帮助你跟踪和调试数据库操作,但应该谨慎使用,因为它会记录大量的信息,可能对系统性能和磁盘空间产生负面影响。以下是启用General Query Log的一般步骤:

  1. 找到MySQL的配置文件(如my.cnf或my.ini)并打开它。
  2. 找到[mysqld]模块,并添加或修改以下参数:
general_log = 1
general_log_file = /path/to/general-query.log

其中:

  • general_log参数用于启用/禁用General Query Log,将其设置为1表示启用,设置为0表示禁用。
  • general_log_file参数用于指定General Query Log文件的路径和文件名。

示例:

general_log = 1
general_log_file = /var/log/mysql/general-query.log

  1. 保存并关闭配置文件。
  2. 重启MySQL服务以使配置生效。可以通过以下命令重启服务:
sudo systemctl restart mysql

  1. 现在,所有经过MySQL服务器的查询语句,包括登录和注销等操作,都将被记录在指定的General Query Log文件中。

注意事项:

  • 启用General Query Log会生成大量的日志信息,可能对系统性能和磁盘空间产生负面影响。在生产环境中,应避免长时间启用General Query Log,可以根据需要进行临时性的开启和关闭。
  • General Query Log记录的是所有经过MySQL服务器的查询操作,因此该日志文件可能包含敏感信息。请妥善保护好该文件,防止未授权的访问。

通过启用General Query Log,你可以详细了解MySQL服务器上执行的所有查询操作,从而帮助你进行调试和分析数据库的行为。但请注意,由于General Query Log产生的大量日志数据,建议在必要时才启用,并定期对日志文件进行维护和清理。

管理命令

   general query log命令管理   ==>  展开 / 收起