How To Enable Binary Logs In MYSQL Server In Linux

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. The binary log has two important purposes:

• 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.

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
OR
log-bin=mysql-bin
expire_logs_days = 2
# binary logging format – mixed recommended
binlog_format=mixed

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 using below command

# service mysqld start