…and other MySQL Oddities.
If your MySQL binary log files are a “Growing” problem… there are a few simple steps you can use to curtail their growth and save your disk space from these huge files.
They probably look like:
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
…
And around 1 gig in size each.
These files are generated when you have log-bin=mysql-bin or other log-bin= line in your /etc/my.cnf configuration file.
There are two ways to fix this problem:
The first is to stop the MySQL server from creating these binary files. The only reason you would need the binary log files is if you are doing mysql replication from a master to a slave. In this case, these files are necessary, at least until the slave(s) has caught up with the master. To stop this from logging, comment out (using #) the line containing ‘log-bin’.
The second is to continue logging – this is necessary as stated above if you are performing SQL replication.
You can add a line to your /etc/my.cnf file which will only keep these files around as long as necessary. The line is:
expire_logs_days = X
Where X is the number of days you’d like to keep them around. I would recommend 10, but this depends on how busy your MySQL server is and how fast these log files grow. Just make sure it is longer than the slowest slave takes to replicate the data from your master.
Just a side note: You know that you should do this anyway, but make sure you back up your mysql database. The binary log can be used to recover the database in certain situations; so having a backup ensures that if your database server does crash, you will be able to recover the data.
6 comments
After commenting log-bin=mysql-bin in /etc/my.cnf file i am not able to stop of generation of mysql-bin log file that are generated in /var/lib/mysql folder
After commenting log-bin=mysql-bin in /etc/my.cnf file i am not able to stop of generation of mysql-bin log file that are generated in /var/lib/mysql folder
Binary logs are also useful for backups, where they are used to provide incremental backup and support snapshot backups.
Binary logs are also useful for backups, where they are used to provide incremental backup and support snapshot backups.
If you are regularily dealing with large imports of data e.g. load data infile and have binary logging enabled remember that for a file of size x your db file will be at least size x, binary log at least size x and also your indexes will take up space. Therefore a file of size x you could require up to 3x disk capacity. Note that if you are reloading the data several times in a short period of time for testing purposes deleteing the tables will not purge the log, your disk space will get chewed up pretty fast! Consider issueing the command purge binary logs before ‘2009-07-31’; command…
If you are regularily dealing with large imports of data e.g. load data infile and have binary logging enabled remember that for a file of size x your db file will be at least size x, binary log at least size x and also your indexes will take up space. Therefore a file of size x you could require up to 3x disk capacity. Note that if you are reloading the data several times in a short period of time for testing purposes deleteing the tables will not purge the log, your disk space will get chewed up pretty fast! Consider issueing the command purge binary logs before ‘2009-07-31’; command…
Comments are closed.