On a mysql server you have each table stored in a datafile. These contain all of the data in your database, but things like power loss, drive corruption, improper shutdown, and server crashes can often corrupt these files. Often, the only way to get the data back is via restoring a backup or running a lengthy repair that will likely fail anyway.

Here is where the mysql binary log file comes in. Normally this file is used for mysql replication, but it plays a second and equally important role. Whenever a query is run on your database, it is also written to the binary log file. If your main database becomes corrupted, mysql will attempt to use the binary log to replay the last X queries and will usually result in correcting whatever data loss may have taken place. It is almost like a live backup (but shouldn't be used to replace real backups).

Now, the binary log does have a downside. It slows down the server slightly because every query has to be written to a file (normally mysql queries take place in memory if your dataset fits there). The log is written sequentially, so a standard SATA drive is fine and plenty fast to store the file. You should always try to keep the binary log on a drive separate from your mysql data directory. Not only to protect you from data loss if a drive fails, but also to cut back on I/O and keep your database as fast as possible.

So, how do I enable the binary log? It's easy, just hit up the my.cnf file on your server (usually /etc/my.cnf)

Add the following line somewhere in the [mysqld] section:


you can specify the path where the file/s should be stored

Save the file and restart mysql (service mysqld stop and service mysqld start)

Check the path you specified, refresh a couple times and you should see the file growing. Done...