Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割

 

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10s以上的语句。

默认情况下,MySQL数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志或多或少会带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。

 

1. 找服务器大文件,释放Linux服务器上的磁盘空间

要找出正在使用的磁盘空间:

  1. 运行命令到根目录 cd /
  2. 运行 sudo du -h --max-depth=1
  3. 请注意哪些目录使用了大量磁盘空间。
  4. cd 进入一个大目录。
  5. 运行ls -l以查看哪些文件占用了大量空间。删除任何你不需要的东西。
  6. 重复步骤2到5。

 

2. /var/lib/mysql 清理

通过第一步找到了大文件在 /var/lib/mysql,进入mysql文件夹,如果有报错:

-bash: cd: mysql: Permission denied

 

那么运行:

# 获取根目录权限,如果想退出根目录权限,可以使用命令 exit
sudo -i 
cd /var/lib/mysql

 

再运行:

# h显示大小,S排序
ls -lhS

# 或者
# sudo du -h --max-depth=1

 

发现最大的是 *-slow.log,下面是清理方法:

1.首先连接进入mysql,查看慢日志的状态,show variables like ‘%slow%’;

 

2.此时慢日志的状态是开启的,且慢日志的路径也可以看到,接下来就是要先关闭慢日志,使用  set global slow_query_log=0;  或者 set global slow_query_log = ‘OFF’; 关闭之后再次检查下慢日志的状态,已经是关闭了,如图:

 

3.关闭慢日志后,我们重置慢日志的路径,路径可以根据自己的服务器配置,语句如下:

set global slow_query_log_file=’/var/lib/mysql/station-slow-20190909.log’;

 

4.重置完成后,再次开启慢日志:set global slow_query_log=1; 或者 set global slow_query_log=’ON’;

 

5.再次检查慢日志的状态:show variables like ‘%slow%’;  可以看到慢日志已经开启,路径也变了

 

6.接下来我们测试一下慢日志记录是否成功,使用语句:select sleep(10) as a, 1 as b;  效果如图:

Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割
Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割

 

7.最后一步就是打卡慢日志查看是否记录成功了,如果记录成功会有如下记录:

Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割
Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割

 

8.最后一步就是把之前的慢日志文件给删除了,释放空间。但我使用的方式是清空文件内容,但是保留文件。在慢日志的文件夹路径下,执行语句:>slow_querys.log

Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割
Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割

3. MySQL 慢查询slow log设置

 

一、slow log相关参数

以下参数都是动态参数,可以在实例运行时修改。

slow_query_log=1 #是否启用慢查询日志,1为启用,0为禁用
slow_query_log_file=slow.log #指定慢查询日志文件的路径和名字,可使用绝对路径指定;默认值是'主机名_slow.log',位于datadir目录 
long_query_time=2 #SQL语句运行时间阈值,执行时间大于参数值的语句才会被记录下来 
min_examined_row_limit=100 #SQL语句检测的记录数少于设定值的语句不会被记录到慢查询日志,即使这个语句执行时间超过了long_query_time的阈值 
log_queries_not_using_indexes=1 #将没有使用索引的语句记录到慢查询日志 
log_throttle_queries_not_using_indexes=10 #设定每分钟记录到日志的未使用索引的语句数目,超过这个数目后只记录语句数量和花费的总时间 
log-slow-admin-statements=1 #记录执行缓慢的管理SQL,如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。 
log_slow_slave_statements=0 #记录从库上执行的慢查询语句 
log_timestamps=system #5.7版本新增时间戳所属时区参数,默认记录UTC时区的时间戳到慢查询日志,应修改为记录系统时区 
log_output=FILE,TABLE #指定慢查询日志的输出方式,从5.5版本开始可以记录到日志文件(FILE,慢查询日志)和数据库表(TABLE,mysql.slow_log)中

 

Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割
Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割

 

二、使用MySQL slow log

1. 在线修改数据库相关参数:

mysql> set global slow_query_log=1; 
mysql> set global slow_query_log_file='my57-slow.log'; 
mysql> set global long_query_time=2; 
mysql> set global min_examined_row_limit=50; 
mysql> set global log_queries_not_using_indexes=1; 
mysql> set global log_throttle_queries_not_using_indexes=10; 
mysql> set global log_slow_admin_statements=1;

 

2. 查看slow log内容:

/usr/local/mysql/bin/mysqld, Version: 5.7.17 (MySQL Community Server (GPL)). started with:  
Tcp port: 3306  Unix socket: /tmp/mysql1.sock  
Time                 Id Command    Argument

 

3. 执行一个很慢的SQL后,slow log记录的内容:

Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割
Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割

 

第一行:标记日志产生的时间,准确说是SQL执行完成的时间点,改行记录每一秒只打印一条。

第二行:客户端的账户信息,两个用户名(第一个是授权账户,第二个为登录账户),客户端IP地址,还有mysqld的线程ID。

第三行:查询执行的信息,包括查询时长,锁持有时长,返回客户端的行数,扫描行数。通常我需要优化的就是最后一个内容,尽量减少SQL语句扫描的数据行数。

第四行:通过代码看,貌似和第一行的时间没有区别。

第五话:最后就是产生慢查询的SQL语句。

–log-short-format=true:

如果mysqld启动时指定了–log-short-format参数,则不会输出第一、第二行。

log-queries-not-using-indexes=onlog_throttle_queries_not_using_indexes > 0 :

如果启用了以上两个参数,每分钟超过log_throttle_queries_not_using_indexes配置的未使用索引的慢日志将会被抑制,被抑制的信息会被汇总,每分钟输出一次。

格式如下:

Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割
Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割

 

4. 修改配置文件my.cnf

修改配置文件my.cnf,

CentOS/RHEL

vi /etc/my.cnf

Debian/Ubuntu

vi /etc/mysql/my.cnf

在[mysqld]下的下方加入

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

 

重启MySQL服务

sudo service mysqld restart

 

查看设置后的参数
mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

 

5. Linux日志文件总管——logrotate

日志文件包含了关于系统中发生的事件的有用信息,在排障过程中或者系统性能分析时经常被用到。对于忙碌的服务器,日志文件大小会增长极快,服务器会很快消耗磁盘空间,这成了个问题。除此之外,处理一个单个的庞大日志文件也常常是件十分棘手的事。

logrotate是个十分有用的工具,它可以自动对日志进行截断(或轮循)、压缩以及删除旧的日志文件。例如,你可以设置logrotate,让/var/log/foo日志文件每30天轮循,并删除超过6个月的日志。配置完后,logrotate的运作完全自动化,不必进行任何进一步的人为干预。另外,旧日志也可以通过电子邮件发送,不过该选项超出了本教程的讨论范围。

主流Linux发行版上都默认安装有logrotate包,如果出于某种原因,logrotate没有出现在里头,你可以使用apt-get或yum命令来安装。

在Debian或Ubuntu上:

apt-get install logrotate cron

在Fedora,CentOS或RHEL上:

yum install logrotate crontabs

logrotate的配置文件是/etc/logrotate.conf,通常不需要对它进行修改。日志文件的轮循设置在独立的配置文件中,它(们)放在/etc/logrotate.d/目录下。

 

更多请参考:Linux日志文件总管——logrotate, MySQL慢日志分割, 配置 logrotate 的终极指导

 

6. 慢日志分析工具

  1. 官方自带工具: mysqldumpslow
  2. 开源工具:mysqlsla
  3. percona-toolkit:工具包中的pt-query-digest工具可以分析汇总慢查询信息,具体逻辑可以看SlowLogParser这个函数。

总的来说,MySQL的日志内容本身不复杂,上面3个工具都是用perl脚本实现,代码行数不超过200行,有兴趣的同学也可以自己尝试着解析下。

 

 

本文:Mysql:/var/lib/mysql清理, 清理mysql慢日志, mysql慢日志时间设置, mysql慢日志logrotate设置, 释放Linux服务器上的磁盘空间, MySQL慢日志分割

 

Leave a Reply