Free disk space of linux by cleaning mysql logs

Published On November 01, 2016

category mysql | tags sql mysql


I suddenly find the disk storage of my linux virtual machine declined by 10G today. So I am going to solve it.

step 1: Find the big files

Find the biggest files by using the command below from root path

du -h --max-depth=1
This commands show the size of every folder of current directory. By following the biggest folder deeper and deeper until I find that the space of /var/lib/mysql is up to 10G.

The command below shows size of every file in this directory

ls -lhS
The option h helps show a human-readable size of each file. The option S make the list sorted by file size, largest first.

I see there are many big log files prefixed with mysql-bin. But the space of them add up to less than 5G. And in path /var/lib/mysql/mysql I find there is a big file named general_log.CSV whose's size is up to 5G. I remember I turned on the general log a month ago. Why is it a CSV file although the log-output I used is TABLE? Because by default, the log tables use the CSV storage engine that writes data in comma-separated values format.

step 2: Clean them

As we have seen, there are mainly two types of log to clean. One is binary log and the other is general log.

clean binary log

I use URGE BINARY LOGS. The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or date.

Execute the sql below to clean all logs generated before 'mysql-bin.000038' which is the latest log in my case.

PURGE MASTER LOGS to 'mysql-bin.000037';

clean general log

  • First I need to turn off general log as it will take a large amount of space.
  • Then comment the line general_log in the option file which is /etc/mysql/my.cnf in my linux.
  • And then restart mysql server. The following command will work as I am using arch linux.
    systemctl restart mysqld
    
  • Run the sql below at last to clean the logs.
    TRUNCATE table mysql.general_log
    

Finally I release about 10G space. Well done.


qq email facebook github
© 2018 - Xurui Yan. All rights reserved
Built using pelican