mysql 日志

       MySQL日志记录了MySQL数据库日常操作和错误信息。MySQL有不同类型的日志文件(各自存储了不同类型的日志)，从日志当中可以查询到 MySQL数据库的运行情况、用户操作、错误信息等，可以为MySQL管理和优化提供必要的信息。对于MySQL 的管理工作而言，这些日志文件是不可缺少的。

1，日志简介

        MySQL日志主要分为4类，使用这些日志文件，可以查看MySQL内部发生的事情。这4类日志分别是：

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

        查询日志：记录建立的客户端连接和执行的语句。

        二进制日志：记录所有更改数据的语句，可以用于数据复制。

        慢查询日志：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询默认情况下，所有日志创建于MySQL数据目录中。通过刷新日志，可以强制MySQL关闭和重新打开日志文件(或者在某些情况下切换到一个新的日志)。当执行一个FLUSH LOGS语句或执行MySQLadmin flush-logs或 MySQLadmin refresh时，将刷新日志。

        如果正使用MySQL复制功能，在复制服务器上可以维护更多日志文件，这种日志称为接替日志。

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

2，二进制日志

        二进制日志主要记录MySQL数据库的变化。二进制日志以一种有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE)的语句。语句以“事件”的形式保存，描述数据更改。

        二进制日志还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果想要记录所有语句(例如，为了识别有问题的查询)，需要使用一般查询日志。使用二进制日志的主要目的是最大可能地恢复数据库,因为二进制日志包含备份后进行的所有更新。本节将介绍二进制日志相关的内容。

1，启动和设置二进制日志

        默认情况下，二进制日志是关闭的，可以通过修改MySQL的配置文件来启动和设置二进制日志。

my.ini 中[MySQLd]组下面有几个设置是关于二进制日志的：

1og-bin [-path/[filename] ]
expire logs_days = 10
max_binlog_size = 100M

        log-bin定义开启二进制日志；path表明日志文件所在的目录路径；filename指定了日志文件的名称，如文件的全名为filename.000001，filename.000002等，除了上述文件之外，还有一个名称为filename.index 的文件，文件内容为所有日志的清单，可以使用记事本打开该文件。

        expire_logs_days定义了MySQL清除过期日志的时间，即二进制日志自动删除的天数。默认值为0，表示“没有自动删除”。当MySQL启动或刷新二进制日志时可能删除该文件。

        max_binlog_size定义了单个文件的大小限制，如果二进制日志写入的内容大小超出给定值，日志就会发生滚动(关闭当前文件，重新打开一个新的日志文件)。不能将该变量设置为大于1GB或小于4096B。默认值是1GB。

        如果正在使用大的事务，二进制日志文件大小还可能会超过max_binlog_size定义的大小。在my.ini 配置文件中的[MySQLd]组下，添加以下几个参数与参数值(PS：先目录下添加一个logs目录)：

log-bin=E:/tool/mysql/mysql-5.7/logs/log
server-id=123456 
expire_logs_days=10
max_binlog_size=100M

        添加完毕之后，关闭并重新启动MySQL服务进程，即可打开二进制日志，然后可以通过SHOW VARIABLES语句来查询日志设置。

        PS：mysql5.7版本开始要加个server-id。随机指定一个不能和其他集群中机器重名的字符串，如果只有一台机器或是本机，那就可以随便指定了

        【例1】使用SHOW VARIABLES 语句查询日志设置，执行的语句及结果如下：

SHOW VARIABLES LIKE 'log_%';

     

   通过上面的查询结果可以看出，log_bin变量的值为ON，表明二进制日志已经打开。MySQL重新启动之后，可以在自己机器上的 MySQL数据文件夹下面看到新生成的后缀为.000001和.index的两个文件，文件名称为默认主机名称。例如，在机器上的文件名称为Kevin-bin.000001和 Kevin-bin.index。

        如果想改变日志文件的目录和名称，要先提交建好目录，比如建立binlog目录可以对my.ini中的log-bin参数修改如下：

log-bin=E:/tool/mysql/mysql-5.7/binlog/binlog

        关闭并重新启动MySQL服务之后，新的二进制日志文件将出现在E:/tool/mysql/mysql-5.7/binlog 文件夹下面，名称为 binlog.000001和 binlog.index。可以根据情况灵活设置。

        提示：数据库文件最好不要与日志文件放在同一个磁盘上，这样，当数据库文件所在的磁盘发生故障时，可以使用日志文件恢复数据。

2，查看二进制日志

        MySQL二进制日志存储了所有的变更信息,MySQL二进制日志是经常用到的。当MySQL创建二进制日志文件时，首先创建一个以“filename”为名称，以“.index”为后缀的文件；再创建一个以“filename”为名称，以“.000001”为后缀的文件。当MySQL服务重新启动一次，以“.000001”为后缀的文件会增加一个，并且后缀名加1递增;如果日志长度超过了max_binlog_size的上限（默认是1GB）也会创建一个新的日志文件。

        show binary logs语句可以查看当前的二进制日志文件个数及其文件名。MySQL二进制日志并不能直接查看，如果要查看日志内容，可以通过MySQLbinlog命令查看。

        【例2】使用SHOW BINARY LOGS查看二进制日志文件个数及文件名，执行命令及结果如下：

SHOW BINARY LOGS;

                可以看到，当前只有一个二进制日志文件。日志文件的个数与MySQL服务启动的次数相同。每启动一次MySQL服务，将会产生一个新的日志文件。

        【例3】使用MySQLbinlog查看二进制日志，执行命令及结果如下：

win + r 然后输入 cmd 再输入口令

mysqlbinlog E:/tool/mysql/mysql-5.7/binlog/binlog.000001

        这是一个简单的日志文件，日志中记录了一些用户的操作。从文件内容中可以看到，用户对fruits表进行了更新操作语句为

UPDATE fruits set f_price = 5.00 
WHERE f_id= 'al';

3，删除二进制日志

        MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法：RESET MASTER删除所有的二进制日志文件；PURGE MASTER LOGS只删除部分二进制日志文件。

1，使用RESET MASTER语句删除所有二进制日志文件

        RESETMASTER语法如下：

RESET MASTER;

        执行完该语句后，所有二进制日志将被删除，MySQL会重新创建二进制日志，新的日志文件扩展名将重新从000001开始编号。

2，使用PURGE MASTER LOGS语句删除指定日志文件

PURGE MASTER LOGS语法如下：

PURGE {MASTER | BINARY} LOGS TO 'log_name' 
PURGE {MASTER | BINARY} LOGS BEFORE 'date'

        第1种方法指定文件名，执行该命令将删除文件名编号比指定文件名编号小的所有日志文件。

        第2种方法指定日期，执行该命令将删除指定日期以前的所有日志文件。

        【例4】使用PURGE MASTER LOGS删除创建时间比 binlog.000003早的所有日志文件。首先，为了演示语句操作过程，准备多个日志文件，可以对MySQL服务进行多次重新启动。例如这里有10个日志文件，如下：

SHOW binary Logs;

执行删除命令如下：

PURGE MASTER LOGS To "binlog.000003";

执行完成后，使用SHOW binary logs 语句查看二进制日志：

SHOW binary logs;

        可以看到，binlog.000001、binlog.000002两个日志文件被删除了。

        【例5】使用PURGE MASTER LOGS删除2024年1月30日前创建的所有日志文件，执行命令及结果如下：

PURGE MASTER LOGS BEFORE '20240130';

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

        使用MySQLbinlog可以查看指定日志的创建时间，如前面的【例3】所示，部分日志内容如下：

mysqlbinlog E:/tool/mysql/mysql-5.7/binlog/binlog.000001

其中240130为日志创建的时间，即2024年1月30日。

4，使用二进制日志恢复数据库

        如果MySQL 服务器启用了二进制日志，在数据库出现意外丢失数据时，可以使用MySQLbinlog 工具从指定的时间点开始（例如，最后一次备份）直到现在，或另一个指定的时间点的日志中恢复数据。

        要想从二进制日志恢复数据，需要知道当前二进制日志文件的路径和文件名。一般可以从配置文件（即my.cnf或者my.ini，文件名取决于MySQL服务器的操作系统）中找到路径。

        MySQLbinlog恢复数据的语法如下：

mysqlbinlog [option] filename | mysql -uuser -ppass

        option是一些可选的选项，filename是日志文件名。比较重要的两对option参数是--start-date、--stop-date和--start-position、--stop-position。--start-date、--stop-date可以指定恢复数据库的起始时间点和结束时间点。--start-position、--stop-position可以指定恢复数据的开始位置和结束位置。

        【例6】使用MySQLbinlog恢复MySQL数据库到2024年1月30日15:27:48时的状态执行命令及结果如下：

mysqlbinlog --stop-date-"2024-01-30 15:27:48"
E:\tool\mysql\mysql-5.7\binlog\binlog.000008 | mysql -uuser -ppass

        该命令执行成功后，会根据binlog.000008日志文件恢复2024年01月30日15:27:48 以前的所有操作。这种方法对于意外操作非常有效，比如因操作不当误删了数据表。

5，暂时停止二进制日志功能

        如果在 MySQL 的配置文件中配置启动了二进制日志，MySQL会一直记录二进制日志。修改配置文件，可以停止二进制日志，但是需要重启MySQL数据库。MySQL提供了暂时停止二进制日志的功能。通过SET SQL_LOG_BIN语句可以使用MySQL暂停或者启动二进制日志。

SET SQL_LOG_BIN的语法如下：

SET sql_log_bin = {0|1}

执行如下语句将暂停记录二进制日志：

 SET sql_1og_bin = 0;

执行如下语句将恢复记录二进制日志：

 SET sql_1og_bin = 1;

3，错误曰志

        错误日志文件包含了当MySQLd启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。在 MySQL 中，错误日志也是非常有用的，MySQL会将启动和停止数据库信息以及一些错误信息记录到错误日志文件中。

1，启动和设置错误日志

        在默认情况下，错误日志会记录到数据库的数据目录下。如果没有在配置文件中指定文件名，则文件名默认为hostname.err。例如，MySQL所在的服务器主机名为MySQL-db，记录错误信息的文件名为MySQL-db.err。如果执行了FLUSH LOGS，错误日志文件会重新加载。

        错误日志的启动和停止以及指定日志文件名，都可以通过修改my.ini(或者my.cnf)来配置。错误日志的配置项是log-error。在[MySQLd]下配置log-error，则启动错误日志。如果需要指定文件名，则配置项如下：

log-error = [path /[file_name]]

        path 为日志文件所在的目录路径，file_name为日志文件名。修改配置项后，需要重启MySQL服务以生效。

2，查看错误日志

        通过错误日志可以监视系统的运行状态，便于及时发现故障、修复故障。MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看MySQL错误日志。

        如果不知道日志文件的存储路径，可以使用SHOW VARIABLES语句查询错误日志的存储路径。SHOw VARIABLES语句如下：

SHOW VARIABLES LIKE 'log_error';

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

首先，通过SHow VARIABLES语句查询错误日志的存储路径和文件名：

SHOW VARIABLES LIKE 'log_error';

        可以看到错误的文件是Kevin.err，位于MySQL 默认的数据目录下，使用记事本打开该文件，可以看到MySQL的错误日志：

        以上是错误日志文件的一部分，这里面记载了系统的一些错误。

3，删除错误日志

        MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以直接删除。

        对于MySQL 5.5.7 以前的版本，flush logs可以将错误日志文件重命名为filename.err_old,并创建新的日志文件。但是从MySQL 5.5.7开始，flush logs只是重新打开日志文件，并不做日志备份和创建的操作。如果日志文件不存在，MySQL启动或者执行flush logs时会创建新的日志文件。

        在运行状态下删除错误日志文件后，MySQL并不会自动创建日志文件。flush logs在重新加载日志的时候，如果文件不存在，则会自动创建。所以在删除错误日志之后，如果需要重建日志文件,需要在服务器端执行以下命令：

mysqladmin -u root -p flush-logs

        或者在客户端登录MySQL数据库,执行flush logs 语句：

flush logs;

4，通用查询日志

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

1，启动和设置通用查询日志

        MySQL 服务器默认情况下并没有开启通用查询日志。如果需要通用查询日志，可以通过修改my.ini(或my.cnf》配置文件来开启。在 my.ini(或my.cnf)的[MySQLd]组下加入 log选项，形式如下：

log [=path/ [filename]]

        path 为日志文件所在目录路径，file_name为日志文件名。如果不指定目录和文件名，通用查询日志将默认存储在MySQL数据目录中的hostname.log文件中。hostname是 MySQL数据库的主机名。这里在[MySQLd]下面增加选项log，后面不指定参数值。格式如下：

log

2，查看通用查询日志

        通用查询日志中记录了用户的所有操作。通过查看通用查询日志,可以了解用户对MySQL进行的操作。通用查询日志是以文本文件的形式存储在文件系统中的，可以使用文本编辑器直接打开通用日志文件进行查看，Windows下可以使用记事本，Linux下可以使用vim、gedit等。

【例8】使用记事本查看MySQL通用查询日志。

        使用记事本打开mysql安装目录下的datal目录下的my-PC.log，可以看到如下内容：

        上面是打开的通用查询日志的一部分，可以看到MySQL启动信息和用户root连接服务器与执行查询语句的记录。

3，删除通用查询日志

        通用查询日志是以文本文件的形式存储在文件系统中的。通用查询日志记录用户的所有操作，因此在用户查询、更新频繁的情况下，通用查询日志会增长得很快。数据库管理员可以定期删除比较早的通用日志，以节省磁盘空间。将介绍通用日志的删除方法。

        可以用直接删除日志文件的方式删除通用查询日志。要重新建立新的日志文件，可使用语句

MySQLadmin -flush logs

        【例9】直接删除MySQL通用查询日志。执行步骤如下：

        步骤1：查看my.ini(或者my.cnf)通用查询日志的文件目录，在my.ini中通用查询日志配置如下。

log

        可以看到,配置文件中没有指定日志文件名和存储目录,通用查询日志文件存储在MySQL默认数据目录中。

        步骤2：在数据目录中找到日志文件所在目录C：Documents and SettingsAll Users.WINDOws：Application DataMySQLMySQL Server 5.7 datal，删除该后缀为.err的文件。

步骤3：通过MySQLadmin -flush logs命令建立新的日志文件，执行命令如下。

C：\>mysqladmin -u root p flush-1ogs

        步骤4：执行完该命令，可以看到，C：Documents and SettingsAll Users.WINDOWS ApplicationData\MySQLMySQL Server 5.7data\目录中已经建立了新的日志文件Kevin.logo

5，慢查询日志

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

1，启动和设置慢查询日志

        MySQL中慢查询日志默认是关闭的，可以通过配置文件 my.ini或者 my.cnf中的log-slow-queries选项打开,也可以在MySQL服务启动的时候使用--log-slow-queries[=file_name]启动慢查询日志。启动慢查询日志时，需要在my.ini或者my.cnf文件中配置long_query_time选项指定记录阙值，如果某条查询语句的查询时间超过了这个值，这个查询过程将被记录到慢查询日志文件中。

在my.ini或者my.cnf开启慢查询日志的配置如下：

log-slow-queries[-path/ [filename]]
long_query_time = n

        path为日志文件所在目录路径，filename为日志文件名。如果不指定目录和文件名称，默认存储在数据目录中，文件为hostname-slow.log，hostname是 MySQL服务器的主机名。参数n是时间值，单位是秒。如果没有设置long_query_time选项，默认时间为10秒。

2，查看慢查询日志

        MySQL 的慢查询日志是以文本形式存储的，可以直接使用文本编辑器查看。在慢查询日志中，记录着执行时间较长的查询语句，用户可以从慢查询日志中获取执行效率较低的查询语句，为查询优化提供重要的依据。

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

        可以看到，这里记录了一条慢查询日志。执行该条查询语句的账户是root@localhost，查询时间是 136.500000秒，查询语句是

SELECT BENCHMARK(100000000,PASSWORD('newpwd')); 

        该语句的查询时间大大超过了默认值10秒钟，因此被记录在慢查询日志文件中。

        借助慢查询日志分析工具，可以更加方便地分析慢查询语句。比较著名的慢查询工具MySQL Dump Slow、MySQL SLA、MySQL Log Filter、MyProfi。关于这些慢查询分析工具的用法，可以参考相关软件的帮助文档。

3，删除慢查询日志

        和通用查询日志一样，慢查询日志也可以直接删除。删除后在不重启服务器的情况下，需要执行MySQLadmin -u root-p flush-logs重新生成日志文件，或者在客户端登录到服务器执行flush logs语句重建日志文件。

6，疑问解答

疑问1：平时应该打开哪些日志?

        日志既会影响MySQL的性能，又会占用大量磁盘空间。因此，如果不必要，应尽可能少地开启日志。根据不同的使用环境，可以考虑开启不同的日志。例如，在开发环境中优化查询效率低的语句，可以开启慢查询日志；如果需要记录用户的所有查询操作，可以开启通用查询日志;如果需要记录数据的变更，可以开启二进制日志；错误日志是默认开启的。

疑问2∶如何使用二进制日志?

        二进制日志主要用来记录数据变更。如果需要记录数据库的变化，可以开启二进制日志。基于二进制日志的特性，不仅可以用来进行数据恢复，还可用于数据复制。在数据库定期备份的情况下，如果出现数据丢失，可以先用备份恢复大部分数据，然后使用二进制日志恢复最近备份后变更的数据。在双机热备情况下，可以使用MySQL的二进制日志记录数据的变更，然后将变更部分复制到备份服务器上。

疑问3∶如何使用慢查询日志?

        慢查询日志主要用来记录查询时间较长的日志。在开发环境下，可以开启慢查询日志来记录查询时间较长的查询语句，然后对这些语句进行优化。通过配置long_query_time的值，可以灵活地掌握不同程度的慢查询语句。

总结：

        平时用得多一些的就是慢查询日志，其它的，感觉比较少，可能主要是没接触吧，后续有碰到，再详解。

        上一篇： 《mysql 性能优化》

        下一篇： 《myql 基础学习目录》