[MySQL] MySQL Bin Files Eating Lots of Disk Space Eating, Disk, Bin, Space, Files

scyzxp

Q. I get a large amountof bin files in the MySQL data directory called "server-bin.n" ormysql-bin.00000n, where n is a number that increments. What is MySQLBinary Log? How do I stop these files being created?


A. Usually /var/lib/mysqlstores the binary log files. The binary log contains all statementsthat update data or potentially could have updated it. For example, aDELETE or UPDATE which matched no rows. Statements are stored in theform of events that describe the modifications. The binary log alsocontains information about how long each statement took that updateddata.
The purpose of MySQL Binary LogThe binary log has two important purposes:

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

Disable MySQL binloggingIf 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), enter:
# vi /etc/my.cnf
Find a line that reads "log_bin" and remove or comment it as follows:
#log_bin                        = /var/log/mysql/mysql-bin.logYou also need to remove or comment following lines:
#expire_logs_days        = 10
#max_binlog_size         = 100MClose and save the file. Finally, restart mysql server:
# service mysql restart
Purge Master LogsIf you ARE replicating,then you need to periodically RESET MASTER or PURGE MASTER LOGS toclear out the old logs as those files are necessary for the properoperation of replication. Use following command to purge master logs:
$ mysql -u root -p 'MyPassword' -e "PURGE BINARY LOGS TO 'mysql-bin.03';"
OR
$ mysql -u root -p 'MyPassword' -e "PURGE BINARY LOGS BEFORE '2008-12-15 10:06:06';"/code>
Suggested readings:
http://www.cyberciti.biz/faq/what-is-mysql-binary-log/