Using MySQL Show Binary Logs Command Made Easy: The Ultimate Guide 101

In today’s business environment, data is king. When data is needed, it must be available right away. Your business needs this to be successful, not only in order to remain compliant with security regulations, but also in order to run efficiently.

Each time you make a change to a MySQL or MariaDB database, the event is logged. With the MySQL Show Binary Logs command, you can view the binary log files on the server. 

In this article, you will learn the concept of MySQL Show Binary Logs commands and their architecture.

Table of Contents

• What is MySQL?
  • Key Features of MySQL
• MySQL Show Binlog Events Command
• MySQL Show Binary Logs: Binary Log
• How Does MySQL Show Binary Logs Work in MySQL?
• Conclusion

What is MySQL?

MySQL is a prominent Relational Database Management System that is open-source. MySQL data is organised into tables with rows and columns. MySQL was first released in 1995 and is created, distributed, and supported entirely by Oracle Corporation. MySQL is written in the C and C++ programming languages from a technical standpoint.

MySQL runs on a variety of operating systems, including Microsoft Windows, Oracle Solaris, AIX, Symbian, Linux, and macOS. SQL instructions can be used to obtain, edit, or add data to MySQL tables. MySQL is also an important part of the Modern LAMP stack, which includes a Linux-based operating system, an Apache Web Server, a MySQL database, and PHP for processing.

SQL can also be used to create the table schema and even the database’s relationships between tables. SQL commands can be typed into client-side graphical user interfaces like MySQL WorkBench, SequelPro, or DBVisualizer, and the server will respond with the necessary information. A commercial Enterprise edition of MySQL is also available, which provides premium support services and a number of extensions that can be added as Server Plugins.

Key Features of MySQL

Organizations such as Facebook, Flickr, Twitter, Wikipedia, and YouTube all use MySQL. This is due to the following set of MySQL’s unique characteristics:

• Ease of Use: MySQL may be used with a wide range of programming languages, including PHP, PERL, C, C++, and JAVA. It also includes a collection of user-friendly tools for activities like server administration, reporting, and data analysis. MySQL supports numerous Data structures, JSON and Geospatial data, as well as Logical, Numeric, Alphanumeric, Date, and Time data types, allowing you to work with a wider range of datasets.
• Query-Performance: MySQL provides best-in-class query performance on a variety of clustered servers. It comes with a separate memory cache and table index partitioning for quick loading. MySQL can also handle changing workloads and process massive amounts of data at lightning speed. With the default file size restriction of 4 GB, you can store more than 50 million rows of data in a table. This can be expanded to a theoretical limit of 8 million terabytes depending on the operating system setup (TB).
• Open-Spourced: MySQL is open-source and is covered by the GNU General Public License (GPL), which guarantees it will always be free to use. It allows you to customise it based on your needs using Oracle’s Open-Source MySQL codebase. Because MySQL is Open-Source software, it has attracted a significant community that contributes to its documentation and online support culture on a regular basis.
• User-Friendly: MySQL supports many character sets, including latin1 (cp1252), deutsch, big5, ujis, and others, to make it more user-friendly. You can optionally specify the language in which the server sends the client error messages. With the option to establish particular time zones for individual clients, the MySQL Server Time can also be dynamically adjusted.
• Data Access Control: MySQL gives you complete control over the settings for Data Access Control. Access Privilege Systems and User Account Management are powerful tools for controlling who may see and access your MySQL data. With Host-based Verification and Password Encryption, MySQL sets the bar high.

MySQL Show Binlog Events Command

By using SHOW BINLOG EVENTS, you can view the events in a MySQL Show Binary Log. A default log is displayed if you don’t specify a log name. You need the REPLICATION SLAVE privilege to execute this statement. 

Here, you will learn about:

• Syntax
• The IN Clause
• The FROM Clause
• The LIMIT Clause

For each event in the MySQL Show Binary Log, SHOW BINLOG EVENTS displays the following information:

• Log_name: is the name of the file which is being listed.
• Pos: Refers to a location where the event occurs.
• Event_type: is an identifier that identifies the event type.
• Server_id: Is the ID of the server from which the event originated.
• End_log_pos: is the position where the next event starts, which is the same as Pos plus the size of the event.
• Info: A description of the event type in more detail. The format of this information varies based on the event type.

Syntax

The syntax of the MySQL SHOW BINLOG EVENTS statement in MySQL Show Binary Logs is as follows:

SHOW BINLOG EVENTS
   [IN 'log_name']
   [FROM pos]
   [LIMIT [offset,] row_count]

For example, the events can be displayed in a MySQL Show Binary Log as shown below:

mysql> SHOW BINLOG EVENTS\G;
*************************** 1. row ***************************
   Log_name: TP-bin.000105
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 125
       Info: Server ver: 8.0.22, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: TP-bin.000105
        Pos: 125
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 156
       Info:
*************************** 3. row ***************************
   Log_name: TP-bin.000105
        Pos: 156
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 233
   Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: TP-bin.000105
        Pos: 233
 Event_type: Query
  Server_id: 1
End_log_pos: 366
   Info: use `test`; DROP TABLE `employee` /* generated by server */ /* xid=8 */
*************************** 5. row ***************************
   Log_name: TP-bin.000105
        Pos: 366
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 445
   Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 6. row ***************************
   Log_name: TP-bin.000105
        Pos: 445
 Event_type: Query
  Server_id: 1
End_log_pos: 730
       Info: use `test`; CREATE TABLE EMPLOYEE(
         ID INT NOT NULL,
         FIRST_NAME CHAR(20) NOT NULL,
         LAST_NAME CHAR(20),
         AGE INT,
         SEX CHAR(1),
         INCOME FLOAT,
         CONTACT INT
) /* xid=9 */
*************************** 7. row ***************************
   Log_name: TP-bin.000105
        Pos: 730
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 809
   Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'

The IN Clause

The IN clause in MySQL Show Binary Logs allows you to display the details of the events in a particular file.

mysql> SHOW BINLOG EVENTS IN TP-bin.000113';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| TP-bin.000113 | 4   | Format_desc    | 1         | 125         | Server ver: 8.0.22, Binlog ver: 4 |
| TP-bin.000113 | 125 | Previous_gtids | 1         | 156         |                                   |
| TP-bin.000113 | 156 | Stop           | 1         | 179         |                                   |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
3 rows in set (0.00 sec)

The FROM Clause

FROM clauses are used to display data from a specific position in a log in MySQL Show Binary Logs.

mysql> SHOW BINLOG EVENTS IN TP-bin.000111' FROM 1241 G;
*************************** 1. row ***************************
   Log_name: TP-bin.000111
        Pos: 1241
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 1320
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 2. row ***************************
   Log_name: TP-bin.000111
        Pos: 1320
 Event_type: Query
  Server_id: 1
End_log_pos: 1543
       Info: use `xo`; CREATE DEFINER=`root`@`localhost` PROCEDURE `sample4`()
BEGIN
      SELECT 'This is a sample procedure';
END /* xid=12 */
*************************** 3. row ***************************
   Log_name: TP-bin.000111
        Pos: 1543
 Event_type: Stop
  Server_id: 1
End_log_pos: 1566
       Info:
3 rows in set (0.00 sec)

The LIMIT Clause

You can use LIMIT clause to limit the events displayed in a log in MySQL Show Binary Logs.

mysql> SHOW BINLOG EVENTS IN TP-bin.000113' LIMIT 2G;
*************************** 1. row ***************************
   Log_name: TP-bin.000113
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 125
       Info: Server ver: 8.0.22, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: TP-bin.000113
        Pos: 125
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 156
       Info:
2 rows in set (0.00 sec)

MySQL Show Binary Logs: Binary Log

In MySQL Show Binary Logs, every time you make a change in a MySQL or MariaDB database, it is logged. This information is contained in the binary log. Simply put, the binary log is a set of log files containing information about modifications made to a MySQL server instance. This includes information about updating a database, deleting a database, creating and deleting tables, etc.

When you use MySQL replication, you can benefit greatly from the binary log. The master MySQL server will send the data to the slave MySQL server by means of a binary log. MySQL recovery can also be performed by utilizing the binary log in MySQL Show Binary Logs.

How Does MySQL Show Binary Logs Work in MySQL?

To view all the binary logs in MySQL Show Binary Logs, we use the following syntax:

SHOW BINARY LOGS;/* - - here we are updating the row - - */

Following is the syntax for reading the contents of the MySQL Show Binary Logs:

mysqlbinlog [options] <log_file_name>

In order to view the contents of the MySQL Show Binary Log in a human-readable format, you can use the mysqlbinlog command line tool.

For example, using the following command, you can see the content of mysql-bin.000001:

mysqlbinlog --no-defaults /var/log/mysql/mysql-bin.000001

As a result, you should receive the following output after using MySQL Show Binary Logs:

#210303 11:52:15 server id 1  end_log_pos 503 CRC32 0x7260a26e  GTID 0-1-2 ddl
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
# at 503
#210303 11:52:15 server id 1  end_log_pos 596 CRC32 0x2b572f9f  Query   thread_id=38    exec_time=0 error_code=0
SET TIMESTAMP=1614772335/*!*/;
create database testdb1
/*!*/;
# at 596
#210303 11:53:26 server id 1  end_log_pos 619 CRC32 0xa1f8c5c4  Stop
DELIMITER ;
# This is the end of the log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

If you run the above command, you can see that a large amount of data is displayed. The -d option allows you to specify a database and display information associated with that database.

Use the following command to dump all the events from the testdb database into the testdb.txt file.

mysqlbinlog --no-defaults -d testdb /var/log/mysql/mysql-bin.000001 > testdb.txt

If you only want to view queries from the MySQL Show Binary Log file, run the following command:

mysqlbinlog --no-defaults -s /var/log/mysql/mysql-bin.000001

Sometimes you do not want to log events in MySQL Show Binary Log files, for example, during database recovery. In that case, run the following command to disable the binary log:

mysqlbinlog --no-defaults -D /var/log/mysql/mysql-bin.000001

Conclusion 

The MySQL Binlog contains all of the database modifications that have occurred. All of the changes are recorded in binary form on the server in MySQL Show Binary Logs. The “mysqlbinlog” utility is used to read the content of the file. Binary logs are useful for MySQL replication, in which the main server sends data from binary logs to the remote servers. The binary logs are stored when we perform operations like creating a table or updating data from an existing table.

However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, and Marketing Platforms to your MySQL Database can seem to be quite challenging. If you are from non-technical background or are new in the game of data warehouse and analytics, Hevo Data can help!

Visit our Website to Explore Hevo

Hevo Data will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 150+ multiple sources to Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

You can also have a look at our unbeatable Hevo Pricing that will help you choose the right plan for your business needs!