How to clear old mysql bin log files in mysql 5.7

Advertisements

We have seen how to do mysql master slave replication in one of our previous post. Once we setup mysql server as master with traditional replication, it will start creating bin-logs. This will go on and on, depending on the transactions happening on you database it may eat all your storage space.

One day I checked the disk usage of one of our mysql instance and found that 82% of disk is utilized!

[root@mysql mysql]# df -hT
Filesystem           Type   Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                            ext4    50G   39G  8.6G  82% /
tmpfs                   tmpfs  3.0G     0  3.0G   0% /dev/shm
/dev/xvda1           ext4   477M   55M  398M  12% /boot
/dev/mapper/VolGroup-lv_home
                           ext4    94G  642M   89G   1% /home

You can check in you master mysql console which is the current bin-log file.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000089 |    25682 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+

Then I checked the size of /var/lib/mysql and found a lot of bin-log files. When I checked the master status I found that the current bin file is mysql-bin.000089. So I can delete the files till that. Or the files till the last week.

And you can delete all the old bin-log files upto the latest one

mysql> PURGE BINARY LOGS TO 'mysql-bin.000075';
Query OK, 0 rows affected (0.91 sec)

You can actually automate this. You need to specify the expire log days variable in my.cnf of the master mysql server.

for example.
vim /etc/my.cnf
under [mysqld] add
expire_logs_days=7

Restart the mysql service and you are done.