Menu
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?
571
No, you should not delete them by hand. If you delete them at the disk level, mysql will crash. The command to remove them is: PURGE BINARY LOGS TO 'mysql-bin.
The binary logs are used for master-slave replication. When any change occurs on the primary/master database, the events that contain the changes are sent to the slaves. These events are executed on the slave servers to keep master and slave servers in synchronization.
If you're on Linux, you can use mv to rename log files while they're in use, and then after FLUSH LOGS, you know that MySQL is writing to a new, small file, and you can remove the old big files. Binary logs are different. To eliminate old binlogs, use PURGE BINARY LOGS.
To delete all binary log files, use RESET MASTER. To move to a new log file (for example if you want to remove the current log file), use FLUSH LOGS before you execute PURGE LOGS .
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
67
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.
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
