…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
  1. 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

  2. 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

  3. Binary logs are also useful for backups, where they are used to provide incremental backup and support snapshot backups.

  4. Binary logs are also useful for backups, where they are used to provide incremental backup and support snapshot backups.

  5. 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…

  6. 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.

You May Also Like

Dead linux users?

Not dead as in dead, but dead as in the user has logged out of the system and for some reason their shell is still open. This might happen if your system crashes before you can log out, there are network problems and you are disconnected, or a number of other reasons. This article explains how to log these “dead” shell users out.

Installatron WordPress Upgrade Disables Plugins

I just found a quick tip on the Installatron forums. By default…

How To Reset Windows XP, Vista, Windows 7 Passwords with Ubuntu 9.10 Live Image and a USB Drive

I put this together for a project in a class I am…

Fixing terminal page up and page down

If you use a windows ssh terminal client, or even macosx’s terminal.app,…