This article describes what to do if you are using MySQL and your disk is exhausted due to binary logs.
The data area used by MySQL was exhausted, the disk usage became 100%, and MySQL could not be started.
After investigating the cause, it was confirmed that a large amount of 1.1G binary log was output to the directory under / data / mysql / used as the data area.
I want to puge and delete the binary log, but I can't even log in to mysql because I can't start MySQL either.
We will describe the provisional and permanent support for such cases.
First, delete the oldest binary log with the rm command, and execute the df command to confirm that free space is secured.
# rm /data/mysql/binlog.000169
/dev/sdb1 50G 49G 1.1G 98% /data
Then start mysql. Confirm that it started normally, and log in to mysql.
# systemctl start mysql
# systemctl status mysql
# mysql -u root -p
Execute the following command to check the status of the binary log.
--Latest binary log
mysql> SHOW MASTER STATUS\g;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000215 | 155 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
--List of binar logs
mysql> SHOW MASTER LOGS;
+---------------+------------+-----------+
| Log_name | File_size | Encrypted |
+---------------+------------+-----------+
| binlog.000170 | 1073742751 | No |
| binlog.000171 | 1073751257 | No |
| binlog.000172 | 1073757631 | No |
| binlog.000173 | 1073755121 | No |
| binlog.000174 | 1073752838 | No |
| binlog.000175 | 1073743951 | No |
| binlog.000176 | 1073748347 | No |
| binlog.000177 | 1073751179 | No |
| binlog.000178 | 1073763386 | No |
| binlog.000179 | 1073765359 | No |
| binlog.000180 | 1073745938 | No |
| binlog.000181 | 1073763697 | No |
| binlog.000182 | 1073744490 | No |
| binlog.000183 | 1073761859 | No |
| binlog.000184 | 1073775954 | No |
| binlog.000185 | 1073757908 | No |
| binlog.000186 | 1073773373 | No |
| binlog.000187 | 1073755666 | No |
| binlog.000188 | 1073743518 | No |
| binlog.000189 | 1073782889 | No |
| binlog.000190 | 1073756995 | No |
| binlog.000191 | 1073770640 | No |
| binlog.000192 | 1073743582 | No |
| binlog.000193 | 1073757032 | No |
| binlog.000194 | 1073754014 | No |
| binlog.000195 | 1073757718 | No |
| binlog.000196 | 1073746065 | No |
| binlog.000197 | 1073745350 | No |
| binlog.000198 | 1073751875 | No |
| binlog.000199 | 1073745702 | No |
| binlog.000200 | 1073754484 | No |
| binlog.000201 | 1073762857 | No |
| binlog.000202 | 1073761196 | No |
| binlog.000203 | 1073755084 | No |
| binlog.000204 | 1073836164 | No |
| binlog.000205 | 1073745617 | No |
| binlog.000206 | 1073753921 | No |
| binlog.000207 | 1073771413 | No |
| binlog.000208 | 1073764906 | No |
| binlog.000209 | 1073767754 | No |
| binlog.000210 | 1073781899 | No |
| binlog.000211 | 1073744615 | No |
| binlog.000212 | 1073744221 | No |
| binlog.000213 | 1073770872 | No |
| binlog.000214 | 323286661 | No |
| binlog.000215 | 398814 | No |
| binlog.000216 | 896660 | No |
+---------------+------------+-----------+
47 rows in set (0.03 sec)
Delete unnecessary binary logs accumulated as a temporary measure. to deletes the files before the specified binlog.
--Binary log deletion
mysql> PURGE MASTER LOGS TO 'binlog.000212';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Execute the df command again and confirm that the disk usage has decreased.
/dev/sdb1 50G 4.0G 46G 8% /data
The direct cause of the event is according to the MySQL Specifications, and the default settings are for binary logs. Binary logs continued to accumulate because the output was valid.
In previous MySQL versions, binary logging was disabled by default and enabled if you specified the --log-bin option. Starting with MySQL 8.0, binary logging is enabled by default regardless of whether you specify the --log-bin option.
Set log rotation as a permanent support, and change the setting so that binary logs are not accumulated. After changing the settings, restart MySQL.
# vi /etc/mysql/mysql.conf.d/mysqld.cnf
# binlog rotation
expire_logs_days = 7
In the above case, log rotation will take place in 7 days.
The root cause is lack of consideration at design time.
Recommended Posts