MySQL 日志管理

概述

日志文件记录 MySQL 数据库运行期间发生的变化,当数据库遭到意外的损害时,可以通过日志文件查询出错原因,并进件数据恢复

MySQL 日志文件可以分成以下几类:

  • 二进制日志:记录所有更改数据的语句,可以用于主从复制
  • 错误日志:记录 MySQL 服务出现的问题
  • 查询日志:记录建立的客户端连接和执行的语句
  • 慢查询语句:记录所有执行时间超过 long_query_time 的所有查询或不适用索引的查询
  • 中继日志:记录复制时从服务器从主服务器收到的数据该表
  • 数据定义语句日志:记录数据定义语句执行的元数据操作

操作二进制日志

1. 启动二进制日志

默认情况下,二进制文件是关闭的,可以通过以下 SQL 语句来查询二进制开关

SHOW VARIABLES LIKE 'log_bin%';

修改 my.cnf 或 my.ini 文件,以 Windows 系统为例,打开 MySQL 目录下的 my.ini 文件,将 log-bin 加入 [mysqld] 组

[mysqld]
log-bin
server-id=201811
expire_logs_days=10
max_binding_size=100M

重新启动 MySQL 服务,再执行上述的 SQL 语句,看到 log_bin 变量的值为 ON,表明二进制日志已经打开

如果想改变日志文件的目录和名称,可以对 my.ini 中的 log_bin 参数修改如下:

[mysqld]
log-bin="d:\mysql\logs"

2. 查看二进制日志

当 MySQL 创建二进制日志文件时,先创建一个 binlog.000001 文件,以后 MySQL 服务重新启动一次,以 .000001 为后缀的文件就会增加一个,后缀名按 1 递增。如果日志长度超过了 max_binlog_size 的上限,就会创建一个新的日志文件

SHOW BINARY LOGS;

二进制日志文件不能直接查看,可以使用 mysqlbinlog 命令查看

mysqlbinlog [日志文件路径]

3. 使用二进制日志恢复数据库

如果 MySQL 服务器启用了二进制日志,在数据库出现意外丢失数据时,可以使用 MySQLbinlog 工具从指定的时间点开始恢复数据

mysqlbinlog [option] filename|mysql -uroot -p;
  • option 是一些可选的选项:
    • –start-date:指定恢复数据库的起始时间点
    • –stop-date:指定恢复数据库的结束时间点
    • –start-position:指定恢复数据库的起始位置
    • –stop-position:指定恢复数据库的结束位置
  • filename 是日志文件名

4. 暂停二进制日志

在配置文件设置了 log-bin 选项以后,MySQL 服务会一直开启二进制日志功能,删除该选项就可以停止二进制日志功能

MySQL 提供暂时停止二进制日志功能的语句,如果用户不希望自己执行的某些 SQL 语句记录在二进制日志,可以使用 SET 语句来暂停二进制日志功能,0 表示停止,1 表示恢复

SET SQL_LOG_BIN={0|1}

5. 删除二进制日志

MySQL 的二进制文件可以配置自动删除,同时 MySQL 也提供手动删除二进制文件的方法

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

# 删除创建时间比指定日志文件早的日志文件,以后缀为判断标准
PURGE {MASTER|BINARY} LOGS TO 'log_name'
# 删除指定时间前的日志文件
PURGE {MASTER|BINARY} LOGS BEFORE 'date'

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

RESET MASTER

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

操作错误日志

1. 启动错误日志

在 MySQL 数据库中,错误日志是默认开启的,并且无法被禁止。错误日志文件的名称默认为 hostname.err,其中 hostname 表示 MySQL 服务器的主机名

2. 查看错误日志

SHOW VARIABLES LIKE 'log_err%';

3. 删除错误日志

MySQL 的错误日志是以文本形式存储的,可以直接删除

通用查询日志

1. 启动通用查询日志

MySQL 默认没有开启通用查询日志,可以通过修改 my.cnf 或 my.ini 配置文件来设置

[mysqld]
general_log=ON
general_log_file=[path[filename]]

重启 MySQL,在 MySQL 的 data 目录下生成新的通用查询日志

从 5.1.6 以后开始,MySQL 支持全局级别的动态修改,不需要重启

SET GLOBAL general_log=on;
SET GLOBAL general_log=off;
SET GLOBAL general_log_file='path/filename';

2. 查看通用查询日志

SHOW VARIABLES LIKE 'general_log%';

3. 停止通用查询日志

MySQL 停止通用查询日志功能有两种方式,一种是在配置文件把 general_log 值设置为 off,再重启 MySQL,另一种是使用 SET 语句设置,即动态修改

4. 删除通用查询日志

通用查询日志可以手动删除,也可以使用命令删除

mysqladmin -uroot -p flush-logs

此种方式新的通用查询日志会直接覆盖旧日志

慢查询日志

1. 启动慢查询日志

MySQL 慢查询日志默认是关闭的,可以通过修改 my.cnf 或 my.ini 配置文件来设置

[mysqld]
long_query_time=n
slow_query_log=ON
slow_query_log_file=[path[filename]]
  • long_query_time 设定慢查询的阈值,超出此设定值的 SQL 都会被记录,默认为 10 秒

MySQL 也支持 SET 语句开启

set global slow_query_log=ON;
set global long_query_time=2;
set session long_query_time=2;

2. 查看慢查询日志

查看慢查询日志所在目录

SHOW VARIABLES LIKE '%slow_query_log_file%';

查看慢查询日志的超时时长

SHOW VARIABLES LIKE '%long_query_time%';

3. 停止慢查询日志

可以在配置文件把 slow_query_log 的值设为 OFF,重启 MySQL,也可以使用 SET 语句

4. 删除慢查询日志

慢查询日志可以手动删除,也可以使用命令删除

mysqladmin -uroot -p flush-logs

此种方式新的慢查询日志会直接覆盖旧日志

    原文作者:低吟不作语
    原文地址: https://www.cnblogs.com/Yee-Q/p/16219751.html
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞