MySQL logs filling up /var

Ran into an issue where the MySQL log was filling up the /var partition.  Size of the log was 21G and growing.

root@system_name:/path# > /var/log/mysqld.log (This will zero the mysqld.log)

This will cause a blip in mysql connections. A better method is above to not interrupt connections.

#1. Move the current log file: mv mysql.log mysql.log.YYYYMMDD

#2. The MySQL service should still be writing to the mysql.log.YYYYMMDD file.

#3. Edit perms of the new mysql.log file: touch mysql.log; chown mysql:mysql mysql.log

#4. Now the fun part especially on a production MySQL server, we need to HUP the MySQL process to get it to start logging to the new file: ps aux | grep mysql; kill -HUP PID 

#5. Make sure you are still getting mysql connections: mysql -u root -p ; mysql> show full process list;

#6. According to this thread (http://dev.gigigo.tw/index.php?option=com_content&view=article&id=341&Itemid=330) the HUP does this: Correct, by default this signal is caught and the logs, table cache, status info, privileges and host
cache are all flushed. This is equivalent to bash# mysqladmin reload.

#7.  We had a connection timeout in our Nagios from our frontend web servers when we ran this command but as far as we could tell we didn’t lose any database connections and the mysql service started logging to the new logfile.

About these ads
  1. No trackbacks yet.

You must be logged in to post a comment.
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: