What to do if your disk runs out due to MySQL binary logs

Introduction

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.

temporary solution

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

Permanent support

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.

in conclusion

The root cause is lack of consideration at design time.

Recommended Posts

What to do if your disk runs out due to MySQL binary logs
What to do if you lose your EC2 key pair
What to do if yum breaks
What to do if you can't build your project with Maven
What to do if you forget your login password on Manjaro Linux
What to do if fprintd requires a password when registering your fingerprint
What to do if pipreqs results in UnicodeDecodeError
Note: What to do if pip install fails
What to do if mod_fcgid cannot resolve UnicodeEncodeError
What to do if rails s doesn't work
What to do if pip cannot be installed
What to do if atom autocomplete-python doesn't work
What to do if Docker-sync suddenly stops working
What to do if "amazon-linux-extras" → "No module named amazon_linux_extras"
What to do if you get an "unknown service" error from your gRPC server
What to do if ubuntu says your password is too short to change [passwd command] [ubuntu]
What to do if your Jupyter Notebook for beginners asks for a password or token
What to do if you can't pip install mysqlclient
No module named What to do if you get'libs.resources'
ModuleNotFoundError: No module What to do if you get'tensorflow.contrib'
What to do if SciPy installation fails on CentOS
What to do if a UnicodeDecodeError occurs in pip
What to do if pip install fails to install dependent libraries
What to do if sys / cdefs.h does not exist
What to do if pyenv is not enabled (zsh)
What to do if Jupyter Notebook on WSL does not start automatically in your browser
What to do if Linux Modem Manager is interfering with writing keymaps to your own keyboard
What to do if ʻarguments [0] .scrollIntoView ();` fails in python selenium
What to do if the package dependency cannot be repaired
What to do if pip gives a DistributionError in Homebrew
What to do if you get "coverage unknown" in Coveralls
What to do if you can't sort files with subscripts
What to do if a 0xC0000005 error occurs in tf.train.start_queue_runners ()
What to do if package installation fails when deploying to heroku
What to do if `pip install matplotlib` fails on Mac
What to do if you can't log in as root
What to do if you can't use WiFi on Linux
What to do if Linux VLC can no longer rotate
What to do if pip install mysqlclient fails on MacOS
What to do if you cat or tail a binary file and the terminal is garbled
CommandNotFoundError: Your shell has not been properly configured to use What to do if you get'conda activate'
What to do if you grep a text file and it becomes Binary file (standard input) matches
What to do if Python doesn't work on Git for Windows
What to do if you can't install pyaudio with pip #Python
What to do if you get a minus zero in Python
What to do if intellisense doesn't work with Anaconda + VSCode + Tensorflow2.1
What to do if python says "fatal error:'stdio.h' file not found"
What to do if you get a UnicodeDecodeError with pip install
What to do if Insecure Platform Warning appears when running Python
What to do if "Unnamed: 0" is added in to_csv-> read_csv in pandas
What to do if the inode is exhausted on EC2 Linux
What to do if PyAudio cannot be installed on Python 3.7, 3.8, 3.9 on Windows
What to do if you can't use the trash in Lubuntu 18.04.
Django: What to do if TemplateDoesNotExist at / admin / after setting templates
What to do if grep: empty (sub) expression appears on Mac grep