Removing Old MySQL Binary Logs
Came across a server with MySQL crashing. It was a master server that replicates to a slave. It was out of drive space as there were a ton of these:
-rw-rw---- 1 mysql mysql 1073795569 Jan 14 09:54 mysql01-bin.000307 -rw-rw---- 1 mysql mysql 1073765655 Jan 14 10:31 mysql01-bin.000308 -rw-rw---- 1 mysql mysql 1073927777 Jan 14 11:08 mysql01-bin.000309 -rw-rw---- 1 mysql mysql 1073776505 Jan 14 11:49 mysql01-bin.000310 -rw-rw---- 1 mysql mysql 1073994517 Jan 14 12:30 mysql01-bin.000311 -rw-rw---- 1 mysql mysql 1073886795 Jan 14 13:08 mysql01-bin.000312 -rw-rw---- 1 mysql mysql 1073872052 Jan 14 15:57 mysql01-bin.000313 -rw-rw---- 1 mysql mysql 1074711434 Jan 14 20:10 mysql01-bin.000314 -rw-rw---- 1 mysql mysql 1078242304 Jan 15 06:07 mysql01-bin.000315 -rw-rw---- 1 mysql mysql 1073754426 Jan 15 11:24 mysql01-bin.000316 -rw-rw---- 1 mysql mysql 1074018667 Jan 15 12:09 mysql01-bin.000317 -rw-rw---- 1 mysql mysql 1073743679 Jan 15 13:13 mysql01-bin.000318 -rw-rw---- 1 mysql mysql 1073750433 Jan 15 21:23 mysql01-bin.000319 -rw-rw---- 1 mysql mysql 1406685635 Jan 16 06:28 mysql01-bin.000320 -rw-rw---- 1 mysql mysql 1073749072 Jan 16 09:35 mysql01-bin.000321 -rw-rw---- 1 mysql mysql 1073872884 Jan 16 15:45 mysql01-bin.000322 -rw-rw---- 1 mysql mysql 1073747581 Jan 17 02:17 mysql01-bin.000323 -rw-rw---- 1 mysql mysql 1242927593 Jan 17 06:27 mysql01-bin.000324 -rw-rw---- 1 mysql mysql 1073753145 Jan 17 11:13 mysql01-bin.000325 -rw-rw---- 1 mysql mysql 1073744074 Jan 17 20:28 mysql01-bin.000326
To delete old logs, follow this command:
mysql> PURGE BINARY LOGS TO 'mysql01-bin.000307'; Query OK, 0 rows affected (14.20 sec)
This purged all logs up until 'mysql01-bin.000307'. Also for configuration purposes, you may want to add the following directive in your /etc/my.cnf
:
expire_logs_days=7
Depending on your usage, you may want to keep less or more days worth of binary logs in rotation.