Enable Binary Logs In MySQL Server


Overview :

The binary log contains events that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows). The binary log also contains information about how long each statement took that updated data.

Why Binary Logs are Required :

The binary log has two important purpose:

  • For 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.
  • Certain data recovery operations require use of the binary log. After a backup 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.

Steps To Enable Binary Logs :

 Step:1 Stop the mysql service using below command

 # service mysqld stop 

Step:2 Edit the mysql configuration files (/etc/my.cnf) and add below lines

 log-bin = /var/lib/mysql/<application-name>-mysql-bin.log
 expire_logs_days = 2
 # binary logging format – mixed recommended

The logs will go to the mysql data directory and will have the mysql-bin prefix if we use "log-bin=mysql-bin" otherwise we can mentioned the location as we have done in above file."expire_logs_days" system variable to expire binary log files automatically after a given number of days.

Step:3 Now start the mysql service

# service mysqld start