…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

Firesheep Should Be A Call To Arms For System, Network & Web Admins

Firesheep by Eric Butler has just been released to the world. This Firefox plugin does a few things that have already been fairly easy to do for a while, but rolled up in one easy to use package: Sniffs data on unencrypted Wireless Networks Looks for unencrypted login cookies sent to known popular insecure sites Allows you to login to that account with ‘One Click’ So what sites are impacted by default? Amazon.com, Basecamp, bit.ly, Cisco, CNET, Dropbox, Enom, Evernote, Facebook, Flickr, Github, Google, HackerNews, Harvest, Windows Live, NY Times, Pivotal Tracker, Slicehost, tumblr, Twitter, WordPress, Yahoo, and Yelp are among the few. A plugin system allows anyone to add their own sites (and cookie styles) to the plugin. Yikes! It goes without saying that this is a major security problem for anyone who uses unencrypted wireless networks. Includes on this list are many universities and companies such as Starbucks.

Command Line Packet Sniff Existing Running Process in Linux

Have you ever come across a server that is doing a lot…

Disabling The hald-addon-storage Service On CentOS/RedHat

The hald – Hardware Access Layer Daemon – runs several processes in…

Converting Public SecureCRT to OpenSSH one line Key

When you generate a key pair in SecureCRT, it generates it in…