MySQL binary log : mysqlbinlog utility

• Mysql binary log is a special kind of logging facility provided by MySql by which we can record the log of the database changes statement along with the moment when statement get executed.
• We can log all insert, update and delete statement of the database in the mysql binary log.

What is MySQL binary log?

As we have already discus above that this is the special kind of log provided by mysql which record the log of database changes.

It store data in binary format, that is why name of the log is binary log. It store data in binary format because it is being used by mysqlbinlog utility to restore data from binary log. Basically it record the insert , update and delete query executed on the database in the binary log format along with the time. This is the reason it is being used to recover the database.

Why MySQL binary log?

• To get incremental backup of the database:   As mysql binary logging record all changes happen to the specific database table, it is used to get incremental backup of the database. Because if we have full backup of till 10th November and we have set up the binary log then we can get the further changes made in the database. So it is very good option to record the database changes.
• To do the database recovery in case of data loss:   As it store the change made on the database, we can do the database recovery by mysql binary log.
• To configure mysql database replication:  We can use mysql binary log to replicate database on the remote server.

Setup and Use of Binary log:

In Linux

• Stop the MySQL services:

# service mysqld stop

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

• Start the MySQL service

# service mysqld start

Posts to Review:

Purge Binary log MySQL Server Logs : Error log and General Query Log