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

Posts to Review:

MySQL Server Logs : Error log and General Query Log MYSQL Partitioning : What is and HOW To ? Rename table in MYSQL Client Programming Security Guidelines – MySQL