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

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

mysqlbinlog 官方操作文档：[https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html][https_dev.mysql.com_doc_refman_5.6_en_mysqlbinlog.html]

windows和linux下操作请找到mysqlbinlog可执行命令所在位置 。

数据库需要开启log\_bin功能并设置开启binlog行级模式(ROW):

![20190617163259558.png][]

操作选项：

<table> 
 <thead> 
  <tr> 
   <th>Option Name</th> 
   <th>Description</th> 
   <th>Introduced</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_base64-output" rel="nofollow">--base64-output</a></td> 
   <td>Print binary log entries using base-64 encoding</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_bind-address" rel="nofollow">--bind-address</a></td> 
   <td>Use specified network interface to connect to MySQL Server</td> 
   <td>5.6.1</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_binlog-row-event-max-size" rel="nofollow">--binlog-row-event-max-size</a></td> 
   <td>Binary log max event size</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_character-sets-dir" rel="nofollow">--character-sets-dir</a></td> 
   <td>Directory where character sets are installed</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_connection-server-id" rel="nofollow">--connection-server-id</a></td> 
   <td>Used for testing and debugging. See text for applicable default values and other particulars.</td> 
   <td>5.6.20</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_database" rel="nofollow">--database</a></td> 
   <td>List entries for just this database</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_debug" rel="nofollow">--debug</a></td> 
   <td>Write debugging log</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_debug-check" rel="nofollow">--debug-check</a></td> 
   <td>Print debugging information when program exits</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_debug-info" rel="nofollow">--debug-info</a></td> 
   <td>Print debugging information, memory, and CPU statistics when program exits</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_default-auth" rel="nofollow">--default-auth</a></td> 
   <td>Authentication plugin to use</td> 
   <td>5.6.2</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_defaults-extra-file" rel="nofollow">--defaults-extra-file</a></td> 
   <td>Read named option file in addition to usual option files</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_defaults-file" rel="nofollow">--defaults-file</a></td> 
   <td>Read only named option file</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_defaults-group-suffix" rel="nofollow">--defaults-group-suffix</a></td> 
   <td>Option group suffix value</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_disable-log-bin" rel="nofollow">--disable-log-bin</a></td> 
   <td>Disable binary logging</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_exclude-gtids" rel="nofollow">--exclude-gtids</a></td> 
   <td>Do not show any of the groups in the GTID set provided</td> 
   <td>5.6.5</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_force-if-open" rel="nofollow">--force-if-open</a></td> 
   <td>Read binary log files even if open or not closed properly</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_force-read" rel="nofollow">--force-read</a></td> 
   <td>If mysqlbinlog reads a binary log event that it does not recognize, it prints a warning</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_help" rel="nofollow">--help</a></td> 
   <td>Display help message and exit</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_hexdump" rel="nofollow">--hexdump</a></td> 
   <td>Display a hex dump of the log in comments</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_host" rel="nofollow">--host</a></td> 
   <td>Connect to MySQL server on given host</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_include-gtids" rel="nofollow">--include-gtids</a></td> 
   <td>Show only the groups in the GTID set provided</td> 
   <td>5.6.5</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_local-load" rel="nofollow">--local-load</a></td> 
   <td>Prepare local temporary files for LOAD DATA in the specified directory</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_login-path" rel="nofollow">--login-path</a></td> 
   <td>Read login path options from .mylogin.cnf</td> 
   <td>5.6.6</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_no-defaults" rel="nofollow">--no-defaults</a></td> 
   <td>Read no option files</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_offset" rel="nofollow">--offset</a></td> 
   <td>Skip the first N entries in the log</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_open-files-limit" rel="nofollow">open_files_limit</a></td> 
   <td>Specify the number of open file descriptors to reserve</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_password" rel="nofollow">--password</a></td> 
   <td>Password to use when connecting to server</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_plugin-dir" rel="nofollow">--plugin-dir</a></td> 
   <td>Directory where plugins are installed</td> 
   <td>5.6.2</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_port" rel="nofollow">--port</a></td> 
   <td>TCP/IP port number for connection</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_print-defaults" rel="nofollow">--print-defaults</a></td> 
   <td>Print default options</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_protocol" rel="nofollow">--protocol</a></td> 
   <td>Connection protocol to use</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_raw" rel="nofollow">--raw</a></td> 
   <td>Write events in raw (binary) format to output files</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_read-from-remote-master" rel="nofollow">--read-from-remote-master</a></td> 
   <td>Read the binary log from a MySQL master rather than reading a local log file</td> 
   <td>5.6.5</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_read-from-remote-server" rel="nofollow">--read-from-remote-server</a></td> 
   <td>Read binary log from MySQL server rather than local log file</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_result-file" rel="nofollow">--result-file</a></td> 
   <td>Direct output to named file</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_secure-auth" rel="nofollow">--secure-auth</a></td> 
   <td>Do not send passwords to server in old (pre-4.1) format</td> 
   <td>5.6.17</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_server-id" rel="nofollow">--server-id</a></td> 
   <td>Extract only those events created by the server having the given server ID</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_server-id-bits" rel="nofollow">--server-id-bits</a></td> 
   <td>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</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_set-charset" rel="nofollow">--set-charset</a></td> 
   <td>Add a SET NAMES charset_name statement to the output</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_shared-memory-base-name" rel="nofollow">--shared-memory-base-name</a></td> 
   <td>Name of shared memory to use for shared-memory connections</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_short-form" rel="nofollow">--short-form</a></td> 
   <td>Display only the statements contained in the log</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_skip-gtids" rel="nofollow">--skip-gtids</a></td> 
   <td>Do not print any GTIDs; use this when writing a dump file from binary logs containing GTIDs.</td> 
   <td>5.6.5</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_socket" rel="nofollow">--socket</a></td> 
   <td>The Unix socket file or Windows named pipe to use</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_ssl" rel="nofollow">--ssl-crl</a></td> 
   <td>File that contains certificate revocation lists</td> 
   <td>5.6.3</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_ssl" rel="nofollow">--ssl-crlpath</a></td> 
   <td>Directory that contains certificate revocation-list files</td> 
   <td>5.6.3</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_ssl" rel="nofollow">--ssl-mode</a></td> 
   <td>Desired security state of connection to server</td> 
   <td>5.6.30</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_start-datetime" rel="nofollow">--start-datetime</a></td> 
   <td>Read binary log from first event with timestamp equal to or later than datetime argument</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_start-position" rel="nofollow">--start-position</a></td> 
   <td>Read binary log from first event with position equal to or greater than argument</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_stop-datetime" rel="nofollow">--stop-datetime</a></td> 
   <td>Stop reading binary log at first event with timestamp equal to or greater than datetime argument</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_stop-never" rel="nofollow">--stop-never</a></td> 
   <td>Stay connected to server after reading last binary log file</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_stop-never-slave-server-id" rel="nofollow">--stop-never-slave-server-id</a></td> 
   <td>Slave server ID to report when connecting to server</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_stop-position" rel="nofollow">--stop-position</a></td> 
   <td>Stop reading binary log at first event with position equal to or greater than argument</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_to-last-log" rel="nofollow">--to-last-log</a></td> 
   <td>Do not stop at the end of requested binary log from a MySQL server, but rather continue printing to end of last binary log</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_user" rel="nofollow">--user</a></td> 
   <td>MySQL user name to use when connecting to server</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_verbose" rel="nofollow">--verbose</a></td> 
   <td>Reconstruct row events as SQL statements</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_verify-binlog-checksum" rel="nofollow">--verify-binlog-checksum</a></td> 
   <td>Verify checksums in binary log</td> 
   <td>5.6.1</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_version" rel="nofollow">--version</a></td> 
   <td>Display version information and exit</td> 
  </tr> 
 </tbody> 
</table>

操作语法：

**格式：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同理

更多参考：

[超级有用的15个mysqlbinlog命令][15_mysqlbinlog]

[Linux 上通过binlog文件 恢复mysql 数据库详细步骤][Linux _binlog_ _mysql]

[windows下mysqlbinlog二进制恢复][windows_mysqlbinlog]

[MySQL的binlog恢复（Windows下）][MySQL_binlog_Windows]

关闭binlog恢复错误问题处理：

[https://yq.aliyun.com/articles/700466][https_yq.aliyun.com_articles_700466]

注意：如果过程中报错请附上编码设置选项。

[https_dev.mysql.com_doc_refman_5.6_en_mysqlbinlog.html]: https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html
[20190617163259558.png]: /images/20220113/81836c1d0ef44266861641598d6dbbb2.png
[15_mysqlbinlog]: http://www.ttlsa.com/mysql/super-useful-mysqlbinlog-command/
[Linux _binlog_ _mysql]: https://www.cnblogs.com/hanyifeng/p/5756462.html
[windows_mysqlbinlog]: https://www.cnblogs.com/ericzh/p/6252393.html
[MySQL_binlog_Windows]: https://www.cnblogs.com/jian0110/p/9404773.html
[https_yq.aliyun.com_articles_700466]: https://yq.aliyun.com/articles/700466