Purge Binary log

If you have enabled binary logging for the point-in-time recovery (or using replication in your environment) option and forgot to purge it then it may eat up memory in no time, so to “purge” binary logs from production server follow the steps(depending upon your environment)

  • List the current binary logs

List the current binary logs using “show master logs”/ “show binary logs” SQL command

mysql> show binary logs;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000150 | 524288218 |
| mysql-bin.000151 | 524288143 |
| mysql-bin.000152 | 524288132 |
| mysql-bin.000153 | 524288477 |
| mysql-bin.000154 | 366621412 |
| mysql-bin.000155 | 63230512 |
| mysql-bin.000156 | 524288109 |
| mysql-bin.000157 | 524288505 |
| mysql-bin.000158 | 524288201 |
| mysql-bin.000159 | 524288460 |
| mysql-bin.000160 | 524288075 |
| mysql-bin.000161 | 524288397 |
| mysql-bin.000162 | 524288330 |
| mysql-bin.000163 | 524288348 |
| mysql-bin.000164 | 524288353 |

| mysql-bin.000186 | 524288063 |
| mysql-bin.000187 | 524288387 |
| mysql-bin.000188 | 524288091 |
| mysql-bin.000189 | 49126815 |
+——————+———–+
40 rows in set (0.00 sec)

  • PURGE BINARY LOGS

Run PURGE BINARY LOGS statement deletes all the binary log files up to listed in the log index file prior to the specified log file name mysql-bin.000188.

mysql> PURGE BINARY LOGS TO ‘mysql-bin.000188’;

Note:

You can also set the binary log expiry date in my.cnf file to automatically purge binary log file. To know how to do this click here  .

MySQL binary log : mysqlbinlog utility

 

Related Posts

  • 80
    Mysql binary log is a special kind of logging facility provided by MySql by which we can record the log of the database changes statement along with the moment when statement get executed. We can log all insert, update and delete statement of the database in the mysql binary log.…
    Tags: log, binary, mysql
  • 43
    To learn more about what is binary log and how to setup Click here MySQL binary log : mysqlbinlog utility mysqldump – MySQL Database Backup and restore program Purge Binary log MySQL Master-Master-Slave-Slave Replication Database Recover from MySQL binary log: Binary logs store all the queries (in STATEMENT format) or…
    Tags: binary, mysql, log
  • 41
    Of course, there are many aspects of MySQL replication, but my main focus will be the logistics – how replication events are written on the master, how they are transferred to the replication slave and then how they are applied there. Note that this is NOT a HOWTO setup replication,…
    Tags: log, binary
  • 41
    MySQL Server has several logs that can help you find out what activity is taking place. By Default , No Logs are enabled in MySQL. By default, the server writes files for all enabled logs in the data directory. Logs can be flush by Issuing FLUSH LOGS statement. Binary log…
    Tags: log, logs, mysql
  • 38
    ALTER IGNORE TABLE `cdr` ADD UNIQUE(`uniqueid`)      
    Tags: mysql, delete

Satya Prakash

VOIP Expert: More than 8 years of experience in Asterisk Development and Call Center operation Management. Unique Combination of Skill Set as IT, Analytics and operation management.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: