Causes and solutions of mysql occupying a lot of disk space

foreword¶

When we use mysql or other data, we always encounter that the database occupies a lot of disk space, resulting in insufficient disk space, which leads to other problems (for example: the database cannot be started after restarting; the database cannot write data, etc.). To solve this problem, the method is to delete some unnecessary files in the database. The binlog log that takes up a lot of disk space in the mysql database is generally the binlog log, because it records the changes in the data in the database, so it will continue to grow larger with the accumulation of time. Time or location based recovery and error handling.

The following is a solution to solve the problem that the mysql database occupies a lot of disk space.

Solution¶

【Method 1】Delete manually¶

[1] Check whether the binlog log of mysql is too large

Due to the installation version or installation method of mysql, the location or log name of the binlog log is different. You need to check the log size according to your own situation. (The usual location is:

1. /var/lib//mysql/ or
2. /usr/local/mysql/var/)

1 view

cd /mysql/data/
du -sh *

The picture above shows the location of my actual binlog. As can be seen from the above figure, it is true that the binlog of mysql is too large and takes up disk space.

【2】Delete the log file

There are two cases

Case 1: The database doesn't have master and slave

delete log

Execute in the database:

reset master;

This command can also be used if the database has master-slave, but it is easy to cause master-slave synchronization failure.

Case 2: The database has master and slave

Check which file is the binlog being used by the master library and each slave library

show master status;

show slave status\G;

Delete all binary logs in the log index before the specified log sequence number or date

purge master logs to 'mysql-bin.000003';
purge master logs before '2018-08-29 12:00:00';

Notice: Don not delete binlog in use. The time and file name cannot be wrongly written (depending on your actual situation)

[3] Difference between reset master and purge binary logs

• reset master: delete all binglog log files, empty the log index file, and start over with a new log file.
• purge binary logs: delete logs based on a point in time

[Method 2] The system regularly deletes the binlog file¶

View the current log retention days

show variables like 'expire_logs_days';

Set the binlog to keep only 3 days

(Temporarily, restarting the mysql parameter will fail)

set global expire_logs_days = 3;

(Permanent, added in my.cnf, it will take effect after restart),

expire_logs_days = 3

Notice: The default value is 0, which means "Auto delete is not enabled". The expiration time should be set appropriately

[Method 3] Disable binlog¶

If master-slave is used, this operation is prohibited

1 Comment out the following in my.cnf

#log-bin=mysql-bin
#binlog_format=mixed

2 restart mysql

systemctl restart mysql (centos 7)
/etc/init.d/mysql restart (centos 6)