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.
Leave a Reply