The availability of data is a crucial aspect of every business or organization. Organizations practice various methods to ensure that data is available for replication & recovery. MySQL BinLogs play a significant role in carrying out these processes. This article is aimed at educating you with in-depth knowledge about the various concepts associated with MySQL BinLogs. Follow our easy step-by-step guide to implement these concepts and understand your MySQL binary logs better.
Table Of Contents
- Introduction To MySQL BinLogs
- Working With MySQL BinLogs
Introduction To MySQL BinLogs
In MySQL, the changes that occur within the database are logged simultaneously. The MySQL BinLog (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. These can be the changes made to the data or an operation like new table creation, etc. They even provide information on statements that could have lead to a potential change.
The MySQL BinLogs serve two important purposes:
- Replication: When working on a master server, the binary logs contain a record of the changes that have occurred. These records are sent to the slave servers to help them execute those events & make the same data changes that were made on the master server. For more information on implementing replication, you can look into the replication manual.
- Data Recovery: Some recovery operations require using binary logs. Once the backup is restored, the recorded events are re-executed and this brings the database up to date from the time of backup.
An Easier Approach To Work With MySQL: Hevo Data
A fully managed, No-code Data Pipeline platform like Hevo Data, helps you export log data from MySQL (among 100+ Sources) to your desired destination in real-time & in an effortless manner. It allows the users to seamlessly export log data in a fully automated and convenient manner rather than requiring them to write the code repeatedly to access BinLogs hence Hevo performs the same operation automatically in a completely secure and reliable way.Get started with hevo for free
Check out What makes Hevo amazing:
- Reliable Data Migration: Hevo’s robust infrastructure ensures that there is no data loss and the data transfer is highly reliable.
- Secure: End-to-end encryption and two-factor authentication ensure that your data is secured.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
- Working knowledge of MySQL.
- Working knowledge of MySQL Shell.
- Using command line.
- MySQL installed on host workstation.
Working With MySQL BinLogs
Let’s have a look at the concepts you will come across here:
- Enabling A MySQL BinLog & Its Verification
- Determining The Current BinLogs
- Retrieving Contents Of A MySQL BinLog
- Using BinLogs To Get Entries Of A Particular Database
- Disabling MySQL BinLogs For Recovery
- Setting Up The Binary Log Format
- Specific Entry Extraction In BinLogs
- MySQL BinLog Retention
Enabling A MySQL BinLog & Its Verification
Enabling a MySQL BinLog is a must before being able to access the binary log. This can be done using MySQL’s my.ini file.
First, stop the currently running MySQL service using the following command:
# service mysqld stop
Now make 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
There’s another way to assign a value to the log-bin parameter:
log-bin = /var/lib/mysql/<application-name>-mysql-bin.log
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%';
This code will result in an output similar to this:
+---------------------------------+----------------------+ | Variable_name | Value | +---------------------------------+----------------------+ | binlog_cache_size | 38152 | | binlog_format | MIXED | | innodb_locks_unsafe_for_binlog | OFF | | log_bin | ON | | log_bin_trust_function_creators | OFF | | log_bin_trust_routine_creators | OFF | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 419430400 | | sql_log_bin | ON | | sync_binlog | 0 | +---------------------------------+----------------------+ 10 rows in set (0.00 sec)
The parameter to check here is the log_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;
Determining The Current BinLogs
To retrieve a list of all the BinLogs present in your system, make use of the following command:
mysql> SHOW BINARY LOGS;
This command will display a list of all binary logs present in the system only when the binary log is enabled otherwise, it gives an error.
Retrieving Contents Of A MySQL BinLog
The content stored in the binary log can be viewed in a human-friendly format using the mysqlbinlog command. To use this command from the terminal, you need to first add the path of the MySQL bin directory saved on your system as a path variable.
The command can be used in the following way:
Example query: Here the name of the log file being used is mysql-bin.000001.
This command returns a description of all events that have occurred for every database in the system.
Using BinLogs To Get Entries Of A Particular Database
The mysqlbinlog command displays the log file contents for all databases that are a part of the system. This command can be modified to display the events that have occurred only for a particular database using -d or -database option. These options are followed by the database name for the which the logs are required.
mysqlbinlog -d mdata mysqld-bin.000001 > crm-event_log.txt
mysqlbinlog --database mdata mysqld-bin.000001 > crm-event_log.txt
Both these commands are valid and retrieve the events log for a database called mdata and stores it in a file called event_log.txt.
Disabling MySQL BinLogs For Recovery
Creation of a MySQL BinLog during a database recovery is highly undesirable as it creates an unending loop that will keep on restoring data infinitely and each restore process will further create a binary log.
Disabling the BinLogs is, therefore a must and this can be done using the -D option in the mysqlbinlog command.
mysqlbinlog -D mysqld-bin.000001
Using -D option results in an extra line in the output, which says SQL_LOG_BIN=0.
You can also use the -disable-log-bin as follows:
mysqlbinlog --disable-log-bin mysqld-bin.000001
Setting Up The Binary Log Format
There are three possible formats in which the MySQL BinLogs can exist. These are statement, row, and mixed. The format of the BinLog can be set up using the –binlog-format option as follows:
It is possible to determine the format being used by the binary log file using the following command:
show variables like "%binlog_format%"
The type being used by the BinLogs can be modified using the SET GLOBAL command as follows:
SET GLOBAL binlog_format = 'STATEMENT'; SET GLOBAL binlog_format = 'MIXED'; SET GLOBAL binlog_format = 'ROW';
This will modify the BinLog type as per the users choice.
Specific Entry Extraction In BinLogs
Reading an entire binary log can be a gruelling task. It is possible to read-only the specific portions of the log file using an offset value in the mysqlbinlog command. This can be done using the -O option as follows:
mysqlbinlog -o 5 mysqld-bin.000001
This command will skip the first 5 rows of the binary log.
It is possible to extract data from a particular position in the binary log, you can specify the position you want to read data from, using the -j option as follows:
mysqlbinlog -j 123 mysqld-bin.000002 > from-123.txt
It is also possible to specify a position up to where you want the data to be extracted, this is called the stop position. You can use the -stop option as follows:
mysqlbinlog --stop-position=219 mysqld-bin.000001 > upto-219.txt
MySQL BinLog Retention
The period for which a binary log exists can be set using the expire_logs_days variable. You can fix some days as a value for it. This will ensure that the logs are retained and get deleted only afterwards.
To check the current value of the expire_logs_days variable, use the following command. If you have never changed it, it will have a value of 0 by default.
show variables like "expire_logs_days";
To set a new value for this variable you can use the following command:
SET GLOBAL expire_logs_days = number_of_days;
For further information on MySQL BinLogs, you can look into the binary log documentation.
This article outlines how to master the skill of utilizing the power of MySQL BinLogs to monitor the changes that have happened in the database & act upon them practically in real-time with no hassle. With data growing at an exponential rate, in real-life situations handling such humongous amounts of data can be gruelling and this is where Hevo comes into the picture offering its users a fully automated, No-code solution that helps monitor data quite easily with minimal supervision.visit our website to explore hevo
Give Hevo an opportunity to amaze you with its seamless experience! sign up for a 14-day free trial!
Let us know about your experience of working with MySQL binary logs! Share your thoughts in the comment section below.