MySQL Binary Logs

If you have binary logging enabled in MySQL, it’s quite possible that the disk used by these binary logs to be is quite high.

For instance:

bash-3.2# ls -al /opt/mysql/var/
total 16240180
drwx------  5 mysql root        4096 Sep 30 15:33 .
drwxr-xr-x 11 mysql mysql       4096 Sep 30 15:33 ..
drwx------  2 mysql root        4096 Aug 15 02:15 mysql
-rw-rw----  1 mysql mysql      19103 Aug 15 02:15 mysql-bin.000001
-rw-rw----  1 mysql mysql     729901 Aug 15 02:15 mysql-bin.000002
-rw-rw----  1 mysql mysql        125 Aug 15 02:15 mysql-bin.000003
-rw-rw----  1 mysql mysql        106 Aug 15 02:15 mysql-bin.000004
-rw-rw----  1 mysql mysql 1074557984 Sep 18 17:59 mysql-bin.000005
-rw-rw----  1 mysql mysql 1074199027 Sep 18 18:52 mysql-bin.000006
-rw-rw----  1 mysql mysql   47809403 Sep 18 18:53 mysql-bin.000007
-rw-rw----  1 mysql mysql 1077169445 Sep 18 18:55 mysql-bin.000008
-rw-rw----  1 mysql mysql 1074419095 Sep 18 18:57 mysql-bin.000009
-rw-rw----  1 mysql mysql 1073742057 Sep 18 21:41 mysql-bin.000010
-rw-rw----  1 mysql mysql 1073742056 Sep 19 03:58 mysql-bin.000011
-rw-rw----  1 mysql mysql 1073781146 Sep 19 10:53 mysql-bin.000012
-rw-rw----  1 mysql mysql 1073742068 Sep 20 01:15 mysql-bin.000013
-rw-rw----  1 mysql mysql 1073754553 Sep 22 18:22 mysql-bin.000014
-rw-rw----  1 mysql mysql  158604158 Sep 25 18:33 mysql-bin.000015
-rw-rw----  1 mysql mysql        125 Sep 25 18:36 mysql-bin.000016
-rw-rw----  1 mysql mysql        125 Sep 25 19:21 mysql-bin.000017
-rw-rw----  1 mysql mysql        125 Sep 25 19:47 mysql-bin.000018
-rw-rw----  1 mysql mysql    5212790 Sep 25 20:34 mysql-bin.000019
-rw-rw----  1 mysql mysql 1073741925 Sep 26 01:43 mysql-bin.000020
-rw-rw----  1 mysql mysql 1073741893 Sep 26 03:43 mysql-bin.000021
-rw-rw----  1 mysql mysql 1073741937 Sep 26 14:24 mysql-bin.000022
-rw-rw----  1 mysql mysql 1073741938 Sep 27 08:38 mysql-bin.000023
-rw-rw----  1 mysql mysql 1073746311 Sep 28 20:43 mysql-bin.000024
-rw-rw----  1 mysql mysql  875141163 Sep 30 15:22 mysql-bin.000025
-rw-rw----  1 mysql mysql  488120905 Oct  4 01:52 mysql-bin.000026
-rw-rw----  1 mysql mysql        494 Sep 30 15:33 mysql-bin.index
...
bash-3.2# du -ch mysql-bin*|grep total
16G     total

I’ve had these binary logs fill up a neglected server’s disks to 100% which caused quite a bit of pain.

To manually clear these logs, run the PURGE BINARY LOGS command in MySQL.


mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 |      19103 |
| mysql-bin.000002 |     729901 |
| mysql-bin.000003 |        125 |
| mysql-bin.000004 |        106 |
| mysql-bin.000005 | 1074557984 |
| mysql-bin.000006 | 1074199027 |
| mysql-bin.000007 |   47809403 |
| mysql-bin.000008 | 1077169445 |
| mysql-bin.000009 | 1074419095 |
| mysql-bin.000010 | 1073742057 |
| mysql-bin.000011 | 1073742056 |
| mysql-bin.000012 | 1073781146 |
| mysql-bin.000013 | 1073742068 |
| mysql-bin.000014 | 1073754553 |
| mysql-bin.000015 |  158604158 |
| mysql-bin.000016 |        125 |
| mysql-bin.000017 |        125 |
| mysql-bin.000018 |        125 |
| mysql-bin.000019 |    5212790 |
| mysql-bin.000020 | 1073741925 |
| mysql-bin.000021 | 1073741893 |
| mysql-bin.000022 | 1073741937 |
| mysql-bin.000023 | 1073741938 |
| mysql-bin.000024 | 1073746311 |
| mysql-bin.000025 |  875141163 |
| mysql-bin.000026 |  492014457 |
+------------------+------------+
26 rows in set (0.00 sec)

mysql> purge binary logs before '2011-08-01';
Query OK, 0 rows affected (1.61 sec)

The above purge command did not clear anything. Rather than specifying the date, it’s also possible to purge all logs before a specified file by running something like:

mysql> purge binary logs to 'mysql-bin.000005';
Query OK, 0 rows affected (6.25 sec)

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000005 | 1074557984 |
| mysql-bin.000006 | 1074199027 |
| mysql-bin.000007 |   47809403 |
| mysql-bin.000008 | 1077169445 |
| mysql-bin.000009 | 1074419095 |
| mysql-bin.000010 | 1073742057 |
| mysql-bin.000011 | 1073742056 |
| mysql-bin.000012 | 1073781146 |
| mysql-bin.000013 | 1073742068 |
| mysql-bin.000014 | 1073754553 |
| mysql-bin.000015 |  158604158 |
| mysql-bin.000016 |        125 |
| mysql-bin.000017 |        125 |
| mysql-bin.000018 |        125 |
| mysql-bin.000019 |    5212790 |
| mysql-bin.000020 | 1073741925 |
| mysql-bin.000021 | 1073741893 |
| mysql-bin.000022 | 1073741937 |
| mysql-bin.000023 | 1073741938 |
| mysql-bin.000024 | 1073746311 |
| mysql-bin.000025 |  875141163 |
| mysql-bin.000026 |  495852653 |
+------------------+------------+
22 rows in set (0.00 sec)

As you can see, all logs up to 000005 was deleted.

To disable binary logging if your server is not replicating itself by editing removing log-bin=mysql-bin in mysql.cnf