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

1、资源项目源码均已通过严格测试验证,保证能够正常运行; 2、项目问题、技术讨论,可以给博主私信或留言,博主看到后会第一时间与您进行沟通; 3、本项目比较适合计算机领域相关的毕业设计课题、课程作业等使用,尤其对于人工智能、计算机科学与技术等相关专业,更为适合; 4、下载使用后,可先查看REAdMe.md或论文文件(如有),本项目仅用作交流学习参考,请切勿用于商业用途。 5、资源来自互联网采集,如有侵权,私聊博主删除。 6、可私信博主看论文后选择购买源代码。 1、资源项目源码均已通过严格测试验证,保证能够正常运行; 2、项目问题、技术讨论,可以给博主私信或留言,博主看到后会第一时间与您进行沟通; 3、本项目比较适合计算机领域相关的毕业设计课题、课程作业等使用,尤其对于人工智能、计算机科学与技术等相关专业,更为适合; 4、下载使用后,可先查看REAdMe.md或论文文件(如有),本项目仅用作交流学习参考,请切勿用于商业用途。 5、资源来自互联网采集,如有侵权,私聊博主删除。 6、可私信博主看论文后选择购买源代码。 1、资源项目源码均已通过严格测试验证,保证能够正常运行; 2、项目问题、技术讨论,可以给博主私信或留言,博主看到后会第一时间与您进行沟通; 3、本项目比较适合计算机领域相关的毕业设计课题、课程作业等使用,尤其对于人工智能、计算机科学与技术等相关专业,更为适合; 4、下载使用后,可先查看READme.md或论文文件(如有),本项目仅用作交流学习参考,请切勿用于商业用途。 5、资源来自互联网采集,如有侵权,私聊博主删除。 6、可私信博主看论文后选择购买源代码。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值