Purger les mysql-bin logs
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
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 :
echo "" > slow-query.log
2.3 Rotate logs
mv slow-query.log slow-query.log.old mysqladmin flush-logs
3 Bin logs
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 :
#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 :
mysql -u root -p 'MyPassword' -e "PURGE MASTER LOGS TO 'mysql-bin.03';"
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 :
expire_logs_days = 14
Then restart MySQL server.