Mysql通过binlog日志文件恢复数据-蒲公英云

Mysql通过binlog日志文件恢复数据

野性酷女 2022-10-02 07:46 300阅读 0赞

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同理