MySQL slow query log consists of SQL statements that took more than long_query_time seconds to complete execution & required atleast min_examined_row_limit to be examined. By default, administrative queries & those that don’t use indexes for lookups are not logged.
Two common techniques used by Logrotate are:
copytruncate: Instead of moving the old log file & optionally creating a new one, logrotate truncates the original log file in place after creating a copy.
nocopytruncate: Do not truncate the original log file in place after creating a copy.
Truncating log files can block MySQL because the OS serializes access to the inode during the truncate operation. Therefore, it is recommended to temporarily stop slow query logging, flush slow logs, rename the old log file & finally re-enable slow query logging.
Flushing logs might take a considerable amount of time, so, to avoid filling slow log buffer, it’s advisable to temporarily disable MySQL slow query logging & re-enabling it once the rotation is complete.
Manual Rotation
To manually rotate slow query logs, we’ll temporarily disable slow query logging, flush slow logs, rename the original file & finally re-enable slow query logging.
get the path to slow query log file
| MariaDB [(none)]> show variables like '%slow_query%'; |
|---|
| Variable_name | Value |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log |
2 rows in set (0.00 sec)
temporarily disable slow query logging
MariaDB [(none)]> set global slow_query_log=off;
Query OK, 0 rows affected (0.00 sec)
flush only slow logs
MariaDB [(none)]> flush slow logs;
Query OK, 0 rows affected (0.00 sec)
rename the old log file and or compress it
root@db01:~# mv /var/lib/mysql/mysql-slow.log /var/lib/mysql/mysql-slow-$(date +%Y-%m-%d).log
root@db01:~# gzip -c /var/lib/mysql/mysql-slow-$(date +%Y-%m-%d).log > /var/lib/mysql/mysql-slow-$(date +%Y-%m-%d).log.gz
finally, re-enable slow query logging
MariaDB [(none)]> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)
使用 logrotate
使用 logrotate 您可以安全的对 mysql query log 的日志进行切分
logrotate: /etc/logrotate.d/mysql-query
/var/lib/mysql/ubuntu.log {
daily
size 1G
rotate 2
missingok
create 640 mysql adm
compress
sharedscripts
postrotate
test -x /usr/bin/mysqladmin || exit 0
# If this fails, check debian.conf!
MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then
# Really no mysqld or rather a missing debian-sys-maint user?
# If this occurs and is not a error please report a bug.
#if ps cax | grep -q mysqld; then
if killall -q -s0 -umysql mysqld; then
exit 1
fi
else
$MYADMIN flush-logs
fi
endscript
}
参数 功能
compress 通过gzip 压缩转储以后的日志
nocompress 不需要压缩时，用这个参数
copytruncate 用于还在打开中的日志文件，把当前日志备份并截断
nocopytruncate 备份日志文件但是不截断
create mode owner group 转储文件，使用指定的文件模式创建新的日志文件
nocreate 不建立新的日志文件
delaycompress 和 compress 一起使用时，转储的日志文件到下一次转储时才压缩
nodelaycompress 覆盖 delaycompress 选项，转储同时压缩。
errors address 专储时的错误信息发送到指定的Email 地址
ifempty 即使是空文件也转储，这个是 logrotate 的缺省选项。
notifempty 如果是空文件的话，不转储
mail address 把转储的日志文件发送到指定的E-mail 地址
nomail 转储时不发送日志文件
olddir directory 转储后的日志文件放入指定的目录，必须和当前日志文件在同一个文件系统
noolddir 转储后的日志文件和当前日志文件放在同一个目录下
prerotate/endscript 在转储以前需要执行的命令可以放入这个对，这两个关键字必须单独成行
postrotate/endscript 在转储以后需要执行的命令可以放入这个对，这两个关键字必须单独成行
daily 指定转储周期为每天
weekly 指定转储周期为每周
monthly 指定转储周期为每月
rotate count 指定日志文件删除之前转储的次数，0 指没有备份，5 指保留5 个备份
tabootext [+] list 让logrotate 不转储指定扩展名的文件，缺省的扩展名是：.rpm-orig, .rpmsave, v, 和 ~
size 1G 当日志文件到达指定的大小时才转储，Size 可以指定 bytes (缺省)以及KB (sizek)或者MB (sizem).
dateext: archive old log files by adding a date extension using the format YYYYMMDD instead of using a number.
missingok: if a log file is missing, don’t issue an error message
rotate 20: 保存多少个切分文件
notifempty: don’t rotate empty log files
sharedscripts: run prerotate & postrotate scripts only once, no matter how many logs match the wildcard pattern