Sunday, September 13, 2009

MySQL log file rotation

Rotating MySQL Log Files on Linux.
A. Check logrotate must on daily cron
#less /etc/cron.daily/logrotate

B. Check on logrotate.d there must be mysql
#less /etc/logrotate.d/mysql
Its default Content is:

# by setting the variable "err-log"
# in the [safe_mysqld] section as follows:
#
# [safe_mysqld]
# err-log=/var/lib/mysql/mysqld.log
#
# If the root user has a password you have to create a
# /root/.my.cnf configuration file with the following
# content:
#
# [mysqladmin]
# password =
# user= root
#
# where "" is the password.
#
# ATTENTION: This /root/.my.cnf should be readable ONLY
# for root !

/var/lib/mysql/mysqld.log {
# create 600 mysql mysql
notifempty
daily
rotate 3
missingok
compress
postrotate
# just if mysqld is really running
if test -x /usr/bin/mysqladmin && \
/usr/bin/mysqladmin ping &>/dev/null
then
/usr/bin/mysqladmin flush-logs
fi
endscript
}

Read uses of variable
My file :

# This logname can be set in /etc/my.cnf
# by setting the variable "err-log"
# in the [safe_mysqld] section as follows:
#
# [safe_mysqld]
# err-log=/var/lib/mysql/mysqld.log
#
# If the root user has a password you have to create a
# /root/.my.cnf configuration file with the following
# content:
#
# [mysqladmin]
# password =
# user= root
#
# where "" is the password.
#
# ATTENTION: This /root/.my.cnf should be readable ONLY
# for root !

# - I put everything in one block and added sharedscripts, so that mysql gets
# flush-logs'd only once.
# Else the binary logs would automatically increase by n times every day.
# - The error log is obsolete, messages go to syslog now.
/var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log {
daily
rotate 7
missingok
create 640 mysql adm
compress
sharedscripts
postrotate
test -x /usr/bin/mysqladmin || exit 0

# If this fails, check debian.conf!
export HOME=/etc/mysql/my.cnf
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
exit 1
fi
else
$MYADMIN flush-logs
fi
endscript


C. Create a file /root/.my.cnf.
#vi /root/.my.cnf (# ATTENTION: This /root/.my.cnf should be readable ONLY)

Its contain ;
[mysqladmin]
password = xxxx
user= root


Run manually ;

#logrotate -f /path/to/some/logrotate.conf
if you want to force a rotatation which is uncalled for in the config files, you need to give it a "-f":

2 comments:

Home Audio Diy said...

thanks for the syntax, it help me alot

Vic said...

Thank you. I was looking for the syntax for rotating multiple files and it helped me out as well.

SHOW ENGINE INNODB STATUS

  The SHOW ENGINE INNODB STATUS command in MySQL provides detailed information about the internal state of the InnoDB storage engine. This ...