The binary log contains information about SQL statements that modify database contents. This information is stored in the form of “events” that describe the modifications.

The binary log has two important purposes:

1. Replication:

The binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.

2. Data Recovery:

Certain data recovery operations require use of the binary log. After a backup file has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

How to enable binary logging in MySql?

To enable binary logging open the MySql configuration file my.cnf /my.ini and add the following line to the end of [mysqld] section and restart the server:

log_bin=base_name

Lets say base_name is given as ‘mysql-bin’.

This will write the binary log files in the MySql data directory. Mysql appends a numeric extension to the binary log basename to generate binary log file names. The number increases each time the server creates a new log file, thus creating an ordered series of files.

So the binary log file generated will be like ‘mysql-bin.0000001′.

If you want to write the binary log files to a different directory then specify the basename with a leading absolute path name to directory as below:

log_bin=directory_path/base_name

You do not need to provide any extension (like log_bin=base_name.extension) for the binary log files but if you have added it, it will silently removed and ignored.

Adding the above configuration will do binary logging for all the databases on your MySql server.

In case you want to do binary logging for only selected database(s) then you can use the option “binlog-do-db“as follows:

log_bin=base_name

binlog-do-db=db_name1

binlog-do-db=db_name2

Alternatively if you want to prevent binary logging of few databases then you can use the option “binlog-ignore-db” as below:

log_bin=base_name

binlog-ignore-db=db_name1

binlog-ignore-db=db_name2

Binary log files need to periodically deleted else your disk space may be full. Large binary files may slow up your database so the size of the binary files must be considered. You can use the option “max_binlog_size” to specify the size of your binary files.

log_bin=base_name

binlog-ignore-db=db_name1

max_binlog_size=100M

MySql server creates a new binary log file automatically after the current log’s size reaches “max_binlog_size“. Also the MySql server creates a new file in the series each time it starts or flushes the logs. In case of large transactions the log file size may become larger than “max_binlog_size” as the transactions are not split between files.

If you check your binary logs folder you will see a file with .index extension, its the binary log index file. The binary log index file contains the names of  all the binary log files used to keep a track of them. By default the index file has the same basename as the binary log file and an extension ‘.index’.

If you want the change the binary log index file base name then you can use the “log-bin-index” option as follows:

log_bin=base_name

log-bin-index=file_name

binlog-ignore-db=db_name1

max_binlog_size=100M

To purge the binary logs automatically after a specified number of days you can use the “expire_logs_days” option as below:

log_bin=base_name

log-bin-index=file_name

binlog-ignore-db=db_name1

max_binlog_size=100M

expire_logs_days=10

This will purge the MySql logs automatically after 10 days. For manually purging the binary logs, click here to read another article.

Do not forget to restart the MySql server after making changes to the config file.

References:

http://dev.mysql.com/doc/refman/5.6/en/binary-log.html