LinuxQuestions.org
Support LQ: Use code LQ3 and save $3 on Domain Registration
Go Back   LinuxQuestions.org > Blogs > anomie
User Name
Password

Notices

Rate this Entry

Rotating MySQL server log files

Posted 03-09-2012 at 06:09 PM by anomie

This may seem like an inconsequential, overly specific topic, but I'm posting here because - following a couple 'net searches - I observed a dearth of helpful information on MySQL server log file rotation. All the resources I found on the topic essentially said:
  • Use logrotate(8) -- 95% of the hits I found; or
  • Use "some_contrived_steps, not explained well" -- the other 5%

So I am sharing my own contrived steps here. There's nothing wrong with logrotate(8), in particular, but I wanted an alternative approach.

----------

The approach is simple. But, first, my caveats: do not use this example script if you don't understand what it's doing. Do not use this script in a production environment without properly testing and ensuring proper backups in advance. The script is here as a convenience, not as a tool to blindly copy and paste.

Also, read the MySQL documentation on FLUSH LOGS. You may not want to follow the approach I propose here if you've enabled binary logging. If you maintain several different types of log files, you'll need to build in logic for those.

Without further ado, here's what the script does:
  1. It uses mv(1) to rename the MySQL log file to a .prev suffix. As you may already know, this operation (on the same filesystem) does not change the file's inode. As a result, MySQL server continues writing to the .prev log.
  2. It creates a new log file (which must match the 'log=' name specified in my.cnf, of course).
  3. It sets proper ownership and permissions on the new log file. (Will likely differ on your system.)
  4. It fires up the MySQL client as the root user (replace as appropriate for your environment), and issues the FLUSH LOGS command. This commits any outstanding logging to the .prev log file, and then forces MySQL server to begin writing to the new log file.

----------

And here's the very simple example script. YMMV.

Code:
#!/bin/sh

# Log rotation script for MySQL server logs. 

#---------------------------------------------------------------------#
# Variable assignment
#---------------------------------------------------------------------#

_log='/mydb/log/mysqld-detailed.log'
_log_owner='mysql:mysql'

_socket='/mydb/mysql/mysql.sock'

_user='root'
_pass='good%good%password'


#---------------------------------------------------------------------#
# Functions
#---------------------------------------------------------------------#

check_rc() {

  if [ ${1} -ne 0 ] ; then

    echo "Problem with ${0}. Please investigate."
    exit 1

  fi

}


#---------------------------------------------------------------------#
# Main logic
#---------------------------------------------------------------------#

# Even following the mv(1) below, the MySQL daemon will continue
# writing to the .prev file.
#
mv ${_log} ${_log}.prev
check_rc $?

touch ${_log}
check_rc $?

chown ${_log_owner} ${_log}
check_rc $?

chmod 0660 ${_log}
check_rc $?

# The FLUSH LOGS command commits any outstanding logging to disk, 
# and causes the daemon to begin logging to the newly created file.
#
mysql -S${_socket} -u${_user} -p${_pass} -e'FLUSH LOGS'
check_rc $?

exit 0
Posted in Uncategorized
Views 803 Comments 0
« Prev     Main     Next »

  



All times are GMT -5. The time now is 12:37 AM.

Main Menu
Advertisement

My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration