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

FAQ
It is currently Wed Jan 18, 2017 7:45 pm


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

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

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

Offline
 

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





Top
designeru
Post  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

Offline
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
Display posts from previous:  Sort by  
E-mail friendPrint view

Topics related to - "Mysql: Cleaning binary logs"
 Topics   Author   Replies   Views   Last post 
There are no new unread posts for this topic. MySql doesn't start: libexec/mysqld: File './mysql-bin.index' not found (Errcode: 13)

debuser

0

880

Mon Feb 22, 2010 1:43 pm

debuser View the latest post

There are no new unread posts for this topic. Mysql: add column to table

debuser

1

1414

Fri Oct 08, 2010 1:38 am

sonee4 View the latest post

There are no new unread posts for this topic. Rename MySql table

debuser

1

390

Fri Oct 08, 2010 1:36 am

sonee4 View the latest post

There are no new unread posts for this topic. MySql rename table

admin

2

662

Mon Nov 28, 2011 3:39 am

Darwin View the latest post

There are no new unread posts for this topic. How to use a mysql stored procedure

debuser

1

361

Tue Nov 22, 2011 1:23 am

Darwin View the latest post

There are no new unread posts for this topic. Mysql change column's default value

debuser

0

871

Thu Aug 12, 2010 3:43 am

debuser View the latest post

There are no new unread posts for this topic. Mysql drop (remove) unique key

mandrei99

0

700

Wed Jan 14, 2015 7:53 pm

mandrei99 View the latest post

There are no new unread posts for this topic. Mysql change root password

mandrei99

0

771

Tue Feb 10, 2015 6:50 pm

mandrei99 View the latest post

There are no new unread posts for this topic. Mysql add Unique index to a table column

debuser

0

914

Fri Aug 06, 2010 10:01 am

debuser View the latest post

There are no new unread posts for this topic. Mysql remove column from an existing table

debuser

0

506

Fri Aug 06, 2010 10:06 am

debuser View the latest post

 

Who is online
Users browsing this forum: No registered users and 0 guests
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:  
News News Site map Site map SitemapIndex SitemapIndex RSS Feed RSS Feed Channel list Channel list


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



phpBB SEO