The MySQL binary log contains “events” that describe database changes such as table creation operations or changes to table data. It is the oplog of mysql, and we can subscribe the log to follow all the actions.
Please see here for the official introduction.
In this page, we are supposed to introduce some management issue.
In a working database, we may find a set of binlog.xxx in our databases
# ls -alh /var/lib/mysql -rw-r----- 1 mysql mysql 1.1G May 14 20:54 binlog.000014 -rw-r----- 1 mysql mysql 1.1G May 16 11:55 binlog.000015 -rw-r----- 1 mysql mysql 1.1G May 20 16:43 binlog.000016 -rw-r----- 1 mysql mysql 1.1G May 22 15:04 binlog.000017 -rw-r----- 1 mysql mysql 1.1G May 24 06:42 binlog.000018 -rw-r----- 1 mysql mysql 1.1G May 25 16:20 binlog.000019 -rw-r----- 1 mysql mysql 1.1G May 27 14:35 binlog.000020 -rw-r----- 1 mysql mysql 1.1G May 29 11:16 binlog.000021 -rw-r----- 1 mysql mysql 1.1G May 30 20:09 binlog.000022 -rw-r----- 1 mysql mysql 1.1G Jun 1 10:25 binlog.000023 -rw-r----- 1 mysql mysql 1.1G Jun 3 01:15 binlog.000024 -rw-r----- 1 mysql mysql 1.1G Jun 4 23:17 binlog.000025 -rw-r----- 1 mysql mysql 1.1G Jun 6 07:27 binlog.000026 -rw-r----- 1 mysql mysql 1.1G Jun 7 04:20 binlog.000027 -rw-r----- 1 mysql mysql 1.1G Jun 10 18:38 binlog.000028 -rw-r----- 1 mysql mysql 730M Jun 13 06:07 binlog.000029 -rw-r----- 1 mysql mysql 1.8G Jun 13 11:08 binlog.000030 -rw-r----- 1 mysql mysql 11G Jun 13 13:59 binlog.000031 -rw-r----- 1 mysql mysql 345 Jun 13 14:51 binlog.000032 -rw-r----- 1 mysql mysql 304 Jun 13 13:59 binlog.index
Of course they are useful, but sometimes they may heavily occupied our storage. We may consider to reduce the size carefully.
In a running database, we can use SHOW BINARY LOGS Statement to list all the binlogs:
mysql> show binary logs; +---------------+-------------+-----------+ | Log_name | File_size | Encrypted | +---------------+-------------+-----------+ | binlog.000014 | 1073759719 | No | | binlog.000015 | 1073774705 | No | | binlog.000016 | 1073782121 | No | | binlog.000017 | 1073743580 | No | | binlog.000018 | 1073755312 | No | | binlog.000019 | 1073747705 | No | | binlog.000020 | 1073749601 | No | | binlog.000021 | 1073744165 | No | | binlog.000022 | 1073776296 | No | | binlog.000023 | 1073742562 | No | | binlog.000024 | 1073748929 | No | | binlog.000025 | 1073754303 | No | | binlog.000026 | 1073743403 | No | | binlog.000027 | 1073748589 | No | | binlog.000028 | 1073745944 | No | | binlog.000029 | 765304560 | No | | binlog.000030 | 1832543636 | No | | binlog.000031 | 11350721856 | No | | binlog.000032 | 345 | No | +---------------+-------------+-----------+ 19 rows in set (0.00 sec)
And then use PURGE BINARY LOGS Statement to cleanup the logs
mysql> purge binary logs to 'binlog.000032'; Query OK, 0 rows affected (0.15 sec)
We can see it take 150ms, but 0 rows affected. However, we can check the folder again:
# ls -alh /var/lib/mysql -rw-r----- 1 mysql mysql 345 Jun 13 14:51 binlog.000032 -rw-r----- 1 mysql mysql 304 Jun 13 13:59 binlog.index
All the previous binlogs are gone.
For future usage, we can set binlog_expire_logs_seconds to change it.
Check variable:
mysql> select @@binlog_expire_logs_seconds; +------------------------------+ | @@binlog_expire_logs_seconds | +------------------------------+ | 2592000 | +------------------------------+ 1 row in set (0.00 sec)
let's modify it:
mysql> set global binlog_expire_logs_seconds = 604800; Query OK, 0 rows affected (0.00 sec) mysql> select @@binlog_expire_logs_seconds; +------------------------------+ | @@binlog_expire_logs_seconds | +------------------------------+ | 604800 | +------------------------------+ 1 row in set (0.00 sec)
This state may changed after mysql restarted, and you can may this config to /etc/my.cnf in mysqld section. e.g.
[mysqld] binlog_expire_logs_seconds = 604800
Notice: expire_logs_days may also work, but will gone soon.
Specifies the number of days before automatic removal of binary log files. expire_logs_days is deprecated, and you should expect it to be removed in a future release. Instead, use binlog_expire_logs_seconds, which sets the binary log expiration period in seconds. If you do not set a value for either system variable, the default expiration period is 30 days. Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in Section 5.4, “MySQL Server Logs”.
