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