What is MySQL Binary logs ,their usages and how to purge binary logs ?

Define Binary logs :

In MySQL, the changes that occur within the database are logged simultaneously. The binary log is a set of log files that contain information about data modifications made to a MySQL server instance. The log is enabled by starting the server with the –log- bin option. The MySQL Bin Log (Binary Logs) is responsible for handling these updates and hence provide a description of such events to indicate the changes made to the database being used. 

 The binary log has two important purposes:

1. Replication: The binary log on a primary replication server provides a record of the data changes to be sent to secondary server. The primary server sends the events contained in its binary log to its secondaries, which execute those events to make the same data changes that were made on the primary.
2. Data Recovery:  The binary logs can be used to perform the point in time recovery. Once the full backup is restored, the events recorded after the full backup can be re-executed from the binary logs to restore the state of the database to the point of time of the failure

Their are following types of the binary logging:

1. Statement-based:  Events in this binary log contain the DML queries (Insert, Update, and Delete) used to change the data.
2. Row-based:  Events in this binary log describe changes that occurred on the individual rows of the tables.
3. Mixed-Logging: In the mixed-logging mode, by default, MySQL uses statement-based logging, but if required, it automatically changes to row-based logging.

 MySQL Binary log & Its Verification :

Binary logging is enabled by default (the log-bin system variable is set to ON). The exception is if you use mysqld to initialize the data directory manually by invoking it with the –initialize and –initialize–secure option, when binary logging is disabled by default, but can be enabled by specifying the –log —bin option.

To disable binary logging, you can specify the — skip-log- bin or –disable-log-bin option at startup. If either of these options is specified and –log-bin is also specified, the option specified later takes precedence.

 MySQL Binary log is a must before being able to access the binary log.

First, stop the currently running MySQL service using the following command:

 # service mysqld stop

 Changes in the MySQL configuration files (/etc/my.cnf) and append the following lines of code in it :

log-bin=mysql-bin

expire_logs_days = 2

binlog_format=mixed # Recommended binary logging format – mixed

Restart the service, to bring the changes into effect:

# service mysqld start

This is how you can enable a binary log in MySQL.

It always a good practice to verify whether the process to enable binary logs succeeded or not. This can be done using the following command:

mysql> show variables like '%bin%';

If the value of the log_bin is ON it indicates a successful process however, if it’s OFF you can turn it on as follows:

mysql>SET GLOBAL log_bin = ON;

How to purge binary logs ?

Bin-log files in the MySQL database are used to keep track of all the modifications made to the database.

Bin- log files contain events for MySQL statements like create, update, and delete. However, binary files can fill the available storage space in the MySQL server database. you need to purge the MySQL Bin-log statements files using the MySQL PURGE BINARY LOGS statement to solve this problem.

The MySQL server creates the index file to keep the record of binary files with its name.

The MYSQL PURGE BINARY LOGS statement will delete all the binary files listed in the log index file. The deleted binary log files are removed from the list in the index file, thereby saving space on the MySQL server database.

PURGE BINARY LOGS Statement :

 PURGE { BINARY | MASTER } LOGS {
    TO 'log_name'
  | BEFORE datetime_expr
}

The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or date. BINARY and MASTER are synonyms. Deleted log files also are removed from the list recorded in the index file, so that the given log file becomes the first in the list.

PURGE BINARY-LOG requires the BINGLOG-ADMIN privilege. This statement has no effect if the server was not started with the –log- bin option to enable binary logging.