MySQL Clear Binary Log

clear_binary_log_mysql.txt

Can I Remove MySQL Binary Log Yes, as long as the data is replicated to Slave server, it’s safe to remove the file. It’s recommend only remove MySQL Binary Log older than 1 month.

Besides, if Recovery of data is the main concern, it’s recommend to archive MySQL Binary Log.

There are several ways to remove or clean up MySQL Binary Log, it’s not recommend to clean up the file manually, manually means running the remove command.

Remove MySQL Binary Log with RESET MASTER Statement Reset Master statement is uses for new database start up during replication for Master and Slave server. This statement can be used to remove all Binary Log.

To clean up Binary Log on Master Server

```
shell> mysql -u username -p
mysql> RESET MASTER;
```
To clean up Binary Log on Slave Server

```
mysql -u username -p
mysql> RESET SLAVE;
```

Remove MySQL Binary Log with PURGE BINARY LOGS Statement PURGE BINARY LOGS statement can remove Binary Log base on date or up to a Binary Log sequence number

Base on the binary logs example shown above, I would like to remove binary up to mysql-bin.000015

```
shell> mysql -u username -p
mysql>PURGE BINARY LOGS TO 'mysql-bin.000015';
```
Alternatively, you can remove the binary older than a specific date.

```
shell> mysql -u username -p
mysql> PURGE BINARY LOGS BEFORE '2009-05-01 00:00:00';
```

Remove MySQL Binary Log with mysqladmin flush-logs Command Another method is running mysqladmin flush-logs command, it will remove binary logs more than 3 days old.

```
shell> mysqladmin -u username -p flush-logs
```

Keep MySQL Binary Log for X Days All of the methods above required monitoring on disk usage, to “rotate” and keep the binary logs for x number of day. The option below can be configured on MySQL’s config file, my.cnf
```
expire_logs_days = 7
```
Consider turning off MySQL Binary Log if MySQL Replication is not deploy on the database server and recovery is not the main concern.

I have additional 15GB if disk space now

Please do not just delete them in the OS.

You need to let mysqld do that for you. Here is how mysqld manages it:

The file mysql-bin.[index] keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.[index] are:

PURGE BINARY LOGS TO 'binlogname';
PURGE BINARY LOGS BEFORE 'datetimestamp';
These will clear all binary logs before the binlog or timestamp you just specified.

For example, if you run

PURGE BINARY LOGS TO mysql-bin.000223;
this will erase all binary logs before mysql-bin.000223.

If you run

PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;
this will erase all binary logs before midnight 3 days ago.

If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:

mysql> SET GLOBAL expire_logs_days = 3;
then add this to /etc/my.cnf

[mysqld]
expire_logs_days=3
and mysqld will delete them logs for you

SHOW SLAVE STATUS\G

This is critical. When you run SHOW SLAVE STATUS\G, you will see two binary logs from the Master:

Master_Log_File
Relay_Master_Log_File
When replication has little or no lag these are usually the same value. When there is a lot of replication lag, these values are different. Just to make it simple, choose whatever Relay_Master_Log_File is, and go back to the Master and run

PURGE BINARY LOGS TO 'Whatever Relay_Master_Log_File Is';
That way, replication is not interrupted.

Give it a try!!!

http://www.uptimemadeeasy.com/mysql/using-the-mysql-binary-log/

PURGE BINARY LOGS BEFORE NOW();