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/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

skip-name-resolve

character-set-server=utf8
character-set-client-handshake=utf8

server-id = 1
binlog_format = MIXED
expire-logs-days = 10
log-bin = /var/lib/mysql/webdb-bin
sync-binlog = 0

query-cache-type = 1
query-cache-size = 32M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-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 its
affiliates. Other names may be trademarks of their respective
owners.

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>

 

Tagged In:

I'm a IT Infrastructure and Operations Architect with extensive experience and administration skills and works for Turk Telekom. I provide hardware and software support for the IT Infrastructure and Operations tasks.

205 Total Posts
Follow Me

Related Post