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
/var/lib/mysqlis up to 10G.
The command below shows size of every file in this directory
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
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_login the option file which is
/etc/mysql/my.cnfin 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.