In this article, we are going to learn about the concept of MySQL Binary Logs and their architecture. I am covering the following details in this article:

1. An overview of MySQL binary logs and their architecture
2. How to enable and disable binary logging
3. View and change the location of the binary logs

An overview of MySQL binary logs

The concept of MySQL binary logs and SQL Server transaction logs are the same. The binary logs contain information about the data changes that occurred on the MySQL instance. The binary logs contain all SQL statements, which update the data (INSERT, UPDATE DELETE) and the DDL statements (Create database object, drop database objects, truncate table) within the database. It contains the time taken to execute a statement that creates and updates the data. The SQL statement that is used to update the data are stored in the EVENT form. The EVENTs describe the global changes that occurred on the MySQL Server during any operations. It helps us to reproduce the change of the global state that had happened on the MySQL Server.

The purpose of the MySQL binary logs is the following:

1. The binary logs are used for master-slave replication. When any change occurs on the primary/master database, the events that contain the changes are sent to the slaves. These events are executed on the slave servers to keep master and slave servers in synchronization. When the changes are sent to the slave, the slave servers store the changes in the relay logs until they are executed. The format of the binary logs and relay logs are the same
2. 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

Following are the types of the binary logging:

1. Statement-based: The events in this binary log contain the DML queries (Insert, Update, and Delete) used to change the data
2. Row-based: The 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

The MySQL Server logs contain a set of binary logs and an index file. The binary log file contains:

1. A 4-byte magic number. The magic number bytes are 0xfe 0x62 0x69 0x6e = 0xfe ‘b”i”n’
2. The set of events that describes the changes made in the data. The event contains the following details
   1. Header bytes that provide the information about the type of event and the time when the event had occurred
   2. The data bytes provide the information of the specific event
3. The first event describes the format of the log file, and the final event is specifying the next binary log file
4. The index file contains the list of current binary logs

The naming convention of the log file is hostname-bin.NNNNNN. Here NNNN is a sequence. The hostname of my workstation is NISARG-PC; therefore, the naming convention is:

Nisarg-pc-bin.000001
Nisarg-pc-bin.000002
Nisarg-pc-bin.000003
…
Nisarg-pc-bin.index

If you have set up master-slave replication, then the naming convention of the relay log file will be as following:

Nisarg-pc-relay.000001
Nisarg-pc-relay.000002
Nisarg-pc-relay.000003
…
Nisarg-pc-relay.index

Let us see some important commands to manage the binary logs. For the demonstration, I have installed MySQL Server 8.0 on my workstation and the operating system that I am using is windows 10.

Enabling the binary logs

MySQL binary logs can be enabled or disabled by adding or removing the log-bin configuration option. The log-bin parameter is in the configuration files named my.ini. When we install MySQL Server, the configuration file is created in C:\ProgramData\MySQL\MySQL Server 8.0 directory. The directory is hidden, therefore, to view the ProgramData folder in windows explorer you must enable the ‘Show hidden files’ option from the Folder Option.

First, let us check the bin-log parameter’s value by querying the metadata table namedinformation_schema.global_variables. Below is the query:

mysql> select * from information_schema.global_variables;

Output:

ERROR 1109 (42S02): Unknown table ‘GLOBAL_VARIABLES’ in information_schema

We receive the above error because the information_schema tables are deprecated and removed in MySQL 8.0; therefore, instead of using the system table, we can check the status of the parameter by running the following query

mysql> show global variables like ‘log_bin’;

Screenshot of query output:

As you can see in the above image, the value of the log-bin parameter is OFF, which indicates that binary logging is disabled. Now, to enable binary logging, copy and paste the following line in the my.ini configuration file.

log-bin=”[HostName]-bin”

In the above lines, replace the [hostname] with your desired name. Restart the MySQL Server by executing the following command in PowerShell.

PS C:\WINDOWS\system32> Restart-Service MySQL80

Check the status of binary logging by executing the following query

mysql> show global variables like ‘log_bin’;

As you can see, the value of the log-bin parameter is ON, which indicates that binary logging is enabled.

Disabling binary logging

To disable the binary logging, add the following lines in the my.ini file.

[mysqld]
skip-log-bin

Restart the MySQL services.

PS C:\WINDOWS\system32> Restart-Service MySQL80

Run the following query in the MySQL command line:

mysql> show global variables like ‘log_bin’;

View the binary log location

To view the default location of the binary location, execute the following query:

mysql> show global variables like ‘%log_bin%’;

The output of the query:

To view the list of the binary logs, run the below command in MySQL command line utility:

mysql> show binary logs;

Change the default location of the Binary Logs

The MySQL binary logs and index files are saved in the C:\ProgramData\MySQL\MySQL Server 8.0 directory. We can change the default location of the binary logs. To do that, we must follow the below steps.

Step 1: Shutdown the MySQL service

To shut down the MySQL services, Open control panel open Administrative tools Open Services Locate the MySQL80 from the list of services Right-click on MySQL80 Click on Stop.

Step 2: Change the value of log-bin parameters

Now, change the value of the log-bin parameter. To do that, open the configuration file named my.ini. The file is in C:\ProgramData\MySQL\MySQL Server 8.0 directory. We want to move it to the D:\Binarylogs. Replace the value of the log-bin parameter, as shown below:

• Existing value: log-bin=”NISARG-PC-bin”
• New Value: log-bin=” D:\Binarylogs\NISARG-PC-bin”

Screenshot of the configuration file:

Save the configuration file.

Step 3: Copy the binary logs to the new directory

Copy all the binary logs from the C:\ProgramData\MySQL\MySQL Server 8.0 directory to the D:\Binarylogs directory.

Step 4: Start the MySQL services

To start the MySQL services, Open control panel open Administrative tools Open Services Locate the MySQL80 from the list of services Right-click on MySQL80 Click on Start.

Now, let us verify that the location of the binary logs have been updated or not. To do that, execute the following command in the MySQL command-line utility.

As you can see in the above image, the location of the binary logs has been changed.

Summary

In this article, we learned about MySQL binary logs. We have learned the following topics.

1. An overview of binary logs and their architecture
2. How to enable and disable binary logging
3. View and change the location of the binary logs

In the next article, we are going to learn how we can open and interpret the binary log events. To view the binary logs, we can use mysqlbinlog utility. We will be learning more about it in my next articles.

Table of contents

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022