- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
MySQL - SHOW BINLOG EVENTS Statement
MySQL SHOW BINLOG EVENTS Statement
The SHOW BINLOG EVENTS statement displays the events in the specified binary log. Incase if you haven't specified a log name the first log is displayed. To execute this statement, you need the REPLICATION SLAVE privilege. This statement displays the following details −
Log_name − Name of the log file.
Pos − Starting position of the event.
Event_type − Identifier specifying the type of the event.
Server_id − ID of the server.
End_log_pos − ending position of an event in the log.
Info − More information about the event.
Syntax
Following is the syntax of the MySQL SHOW BINLOG EVENTS Statement −
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
Example
You can display the list events in a binary log as shown below −
SHOW BINLOG EVENTS\G;
Output
The above query produces the following output −
********** 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
Using the in clause you can display the details of the events in a particular file.
SHOW BINLOG EVENTS IN TP-bin.000113';
Output
Following is the output of the above mysql query −
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 |
The FROM clause
The FROM clause is used to display the list of events from a particular position in a log.
SHOW BINLOG EVENTS IN TP-bin.000111' FROM 1241 \G;
Output
After executing the above query, it will produce the following output −
********** 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 limit the events in a log while displaying, using the LIMIT clause.
SHOW BINLOG EVENTS IN TP-bin.000113' LIMIT 2\G;
Output
Following is the output of the above query −
********** 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)