logrotate mysql_利用logrotate将mysql log截断

1.授权用户

CREATE USER 'log_rotate'@'localhost' IDENTIFIED BY '123456';

GRANT RELOAD,SUPER ON *.* to 'log_rotate'@'localhost';

FLUSH PRIVILEGES;

2.配置登录信息

2.1 密文

mysql_config_editor set \

--login-path=logrotate \

--host=localhost \

--user=log_rotate \

--port 3306 \

--password

2.2 或者普通明文登录

bash # vi /root/.my.cnf

[client]

user=log_rotate

password=''

bash # chmod 600 /root/.my.cnf

3.测试连接

[root@master logrotate.d]# mysqladmin --login-path=logrotate ping

mysqld is alive

4.确认log路径

mysql --login-path=logrotate -e "show global variables like 'slow_query_log_file'"

mysql --login-path=logrotate -e "show global variables like 'log_error'"

5.logrotate内容

vi /etc/logrotate.d/mysql

/mysqlData/dataerror.log /mysqlData/dataslow.log {

daily

create 660 mysql mysql

dateext

dateformat -%Y%m%d.%s

nocopytruncate

olddir oldlog

rotate 30

missingok

nocompress

sharedscripts

notifempty

postrotate

if test -x /usr/local/mysql/bin/mysqladmin &&

env HOME=/root /usr/local/mysql/bin/mysqladmin --login-path=logrotate ping > /dev/null 2>&1

then

env HOME=/root/ /usr/local/mysql/bin/mysql --login-path=logrotate -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; set global slow_query_log=0; select sleep(2); FLUSH ERROR LOGS; FLUSH SLOW LOGS;select sleep(2); set global long_query_time=@lqt_save; set global slow_query_log=1;' > /var/log/mysqladmin.flush-logs 2>&1

fi

endscript

}

6.crontab

59 23 * * * /usr/sbin/logrotate --force /etc/logrotate.d/mysql