Binary logs contain a record of all changes to the database, both in data and structure. It does not keep track of simple SELECT statements. These are required for MySQL replication and can also be useful for performing point in time backups after a nightly database dump has been restored.
If binary logs are enabled within MySQL, but you are not expiring them after X amount of days, they will eventually cause your server to run out of disk space. Typically I see this value set to 5 days.
As a quick note before we begin, if the server is completely out of disk space, you may need to free up some space beforehand so you can work with the system. You can temporarily free up space by changing the filesystems reserved block setting from 5% to 1% by:
[root@db01 ~]# df -h / /dev/xvda1 79G 76G 0 100% / [root@web01 ~]# tune2fs -m 2 /dev/xvda1 [root@web01 ~]# df -h / /dev/xvda1 79G 76G 1.8G 98% /
To determine how many days worth of logs you can purge, first identify if this server is Master MySQL running running MySQL replication to a Slave MySQL server. If it is, log onto the Slave MySQL server, and see which binary log it is currently reading from:
[root@db02 ~]# mysql mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.xx.xx.xx Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000244 ...
So based off that, we can purge the binary logs up to mysql-bin.000244. So back on the master server, check the MySQL bin log status by:
[root@db01 ~]# mysql mysql> show binary logs; +------------------+------------+ | Log_name | File_size | +------------------+------------+ | mysql-bin.000230 | 454230880 | | mysql-bin.000231 | 511519497 | | mysql-bin.000232 | 483552032 | | mysql-bin.000233 | 472847181 | | mysql-bin.000234 | 443236582 | | mysql-bin.000235 | 408021824 | | mysql-bin.000236 | 531519875 | | mysql-bin.000237 | 468583798 | | mysql-bin.000238 | 495423661 | | mysql-bin.000239 | 474475274 | | mysql-bin.000240 | 689162898 | | mysql-bin.000241 | 1073749465 | | mysql-bin.000242 | 939200512 | | mysql-bin.000243 | 702552334 | | mysql-bin.000244 | 40656827 | +------------------+------------+
In my example, as I only want to keep 5 days worth of binary logs, we can purge all the prior bin logs by:
[root@db01 ~]# mysql mysql> purge binary logs to 'mysql-bin.000240; mysql> show binary logs; +------------------+------------+ | Log_name | File_size | +------------------+------------+ | mysql-bin.000240 | 689162898 | | mysql-bin.000241 | 1073749465 | | mysql-bin.000242 | 939200512 | | mysql-bin.000243 | 702552334 | | mysql-bin.000244 | 40656827 | +------------------+------------+
As I only want to have a 5 day retention set in general, you can do this live without restarting mysql by:
[root@db01 ~]# mysql mysql> show variables like '%expire_logs_days%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 0 | +------------------+-------+ mysql> SET GLOBAL expire_logs_days = 5; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%expire_logs_days%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 5 | +------------------+-------+ 1 row in set (0.00 sec)
Then make the setting persistent across MySQL restarts by adding it to the /etc/my.cnf:
[root@web01 ~]# vim /etc/my.cnf [mysqld] ... expire_logs_days = 5 ...
If you had to modify the filesystems block reservation, change it back to how it was beforehand, which is typically 5%. You can do this by:
[root@db01 ~]# tune2fs -m 5 /dev/xvda1