18

I have a MySQL server with binary logging active. Once a day logs file is "rotated", i.e. MySQL seems to stop writing to it and creates and new log file. For example, I currently have these files in /var/lib/mysql

-rw-rw---- 1 mysql mysql 10485760 Jun  7 09:26 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jun  7 09:26 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jun  2 15:20 ib_logfile1
-rw-rw---- 1 mysql mysql  1916844 Jun  6 09:20 mybinlog.000004
-rw-rw---- 1 mysql mysql 61112500 Jun  7 09:26 mybinlog.000005
-rw-rw---- 1 mysql mysql 15609789 Jun  7 13:57 mybinlog.000006
-rw-rw---- 1 mysql mysql       54 Jun  7 09:26 mybinlog.index

and mybinlog.000006 is growing.

Can I simply take mybinlog.000004 and mybinlog.000005, zip them up and transfer to another server, or I need to do something else before?

What info is stored in mybinlog.index? Only the info about the latest binary log?

UPDATE: I understand I can delete the logs with PURGE BINARY LOGS which updates mybinlog.index file. However, I need to transfer logs to another computer before deleting them (I test if backup is valid on another machine). To reduce the transfer size, I wish to bzip2 the files. What will PURGE BINARY LOGS do if log files are not "there" anymore?

Milan Babuškov
  • 59,775
  • 49
  • 126
  • 179

4 Answers4

25

You can delete old binary logs. Instead of deleting them directly, it is safer to use the MySQL-statement PURGE BINARY LOGS which also updates your mybinlog.index file. This file stores which filenames have been used for binary logging, see

http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html

Further, you can configure your MySQL-Server to delete old binary logs automatically. Set the variables max_binlog_size and expire_logs_days in your server configuration to appropriate values.

The ibdata and ib_logfile files have nothing to do with binary logging. They are used by the innodb storage engine. Do not be mistaken by the fact that they do not seem to grow: If you have innodb-tables on your server, these files are important and deleting them may result in loss of data. You can read more about InnoDB in the docs:

http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

titanoboa
  • 2,548
  • 15
  • 12
  • I need to zip log files and then transfer via network to another computer where I periodically apply them to make sure backup is valid. If I bzip them, the extension will change to .bz2. I guess PURGE BINARY LOGS would not remove them in that case. I could unpack files after transfer, but that just does not sound like the way it should be done. – Milan Babuškov Jun 07 '10 at 18:07
7
mysql> PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;

DELETE ALL bin files before 3 DAYS!

or 

PURGE MASTER LOGS BEFORE '2010-10-08 00:00:00';
zloctb
  • 10,592
  • 8
  • 70
  • 89
6

I finally found the answer on MySQL website. In case somebody needs this information:

Prior to MySQL 5.0.60, PURGE BINARY LOGS TO and PURGE BINARY LOGS BEFORE did not behave in the same way (and neither one behaved correctly) when binary log files listed in the .index file had been removed from the system by some other means (such as using rm on Linux). Beginning with MySQL 5.0.60, both variants of the statement fail with an error in such cases. (Bug#18199, Bug#18453) To handle such errors, edit the .index file (which is a simple text file) manually to ensure that it lists only the binary log files that are actually present, then run again the PURGE BINARY LOGS statement that failed.

This means I should edit .index file manually and everything will be fine. What's interesting is that .index file is a regular textual file. I didn't even notice that until now.

Milan Babuškov
  • 59,775
  • 49
  • 126
  • 179
  • Hey, I recently ran into this issue as well. What did you do? I have over 300GB of log files? Do I erase the files, and the contents of the index text file? – Nomad Mar 24 '14 at 21:42
  • I have the same issue where purge binary logs command isn't removing the binary logs and no error being shown. I wonder whether Milan's idea will work. Since it is a production DB, server I am being cautious. – mezi Apr 03 '14 at 15:49
  • It works. Since 2010. I did just that in production many times. – Milan Babuškov Apr 27 '14 at 17:23
-3

mysql-bin.index usually carry all the .bin files. If u have removed some files pls edit the .index to reflect what are all files available. If u have removed all .bin files remove empty the .index file. This will solve u r problem.

Prasanna k Ram
  • 1
  • 1