Linux, FreeBSD, Juniper, Cisco / Network security articles and troubleshooting guides

Register

FAQ

It is currently Thu Dec 07, 2023 8:09 am

ROOT ‹ Website Development and Databases ‹ Mysql ‹ Mysql: Cleaning binary logs

Mysql Application & Query Optimization, Questions, Indexes, Bugs.

2 posts • Page 1 of 1

Mysql: Cleaning binary logs

« Previous topic | Next topic »

Author

Message

Post subject: Mysql: Cleaning binary logs | Posted: Thu May 13, 2010 5:21 am

Joined: Thu Aug 06, 2009 2:48 am
Posts: 105

Mysql: Cleaning binary logs

Here is how to purge or clean the MySql binary logs (NEVER remove them from the filesystem manually).

1. Using the "PURGE BINARY LOGS" command

Code:

mysql> PURGE BINARY LOGS TO 'mysql-bin.010';
mysql> PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

The BEFORE variant's datetime_expr argument should evaluate to a DATETIME value (a value in 'YYYY-MM-DD hh:mm:ss' format). 

Above commands will remove the binary logs from the disk.

2> Using the "RESET MASTER" command

Code:

mysql> RESET MASTER;

Above command will empty the binary logs, but not remove them.

The above commands should not be used when/before binary logs are used for data integrity check or for replication.

From mysql manual:

Quote:

You should not remove binary logs until you are certain that their contents have been processed by all slaves.

Further reading:
http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html
http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html

			Tweet
Follow @IvordeCom

You might also be interested in:
MySql doesn't start: libexec/mysqld: File './mysql-bin.index' not found (Errcode: 13). Mysql: add column to table. Rename MySql table. MySql rename table. How to use a mysql stored procedure. Mysql change column's default value. Mysql drop (remove) unique key. Mysql change root password. Mysql add Unique index to a table column. Mysql remove column from an existing table.

Top

Post subject: Re: Mysql: Cleaning binary logs | Posted: Tue May 18, 2010 9:45 am

Joined: Thu Dec 31, 2009 4:44 pm
Posts: 10

You can also add to /etc/mysql/my.cnf (in Debian, google for the rest of the distributions) the following

Code:

expire_logs_days = 5

in order to keep only the last 5 days binary logs.

I'm almost sure that the normal user doesn't need then binary logs (except the case when they use master-slave replication or they know what they are doing), so why not disabling them completely? (mysql say that only 1% of the server speed is lost when bin logs are active but still that's something)

Code:

Comment log-bin directive in the /etc/mysql/my.cnf - Debian
#log-bin

Also, why not removing the binary logs (except the last one) with:

Code:

rm -rf file.XXXX

I didn't read anywhere that MySQL is keeping any "an eye" on those files...

Top

2 posts • Page 1 of 1

Topics			Author	Replies	Views	Last post
Topics related to - "Mysql: Cleaning binary logs"
		MySql doesn't start: libexec/mysqld: File './mysql-bin.index' not found (Errcode: 13)	debuser	0	3220	Mon Feb 22, 2010 1:43 pm debuser
		Mysql: add column to table	debuser	1	4264	Fri Oct 08, 2010 1:38 am sonee4
		Rename MySql table	debuser	1	2373	Fri Oct 08, 2010 1:36 am sonee4
		MySql rename table	admin	2	2856	Mon Nov 28, 2011 3:39 am Darwin
		How to use a mysql stored procedure	debuser	1	2349	Tue Nov 22, 2011 1:23 am Darwin
		Mysql change column's default value	debuser	0	2794	Thu Aug 12, 2010 3:43 am debuser
		Mysql drop (remove) unique key	mandrei99	0	2721	Wed Jan 14, 2015 7:53 pm mandrei99
		Mysql change root password	mandrei99	0	2950	Tue Feb 10, 2015 6:50 pm mandrei99
		Mysql add Unique index to a table column	debuser	0	3305	Fri Aug 06, 2010 10:01 am debuser
		Mysql remove column from an existing table	debuser	0	2337	Fri Aug 06, 2010 10:06 am debuser

Who is online

Users browsing this forum: No registered users and 1 guest

You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:

ROOT ‹ Website Development and Databases ‹ Mysql

Delete all board cookies | The team | All times are UTC - 5 hours [ DST ]

phpBB SEO