mysqlbinlog 官方操作文档:https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html
windows和linux下操作请找到mysqlbinlog可执行命令所在位置 。
数据库需要开启log_bin功能并设置开启binlog行级模式(ROW):
操作选项:
Option Name | Description | Introduced |
---|---|---|
--base64-output | Print binary log entries using base-64 encoding | |
--bind-address | Use specified network interface to connect to MySQL Server | 5.6.1 |
--binlog-row-event-max-size | Binary log max event size | |
--character-sets-dir | Directory where character sets are installed | |
--connection-server-id | Used for testing and debugging. See text for applicable default values and other particulars. | 5.6.20 |
--database | List entries for just this database | |
--debug | Write debugging log | |
--debug-check | Print debugging information when program exits | |
--debug-info | Print debugging information, memory, and CPU statistics when program exits | |
--default-auth | Authentication plugin to use | 5.6.2 |
--defaults-extra-file | Read named option file in addition to usual option files | |
--defaults-file | Read only named option file | |
--defaults-group-suffix | Option group suffix value | |
--disable-log-bin | Disable binary logging | |
--exclude-gtids | Do not show any of the groups in the GTID set provided | 5.6.5 |
--force-if-open | Read binary log files even if open or not closed properly | |
--force-read | If mysqlbinlog reads a binary log event that it does not recognize, it prints a warning | |
--help | Display help message and exit | |
--hexdump | Display a hex dump of the log in comments | |
--host | Connect to MySQL server on given host | |
--include-gtids | Show only the groups in the GTID set provided | 5.6.5 |
--local-load | Prepare local temporary files for LOAD DATA in the specified directory | |
--login-path | Read login path options from .mylogin.cnf | 5.6.6 |
--no-defaults | Read no option files | |
--offset | Skip the first N entries in the log | |
open_files_limit | Specify the number of open file descriptors to reserve | |
--password | Password to use when connecting to server | |
--plugin-dir | Directory where plugins are installed | 5.6.2 |
--port | TCP/IP port number for connection | |
--print-defaults | Print default options | |
--protocol | Connection protocol to use | |
--raw | Write events in raw (binary) format to output files | |
--read-from-remote-master | Read the binary log from a MySQL master rather than reading a local log file | 5.6.5 |
--read-from-remote-server | Read binary log from MySQL server rather than local log file | |
--result-file | Direct output to named file | |
--secure-auth | Do not send passwords to server in old (pre-4.1) format | 5.6.17 |
--server-id | Extract only those events created by the server having the given server ID | |
--server-id-bits | Tell mysqlbinlog how to interpret server IDs in binary log when log was written by a mysqld having its server-id-bits set to less than the maximum; supported only by MySQL Cluster version of mysqlbinlog | |
--set-charset | Add a SET NAMES charset_name statement to the output | |
--shared-memory-base-name | Name of shared memory to use for shared-memory connections | |
--short-form | Display only the statements contained in the log | |
--skip-gtids | Do not print any GTIDs; use this when writing a dump file from binary logs containing GTIDs. | 5.6.5 |
--socket | The Unix socket file or Windows named pipe to use | |
--ssl-crl | File that contains certificate revocation lists | 5.6.3 |
--ssl-crlpath | Directory that contains certificate revocation-list files | 5.6.3 |
--ssl-mode | Desired security state of connection to server | 5.6.30 |
--start-datetime | Read binary log from first event with timestamp equal to or later than datetime argument | |
--start-position | Read binary log from first event with position equal to or greater than argument | |
--stop-datetime | Stop reading binary log at first event with timestamp equal to or greater than datetime argument | |
--stop-never | Stay connected to server after reading last binary log file | |
--stop-never-slave-server-id | Slave server ID to report when connecting to server | |
--stop-position | Stop reading binary log at first event with position equal to or greater than argument | |
--to-last-log | Do not stop at the end of requested binary log from a MySQL server, but rather continue printing to end of last binary log | |
--user | MySQL user name to use when connecting to server | |
--verbose | Reconstruct row events as SQL statements | |
--verify-binlog-checksum | Verify checksums in binary log | 5.6.1 |
--version | Display version information and exit |
操作语法:
格式:mysqlbinlog 日志文件 参数 | mysql -u用户名 -p密码
mysqlbinlog liangck.000001 | mysql -uroot –p123456 表示 全部恢复
mysqlbinlog liangck.000002 --start-pos=417 --stop-pos=773 | mysql -uroot -p123456 表示 从417至773位置
mysqlbinlog liangck.000002 --stop-pos=773 | mysql -uroot -p123456 表示 从头至773位置
mysqlbinlog liangck.000002 --start-pos=417 | mysql -uroot -p123456 表示 从417至尾 位置
mysqlbinlog liangck.000002 --start-datetime="2004-12-25 11:25:56" | mysql -uroot -p123456 日期参数和pos同理
更多参考:
Linux 上通过binlog文件 恢复mysql 数据库详细步骤
关闭binlog恢复错误问题处理:
https://yq.aliyun.com/articles/700466
注意:如果过程中报错请附上编码设置选项。