15 mysqlbinlog Command Examples for MySQL Binary Log Files

In MySQL or MariaDB, anytime you make a change to the database, that particular event is logged.

For example, when you create a new table, or update data on an existing table, those events are stored in the binary logs.

Binary log is very helpful in MySQL replication, where the main server will send the data from the binary logs to the remote servers.

You’ll also be dealing with binary log files when you are performing any kind of recovery operations in MySQL.

mysqlbinlog command is used to view the content of the binary log in a readable user friendly format. You’ll also use the mysqlbinlog command to read the content and pipe it to other mysql utilities.

In this tutorial, we’ll discuss the following examples using mysqlbinlog command:

1. Get a List of Current Binary Logs
2. Mysqlbinlog Default Behavior
3. Get Entries for a Specific Database
4. Disable Binary log for Recovery
5. Control base-64 BINLOG in the Output
6. Debug Messages in mysqlbinlog Output
7. Skip First N number of Entries
8. Save Output to a File
9. Extract Entries starting from a Specific Position
10. Extract Entries upto a Specific Position
11. Flush Logs for Clean Binlog Output
12. Display Only SQL Queries in the Output
13. View Entries starting from a Specific Time
14. View Entries upto a Specific Time
15. Get Binary Log from a Remote Server

1. Get a List of Current Binary Logs

From mysql, execute the following show binary logs command which will display all the binary logs in your system.

mysql> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| mysqld-bin.000001 |     15740 |
| mysqld-bin.000002 |      3319 |
..
..

If you are system doesn’t have the binary logging enabled, then you’ll see the following error message.

mysql> SHOW BINARY LOGS;
ERROR 1381 (HY000): You are not using binary logging

By default, the binary log files are located under the /var/lib/mysql directory as shown below.

# ls -l /var/lib/mysql/
-rw-rw----. 1 mysql mysql 15740 Aug 16 14:57 mysqld-bin.000001
-rw-rw----. 1 mysql mysql  3319 Aug 16 14:57 mysqld-bin.000002
..
..

2. Mysqlbinlog Default Behavior

The following will display the content of the specified mysql binary log file (for example: mysqld-bin.000001) in a user friendly format.

# mysqlbinlog mysqld-bin.000001

The following is the partial output of the above command:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170726 14:57:37 server id 1  end_log_pos 106   Start: binlog v 4, server v 5.1.73-log created 170726 14:57:37 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#170726 14:59:31 server id 1  end_log_pos 182   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1501095571/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
..
..
..
# at 14191
#170726 15:20:38 server id 1  end_log_pos 14311         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1501096838/*!*/;
insert into salary(name,dept) values('Ritu', 'Accounting')
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

The above command will display events that happened on all the database on that system.

Note: When you execute mysqlbinlog command, you might sometime get this error message “unknown variable default-character-set=utf8”, in that case, use the –set-charset option in the mysqlbinlog to set appropriate character set. Another option would be to add the “loose-default-character-set=utf8” to your /etc/my.cnf file under the [client] section.

If you are new to MySQL, this will help: 6 Steps to Install and Configure MariaDB MySQL on Linux

3. Get Entries for a Specific Database

The default output of mysqlbinlog can be overwhelming, as it will display lot of data from all the statements.

Using -d option, you can also specify a database name, which will display on the events that happens on the given database.

The following command will dump all the events that belongs to “crm” database into the crm-events.txt file

mysqlbinlog -d crm mysqld-bin.000001 > crm-events.txt

Instead of -d option, you can also use –database option as shown below.

mysqlbinlog --database crm mysqld-bin.000001 > crm-events.txt

4. Disable Binary log for Recovery

When you are using mysqlbinlog to restore from a database crash, you don’t want your restore process to create binary logs. If it does, then you’ll be in an loop, where you’ll keep on restoring, as the restore itself will generate new binary log files.

So, to disable the binary log when you are using the mysqlbinlog command, use the -D option as shown below:

mysqlbinlog -D mysqld-bin.000001

You can also use –disable-log-bin as shown below. The following is exactly same as the above command.

mysqlbinlog --disable-log-bin mysqld-bin.000001 

Note: In the output, when you specify the -D option, you’ll see the additional 2nd line in the output. i.e the one which says SQL_LOG_BIN=0.

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!32316 SET @OLD_SQL_LOG_BIN=@@SQL_LOG_BIN, SQL_LOG_BIN=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

This option will also be helpful when you are –to-last-log option. Also, keep in mind that you need root privilege to execute this command.

For backup and restore, you’ll typically use mysqldump command, but sometimes in a situation where you have to recover from a crash, mysqlbinlog is helpful.

5. Control base-64 BINLOG in the Output

Using base64-output option, you can control the behavior of when the output statement should be base64-encoded BINLOG statements.

The following are possible values for base64-output:

• never
• always
• decode-rows
• auto (this is default)

never: When you specify “never” as shown below, this will display the base64-encoded BINLOG statements in the output.

mysqlbinlog --base64-output=never mysqld-bin.000001

i.e When you use “never”, in the output of the mysqlbinlog command, you’ll not any lines that are similar to the following which has the base64-encoded BINLOG.

BINLOG '
IeZ4WQ8BAAAAZgAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

Please note that the above “never” option for the base64-output will work on binary log files when they don’t contain the row-based events.

always: When you specify “always” option, this will display only the BINLOG entries whenever possible. So, use this only when you specifically debugging some issues.

mysqlbinlog --base64-output=always mysqld-bin.000001

The following is the output of the above with “always”, which shows only the BINLOG entries.

BINLOG '
IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#170726 14:59:31 server id 1  end_log_pos 182
BINLOG '
k+Z4WQIBAAAATAAAALYAAAAIAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI
AHRoZWdlZWtzdHVmZgBCRUdJTg==
'/*!*/;
# at 182
#170726 14:59:30 server id 1  end_log_pos 291
BINLOG '
kuZ4WQIBAAAAbQAAACMBAAAAAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI
AHRoZWdlZWtzdHVmZgBJTlNFUlQgSU5UTyB0IFZBTFVFUygxLCAnYXBwbGUnLCBOVUxMKQ==
'/*!*/;
# at 291
#170726 14:59:30 server id 1  end_log_pos 422
BINLOG '
kuZ4WQIBAAAAgwAAAKYBAAAAAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI
AHRoZWdlZWtzdHVmZgBVUERBVEUgdCBTRVQgbmFtZSA9ICdwZWFyJywgZGF0ZSA9ICcyMDA5LTAx
LTAxJyBXSEVSRSBpZCA9IDE=

decode-rows: This option will decode the row based events into comented SQL statements, especially when you specify –verbose option also along with it as shown below.

mysqlbinlog --base64-output=decode-rows --verbose mysqld-bin.000001

auto: This is the default option. When you don’t specify any base64-decode option, then it will use auto. In that case, mysqlbinlog will print the BINLOG entries only for certain event types like row-based events and format description events.

Both the following statements are exactly the same.

mysqlbinlog --base64-output=auto mysqld-bin.000001

mysqlbinlog mysqld-bin.000001

6. Debug Messages in mysqlbinlog Output

The following debug-check option will check for open file and memory use after it finish processing the given binary log file.

mysqlbinlog --debug-check mysqld-bin.000001

The following debug-info option will display additional debugging option after it finish processing the given binary log file as shown below.

# mysqlbinlog --debug-info mysqld-bin.000001 > /tmp/m.di

User time 0.00, System time 0.00
Maximum resident set size 2848, Integral resident set size 0
Non-physical pagefaults 863, Physical pagefaults 0, Swaps 0
Blocks in 0 out 48, Messages in 0 out 0, Signals 0
Voluntary context switches 1, Involuntary context switches 2

7. Skip First N number of Entries

Instead of reading the whole mysql binary log file, you can also read only certain portion of it by specifying offset.

For this use -o option. -o stands for offset.

The following will skip the first 10 entries in the specified mysql bin log.

mysqlbinlog -o 10 mysqld-bin.000001

To make sure this works properly, give a hug number for the offset, and you’ll not see any entries. The following will skip the first 10,000 entries (events) from the log.

In this example, since this particular log file doesn’t have 10,000 entries, it it nos showing any database events in the output.

# mysqlbinlog -o 10000 mysqld-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
..
..
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

8. Save Output to a File

You can use the simple Linux redirection command > and store the output to a file as shown below.

mysqlbinlog mysqld-bin.000001 > output.log

Or, you can use the -r (result file) option as shown below to store the output in a file. Please note that -r and –result-file are the same.

mysqlbinlog -r output.log mysqld-bin.000001 

Note: You can also specify –server-id and extract log entries that are generated by a mysql server with the given server id.

mysqlbinlog --server-id=1 -r output.log mysqld-bin.000001 

9. Extract Entries starting from a Specific Position

Typically in mysql binary log file, you’ll see position numbers as shown below. The following is a partial output of the mysqlbinlog, where you see “15028” is a position number.

#170726 15:38:14 server id 1  end_log_pos 15028         Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1501097894/*!*/;
insert into salary values(400,'Nisha','Marketing',9500)
/*!*/;
# at 15028
#170726 15:38:14 server id 1  end_log_pos 15146         Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1501097894/*!*/;
insert into salary values(500,'Randy','Technology',6000)

The following command will start reading the binary log entries fro position number 15028.

mysqlbinlog -j 15028 mysqld-bin.000001 > from-15028.out

When you specify multiple binary log files in the command line, then the start position will be applied only to the first binary log in the given list.

You can use -H option to get a hex dump of the given binary log files as shown below.

mysqlbinlog -H mysqld-bin.000001 > binlog-hex-dump.out

10. Extract Entries upto a Specific Position

Just like the previous example, you can also read entries from a mysql binary log upto a specific position as shown below.

mysqlbinlog --stop-position=15028 mysqld-bin.000001 > upto-15028.out

The above example will stop from the binlog exactly at position 15028. When you specify multiple binary log files in the command line, then the stop position will be applied only to the last binary log in the given list.

11. Flush Logs for Clean Binlog Output

When a binary log was not closed properly, you’ll see a warning message in the output as shown below.

# mysqlbinlog  mysqld-bin.000001 > output.log

As you see here, it says the binlog file was not closed properly.

# head output.log
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
..
..
# Warning: this binlog is either in use or was not closed properly.
..
..
..
BINLOG '
IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

When you see that, then connect to the mysql and flush the logs as shown below.

mysql> flush logs;

Once you flush the logs, and execute your mysqlbinlog command again, you’ll not see that binlog not closed properly warning message anymore in your mysqlbinlog output.

12. Display Only SQL Queries in the Output

By default, as you see in output of previous examples, apart from the SQL queries, you’ll also see some additional information in the mysqlbinlog output.

If you only want the regular SQL queries and nothing else, then use the -s option as shown below.

-s here stands for short form. You can also use –short-form option. Both the following examples are exactly the same.

mysqlbinlog -s mysqld-bin.000001 

mysqlbinlog -short-form mysqld-bin.000001  

The following is partial output of the above command. As you see here, it displays only the SQL queries from the given binary log file.

SET TIMESTAMP=1501096106/*!*/;
insert into employee values(400,'Nisha','Marketing',9500)
/*!*/;
SET TIMESTAMP=1501096106/*!*/;
insert into employee values(500,'Randy','Technology',6000)
..
..
..

You’ll not see entires like the following in the short-form:

# at 1201
#170726 15:08:26 server id 1  end_log_pos 1329  Query   thread_id=3     exec_time=0     error_code=0

13. View Entries starting from a Specific Time

The following will extract only the entries that are starting from the specified time. Any entries before this time will be ignored.

mysqlbinlog --start-datetime="2017-08-16 15:00:00" mysqld-bin.000001 

This is very helpful when you want to extract data from a binary file from only a specific time frame that you want to use it to recover or reconstruct certain database activities that happened within that time period.

The format of the timestamp can be anything that is understood by the MYSQL server DATETIME and TIMESTAMP types. So, you have lot of flexibility here.

14. View Entries upto a Specific Time

Just like the previous start time example, you can also specify stop time as shown below.

mysqlbinlog --stop-datetime="2017-08-16 15:00:00" mysqld-bin.000001 

The above command will read the entries upto the given stop time. Any entries from the mysql binary log file that are beyond the given stop time will not be processed.

15. Get Binary Log from a Remote Server

From your local machine, you can also read the mysql binary logs that is located in a remote server.

For this, you need to specify the ip-address, username and password for the remote server as explained below.

Use -R option for this. -R option is same as –read-from-remote–server.

mysqlbinlog -R -h 192.168.101.2 -p mysqld-bin.000001

In the above:

• -R option instructs mysqlbinlog command to read the log file from the remote server
• -h specify the ip-address of the remote server
• -p This will prompt you for the password. By default, it will use “root” as the username. You can also specify an username using -u option.
• mysqld-bin.000001 This is the name of the binary log file from the remote server that we are reading here.

The following command is exactly same as the above command:

mysqlbinlog --read-from-remote-server --host=192.168.101.2 -p mysqld-bin.000001

The following is the partial output of the above command:

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170726 13:57:37 server id 1  end_log_pos 106   Start: binlog v 4, server v 5.1.73-log created 170726 13:57:37 at startup
ROLLBACK/*!*/;
BINLOG '
IeZ4WQ8BAAAAZgAAAGoAAAAAAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#170726 13:59:31 server id 1  end_log_pos 182   Query   thread_id=2     exec_time=0     error_code=0
..
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

If you specify only the -h option, you’ll get the following error message.

# mysqlbinlog -h 192.168.101.2 mysqld-bin.000001
mysqlbinlog: File 'mysqld-bin.000001' not found (Errcode: 2)

When you don’t have enough privilege on the remote database, then you’ll get the following “is not allowed to connect” error message. In this case, make sure you grant proper privileges on the remote database for your local client (i.e where mysqlbinlog command is running)

# mysqlbinlog -R --host=192.168.101.2 mysqld-bin.000001
ERROR: Failed on connect: Host '216.172.166.27' is not allowed to connect to this MySQL server

If you don’t specify the proper password using the -p option, you’ll get the following “access denied” error message

# mysqlbinlog -R --host=192.168.101.2 mysqld-bin.000001
ERROR: Failed on connect: Access denied for user 'root'@'216.172.166.27' (using password: YES)

The following example shows that you can also use -u option to specify the username that mysqlbinlog should use to connect to the remote MySQL database. Please note that this user is mysql user (not Linux server user).

mysqlbinlog -R --host=192.168.101.2 -u root -p mysqld-bin.000001