Berikut adalah cara untuk melakukan logrotate harian terhadap log yang ada pada mysql

Langkah-langkah :

Buat user baru di database mysql yang nantinya akan menjalankan perintah flush logs

mysql> CREATE USER 'log'@'localhost' IDENTIFIED BY 'Passw0rd';
mysql> GRANT RELOAD, SHUTDOWN, PROCESS, SHOW DATABASES, SUPER, LOCK TABLES ON *.* TO 'log'@'localhost'; 
mysql> flush privileges;

Buatlah file debian.cnf dan isikan baris berikut

 
# vi /etc/mysql/debian.cnf

[client]
host     = localhost
user     = log
password = passw0rd
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user     = log
password = passw0rd
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

Selanjutnya pada direktori /etc/logrotate.d buatlah file dengan nama mysql, namun jika sebelumnya sudah ada maka edit saja file sesuaikan nama log yang ingin di rotate

# vi /etc/logrotate.d/mysql

/var/log/mysql/mysql-error.log /var/log/mysql/mysql-slow.log {
	daily
	rotate 5
	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
		    exit 1
		  fi 
		else
		  $MYADMIN flush-logs
		fi
	endscript
}

Script tersebut akan dijalankan oleh cron daily setiap malam, namun jika ingin mengetahui apa script tersebut berjalan dengan benar atau tidak, bisa menggunakan perintah berikut

# logrotate -v -f /etc/logrotate.d/mysql

reading config file /etc/logrotate.d/mysql

Handling 1 logs

rotating pattern: /var/log/mysql.log /var/log/mysql/mysql-error.log /var/log/mysql/mysql-slow.log  forced from command line (7 rotations)
empty log files are rotated, old logs are removed
considering log /var/log/mysql.log
  log /var/log/mysql.log does not exist -- skipping
considering log /var/log/mysql/mysql-error.log
  log needs rotating
considering log /var/log/mysql/mysql-slow.log
  log needs rotating
rotating log /var/log/mysql/mysql-error.log, log->rotateCount is 7
dateext suffix '-20170510'
glob pattern '-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
renaming /var/log/mysql/mysql-error.log.7.gz to /var/log/mysql/mysql-error.log.8.gz (rotatecount 7, logstart 1, i 7), 
old log /var/log/mysql/mysql-error.log.7.gz does not exist
renaming /var/log/mysql/mysql-error.log.6.gz to /var/log/mysql/mysql-error.log.7.gz (rotatecount 7, logstart 1, i 6), 
old log /var/log/mysql/mysql-error.log.6.gz does not exist
renaming /var/log/mysql/mysql-error.log.5.gz to /var/log/mysql/mysql-error.log.6.gz (rotatecount 7, logstart 1, i 5), 
old log /var/log/mysql/mysql-error.log.5.gz does not exist
renaming /var/log/mysql/mysql-error.log.4.gz to /var/log/mysql/mysql-error.log.5.gz (rotatecount 7, logstart 1, i 4), 
old log /var/log/mysql/mysql-error.log.4.gz does not exist
renaming /var/log/mysql/mysql-error.log.3.gz to /var/log/mysql/mysql-error.log.4.gz (rotatecount 7, logstart 1, i 3), 
old log /var/log/mysql/mysql-error.log.3.gz does not exist
renaming /var/log/mysql/mysql-error.log.2.gz to /var/log/mysql/mysql-error.log.3.gz (rotatecount 7, logstart 1, i 2), 
old log /var/log/mysql/mysql-error.log.2.gz does not exist
renaming /var/log/mysql/mysql-error.log.1.gz to /var/log/mysql/mysql-error.log.2.gz (rotatecount 7, logstart 1, i 1), 
old log /var/log/mysql/mysql-error.log.1.gz does not exist
renaming /var/log/mysql/mysql-error.log.0.gz to /var/log/mysql/mysql-error.log.1.gz (rotatecount 7, logstart 1, i 0), 
old log /var/log/mysql/mysql-error.log.0.gz does not exist
log /var/log/mysql/mysql-error.log.8.gz doesn't exist -- won't try to dispose of it
rotating log /var/log/mysql/mysql-slow.log, log->rotateCount is 7
dateext suffix '-20170510'
glob pattern '-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
renaming /var/log/mysql/mysql-slow.log.7.gz to /var/log/mysql/mysql-slow.log.8.gz (rotatecount 7, logstart 1, i 7), 
old log /var/log/mysql/mysql-slow.log.7.gz does not exist
renaming /var/log/mysql/mysql-slow.log.6.gz to /var/log/mysql/mysql-slow.log.7.gz (rotatecount 7, logstart 1, i 6), 
old log /var/log/mysql/mysql-slow.log.6.gz does not exist
renaming /var/log/mysql/mysql-slow.log.5.gz to /var/log/mysql/mysql-slow.log.6.gz (rotatecount 7, logstart 1, i 5), 
old log /var/log/mysql/mysql-slow.log.5.gz does not exist
renaming /var/log/mysql/mysql-slow.log.4.gz to /var/log/mysql/mysql-slow.log.5.gz (rotatecount 7, logstart 1, i 4), 
old log /var/log/mysql/mysql-slow.log.4.gz does not exist
renaming /var/log/mysql/mysql-slow.log.3.gz to /var/log/mysql/mysql-slow.log.4.gz (rotatecount 7, logstart 1, i 3), 
old log /var/log/mysql/mysql-slow.log.3.gz does not exist
renaming /var/log/mysql/mysql-slow.log.2.gz to /var/log/mysql/mysql-slow.log.3.gz (rotatecount 7, logstart 1, i 2), 
old log /var/log/mysql/mysql-slow.log.2.gz does not exist
renaming /var/log/mysql/mysql-slow.log.1.gz to /var/log/mysql/mysql-slow.log.2.gz (rotatecount 7, logstart 1, i 1), 
old log /var/log/mysql/mysql-slow.log.1.gz does not exist
renaming /var/log/mysql/mysql-slow.log.0.gz to /var/log/mysql/mysql-slow.log.1.gz (rotatecount 7, logstart 1, i 0), 
old log /var/log/mysql/mysql-slow.log.0.gz does not exist
log /var/log/mysql/mysql-slow.log.8.gz doesn't exist -- won't try to dispose of it
renaming /var/log/mysql/mysql-error.log to /var/log/mysql/mysql-error.log.1
creating new /var/log/mysql/mysql-error.log mode = 0640 uid = 110 gid = 4
renaming /var/log/mysql/mysql-slow.log to /var/log/mysql/mysql-slow.log.1
creating new /var/log/mysql/mysql-slow.log mode = 0640 uid = 110 gid = 4
running postrotate script
compressing log with: /bin/gzip
compressing log with: /bin/gzip

cek hasil logrotate

# ls -al /var/log/mysql

-rw-r-----  1 mysql adm     10277 Nov  2  2016 error.log
-rw-r-----  1 mysql adm         0 May 10 11:41 mysql-error.log
-rw-r-----  1 mysql root  3970514 May 10 10:44 mysql-error.log.1.gz
-rw-r-----  1 mysql adm    245054 May 10 11:46 mysql-slow.log
-rw-r-----  1 mysql adm        20 May 10 06:25 mysql-slow.log.1.gz
Advertisements