Tuesday, May 4, 2010

Deleting MySQL Database log files

We have a master database and its slave database running on different nodes. The master DB is subjected to HUGE no. of inserts/updates because of which the log files written by it grew by enormous proportions (log files at one time occupied more than 80% of the disk space).

Log files are important because using them we can track the changes made to the DB and in case of any DB crash, we can rebuild it again from the scratch. But in our case, we didn't require the log files and had the luxury to get rid of them instead of finding a reliable storage to keep them safe.

We decided to go ahead with deleting the log files. We automated this process by setting a flag in configuration file so that MySQL automatically deleted the log files which are older than 90 days.

This can be done as follows:

1) Stop the Master MySQL server.

2) Add the follwing parameter to the my.cnf file (here the figure on the right hand side is the no. of days after which the log file must be cleared)

expire_logs_days = 90

3) Start the server.

Simple !!!


Incase, you just need to delete the log files without automating the process then it can be done using the following command (use desired date value):

PURGE BINARY LOGS BEFORE '2010-05-03 14:56:23';

Since the replication node uses the log files, we need to make sure we only delete files that were created before the logfile that the slave node is currently reading. We can get this data by using the following command on the slave database:

show slave status;

This command gives the details about the slave. The value of the column 'Master_Log_file' is the name of the log file currently being read by the slave. Once we have the name of this file, we can get its creation date. Lets assume the creation date was '2010-05-03 14:56:23'. All log files created before this time can be deleted with the following command

PURGE BINARY LOGS BEFORE '2010-05-03 14:56:23';

References:
MySQL v5 documentation for purging log files