MySQL How to remove and purge old MySQL binary log files

You can setup binary logs on MySQL where the data isn’t being replicated and critical. This operation will allow you to recover the database to a specific point in time.
You can take a daily MySQL dump with the right configuration on my .cnf file.

# cat /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0 skip-name-resolve character-set-server=utf8character-set-client-handshake=utf8 server-id = 1binlog_format = MIXEDexpire-logs-days = 10log-bin = /var/lib/mysql/webdb-binsync-binlog = 0 query-cache-type = 1query-cache-size = 32M [mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid [mysql]default-character-set=utf8

But the point you need to be careful that sometimes the binary logs can become very large. If you observe this problem please purge old data file with this command sets.

Step 1: Check binary logs size under the dump directory

# ls -ld /var/lib/mysql/webdb-bin.*-rw-rw—-. 1 mysql mysql 1073741982 Nov 9 22:39 /var/lib/mysql/webdb-bin.000346-rw-rw—-. 1 mysql mysql 1073744274 Nov 10 13:54 /var/lib/mysql/webdb-bin.000347-rw-rw—-. 1 mysql mysql 1073745254 Nov 11 00:11 /var/lib/mysql/webdb-bin.000348-rw-rw—-. 1 mysql mysql 1073743510 Nov 11 15:43 /var/lib/mysql/webdb-bin.000349-rw-rw—-. 1 mysql mysql 1073744433 Nov 12 02:49 /var/lib/mysql/webdb-bin.000350-rw-rw—-. 1 mysql mysql 1073744270 Nov 12 18:11 /var/lib/mysql/webdb-bin.000351-rw-rw—-. 1 mysql mysql 1073744501 Nov 13 06:05 /var/lib/mysql/webdb-bin.000352-rw-rw—-. 1 mysql mysql 1073744364 Nov 13 19:11 /var/lib/mysql/webdb-bin.000353-rw-rw—-. 1 mysql mysql 1073742990 Nov 14 09:36 /var/lib/mysql/webdb-bin.000354-rw-rw—-. 1 mysql mysql 1073744772 Nov 14 22:04 /var/lib/mysql/webdb-bin.000355-rw-rw—-. 1 mysql mysql 1073742496 Nov 15 14:11 /var/lib/mysql/webdb-bin.000356-rw-rw—-. 1 mysql mysql 1073744547 Nov 16 00:34 /var/lib/mysql/webdb-bin.000357-rw-rw—-. 1 mysql mysql 1073743805 Nov 16 15:44 /var/lib/mysql/webdb-bin.000358-rw-rw—-. 1 mysql mysql 1073743725 Nov 17 00:05 /var/lib/mysql/webdb-bin.000359-rw-rw—-. 1 mysql mysql 1073742231 Nov 17 13:46 /var/lib/mysql/webdb-bin.000360-rw-rw—-. 1 mysql mysql 1073745418 Nov 17 22:51 /var/lib/mysql/webdb-bin.000361-rw-rw—-. 1 mysql mysql 1073743713 Nov 18 14:10 /var/lib/mysql/webdb-bin.000362-rw-rw—-. 1 mysql mysql 1073746450 Nov 19 02:00 /var/lib/mysql/webdb-bin.000363-rw-rw—-. 1 mysql mysql 1073744049 Nov 19 16:35 /var/lib/mysql/webdb-bin.000364-rw-rw—-. 1 mysql mysql 1073742055 Nov 20 04:37 /var/lib/mysql/webdb-bin.000365-rw-rw—-. 1 mysql mysql 1073745845 Nov 20 17:24 /var/lib/mysql/webdb-bin.000366-rw-rw—-. 1 mysql mysql 1073746132 Nov 21 06:34 /var/lib/mysql/webdb-bin.000367-rw-rw—-. 1 mysql mysql 1073744492 Nov 21 17:41 /var/lib/mysql/webdb-bin.000368-rw-rw—-. 1 mysql mysql 884025641 Nov 24 11:58 /var/lib/mysql/webdb-bin.000369

Step 2:Connect DB and show MySQL binary logs

# mysql -u root Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners. Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement. mysql> SHOW BINARY LOGS;+——————+————+| Log_name | File_size |+——————+————+| webdb-bin.000346 | 1073741982 || webdb-bin.000347 | 1073744274 || webdb-bin.000348 | 1073745254 || webdb-bin.000349 | 1073743510 || webdb-bin.000350 | 1073744433 || webdb-bin.000351 | 1073744270 || webdb-bin.000352 | 1073744501 || webdb-bin.000353 | 1073744364 || webdb-bin.000354 | 1073742990 || webdb-bin.000355 | 1073744772 || webdb-bin.000356 | 1073742496 || webdb-bin.000357 | 1073744547 || webdb-bin.000358 | 1073743805 || webdb-bin.000359 | 1073743725 || webdb-bin.000360 | 1073742231 || webdb-bin.000361 | 1073745418 || webdb-bin.000362 | 1073743713 || webdb-bin.000363 | 1073746450 || webdb-bin.000364 | 1073744049 || webdb-bin.000365 | 1073742055 || webdb-bin.000366 | 1073745845 || webdb-bin.000367 | 1073746132 || webdb-bin.000368 | 1073744492 || webdb-bin.000369 | 885645135 |+——————+————+24 rows in set (0.00 sec) mysql>

Step 3: Remove old Binary logs

This command will remove binary logs that are older than “webdb-bin.000349”.

mysql> PURGE BINARY LOGS TO ‘webdb-bin.000349’;Query OK, 0 rows affected (0.34 sec) mysql>