MySQL - Purge binary logs | OpenWritings.net

MySQL is generating huge binary log files. You can check the size of these files at /var/log/mysql/

>ls -al /var/log/mysql/
total 971M
drwxr-s--- 1 mysql adm   280 Jul 19 16:51 ./
drwxr-xr-x 1 root  root  380 Jul 19 15:52 ../
-rw-rw---- 1 mysql adm  101M Jul 19 16:22 mariadb-bin.000001
-rw-rw---- 1 mysql adm  101M Jul 19 16:27 mariadb-bin.000002
-rw-rw---- 1 mysql adm  101M Jul 19 16:33 mariadb-bin.000003
-rw-rw---- 1 mysql adm  101M Jul 19 16:37 mariadb-bin.000004
-rw-rw---- 1 mysql adm  101M Jul 19 16:39 mariadb-bin.000005
...
-rw-rw---- 1 mysql adm   374 Jul 19 16:51 mariadb-bin.index

Solution

I purged these files using the PURGE BINARY LOGS command. For examples, I did the followings:

Use SHOW BINARY LOGS command to show all binary logs files.
Use PURGE BINARY LOGS TO command to purge files.

MariaDB [(none)]>  SHOW BINARY LOGS;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 105022157 |
| mariadb-bin.000002 | 104944709 |
| mariadb-bin.000003 | 105023060 |
| mariadb-bin.000004 | 105009603 |
| mariadb-bin.000005 | 104875375 |
| mariadb-bin.000006 | 105046989 |
| mariadb-bin.000007 |  68849378 |
| mariadb-bin.000008 | 104869743 |
| mariadb-bin.000009 | 104902219 |
| mariadb-bin.000010 | 104888375 |
| mariadb-bin.000011 |  28850374 |
+--------------------+-----------+
11 rows in set (0.000 sec)
 
PURGE BINARY LOGS TO "mariadb-bin.000011";

MySQL

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.