Mysqlbinlog Introduction to use

Mysqlbinlog is commonly used in binlog export analysis and MySQL data recovery.

Here are some common options for a brief introduction.

--base64-output=name

Determine Binlog output format options

' Never ' option

Do not output SQL statement format

' decode-rows ' option

Convert the output statement format to a comment's SQL statement format, because the Binlog log is now mostly the rows log, not the statement log, so the output of the conversion statement is just the rows changed statement, not the SQL statement that was entered at the time.

If there is no--base64-output[=name] option, the default is "Auto", which gives the full format.

• Auto ("Auto") or unspecified ("Unspec") will automatically display all formats of the BINLOG statement (binary format and statement format).

If no--base64-output option is given, the effect is the same as--base64-output=auto.

--start-datetime=name

Start reading Binlog log for the specified time, time zone is the log local time zone, time format example ' 2016-12-25 11:25:56 ' (to use quotation marks, avoid the shell recognition error.) )

--stop-datetime=name

Stop reading Binlog log for the specified time, time zone is the log local time zone, time format example ' 2016-12-25 11:25:56 ' (to use quotation marks, avoid the shell recognition error.) )

--start-position=#

Starts reading the Binlog log at the specified position number. The read range is more accurate and avoids time zone errors, which are recommended when recovering data.

--stop-position=#

Stop reading the Binlog log at the specified position number. The read range is more accurate and avoids time zone errors, which are recommended when recovering data.

-V rebuilds the pseudo-SQL statement from the row event.

-VV reconstructs a pseudo-SQL statement from a row event and adds a comment to the data type.

--database=name

Specify the exported database

--skip-gtids

The global thing ID number is ignored for output.

Export the Binlog log from the time range, rebuild the pseudo-SQL statement from the row event, use the SQL statement encoding, if there are multiple Binlog files, follow up using >> Add to the export file and use absolute paths as much as possible.

Mysqlbinlog-v--base64-output=decode-rows--start-datetime= ' 2016-12-25 11:25:56 '--stop-datetime= ' 2016-12-25 11:59:59 ' Binlogname > Filename.sql

Capture the exact position number range and export the SQL file.

Mysqlbinlog--start-position=10000--stop-position=20000 binlogname > Filename.sql

MySQL for data import.

Mysql-u ' user '-P ' password ' < Filename.sql #危险操作!!!

Resources:

1,rds MySQL in binlog log view https://help.aliyun.com/knowledge_detail/41709.html

2,mysqlbinlog Official Handbook Https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html

This article is from the "focus on drilling 100 years" blog, please be sure to keep this source http://titandeng.blog.51cto.com/823817/1945303

Mysqlbinlog Introduction to use