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.
root@testserver:~# du -sh /var/lib/mysql
13G /var/lib/mysql
root@coversunkochi:~#
root@coversunkochi:~# 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
root@coversunkochi:~#
root@coversunkochi:~# 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.
- Manually delete Log files.
- Using MySQL (Only works if mysql is starts)
- 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.
root@coversunkochi:/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.
0 Comments