How to clear binary logs in MySQL and reclaim space

8:54 PM

How to clear binary logs when /data usage exceeds threshold & alerts

Check the space usage
df -H
Filesystem             Size   Used  Avail Use% Mounted on
/dev/cciss/c0d0p3      5.3G   4.5G   586M  89% /
/dev/cciss/c0d0p1      104M    38M    61M  39% /boot
none                   4.2G      0   4.2G   0% /dev/shm
/dev/cciss/c0d0p2      5.3G   1.8G   3.4G  35% /home
/dev/cciss/c0d0p5       11G   4.2G   5.9G  42% /var
/dev/cciss/c0d0p7      9.9G   143M   9.3G   2% /tmp
/dev/cciss/c0d0p8      112G    16G    91G  15% /home/mysql-backups
/dev/mapper/mpath0     106G    99G   2.3G  98% /data
So usage is 98%. We need to clear old binary logs to reclaim space:
cd /data/mysql
 
ls -latr
total 73658548
-rw-rw----   1 mysql mysql       2421 Aug  7  2008 innodb_status.27190
-rw-rw----   1 mysql mysql          0 Aug 12  2008 innodb_status.14295
-rw-rw----   1 mysql mysql          0 Aug 12  2008 innodb_status.15941
-rw-rw----   1 mysql mysql          0 Aug 12  2008 innodb_status.16754
-rw-rw----   1 mysql mysql          0 Aug 12  2008 innodb_status.19104
-rw-rw----   1 mysql mysql          0 Aug 12  2008 innodb_status.19756
-rw-rw----   1 mysql mysql          0 Aug 12  2008 innodb_status.20405
-rw-rw----   1 mysql mysql          0 Aug 12  2008 innodb_status.21351
-rw-rw----   1 mysql mysql          0 Aug 12  2008 innodb_status.11471
drwx------   2 mysql mysql       4096 Oct 17  2008 jboss2
drwx------   2 mysql mysql       4096 Oct 17  2008 jboss1
drwx------   2 mysql mysql       4096 Nov 15  2008 userconvert
drwx------   2 mysql mysql       4096 Nov 15  2008 deltaprod
drwx------   2 mysql mysql       4096 Dec 10  2008 occasions
-rw-rw----   1 mysql mysql       3844 Feb  5  2009 innodb_status.23273
drwx------   2 mysql mysql       4096 Mar  5  2009 silverpop
-rw-rw----   1 mysql mysql    1765334 Mar 31  2009 mysql-error.err-old
drwx------   2 mysql mysql       4096 Jun 18  2009 jboss3
drwx------   2 mysql mysql       4096 Jun 19  2009 jboss4
-rw-rw----   1 mysql mysql       2480 Jul 25  2009 innodb_status.24240
-rw-rw----   1 mysql mysql          0 Jul 25  2009 innodb_status.24811
drwx------   3 mysql mysql       4096 Jul 28  2009 batchprocessing
-rw-------   1 mysql mysql        116 Nov  6  2009 .bash_history
drwx------   2 mysql mysql       4096 Jun 14  2010 jboss5
drwx------   2 mysql mysql       4096 Jun 14  2010 jboss6
drwx------   2 mysql mysql       4096 Jun 14  2010 jboss7
drwx------   2 mysql mysql       4096 Jul 26  2010 statistics
drwx------   2 mysql mysql       4096 Sep  3  2010 dbbench
drwx------   2 mysql mysql       4096 Oct 21  2010 order_import
drwx------   2 mysql mysql       4096 Nov  5  2010 automation
-rw-rw----   1 mysql mysql          1 Nov  9  2010 mysql-error.err
-rw-r--r--   1 root  root           6 Nov  9  2010 mysql_upgrade_info
drwx--x--x   2 mysql mysql       4096 Nov  9  2010 mysql
drwxr-xr-x   2 mysql mysql       4096 Nov 18  2010 test
drwx------   2 mysql mysql       4096 Mar  3 02:27 datawarehouse
drwx------   2 mysql mysql       4096 Mar  3 16:29 integration
drwx------   2 mysql mysql       4096 May 17 21:20 opensso
srwxrwxrwx   1 mysql mysql          0 Jun  3 21:39 mysql.sock
-rw-rw----   1 mysql mysql          6 Jun  3 21:39 mysql.pid
drwx------   2 mysql mysql       8192 Jun 16 08:25 enigma
-rw-rw----   1 mysql mysql 1074215304 Jun 21 07:18 mysql-bin.000342
-rw-rw----   1 mysql mysql 1073922353 Jun 21 07:48 mysql-bin.000343
-rw-rw----   1 mysql mysql 1073742657 Jun 22 07:14 mysql-bin.000344
-rw-rw----   1 mysql mysql 1073882190 Jun 22 07:35 mysql-bin.000345
drwx------   3 mysql mysql       8192 Jun 23 00:35 prosperiti
-rw-rw----   1 mysql mysql 1073997532 Jun 23 07:06 mysql-bin.000346
-rw-rw----   1 mysql mysql 1073787965 Jun 23 07:29 mysql-bin.000347
-rw-rw----   1 mysql mysql 1073956788 Jun 23 08:09 mysql-bin.000348
-rw-rw----   1 mysql mysql 1074812148 Jun 23 08:44 mysql-bin.000349
-rw-rw----   1 mysql mysql 1074010045 Jun 23 09:18 mysql-bin.000350
-rw-rw----   1 mysql mysql 1074686880 Jun 23 09:45 mysql-bin.000351
-rw-rw----   1 mysql mysql 1073855821 Jun 23 09:57 mysql-bin.000352
-rw-rw----   1 mysql mysql 1073760358 Jun 23 10:17 mysql-bin.000353
-rw-rw----   1 mysql mysql 1074143024 Jun 23 10:35 mysql-bin.000354
-rw-rw----   1 mysql mysql 1073870601 Jun 23 10:55 mysql-bin.000355
-rw-rw----   1 mysql mysql 1073807368 Jun 24 07:04 mysql-bin.000356
-rw-rw----   1 mysql mysql 1073974688 Jun 24 07:26 mysql-bin.000357
-rw-rw----   1 mysql mysql 1074070947 Jun 24 07:58 mysql-bin.000358
-rw-rw----   1 mysql mysql 1073742494 Jun 24 08:31 mysql-bin.000359
-rw-rw----   1 mysql mysql 1122535658 Jun 24 08:47 mysql-bin.000360
-rw-rw----   1 mysql mysql 1074740602 Jun 24 09:11 mysql-bin.000361
-rw-rw----   1 mysql mysql 1074022609 Jun 24 09:31 mysql-bin.000362
-rw-rw----   1 mysql mysql 1073901860 Jun 24 09:48 mysql-bin.000363
-rw-rw----   1 mysql mysql 1074793297 Jun 24 10:06 mysql-bin.000364
-rw-rw----   1 mysql mysql 1073827340 Jun 24 10:32 mysql-bin.000365
-rw-rw----   1 mysql mysql 1074030626 Jun 25 07:06 mysql-bin.000366
-rw-rw----   1 mysql mysql 1073972959 Jun 25 07:27 mysql-bin.000367
-rw-rw----   1 mysql mysql 1073881241 Jun 26 07:05 mysql-bin.000368
-rw-rw----   1 mysql mysql 1073891116 Jun 26 07:23 mysql-bin.000369
-rw-rw----   1 mysql mysql 1073847518 Jun 27 07:06 mysql-bin.000370
-rw-rw----   1 mysql mysql 1073904699 Jun 27 07:32 mysql-bin.000371
-rw-rw----   1 mysql mysql 1077491192 Jun 27 08:26 mysql-bin.000372
-rw-rw----   1 mysql mysql 1073878435 Jun 27 09:11 mysql-bin.000373
-rw-rw----   1 mysql mysql 1074272691 Jun 27 09:36 mysql-bin.000374
-rw-rw----   1 mysql mysql 1073974228 Jun 27 09:58 mysql-bin.000375
-rw-rw----   1 mysql mysql 1073790787 Jun 27 10:31 mysql-bin.000376
-rw-rw----   1 mysql mysql 1073975414 Jun 27 11:08 mysql-bin.000377
-rw-rw----   1 mysql mysql 1073960038 Jun 27 11:26 mysql-bin.000378
-rw-rw----   1 mysql mysql 1073749040 Jun 27 11:44 mysql-bin.000379
-rw-rw----   1 mysql mysql 1076763567 Jun 28 05:53 mysql-bin.000380
-rw-rw----   1 mysql mysql 1077342774 Jun 28 06:50 mysql-bin.000381
-rw-rw----   1 mysql mysql 1074056805 Jun 28 07:28 mysql-bin.000382
-rw-rw----   1 mysql mysql 1073902238 Jun 28 07:57 mysql-bin.000383
-rw-rw----   1 mysql mysql 1074712309 Jun 28 08:25 mysql-bin.000384
-rw-rw----   1 mysql mysql 1074044174 Jun 28 08:58 mysql-bin.000385
-rw-rw----   1 mysql mysql 1074061253 Jun 28 09:19 mysql-bin.000386
-rw-rw----   1 mysql mysql 1074212376 Jun 28 09:38 mysql-bin.000387
-rw-rw----   1 mysql mysql 1074026403 Jun 28 09:51 mysql-bin.000388
-rw-rw----   1 mysql mysql 1074000168 Jun 28 10:07 mysql-bin.000389
-rw-rw----   1 mysql mysql 1073763631 Jun 28 10:22 mysql-bin.000390
-rw-rw----   1 mysql mysql 1074077727 Jun 28 10:35 mysql-bin.000391
-rw-rw----   1 mysql mysql 1074215722 Jun 29 07:20 mysql-bin.000392
-rw-rw----   1 mysql mysql 1073983682 Jun 29 07:49 mysql-bin.000393
-rw-rw----   1 mysql mysql 1073873312 Jun 29 08:13 mysql-bin.000394
-rw-rw----   1 mysql mysql 1075007555 Jun 29 08:39 mysql-bin.000395
-rw-rw----   1 mysql mysql 1073843896 Jun 29 09:12 mysql-bin.000396
-rw-rw----   1 mysql mysql 1073799263 Jun 29 09:46 mysql-bin.000397
-rw-rw----   1 mysql mysql 1074046670 Jun 29 10:12 mysql-bin.000398
-rw-rw----   1 mysql mysql 1073754590 Jun 29 10:47 mysql-bin.000399
-rw-rw----   1 mysql mysql 1075210493 Jun 29 11:23 mysql-bin.000400
-rw-rw----   1 mysql mysql 1074110148 Jun 29 11:41 mysql-bin.000401
-rw-rw----   1 mysql mysql 1073894516 Jun 30 07:09 mysql-bin.000402
-rw-rw----   1 mysql mysql 1074011636 Jun 30 07:45 mysql-bin.000403
-rw-rw----   1 mysql mysql 1073956755 Jun 30 08:09 mysql-bin.000404
-rw-rw----   1 mysql mysql 1073943824 Jun 30 08:43 mysql-bin.000405
-rw-rw----   1 mysql mysql 1073756084 Jun 30 09:05 mysql-bin.000406
-rw-rw----   1 mysql mysql 1073779141 Jun 30 09:33 mysql-bin.000407
-rw-rw----   1 mysql mysql       2144 Jun 30 09:33 mysql-bin.index
drwxr-xr-x  25 mysql mysql       4096 Jun 30 09:33 .
-rw-rw----   1 mysql root     4864889 Jun 30 09:40 mysql.err
-rw-rw----   1 mysql mysql      25691 Jun 30 09:52 innodb_status.28816
-rw-rw-r--   1 mysql mysql   12228459 Jun 30 09:52 mysql-slow.log
-rw-rw----   1 mysql mysql  807403520 Jun 30 09:52 ibdata2
drwxr-xr-x   4 root  root        4096 Jun 30 09:52 ..
-rw-rw----   1 mysql mysql 1073741824 Jun 30 09:52 ib_logfile0
-rw-rw----   1 mysql mysql  536870912 Jun 30 09:52 ibdata1
-rw-rw----   1 mysql mysql  896455360 Jun 30 09:52 mysql-bin.000408
-rw-rw----   1 mysql mysql 1073741824 Jun 30 09:52 ib_logfile1
 
We can also verify the binary log files and their sizes using following MySQL command
 
mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
   ---              ---
   ---              ---
   ---              ---
Make sure you retain atleast 1 days of binary logs. If this server is a master to slaves, check slave status on all slaves and make sure you do not purge files with date timestamps greater or equal to the timestamp on the file that the slaves are reading from.
For EX: from ls -latr output, the file mysql-bin.000370 has the date timestamp
-rw-rw----   1 mysql mysql 1073847518 Jun 27 07:06 mysql-bin.000370
>show slave status\G
        .
        .
        .
Master_Log_File: mysql-bin.000370
        .
        .
        .
So we should only purge files with date timestamp earlier than the timestamp on the file mysql-bin.000370.
To clear binary logs before Jun 27, 2011 log in to mysql & run
mysql -uroot -p
Enter password:
 
mysql> purge binary logs before "2011-06-27";
Then check & make sure we have reclaimed space so that available space is below the threshold.
df -H
Filesystem             Size   Used  Avail Use% Mounted on
/dev/cciss/c0d0p3      5.3G   4.5G   586M  89% /
/dev/cciss/c0d0p1      104M    38M    61M  39% /boot
none                   4.2G      0   4.2G   0% /dev/shm
/dev/cciss/c0d0p2      5.3G   1.8G   3.4G  35% /home
/dev/cciss/c0d0p5       11G   4.2G   5.9G  42% /var
/dev/cciss/c0d0p7      9.9G   143M   9.3G   2% /tmp
/dev/cciss/c0d0p8      112G    16G    91G  15% /home/mysql-backups
/dev/mapper/mpath0     106G    69G    33G  68% /data

Share This Story

Tags: ,

Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor Aenean massa.

You Might Also Like

0 comments

Contact Form

Name

Email *

Message *

Translate

Wikipedia

Search results