MySQL二进制数据恢复

快来打我* 2023-07-24 05:53 23阅读 0赞

## 一、开启配置 ##

1.1、在\[mysqld\]配置文件中添加 log-bin=mysql-bin

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70][]

**或者** log-bin="C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Data\\mysql-bin"

![20200411180258730.png][]

比如上面的设置重启数据库会生成mysqlbin.000001文件

1.2、 进入mysql的bin目录下，打开cmd窗口输入 mysql -hlocalhost -uroot -p 再输入密码

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 1][]

1.3、查看log-bin开启状态 show variables like 'log\_%';

注：图中的路径一般是存放数据的路径

ON 代表开启；OFF 代表没有开启；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 2][]

1.4、查看所有数据保存日志 show master logs;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 3][]

1.5、查看最新的一次数据日志 show master status;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 4][]

1.6、查看保存路径（一般与上面的路径相同）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 5][]

1.7、其它数据日志操作命令

*  1.7.1、清空所有binlog日志 reset master;
 *  1.7.2、刷新日志（会产生一个新的文件） Flush logs 
 *  1.7.3、

*  1.7.4、

## 二、  ##

## 三、[mysqlbinlog的工具使用][mysqlbinlog] ##

3.1、其实就是MySQL安装目录下的mysqlbinlog.exe应用程序

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 6][]

3.2、在cmd中启动mysqlbinlog.exe应用（先把mysql-bin.000001文件复制到bin目录下比较容易操作）

mysqlbinlog.exe mysql-bin.000001

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 7][]

3.3、以上出现乱码：[MySQL修改日志binlog格式（如何修改binlog格式）][MySQL_binlog_binlog]

3.4、[MySQL使用mysqlbinlog将binlog日志转为sql文件][MySQL_mysqlbinlog_binlog_sql]

## 四、进入主题：[mysql binlog恢复数据][mysql binlog] ##

4.1、查看binlog日志内容 show binlog events;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 8][]

4.2、（可跳过）查看指定binlog文件的内容 show binlog events in 'mysql-bin.000001';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 9][]

4.3、查询语法 继续4.1的操作（表示从Pos参数为402的地方开始）show binlog events in 'mysql-bin.000001' from 402;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 10][]

4.4、查询语法 show binlog events in 'mysql-bin.000001' from 402 limit 1,2;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 11][]

4.5、解析日志文件（退出mysql，cd进入日志文件夹）

=============待完善

[https://blog.csdn.net/songrenqing/article/details/80330592][https_blog.csdn.net_songrenqing_article_details_80330592]

[https://www.cnblogs.com/YCcc/p/10825870.html][mysql binlog]

4.6、[mysql中利用binlog日志恢复数据][https_blog.csdn.net_songrenqing_article_details_80330592]

Mysql 通过binlog日志恢复数据

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70]: /images/20230528/a1c195a0d22a423d8cc469ca45884948.png
[20200411180258730.png]: /images/20230528/a5e6fbe3f1314246bdec8c5d4d8c3999.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 1]: /images/20230528/b9a0920720ab4edf9feb1be3454e4897.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 2]: /images/20230528/72ef5535dd4b4528b599e3e3ea8621bd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 3]: /images/20230528/42bcdb28df374c4db6e1d44c1c3c881e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 4]: /images/20230528/c72bf84e7c63418bb3296c5e55e5f99c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 5]: /images/20230528/efcb7c90108345f2b4250d7f7526257c.png
[mysqlbinlog]: https://www.cnblogs.com/Braveliu/p/11466090.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 6]: /images/20230528/9d980c0951854030bb81351c45c87ea6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 7]: /images/20230528/4bfd361f4431448bb783a823ec02036e.png
[MySQL_binlog_binlog]: https://blog.csdn.net/qq_16946803/article/details/105421757
[MySQL_mysqlbinlog_binlog_sql]: https://blog.csdn.net/qq_16946803/article/details/105422700
[mysql binlog]: https://www.cnblogs.com/YCcc/p/10825870.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 8]: /images/20230528/91bda07355344303ad31f9e4b5ab8a3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 9]: /images/20230528/d8aa6e982bf24d9fa88a65657227dfc1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 10]: /images/20230528/1481bff6c9db4d11b4191a00a1b9b082.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2OTQ2ODAz_size_16_color_FFFFFF_t_70 11]: /images/20230528/ba4db3cf0c17415c8207697319aa5d5e.png
[https_blog.csdn.net_songrenqing_article_details_80330592]: https://blog.csdn.net/songrenqing/article/details/80330592