Purger les mysql-bin logs

From Deimos.fr / Bloc Notes Informatique
Jump to: navigation, search

1 Introduction

Slow query logs or bin logs are essentials for several things. In this documentation, we'll see how to manage them. This is useful when you don't have any disks space.

2 Slow query logs

2.1 Introduction

Slow query logs are simply standard logs. It could be manage as standard servers logs.

2.2 Flush logs

To flush logs, simplys run that command :

Command echo 
echo "" > slow-query.log

2.3 Rotate logs

Command 
mv slow-query.log slow-query.log.old
mysqladmin flush-logs

3 Bin logs

3.1 Introduction

I get a large amount of bin files in the MySQL data directory called "server-bin.n" or mysql-bin.00000n, where n is a number that increments. Usually /var/lib/mysql stores the binary log files. The binary log contains all statements that update data or potentially could have updated it. For example, a DELETE or UPDATE which matched no rows. Statements are stored in the form of events that describe the modifications. The binary log also contains information about how long each statement took that updated data.

Warn : do not delete manually the files or you couldn't restart your database !
 You' ll get this kind of error messages : 

061031 17:38:48  mysqld started
061031 17:38:48  InnoDB: Started; log sequence number 14 1645228884
/usr/libexec/mysqld: File '/var/lib/mysql/mysql-bin.000017' not found
(Errcode: 2)
061031 17:38:48 [ERROR] Failed to open log (file
'/var/lib/mysql/mysql-bin.000017', errno 2)
061031 17:38:48 [ERROR] Could not open log file
061031 17:38:48 [ERROR] Can't init tc log
061031 17:38:48 [ERROR] Aborting
 
061031 17:38:48  InnoDB: Starting shutdown...
 
061031 17:38:51  InnoDB: Shutdown completed; log sequence number 14 1645228884
 
061031 17:38:51 [Note] /usr/libexec/mysqld: Shutdown complete
061031 17:38:51  mysqld ended

3.2 The purpose of MySQL Binary Log

The binary log has two important purposes:

  • Data Recovery : It may be used for data recovery operations. After a backup file has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.
  • High availability / replication : The binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.

3.2.1 Disable MySQL binlogging

If you are not replicating, you can disable binlogging by changing your my.ini or my.cnf file. Open your my.ini or /etc/my.cnf (/etc/mysql/my.cnf), find a line that reads "log_bin" and remove or comment the following lines :

Configuration File /etc/my.cnf 
#log_bin                        = /var/log/mysql/mysql-bin.log
#expire_logs_days        = 10
#max_binlog_size         = 100M

Now restart MySQL

3.2.2 Purge Master Logs

If you ARE replicating, then you need to periodically RESET MASTER or PURGE MASTER LOGS to clear out the old logs as those files are necessary for the proper operation of replication. Use following command to purge master logs :

Command mysql 
mysql -u root -p 'MyPassword' -e "PURGE MASTER LOGS TO 'mysql-bin.03';"

or 

mysql -u root -p 'MyPassword' -e "PURGE MASTER LOGS BEFORE '2008-12-15 10:06:06';"

Note : I recommand to use the penultimate MySQL master log

You may want MySQL to do it by it's own. For that, change the configuration :

Configuration File /etc/my.cnf 
 expire_logs_days = 14

Then restart MySQL server.

4 Ressources

http://www.cyberciti.biz/faq/what-is-mysql-binary-log/
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
http://forums.mysql.com/read.php?10,78659,78660#msg-78660

Retrieved from "https://wiki.deimos.fr/index.php?title=Purger_les_mysql-bin_logs&oldid=9923"