Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what are the mysqlbinlog commands", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "what are the mysqlbinlog commands" this article?

In MySQL or MariaDB, changes made to the database at any time are recorded in the log file. For example, when you add a new table or update a piece of data, these events are stored in a binary log file. Binary log files are very useful in the MySQL master-slave composite, and the master server sends its data to the remote server.

When you need to restore MySQL, you will also need to use binary log files.

The mysqlbinlog command, which displays the contents of the binary log in a user-visible way. At the same time, the contents can also be read out for use by other MySQL utilities.

1 get the current binary log list

Execute the following command in mysql to view a list of binary log files.

Mysql > SHOW BINARY LOGS

+-+ +

| | Log_name | File_size |

+-+ +

| | mysqld-bin.000001 | 15740 | |

| | mysqld-bin.000002 | 3319 | |

..

..

If the bear does not turn on this feature, it will display:

Mysql > SHOW BINARY LOGS

ERROR 1381 (HY000): You are not using binary logging

Binary log files are stored in the / var/lib/mysql directory by default

$ls-1 / var/lib/mysql/

-rw-rw----. 1 mysql mysql 15740 Aug 28 14:57 mysqld-bin.000001

-rw-rw----. 1 mysql mysql 3319 Aug 28 14:57 mysqld-bin.000002

..

..

2 mysqlbinlog default behavior

The contents of the specified binary log file (for example: mysqld.000001) are displayed in a user-friendly format.

$mysqlbinlog mysqld-bin.000001

The mysqlbinlog is displayed by default as follows:

/ *! 40019 SET @ @ session.Maxially inserted delayedthreaded threaded threads @ @ session.Maxially inserted delayedthreads106 Start @ @ session.Maxtionalinsertingdelayedthreads106 Start @ @ session.max.insertingdelayedThreadedThreads @ @ session.Maxsession. SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER / *! * /; # at 4000170726 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.

ROLLBACKUPUBERBACKUBUBUBUR

BINLOG 'IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAh6nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC' / *! * /

# 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.pseudoplastics thread readreadable cards idler / session.

SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=1, @ @ session.unique_checks=1, @ @ session.

SET @ @ session.sqlcards, modewords, 0planks / sessions.

SET @ @ session.auto_increment_increment=1, @ @ session.

/ *!\ C latin1 * /! * /

SET @ @ session. Session. Session setting client.session. Session. Collationalization connections8 session. Session. Collationalization serverationserver8 apperance.

..

..

..

# at 14191

# 170726 15:20:38 server id 1 end_log_pos 14311 Query thread_id=4 exec_time=0 error_code=0SET timestamp 150106838max

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 show all the changes that have occurred to the database on the system.

3 get specific database entries

By default, mysqlbinlog displays everything, which is too messy. Using the-d option, you can specify a database name that displays only events that occur on that database.

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

You can also use the-- database command with the same effect.

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

4 prohibit the recovery process from generating logs

When the binary log file is used for database recovery, the log file is also generated in the process, which enters a cyclic state and continues to recover the data in the process. Therefore, to disable binary logging when using the mysqlbinlog command, use the-D option shown below:

$mysqlbinlog-D mysqld-bin.000001

You can also use the-- disable-log-bin command with the same effect.

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

Note: in the output, when you specify the-D option, you will see the second line in the output. That's 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 is also helpful when using the-to-last-log option. Also, keep in mind that this command requires root permission to execute.

5 Control base-64 BINLOG in the output

Using the base64-output option, you can control when the output statement is an output base64-encoded BINLOG statement. The following are the possible values for the base64 output settings:

Never

Always

Decode-rows

Auto (default)

Never: when you specify "never" as shown below, it displays base64-encoded BINLOG statements in the output.

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

There will not be any lines similar to the following, which have base64-encoded BINLOG.

1BINLOG 'IeZ4WQ8BAAAAZgAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAh6nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

Always: when the "always" option is specified, it will display only the BINLOG entry whenever possible. Therefore, it is used only if you specialize in debugging problems.

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

Here is the output of "always", which shows only the BINLOG entry.

BINLOG 'IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAh6nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC' / *! * /

# 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 event into a SQL statement, especially when the-verbose option is specified, as shown below.

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

Auto: this is the default option. It uses auto when no base64 decoding options are specified. In this case, mysqlbinlog will print BINLOG entries only for certain event types, such as row-based events and format description events.

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

$mysqlbinlog mysqld-bin.000001

6 mysqlbinlog output debugging information

The following debugging options check file opening and memory usage after processing the given binary log file.

$mysqlbinlog-debug-check mysqld-bin.000001

As shown below, the following debug information option displays additional debug information after processing the given binary log file.

$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 the first N entries

In addition to reading the entire mysql binary log file, you can also read specific parts of it by specifying an offset. You can use the-o option. O stands for offset.

The first 10 entries in the specified mysql bin log will be skipped below.

$mysqlbinlog-o 10 mysqld-bin.000001

To ensure that it works properly, give the offset a large number and you will not see any entries. The following skips 10000 entries (events) from the log.

$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*/

In this example, because this particular log file does not have 10000 entries, no database events are displayed in the output.

8 Save output to file

You can also use a simple Linux redirect command to store the output in a file, as shown below.

$mysqlbinlog mysqld-bin.000001 > output.log

Alternatively, you can use the-r (result file) option to store the output in a file, as shown below.

$mysqlbinlog-r output.log mysqld-bin.000001

Note: you can also use-server-id to specify the mysql server to ensure that the logs are generated by the mysql server of the given server id.

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

9 extract entries from a specific location

Usually in the mysql binary log file, you will see the location number shown below. Here is part of the output of mysqlbinlog. You can see that "15028" is a location 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)

/ *! * /

# 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, 500, technological, technological, and 6000)

The following command starts reading at the binary log entry with location number 15028.

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

When multiple binary log files are specified on the command line, the start location option applies only to the first binary log file in the given list. You can also use the-H option to get a hexadecimal dump of a given binary log file, as shown below.

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

10 cut off the item to a specific location

Just like the previous example, you can also cut off an entry at a specific location from the mysql binary log, as shown below.

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

The above example stops binlog at 15028. When multiple binary log files are specified on the command line, the stop location is applied only to the last binary log file in the given list.

11 refresh the log to clear the Binlog output

When the binary log file is not closed correctly, you will see a warning message in the output, as shown below.

$mysqlbinlog mysqld-bin.000001 > output.out

As shown below, the report indicates that the binlog file was not closed correctly.

# head output.log

/ *! 40019 SET @ @ session.max_insert_delayed_threads=0*/

.. ..

# Warning: this binlog is either in use or was not closed properly.

..

.. .. BINLOG 'IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAh6nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

When you see this prompt, you need to connect to mysql and refresh the log, as shown below.

1mysql > flush logs

After refreshing the log, execute the mysqlbinlog command again and you will not see a warning message that binlog does not shut down correctly in the mysqlbinlog output.

12 display only statements in the output

By default, as you can see in the previous sample output, there is some additional information in the mysqlbinlog output in addition to the SQL statement. If you only want to view regular SQL statements and don't need anything else, you can use the-s option, as shown below.

You can also use the-- short-form option, which has the same effect.

$mysqlbinlog-s mysqld-bin.000001

$mysqlbinlog-short-form mysqld-bin.000001

The following is part of the output of the above command. Here, it will only display SQL statements from a given binary log file.

SET timestamp 1501096106

Insert into employee values / *! * /

SET timestamp 1501096106

Insert into employee values (500, 500, technological, technological, and 6000)

..

..

Entries such as the following are not displayed:

# 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 for a specific start time

Only entries that start at the specified time will be extracted. Any previous entries will be ignored.

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

This is useful when you want to extract data from a binary file because you want to use it to recover or reconstruct some database activity that occurs over a certain period of time. The format of the timestamp can be any type in DATETIME and timestamp understood by the MySQL server.

14 View entries for a specific end time

As with the previous start time example, the end time can also be specified here, as shown below.

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

The above command reads the entry at a given end time. Any entries from mysql binary log files that exceed a given end time are not processed.

15 get binary logs from a remote server

On the local machine, you can also read the mysql binary log files located on the remote server. To do this, you need to specify the ip address, user name, and password of the remote server, as shown below.

The-R option is used here. The-R option is the same as-read-from-remote-server.

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

In the above command:

The-R option instructs the mysqlbinlog command to read the log file from the remote server

-h specify the ip address of the remote server

-p prompts for a password. By default, it uses "root" as the user name. You can also use the-u option to specify a user name.

Mysqld-bin.000001 this is the name of the binary log file for the remote server read here.

The following command is exactly the same as the above command:

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

If you specify only the-h option, you will 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 do not have sufficient privileges on the remote database, you will get the following "connection not allowed" error message. In this case, be sure to grant the appropriate privileges to the local client on the remote database.

$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 do not specify the correct password with the-p option, you will get the following access deny 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 the-u option to specify the user name that mysqlbinlog should use to connect to the remote MySQL database. Note that this user is a mysql user (not a Linux server user).

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

The above is all the contents of this article "what are the mysqlbinlog commands?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.