Shulou(Shulou.com)06/01 Report--

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

1. Get the current binary log list

SHOW BINARY LOGS; or

SHOW MASTER LOGS

two。 Get specific database entries

Mysqlbinlog-d databasename mysqld-bin.000001 > data.sql

Same as-- database

3. Prohibit the recovery process from generating logs

Mysqlbinlog-d-D databasename mysqld-bin.000001 > data.sql

Effect: root permission is required to add SQL_LOG_BIN=0 to the second line of the output

4. Control base-64 BINLOG in output

There are four options:

Auto: default

Never: do not show similar lines BINLOG 'IeZ4WQ8BAAAAZgAAABAA

Decode-rows: line-based events are decoded into a SQL statement, especially when the-verbose option is specified

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

Always: (5.7Use unspec) only BINLOG entries are shown

5. Skip the first N entries

-o specify how many entries are skipped before

Mysqlbinlog-o 10000 mysqld-bin.000001

6. Save output to file

Two methods, redirect and-r:

Mysqlbinlog-r output.log mysqld-bin.000001

-- server-id= specifies that the log is generated by a given server

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

7. Extract entries from a specific location

Start reading from the specified location:-- start-position/-j

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

Read to the end position:

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

8. Refresh the log to clear the Binlog output

If Warning: this binlog is either in use or was not closed properly appears in the log

Because the binary log has not been properly closed, you need to connect mysql to refresh the log mysql > flush logs

9. Show only statements in the output

Do not display additional information to add the option-s /-- short-form

10. View entries at a specific time

Intercept according to time-start-datetime-stop-datetime

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

11. Get remote server binary log

Example of reading a remote binary log:

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

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.