第十三章 - MySQL日志文件管理

第十三章 - MySQL日志文件管理

MySQL日志是记录MySQL数据库的日常操作和错误信息的文件。当数据遭到意外发生丢失时，可以通过日志文件来查询出错原因，并且可以通过日志文件进行数据恢复。

首先要了解日志的作用，并且掌握各种日志的使用方法和使用二进制日志还原数据的方法。

13.1 MySQL日志文件

日志是MySQL数据库不可或缺的重要组成部分。通过分析这些日志文件，可以了解MySQL数据库的运行情况、日常操作、错误信息和哪些地方需要进行优化。

13.1.1 日志的特点

MySQL日志是用来记录MySQL数据库的运行情况、用户操作和错误信息等。例如，当一个用户登录到MySQL服务器时，日志文件中就会记录该用户的登录时间和执行的操作等。

当MySQL服务器在某个时间出现异常时，异常信息也会被记录到日志文件中。日志文件可以为MySQL管理和优化提供必要的信息。

如果MySQL数据库系统意外停止服务，可以通过错误日志查看出现错误的原因。并且可以通过二进制日志文件来查看用户执行了哪些操作，对数据库文件做了哪些修改等。然后根据二进制日志文件的记录来修复数据库。

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

日志会占用大量的磁盘空间。对于用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间甚至比数据库文件需要的存储空间还要大。

13.1.2 日志文件分类

默认情况下，所有日志创建于mysqld数据目录中。通过刷新日志，可以强制 mysqld来关闭和重新打开日志文件，也可以切换到一个新的日志。当你执行一个flush logs语句或执行mysqladmin flush-logs或mysqladmin refresh时，出现日志刷新。如果你正使用MySQL复制功能，从复制服务器将维护更多日志文件，被称为接替日志。

MySQL的日志包括二进制日志（ binary log）、错误日志（error log）、通用查询日志（common_query log）和慢查询日志（slow-query log）4类。除二进制日志外，其他日志都是文本文件。

默认情况下，只启动了错误日志的功能。其他3类日志都需要数据库管理员进行设置。日志文件通常存储在MySQL数据库的数据目录下。 4类日志文件的具体功能如下:

  • 二进制日志：以二进制文件的形式记录了数据库中所有更改数据的语句，还可以运用于复制操作。

  • 错误日志：记录MySQL服务的启动、运行和停止mysqld时出现的问题。

  • 通用查询日志：记录用户登录和记录查询的信息。

  • 慢查询日志：记录所有执行时间超过long_query_time秒的查询或不使用索引的查询。

13.2 错误日志

错误日志记载着MySQL数据库系统的诊断和出错信息。错误日志文件包含了当mysqld启动和停止时，以及服务器运行过程中发生任何严重错误时的相关信息。MySQL会将启动和停止数据库信息以及一些错误信息记录到错误日志文件中。

13.2.1 启用和设置错误日志

在MySQL数据库中，错误日志功能是默认开启的。而且，错误日志无法被禁止。默认情况下，错误日志存储在MySQL数据库的数据文件夹下。

错误日志文件通常的名称为hostname.err。其中，hostname表示MySQL服务器的主机名。错误日志的存储位置可以通过log-error选项来设置。

将log-error选项加入到my.ini文件的[mysqld]组中，在Windows操作系统中形式如下：

#my.ini文件
[mysqld]
log-error=[path/[filename]]

说明：

（1）path为日志文件所在的目录路径；

（2）filename为日志文件名，修改配置项后，需要重启MySQL服务才能生效。

13.2.2 查看错误日志

错误日志中记录着开启和关闭MySQL服务的时间，以及服务运行过程中出现哪些异常等信息。如果MySQL服务出现故障，可以到错误日志中查找原因。错误日志是以文本文件的形式存储的，可以直接使用普通文本工具就可以查看。Windows操作系统可以使用文本文件查看器查看。

【例13.1】使用记事本查看MySQL错误日志。

#通过show variables语句查询错误日志的存储路径和文件名：

mysql> show variables like 'log_error';

13.2.3 删除错误日志

数据库管理员可以删除很长时间之前的错误日志，以保证MySQL服务器上的硬盘空间。MySQL数据库中，可以使用mysqladmin命令来开启新的错误日志。mysqladmin命令的语法如下：

C:\> mysqladmin -u root -p flush -logs

执行该命令后，数据库系统会自动创建一个新的错误日志。旧的错误日志仍然保留着，只是已经更名为filename.err-old。

如果在客户端登录MySQL数据库，可以执行flush logs命令：

mysql> flush logs;

13.3 二进制日志

二进制日志主要用于记录数据库的变化情况。通过二进制日志可以查询MySQL数据库中进行了哪些改变。二进制日志以一种有效的格式，包含了所有更新了的数据或者已经潜在更新了的数据的语句，如没有匹配任何行的一条delete语句。

语句以事件的形式保存，描述数据的更改。使用二进制日志的主要目的是最大可能地恢复数据，因为二进制日志包含备份后进行的所有更新。

二进制日志包含关于每个更新数据库语句的执行时间信息。它不包含没有修改任何数据的语句。如果要记录所有语句，需要使用通用查询日志。

13.3.1 启用二进制日志

默认情况下，二进制日志功能是关闭的。通过my.ini文件的log-bin选项可以开启二进制日志。将log-bin选项加入到my.ini文件的[mysqld]组中。

在Windows操作系统中形式如下：

# my.ini文件
[mysqld]
log-bin [=path\[filename]]
expire_logs_days=10
max_binlog_size=100M

在 my.ini配置文件中[mysqld]下面，添加下列的参数，添加完毕启动MySQL服务进程，即可启动二进制日志。

log-bin 
expire_logs_days=5
max_binlog_size=10M

【例13.2】使用show variables语句查询日志设置。

mysql> show variables like 'log_%';

13.3.2 查看二进制日志

使用二进制格式可以存储更多的信息，并且可以使写入二进制日志的效率更高。但是，不能直接打开并查看二进制日志。show binary logs命令可以查看当前的二进制日志文件个数及其文件名。

【例13.3】使用show binary logs将查看二进制日志文件个数及文件名。

mysql>  show binary logs;

查看二进制日志，也可以使用mysqlbinlog命令。

mysqlbinlog命令的语法形式如下：

mysqlbinlog filename.number

【例13.4】使用mysqlbinlog查看二进制日志PGIG1MIWMYPOFBS-bin.000001。

13.3.3 清理二进制日志

二进制日记会记录大量的信息。如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。删除二进制日志的方法如下：

  • 删除所有二进制日志。

  • 根据编号来删除二进制日志。

  • 根据创建时间来删除二进制日志。

1. 删除所有二进制日志

使用reset master语句可以删除所有二进制日志。

命令格式如下：

reset master;

2. 删除指定的日志文件

使用 purge master logs 语句删除指定的日志文件。

删除指定文件的语法有2种格式：

purge {binary| master }logs to‘log_name’
purge {binary| master }logs before‘date’

说明:

（1）log_name格式是指定文件名，执行该命令将删除比此文件名编号小的所有二进制日志文件。
（2）date是指定日期，执行该命令将删除指定日期以前的所有二进制日志文件。

【例13.5】利用purge master logs删除创建时间比PGIG1MIWMYPOFBS-bin.000003早的所有日志文件。

分析：为了演示语句操作过程，准备多个日志文件，可以对MySQL服务进行多次重新启动。例如这里有5个日志文件。删除指定文件，可以在查看二进制日志文件数，可以观察到指定文件被删除了。

mysql> show binary logs;

mysql>  purge master logs TO 'PGIG1MIWMYPOFBS-bin.000003';
Query OK, 0 rows affected (0.03 sec)
mysql> show binary logs;
+----------------------------+-----------+
| Log_name                       | File_size |
+----------------------------+-----------+
| PGIG1MIWMYPOFBS-bin.000003 |        177 |
| PGIG1MIWMYPOFBS-bin.000004 |        177 |
| PGIG1MIWMYPOFBS-bin.000005 |       1207 |
+----------------------------+-----------+
3 rows in set (0.00 sec)

【例13.6】使用purge master logs删除2017年6月12日前创建的所有日志文件。

mysql> purge master logs before ‘20170612’;

说明:

语句执行之后，2017年6月12日之前创建的日志文件都将被删除，但2017年6月12日的日志会被保留，可根据自己机器中创建日志的时间修改命令参数。

13.3.4 利用二进制日志恢复数据库

二进制日志记录了用户对数据库中数据的改变。如insert、update、delete、create等语句都会记录到二进制日志中。一旦数据库遭到破坏，可以使用二进制日志来还原数据库。

如果数据库遭到意外损坏，首先应该使用最近的备份文件来还原数据库。备份之后，数据库可能进行了一些更新。这可以使用二进制日志来还原。因为二进制日志中存储了更新数据库的语句，如update语句、insert语句等。

二进制日志还原数据库的命令如下：

mysqlbinlog [option] filename | mysql  -uuser –ppassword

【例13.7】使用mysqlbinlog恢复MySQL数据库到2017年6月12日17:00:00时的状态。

C:\>mysqlbinlog –stop-date="2017-06-12 17:00:00" C:\Documents and Settings\All Users\MySQL\MySQL Server 5.7\Data\PGIG1MIWMYPOFBS-bin.000005 -uroot  -p
    Enter password: ******

13.3.5 暂时停止二进制日志功能

在配置文件中设置了log-bin选项以后，MySQL服务器将会一直开启二进制日志功能。删除该选项后就可以停止二进制日志功能。如果需要再次启动这个功能，又需要重新添加log-bin选项。MySQL中提供了暂时停止二进制日志功能的语句。

如果用户不希望自己执行的某些SQL语句记录在二进制日志中，那么需要在执行这些SQL语句之前暂停二进制日志功能。

用户可以使用set语句来暂停二进制日志功能。该参数的值为0时，表示暂停记录二进制日志；如果为1，则表示恢复记录二进制日志。

set语句的命令格式如下：

set sql_log_bin={0|1};

13.4 通用查询日志

通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、更新语句、查询语句等。

13.4.1 启动和设置通用查询日志

默认情况下，通用查询日志功能是关闭的。通过my.ini文件的log选项可以开启通用查询日志。将log选项加入到my.ini文件的[mysqld]组中，在Windows操作系统中的形式如下：

# my.ini文件
[mysqld]
log [=path\[filename]]

说明：

（1）path为二进制日志文件所在的目录路径。

（2）filename为通用日志文件名。如果不指定文件名，通用查询日志文件将默认存储在MySQL数据目录中的hostname.log文件中。hostname为MySQL数据库的主机名。

在不指定参数的情况下，启动通用查询日志的格式如下：
[mysqld]
log

13.4.2 查看通用查询日志

用户的所有操作都会记录到通用查询日志中。如果希望了解某个用户最近的操作，可以查看通用查询日志。通用查询日志是以文本文件的形式存储的。
Windows操作系统可以使用文本文件查看器查看。

13.4.3 删除通用查询日志

通用查询日志会记录用户的所有操作。如果数据库的使用非常频繁，那么通用查询日志将会占用非常大的磁盘空间。数据库管理员可以删除很长时间之前的通用查询日志，以保证MySQL服务器上的硬盘空间。

MySQL数据库中，也可以使用mysqladmin命令来开启新的通用查询日志。新的通用查询日志会直接覆盖旧的查询日志，不需要再手动删除了。

mysqladmin命令的语法如下：

mysqladmin -u root -p flush-logs

在Windows中，服务器打开日志文件期间不能重新命名日志文件。首先，必须停止服务器。然后重新命名日志文件。最后，重启服务器来创建新的日志文件。

13.5 慢查询日志

慢查询日志用来记录执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。

慢查询日志是记录查询时长超过指定时间的日志。慢查询日志主要用来记录执行时间较长的查询语句。通过慢查询日志，可以找出执行时间较长、执行效率较低的语句，然后进行优化。

13.5.1 启用慢查询日志

默认情况下，慢查询日志功能是关闭的。通过my.cnf或者my.ini文件的log-slow-queries选项可以开启慢查询日志。通过long_query_time选项来设置时间值，时间以秒为单位。如果查询时间超过了这个时间值，这个查询语句将被记录到慢查询日志。

将log-slow-queries选项和long_query_time选项加入到my.ini文件的[mysqld]组中。

在Windows操作系统中形式如下：

# my.ini
[mysqld]
log-slow-queries [=path\ [filename] ]
long_query_time=n

13.5.2 操作慢查询日志

执行时间超过指定时间的查询语句会被记录到慢查询日志中。如果用户希望查询哪些查询语句的执行效率低，可以从慢查询日志中获得想要的信息。慢查询日志也是以文本文件的形式存储的。可以使用普通的文本文件查看工具来查看。

【例13.8】查看慢查询日志。使用文本编辑器打开数据目录下的PGIG1MIWMYPOFBS -slow.log文件，文件部分如下：

\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe, Version: 5.7.17-log (MySQL Community Server (GPL)). started with:

13.5.3 删除慢查询日志

慢查询日志的删除方法与通用查询日志的删除方法是一样的。可以使用mysqladmin命令来删除。也可以使用手工方式来删除。

mysqladmin命令的语法如下：

mysqladmin -u root -p flush-logs

执行该命令后，命令行会提示输入密码。输入正确密码后，将执行删除操作。新的慢查询日志会直接覆盖旧的查询日志，不需要再手动删除了。数据库管理员也可以手工删除慢查询日志。删除之后需要重新启动MySQL服务。重启之后就会生成新的慢查询日志。如果希望备份旧的慢查询日志文件，可以将旧的日志文件改名。然后重启MySQL服务。

posted @ 2024-05-14 22:30  WNAG_zw  阅读(17)  评论(0)  编辑  收藏  举报