Activities

July 2013
M T W T F S S
« Jun   Aug »
1234567
891011121314
15161718192021
22232425262728
293031  

MySQL Replication : Removing aged MySQL Binary Logs

You will experience lack of disk space if you have setup MySQL replication for long time. Also in some case if there is any bulk insertion/updation occur in the database will also cause to increase the MySQL data folder size as well as binary logs files size too. Sometime these two combination may cause to eat up storage rapidly and get less free space there.

There are two points to be noted.

a.Setting up Mysql binary log file retention period exclusively in config file (my.cnf). I would prefer to keep last 7 days binary log backup if you restore from restore point. MySQL config file entry is

 expire_logs_days=7
max_binlog_size=1000M

b. Manually purge the binary logs by date.
If you are doing bulk data loading or updation in DB, it is advise to delete the binary logs which slave has executed immediately once it is transfer to slave.

Here I’m describing about second one since my MySQL data partition is growing high in my scenario.

a. Login to MySQL slave server and identify the binary file name (Master_Log_File)which Master is executed the identify the exec file name of Slave is being executed(Relay_Master_Log_File). You can get these information from “show slave status” command from Slave server.

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 192.168.20.20
                  Master_User: repl-user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000283
          Read_Master_Log_Pos: 127845468
               Relay_Log_File: slave-relay.000609
                Relay_Log_Pos: 103612141
        Relay_Master_Log_File: mysql-bin.000283
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: db1,db1,logs1
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table :
        Seconds_Behind_Master: 0

This eg. showing both Master and Slave are synchronized properly as “Seconds_Behind_Master” showing Zero value. Now I’m back to MySQL master and delete all the binary files which slave executed.

b. Identify the Binary log file count and disk usage. You can get the mysql binary log file location from config. entry.

   [root@DB01 ~]# grep "log-bin" /etc/my.cnf
       log-bin=/sqldata/mysql_logs/mysql-bin
       [root@DB01 ~]#

c. Choose a date to which showing the recent entry

 [root@DB01]# ls -lt  /sqldata/mysql_logs/| more
total 289682176
-rw-rw----. 1 mysql mysql  628771451 Jul 18 00:39 mysql-bin.000283
-rw-rw----. 1 mysql mysql      10471 Jul 18 00:33 mysql-bin.index
-rw-rw----. 1 mysql mysql 1050209493 Jul 18 00:33 mysql-bin.000282
-rw-rw----. 1 mysql mysql 1050261856 Jul 18 00:23 mysql-bin.000281
-rw-rw----. 1 mysql mysql 1050289533 Jul 18 00:02 mysql-bin.000280
-rw-rw----. 1 mysql mysql 1048884043 Jul 17 23:40 mysql-bin.000279
-rw-rw----. 1 mysql mysql 1050556435 Jul 17 23:21 mysql-bin.000278
-rw-rw----. 1 mysql mysql 1050119150 Jul 17 23:06 mysql-bin.000277
-rw-rw----. 1 mysql mysql 1049334394 Jul 17 22:51 mysql-bin.000276
-rw-rw----. 1 mysql mysql 1051413635 Jul 17 22:39 mysql-bin.000275
-rw-rw----. 1 mysql mysql 1048778704 Jul 17 22:32 mysql-bin.000274
-rw-rw----. 1 mysql mysql 1049436813 Jul 17 22:27 mysql-bin.000273
-rw-rw----. 1 mysql mysql 1049591355 Jul 17 22:22 mysql-bin.000272
-rw-rw----. 1 mysql mysql 1048632311 Jul 17 22:18 mysql-bin.000271
-rw-rw----. 1 mysql mysql 1050639687 Jul 17 22:13 mysql-bin.000270
-rw-rw----. 1 mysql mysql 1050118006 Jul 17 22:08 mysql-bin.000269
-rw-rw----. 1 mysql mysql 1050054077 Jul 17 22:00 mysql-bin.000268
-rw-rw----. 1 mysql mysql 1051799392 Jul 17 21:48 mysql-bin.000267
-rw-rw----. 1 mysql mysql 1049343651 Jul 17 21:36 mysql-bin.000266

d. Delete the aged files.

Now I’m going to delete all the files which created before “Jul 17 23:40:00”. Login to MySQL Master and get in to the shell.

mysql> PURGE MASTER LOGS BEFORE '2013-07-17-21:23:00';
Query OK, 0 rows affected (32.12 sec)
mysql>

There is another easy way. You can point the TO file no. to delete the logs up to the file count.

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.001783 | 1048889634 |
| mysql-bin.001784 | 1048589292 |
| mysql-bin.001785 | 1048625160 |
| mysql-bin.001786 | 1048774170 |
| mysql-bin.001787 | 1048608223 |
| mysql-bin.001788 | 1048583149 |
| mysql-bin.001789 | 1048975021 |
| mysql-bin.001790 | 1048590522 |
| mysql-bin.001791 | 1048584590 |
| mysql-bin.001792 | 1048585800 |
| mysql-bin.001793 | 1048597390 |

 mysql> PURGE BINARY LOGS TO 'mysql-bin.001786';
Query OK, 0 rows affected (0.45 sec)

All the binary log up to “mysql-bin.000282” will be deleted.

Perfect ! Now you have plenty of free disk space in server as I could remove about 300GB binary logs (around 30 files) Happy now !

Welcome back to another Job !!

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>