Make sure you enable binary logs in MySQL

Today, I learned a very valuable lesson. Make sure you always enable binary logs in MySQL. I would have lost atleast 3 months of effort, if it was not enabled. Luckily it was enabled and I was able to do a point in time restore after someone did a wrong click.

If you maintain any kind of MySQL server (even if it is just a stage machine) make sure you enable binary logs in it. It is very easy to do and when something bad happens, you will be happy that you enabled it.

How to enable it

To enable binary logs all you have to do is to enable the following two settings in your my.cnf config file.

log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 100

The first setting specifies the location where the logs should be kept and the second setting specifies the number of days to keep the logs. The default value is 10, but I changed it to 100.

How to restore from binary logs

You can do a point in time restore by using the mysqlbinlog tool, which comes with MySQL. Refer to this excellent tutorial to find out how to use mysqlbinlog tool.

Related posts

Tags: , ,

0 Comments so far

Follow up comments through RSS Feed | Post a comment

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>