MySQL 日志管理

MySQL所支持的日志文件

       每个用户都知道 , 日志文件用来记录其每天的行为 . MySQL软件所支持的日志文件 , 也用来记录该软件运行的各种信息 .例如 , 当用户root登录到MySQL服务器 , 就hi在日志文件里记录该用户的登录时间 , 操作记录等.当MySQL服务器运行时出错 , 出错因袭就会被记录到日志文件里.
       日志操作是数据库维护中最重要的手段之一 , 由于日志文件会记录MySQL服务器的各种信息 , 所以当MySQL服务器遭到以外的损害时 , 不仅可以通过日志文件来查看出错的原因 , 而且还可以通过日志文件进行数据恢复 .
       在MySQL中 , 除二进制文件外 , 其他的日志文件都是文本文件 .默认情况下 , MySQL软件只会启动错误日记文件 , 而其他文件则需要手动启动才可以被启动.

如下是各类文件的作用:

• 二进制日志 :该日志文件会以二进制形式记录数据库的各种操作 , 但是却不记录查询语句.
• 错误日志 : 该日志文件会记录MySQL服务器启动 , 关闭和运行时出错等信息.
• 通用查询日志 : 该日志记录MySQL服务器的启动和关闭信息 , 客户端的连接信息 , 更新数据记录SQL语句和查询数据记录SQL语句.
• 慢查询日志 : 记录执行时间超过指定时间的各种操作 , 通过工具分析慢查询日志可以定位MySQL服务器性能瓶颈所在.
  查看帮助文档可以发现 , 使用日志有优点也有缺点 . 启动日志后 , 虽然可以实现对MySQL服务器进行维护 , 但是会降低MySQL软件的执行速度 . 因此是否启动日志 , 启动什么类型日志要根据具体应用来决定.

二进制日志

       二进制文件(BinLog)是MySQL软件非常重要的日志之一 , 其详细记录了数据库的变化情况 , 即SQL语句中的DDL和DML语句 , 但是不包含数据记录查询操作 . 通过二进制日志文件 , 可以详细了解MySQL数据库中进行了哪些操作.

启动二进制日志

提问:为什么要启动二进制日志?
       如果MySQL数据库意外停止 , 可以通过二进制日志文件来查看用户执行了哪些操作 , 对数据库服务器文件做了哪些修改 , 然后根据二进制日志文件中记录来恢复数据库服务器.
       在默认情况下 ,二进制日志是关闭的 , 如果想启动二进制日志 , 可以通过设置MySQL服务器的配置文件my.ini来实现 , 具体如下:

[mysql]
log-bin[=dir\[filename]]

       在上述语句中，参数dir用来指定二进制文件的存储路径; 参数flename用来指定二进制文件的文件名，具体格式为filename number,其中number的格式为000001,000002 , 000003 等。
        在具体启动二进制日志时，如果没有设置参数 dir 和 flename,二进制日志文件将使用默认名字主机名-bin.number,保存到默认目录------数据库数据文件里。

注意:每次重启MySQL服务器都会生成一个新的二进制日志文件，这些日志文件的文件名里，filename部分不会改变，但是number的值会不断递增。

       在MySQL软件中，与二进制日志相关的文件除了保存内容的filename number文件外，还有一个关于二进制日志文件列表的文件filename index.
例如:修改MySQL的配置文件my.ini , 启动二进制文件
1)打开文件my.ini配置文件 , 在[mysqld]组里添加:

[mysqld]
log-bin

这是一个隐藏文件 , 需要打开显示隐藏文件

一直往下拉 , 你就会看到"#log-bin" , 把"#"去掉 , 然后保存 , 重新启动服务器

重启之后的效果图:(重启是重启服务 , 不是重启客户端)

       根据执行结果发现 , 如果没有为二进制日志配置文件目录和文件名 , 将在默认路径中(win10将文件夹隐藏了)创建文件.

在linux上需要到/etc/mysql/mysql.conf.d 中 , 在mysql.cnf中修改
2) 在启动二进制日志时，二进制日志文件最好不要与数据库的数据文件放在同一磁盘上.这样当存放数据的磁盘遭受到破坏后，即可通过二进制日志文件进行恢复.打开文件my.ini配置文件，在[mysqld]组里添加相应语句，具体内容如下:

[mysql]
log-bin=D://A/a

如图:

重新启动MySQL , 就会发现 在指定的路径上保存了二进制日志

查看二进制日志

       如果要查看二进制日志 , 需要通过执行命令mysqlbinlog来实现 , 如果直接打开该文件 , 将会显示乱码

具体语法 (在linu中)
查看是否开启二进制日志

show variables like 'log_bin'

如果binlog没有开启，可以通过set sql_log_bin=1命令来启用;如果想停用binlog,可以使用set sql_log_bin=0。

mysqlbinlog命令 将在当前文件夹下査找指定的二进制日志。因此需要在二进制日志 filename.number所在的目录下运行该命令，否则将会找不到指定的二进制日志文件。
所以进入保存二进制日志的目录(这里需要管理员权限): /var/lib/mysql , 查看已经保存的二进制日志
然后使用mysqlbinlog , 查看二进制日志的内容

mysqlbinlog filename.number

下面还有很多 , 这里就省略了.

在MySQL端中:

可以查看自己二进制日志的名称

show binary logs;

可以查看已生成的二进制日志

show binlog events;

查看指定binlog文件的内容

show binlog events in 'binlog.000001'

停止二进制日志

       在MySQL服务器的配置文件my.ini里，如果在[mysqld]组添加了log-bin 内容，MySQL服务器将会一直开启二进制日志功能。即用户对MySQL服务器的各种操作将记录到二进制日志里。如果想停止二进制日志功能，只需删除[mysqld]组里的log-bin内容即可。

       在具体操作MySQL服务器时，有时某些操作是不需要记录到二进制日志文件里，这时如果先删除文件my.ini 中[mysqld]组里log-bin 内容，那么执行完这些操作后，还需要重新在[mysqld]组添加log-bin内容。
为了解决上述问题 , MySQL专门提供了一个命令SET , 实现暂停二进制日志 , 具体内容:

SET SQL_LOG_BIN=0

SET SQL_LOG_BIN=0

       在上述命令中 , 当设置SET SQL_LOG_BIN的值为0时 , 表示赞停二进制日志功能 , 当设置SET SQL_LOG_BIN的值为1时 , 表示重新开启二进制日志功能.
注:只有拥有SUPER权限的用户 , 才可以执行SET语句.

删除二进制日志

       如果需要删除进制日志，需要通过执行命令RESET MASTER、PURGE MASTER LOGS TO和PURGE MASTER LOGS BEFORE来实现，具体语法形式分别如下:

reset master;//删除master的二进制日志

purge master logs before 'yyyy-mm-dd hh:MM:ss';  //删除指定日期以前的日志索引中二进制日志文件

purge master logs to 'filename.number';   //删除编号小于number的所有二进制日志文件

如果是在windows端上, 直接删除文件即可.

错误日志

错误日志也是MySQL中非常重要的日志之一，其详细记录了MySQL服务器的开启、关闭和错误信息 .

启动错误日志

       在MySQL数据库服务器里，错误日志默认是开启的，同时该种类型的日志也是无法被禁止。错误日志一般存放在MySQL服务器的数据文件夹下(C:\ProgramData\MySQL\MySQL Server 5.7\Data), 错误日志文件通常的名称格式为hostname.err (cjgong.er),其中参数hostname表示MySQL服务器的主机名

如果想修改错误日志的存放目录 ,可以通过my.ini来实现(和二进制日志基本相同)

[mysqld]
error-bin[=dir\[filename]]

       参数dir用来指定错误日志的存储路径; 参数flename用来指定错误日志的文件名 .如果没有在文件my.ini里设置错误日志的相关信息 , 错误日志文件将使用默认名字 主机名.error , 保存到默认目录-----数据库文件里.

查看错误日志

       错误日志是以文本文件的形式存储内容 , 所以可以直接使用普通文本工具来查看该类型日志的内容.
如图所示:

删除错误日志

如果需要删除错误日志 , 需要执行命令来实现 , 具体语法形式如下 :

mysqladmin -u root -p flush-logs

       执行上述命令 , MySQL服务器首先会穿件一个新的错误日志 , 然后将旧的错误日志更名为filename.err-old.如果数据库管理员认为filename.err-old文件没有任何用处 , 即没有任何存在的理由 , 可以直接删除.

通用查询日志

       通用查询日志主要用来记录用户关于MySQL服务器的所有操作，包含MySQL服务器的启动和关闭信息、客户端的连接信息、更新数据,记录SQL语句和查询数据记录SQL语句。由于该日志记录了客户端连接MySQL的所有请求，如当前实例访问量较大，此日志会急剧增加，会抢占系统IO导致影响MySQL的性能，一般建议关闭此日志，需要时可以通过设置环境变量打开。

启动通用查询日志

       在默认情况下，通用查询日志是关闭的，如果想启动通用查询日志，可以通过设置MySQL服务器的配置文件my.ini来实现，具体内容如下:

[mysqld]
log [=dir\[filename]]

       在上述语句中，参数dir用来指定通用查询日志文件的存储路径;参数filename用来指定通用查询日志的文件名，具体格式为filename.log. 在具体启动通用查询日志时，如果没有设置参数dir和filename,日志文件将使用默认名字 主机名.log , 保存到默认目录一数据库数据文件里 .
       以上方法是通过配置文件指定开启了通用查询日志，此时需要重启MySQL服务器才可使设置生效，除此之外，还有一种方法通过设置MySQL的环境变量进行动态的控制通用查询日志的开启与关闭。通过设置环境变量generallog进行通用查询日志的动态控制，而不需要重启MySQL服务器，操作示例如下:

 set global general_log = on; //on为开启状态 (off为关闭状态)

 show variables like '%general_log%'\G    //查看相关环境变量

       参数general_log 用于动态的控制通用查询日志的开启与关闭，on表示开启通用查询日志，general_log_file 变量指定了通用查询日志文件所处的位置 .

查看通用查询日志

       由于通用查询日志是以文本文件的形式存储内容，所以可以直接使用普通文本工具来查看该类型日志内容 .

停止通用查询日志

       通用查询日志启动后，可以通过两种方法停止该日志，一种是通过设置MysQL服务器的配置文件my.ini来实现，具体内容如下:

[mysqld]
#log [=dir\[filename]]

       在上述语句中，通过将相关配置注释掉，然后重启MsOL服务器 , 则可以停止通用查询日志.
       上述方法需要重启MySQL服务器，这在某些场景比如有业务量访通过设置MySQL可的情况下是不允许的，这时可以通过另一种方法来动态地控制通用查询日志的开启与关闭 .通过设置mySQL 的环境变量general_log 为关闭状态可以停止该日志，操作示例如下:

 set global general_log = off;

 show variables like '%general_log%'\G    //查看相关环境变量

       参数general_log 置为 off 用于动态的控制通用查询日志为关闭状态，general_log_file 变量指定了通用查询日志文件所处的位置 .

删除通用查询日志

如果需要删除通用查询日志 ,需要通过执行命令来实现 , 具体语法形式如下:

 mysqladmin -u root -p flush-logs

       执行上述命令 ,如果为删除查询日志 , MySOL服务器会创建一个新的查询日志，覆盖旧的查询日志 . 如果需要备份旧的日志文件，则必须先将旧的日志文件复制出来或者改名，然后再次执行命令 .
注意 : 在具体删除查询日志时，一旦执行mysqladmin命令，就会先删除旧的通用查询日志文件，然后再创建一个新的查询日志 .

慢查询日志

       慢查询主要用来记录执行时间超过指定时间的查向语句。通过查看该类型日志文件，可以查找到哪些查询语句的执行效率低，以便找出MySQL服务器的性能瓶颈从而进行优化。

启动慢查询日志

       在默认情况下，慢查询日志是关闭的，如果想启动慢查询日志，可以通过设置MySQL服务器的配置文件my.ini来实现，具体内容如下:

[mysqld]
log-slow-queries [=dir\[filename]]
long_queery_time=n

       在上述语句中，参数dir用来指定慢查询日志文件的存储路径 ; 参数flename 用来指定慢查询日志的文件名，具体格式为 filename-slow.log . 在具体启动慢查询日志时，如果没有设置参数 dir 和filename ,慢查询日志文件将使用默认名字 主机名-slow.log ,保存到默认目录一数据库 数据文件里。参数n用来设置时间，该值的单位为秒 . 如果没有设置long query_ time 选项，默认时间为10秒 .
       通过上述方法开启了MySQL服务器的慢查询日志，此时需要重启MySQL服务器以便使设置生效。这在某些场景比如有业务量访问的情况下是不允许的，这时可以通过另外种方法动态的控制慢查询日志的开启与停止。通过设置MySQL的环境变量slow_query_log 为开启状态，操作示例如下:

 set global slow_query_log=on; 
 //设置环境变量slow_query_log 进行慢查询日志的动态控制 , on表示开启状态(off为关闭状态)
 set global long_query_time=3;
 //设置慢查询日志最大允许时间 , 单位为秒

       注意:在通过环境变量开启慢查询日志时，slow_query_ log 针对当前登录的连接实时生效，而long_query_time 针对当前连接并不生效，是针对新增的连接有效，如需启用修改后的变量值，需要重新连接MySQL .

查看慢查询日志

       由于慢查询日志是以文本文件的形式存储内容，所以可以直接使用普通文本工具来查看该类型日志内容 .

分析慢查询的日志

       如果想了解执行时间超过指定时间的查询语句，以便定位MySQL服务器的性能瓶颈，可以分析慢查询日志 . MySQL提供了对应的工具用于分析MySQL慢查询日志的内容，比如，查看慢查询次数最多的语句或者慢查询时间最长的语句 , 对应的工具为mysqldumpslow.pl , 常用参数如下:

“-s” 为分析慢查询日志时指定排序参数 , 可选的有:“al” 表示平均锁定时间，“ar” 表示平均返回记录数，"at “表示平均查询时间。

“-t“参数表示只显示指定的行数。

       注意 : 在分析慢查询日志时 , mysqldumpslow.pl 为perl语言编写的脚本 , 执行该脚本需要对应的perl语言环境 .

停止慢查询日志

如需停止慢查询日志，可以通过设置MySQL服务器的配置文件my.ini来实现，具体内容如下:

[mysqld]
#log-slow-queries [=dir\[filename]]
#long_queery_time=n

       通过将对应配置注释掉可以将MySQL慢查询停止.此时需要重启MySQL服务器，这在某些场景比如有业务量访问的情况下是不允许的 , 这时可以通过另外种方法动态的控制慢查询日志的开启与停止 . 通过设置MySOL的环境变量slow_query_log为会关闭状态可以停止该日志 , 操作实例如下 :

 set global slow_query_log=off; 
 //设置环境变量slow_query_log 进行慢查询日志的动态控制 , off表示开启状态

删除慢查询日志

       如果需要删除慢查询日志，可以通过Windows的直接将慢查询日志文件删除，然后使用以下命令重新创建对应文件。具体语法形式分别如下:

mysqladmin -u root -p flush-logs

       注意 : 执行上述命令，MySQL 服务器会创建一个新的慢查询日志， 此时慢查询日志内容为空。如果需要备份旧的日志文件，则必须先将旧的日志文件复制出来或者改名，然后再次执行命令mysqladmin。