MySQL Bin Files Eating Lots of Disk Space (fix)

by | Aug 23, 2020 | Hosting, Howtos, Linux, MySQL, Servers, VPS | 0 comments

Some time you may find MySQL stopped working and failed to start, and if you check status of MySQL using “systemctl status mysql” or “journalctl -xe” you can find that your “filesystem is out of space”

Trouble Shooting

OK first of all, we have to determine which disk partition is full, MySQL can be configured to store data on different disk or partition. by default it will be stored in /var/lib/mysql , i am going to use df command and find out how much space it uses, btw my vps has 20Gb of file system.


[email protected]:~# du -sh /var/lib/mysql
13G    /var/lib/mysql
[email protected]:~#
[email protected]:~# lsblk
NAME    MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda       8:0    0 19.1G  0 disk
├─sda1    8:1    0   19G  0 part /
├─sda14   8:14   0    1M  0 part
└─sda15   8:15   0   61M  0 part /boot/efi
[email protected]:~#
[email protected]:~# df -h /
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        19G  18.6G   403M  99% /

As you can see my vps has a root directory of 19 Gb and /var/lib/msql uses 13Gb. and total of 18.6 Gb is used by filesystem. 

To Fix this i have 3 Methods.

  1. Manually delete Log files.
  2. Using MySQL (Only works if mysql is starts)
  3. Set Persist binlog settings(this will automatically delete logs after number of days you set)

Ok Lets get started.

Method #1 Manually Delete Log files.

you need to change directory to /var/lib/mysql, if you type type “du-bsh *” you can find your logs some thing like this, for tutorial i have only listed binlog files you can see the file name binlog.000141 to binlog.000148 these are the files which consumes most of you filesystem.


[email protected]:/var/lib/mysql# du -bsh *
48M     /var/lib/mysql/binlog.000141
102M    /var/lib/mysql/binlog.000142
67M     /var/lib/mysql/binlog.000143
104M    /var/lib/mysql/binlog.000144
102M    /var/lib/mysql/binlog.000145
101M    /var/lib/mysql/binlog.000146
103M    /var/lib/mysql/binlog.000147
44M     /var/lib/mysql/binlog.000148

you can also find bindlog.index file this contains log files name, you be careful to do some thing stupid, you need take a backup first, then edit this file and remove indexing “remove names of logs” then save this.
after that you can delete files which is not in the index
now you can restart msyql it will start.

Method #2 Using MySQL

If you MySQL is still working you need to access MySQL then type following command, login to MySQL.


mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000141 |  50260145 | No        |
| binlog.000142 | 106706425 | No        |
| binlog.000143 |  69240464 | No        |
| binlog.000144 | 108516594 | No        |
| binlog.000145 | 106324989 | No        |
| binlog.000146 | 105725450 | No        |
| binlog.000147 | 107466759 | No        |
| binlog.000148 |  98082094 | No        |
+---------------+-----------+-----------+

this will list your binary logs index, to delete logs to a certain number, i am going to delete binlog.000141 so i need to type command like this, the number mentioned below is the number of log which should apper in index file after removing, so in our case 141 will be deteted or all logs from 1 to 141 and rest of the logs will remain.


mysql> PURGE BINARY LOGS TO 'binlog.000142';

I have put MySQL logs for comparison before and after doing the purge.


mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000141 |  50260145 | No        |
| binlog.000142 | 106706425 | No        |
| binlog.000143 |  69240464 | No        |
| binlog.000144 | 108516594 | No        |
| binlog.000145 | 106324989 | No        |
| binlog.000146 | 105725450 | No        |
| binlog.000147 | 107466759 | No        |
| binlog.000148 |  98082094 | No        |
+---------------+-----------+-----------+
8 rows in set (0.01 sec)

mysql> PURGE BINARY LOGS TO 'binlog.000142';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000142 | 106706425 | No        |
| binlog.000143 |  69240464 | No        |
| binlog.000144 | 108516594 | No        |
| binlog.000145 | 106324989 | No        |
| binlog.000146 | 105725450 | No        |
| binlog.000147 | 107466759 | No        |
| binlog.000148 |  98195420 | No        |
+---------------+-----------+-----------+
7 rows in set (0.00 sec)

Method #3 Set Persist binlog settings.

Now we can set automatically delete old log files!

In MySQL 8.0, use binlog_expire_logs_seconds instead, where the default value is 2592000 seconds (30 days). In this example, we reduce it to only 3 days (60 seconds x 60 minutes x 24 hours x 3 days):


mysql> SET GLOBAL binlog_expire_logs_seconds = (60*60*24*3);
Query OK, 0 rows affected (0.00 sec)

mysql> SET PERSIST binlog_expire_logs_seconds = (60*60*24*3);
Query OK, 0 rows affected (0.01 sec)

Enjoy.

SET PERSIST will make sure the configuration is loaded in the next restart. Configuration set by this command is stored inside /var/lib/mysql/mysqld-auto.cnf.

Written By George Sruthin

Founder of techridez.com, a Server Support Engineer, a tech blogger, Loves Web Designing.

Related Posts

Comments

0 Comments

0 Comments

Submit a Comment

Subscribe For Instant News, Updates, and Discounts

Pin It on Pinterest

Shares
Share This