如何全面分析MySQL全日志以优化数据库性能？

分析mysql全日志

一、MySQL日志类型

MySQL数据库系统提供了多种类型的日志，每种日志记录不同的信息，以下是常见的几种MySQL日志类型：

1、错误日志（Error Log）：记录MySQL服务器启动和停止过程中的信息，以及运行时出现的错误和警告。

2、查询日志（General Query Log）：记录所有发送到MySQL服务器的SQL查询语句，不论这些查询是否影响了数据。

3、慢查询日志（Slow Query Log）：记录执行时间超过特定阈值的SQL查询，用于性能优化。

4、二进制日志（Binary Log）：记录所有对数据库进行更改的SQL语句，用于数据恢复和复制。

5、中继日志（Relay Log）：用于复制，从主库接收到的二进制日志事件会被写入中继日志，然后在从库上执行。

二、如何开启与配置查询日志

1. 查看当前配置

要查看当前的查询日志设置，可以使用以下命令：

SHOW VARIABLES LIKE 'general%';

2. 开启查询日志

你可以通过修改MySQL配置文件或直接在MySQL命令行中设置来开启查询日志。

临时开启（重启后失效）：

    SET GLOBAL general_log = 'ON';

永久开启：在MySQL配置文件（如my.cnf或my.ini）中添加以下内容：

    [mysqld]
    general_log = 1
    general_log_file = /path/to/your/general.log

3. 指定日志文件路径

为了避免占用默认的数据目录空间，可以指定日志文件存储路径：

SET GLOBAL general_log_file = '/path/to/your/general.log';

三、查询日志分析

一旦开启了查询日志，你可以使用各种工具和方法对其进行分析，下面是一个简单的示例：

假设有以下查询日志条目：

190604 14:46:14       14 Connect    root@localhost on 
             14 Init DB    test
             14 Query    SELECT * FROM admin WHERE id = 1
             14 Quit  `

我们按列解析如下：

1、Time：时间列，前面是日期，后面是小时和分钟，如果多个SQL语句几乎同时执行，可能不会重复记录时间。

2、Id：线程ID，对应于SHOW PROCESSLIST命令中的线程ID，对于长连接和耗时较长的SQL语句，可以根据此ID查找具体的线程。

3、Command：操作类型，例如Connect表示连接数据库，Query表示查询数据库（包括增删改查），以及其他如Init DB、Quit等操作。

4、Argument：详细信息，例如root@localhost on表示用户root从本地连接到数据库，SELECT * FROM admin WHERE id = 1表示执行的具体查询语句。

四、登录成功与失败的日志分析

通过弱口令工具扫描登录尝试时，日志会记录每次尝试的结果。

190601 22:03:20    98 Connect    root@192.168.204.1 on 
              98 Connect    Access denied for user 'root'@'192.168.204.1' (using password: YES)
103 Connect    mysql@192.168.204.1 on 
103 Connect    Access denied for user 'mysql'@'192.168.204.1' (using password: YES)

上述日志显示了几次登录尝试及其结果，其中包含成功的连接和被拒绝的访问。

五、常见问题与解答

问题1：如何定期清理查询日志以防止磁盘空间耗尽？

答：

可以通过配置日志轮转机制和使用脚本自动删除旧的日志文件来实现定期清理，使用logrotate工具配置日志轮转：

sudo nano /etc/logrotate.d/mysql

添加以下内容：

/var/log/mysql/*.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql adm
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

可以编写一个简单的Shell脚本来删除超过一定天数的旧日志文件：

#!/bin/bash
LOG_DIR="/var/log/mysql"
ARCHIVE_DIR="/path/to/archive"
find $LOG_DIR -name "*.log" -mtime +7 -exec mv {} $ARCHIVE_DIR ;
tar -czf $ARCHIVE_DIR/mysql-logs-$(date +%F).tar.gz -C $ARCHIVE_DIR .
find $ARCHIVE_DIR -name "*.log" -delete

将上述脚本添加到crontab中以定期执行：

0 3 * * * /path/to/archive_script.sh

问题2：如何分析慢查询日志以优化数据库性能？

答：

慢查询日志记录了执行时间超过特定阈值的SQL查询，要分析慢查询日志，可以使用mysqldumpslow工具，该工具随MySQL一起提供，确保启用了慢查询日志：

SHOW VARIABLES LIKE 'slow_query_log';

生成慢查询日志文件并使用mysqldumpslow进行分析：

mysqldumpslow -s t /var/log/mysql/mysql-slow.log

输出示例如下：

Reading mysql slow query log from mysql-slow.log,
Count: 3  Time=3.60s (36s)  Rows_sent=36.0  Rows_examined=720  Lock_time=>0s (0s)  Rows_affected=>0s (0s)  Avg_rows_sent=>12.0  Avg_rows_examined=>240.0  Avg_lock_time=>0s (0s)  Avg_rows_affected=>0s (0s)
Execution time distribution (us):  10 -> 20 | 100 -> 200 | 200 -> 500 | 500 -> 1000 | 1000 -> 2000 | 2000 -> 5000 | 5000+ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Count          Rank  Runtime     Query_time    Lock_time  Rows_sent  Rows_examined  Rows_affected  Full_scan  Last_execut...  Log_rows  Avg_lock_time  Avg_rows_sent  Avg_rows_examined  Avg_rows_affected Exec_master_log_pos  Exec_master_log_pos  Wall ...  Id      Code   Query_time    Lock_time  Rows_sent  Rows_examined  Rows_affected  Full_scan  Last_exec...  Log_id  Kill QPS ... Host               db    Command  Time    Statement
...

根据分析结果，可以识别出哪些查询需要优化，例如添加索引、重写查询或调整数据库架构。

小伙伴们，上文介绍了“分析mysql全日志”的内容，你了解清楚吗？希望对你有所帮助，任何问题可以给我留言，让我们下期再见吧。