mysqlbinlog — Utility for Processing Binary Log Files

本文以 MySQL@5.7 经典版本为基础，官方文档 地址

binary logging format

• Statement-based logging
• Row-based logging

常用选项

–verbose, -v

Reconstruct row events and display them as commented SQL statements. If this option is given twice (by passing in either “-vv” or “–verbose --verbose”), the output includes comments to indicate column data types and some metadata, and row query log events if so configured

注：这应该是程序员看 binlog 最得力的选项啦，The combination of --base64-output=DECODE-ROWS and --verbose provides a convenient way to see row events only as SQL statements，更多请看 mysqlbinlog Row Event Display

–database=db_name, -d db_name

The effects of this option depend on whether the statement-based or row-based logging format is in use

注：按数据库名筛选 binlog，仅支持指定一个，请确保 binlog_format=ROW，通过 show variables like 'binlog_format' 查看，否则你需要详细看官方文档，进而清楚你在做什么

–start-datetime=datetime

Start reading the binary log at the first event having a timestamp equal to or later than the datetime argument

shell> mysqlbinlog --start-datetime="2005-12-25 11:25:56" binlog.000003

–start-position=N, -j N

Start reading the binary log at the first event having a position equal to or greater than N. This option applies to the first log file named on the command line

–stop-datetime=datetime

Stop reading the binary log at the first event having a timestamp equal to or later than the datetime argument

–stop-position=N

Stop reading the binary log at the first event having a position equal to or greater than N. This option applies to the last log file named on the command line

最好知道选项

–base64-output=value

• AUTO
• NEVER
• DECODE-ROWS

注：慎用此选项，just used to suppress the BINLOG statements for row events，更多请看 mysqlbinlog Row Event Display

–raw

–read-from-remote-server

–result-file=name, -r name

–rewrite-db=‘from_name->to_name’

–stop-never

–to-last-log, -t

应用

You can pipe the output of mysqlbinlog into the mysql client to execute the events contained in the binary log. This technique is used to recover from a crash when you have an old backup

shell> mysqlbinlog binlog.000001 | mysql -u root -p
or
shell> mysqlbinlog binlog.[0-9]* | mysql -u root -p

If the statements produced by mysqlbinlog may contain BLOB values, these may cause problems when mysql processes them. In this case, invoke mysql with the --binary-mode option

You can also redirect the output of mysqlbinlog to a text file instead, if you need to modify the statement log first (for example, to remove statements that you do not want to execute for some reason). After editing the file, execute the statements that it contains by using it as input to the mysql program

shell> mysqlbinlog binlog.000001 > tmpfile
shell> ... edit tmpfile ...
shell> mysql -u root -p < tmpfile

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

equal

shell> mysqlbinlog binlog.000001 >  /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -u root -p -e "source /tmp/statements.sql"

use the SHOW BINARY LOGS statement to see the current binary log names

Static and Live Backups

mysqlbinlog --read-from-remote-server --host=host_name --raw binlog.000130 binlog.000131 binlog.000132

mysqlbinlog --read-from-remote-server --host=host_name --raw --to-last-log binlog.000130

mysqlbinlog --read-from-remote-server --host=host_name --raw --stop-never binlog.000130

mysqldump + mysqlbinlog for Backup and Restore

强烈建议去官方文档查看整个备份恢复流程

TODO

Example Backup and Recovery Strategy

Point-in-Time (Incremental) Recovery Using the Binary Log