如何使用mysql binlog 恢复数据

布满荆棘的人生 2023-09-27 11:56 221阅读 0赞

如果想通过 mysql 的 binlog 恢复数据，首先要开启 binlog 。这里搭建一个测试的环境，了解一下 mysql binlog 是如何恢复数据库的。原理比较简单，binlog 会存储mysql中变化的数据，比如你创建了一个数据库，写入了一些数据，这些都会存储在 mysql 的 binlog 中。

需要恢复的时候就找到，两个位置，一个起始位置，一个结束的位置。结束的位置，一半是数据被破坏或者删除前的位置。mysql 8 默认已经开启了 binlog

  1. mysql> show variables like '%log_bin%';
  2. +---------------------------------+--------------------------+
  3. | Variable_name                   | Value                    |
  4. +---------------------------------+--------------------------+
  5. | log_bin                         | ON                       |
  6. | log_bin_basename                | /data/mysql/binlog       |
  7. | log_bin_index                   | /data/mysql/binlog.index |
  8. | log_bin_trust_function_creators | OFF                      |
  9. | log_bin_use_v1_row_events       | OFF                      |
  10. | sql_log_bin                     | ON                       |
  11. +---------------------------------+--------------------------+

可以看到 log_bin 已经开启， 同时可以看到存储的位置在 /daba/mysql 目录 , 前缀是 binlog

  1. ls /data/mysql/binlog.*
  2. /data/mysql/binlog.000143  /data/mysql/binlog.000144  /data/mysql/binlog.000145  /data/mysql/binlog.000146  /data/mysql/binlog.index

可以看到有好几个 binlog 日志文件， 因为这里是测试的数据库（有历史信息,之前的数据，没什么用)，为了方便测试，直接进行重置操作。(删除了全部binlog文件) ，如果是生成环境 ，谨慎操作，数据无价。

  1. show master logs;
  2. +---------------+-----------+-----------+
  3. | Log_name      | File_size | Encrypted |
  4. +---------------+-----------+-----------+
  5. | binlog.000143 |       156 | No        |
  6. | binlog.000144 |       200 | No        |
  7. | binlog.000145 |       156 | No        |
  8. | binlog.000146 |       156 | No        |
  9. +---------------+-----------+-----------+
  10. 4 rows in set (0.01 sec)
  12. mysql> reset master;
  13. Query OK, 0 rows affected (0.02 sec)
  15. mysql> show master logs;
  16. +---------------+-----------+-----------+
  17. | Log_name      | File_size | Encrypted |
  18. +---------------+-----------+-----------+
  19. | binlog.000001 |       156 | No        |
  20. +---------------+-----------+-----------+
  21. 1 row in set (0.00 sec)
  24. ls /data/mysql/binlog.*
  25. /data/mysql/binlog.000001  /data/mysql/binlog.index

执行重置(reset master)后 ，可以看到之前的 binlog 文件已经被删除了，产生一个新的 binlog 文件。

可以查看一下这个文件的内容

  1. $mysqlbinlog binlog.000001
  2. # The proper term is pseudo_replica_mode, but we use this compatibility alias
  3. # to make the statement usable on server versions 8.0.24 and older.
  4. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  5. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  6. DELIMITER /*!*/;

这个时候，我们添加一些数据

  1. mysql <<EOT
  2. create database test_liuhaolin_com;
  3. select now();
  4. EOT
  6. mysql <<EOT
  7. use test_liuhaolin_com;
  9. create table if not exists test(
  10.     \`id\` int unsigned not null auto_increment primary key,
  11.     \`key\` varchar(100),
  12.     \`val\` varchar(255)
  13. ) engine=myisam charset=utf8mb4;
  14. EOT
  17. mysql <<EOT
  18. use test_liuhaolin_com;
  19. insert into test  values ('1','website', 'https://www.liuhaolin.com');
  20. EOT

这个时候，不小心删除了，数据库 test_liuhaolin_com

  1. mysql> drop database test_liuhaolin_com;
  2. Query OK, 1 row affected (0.09 sec)

现在就需要解决一个实际问题，怎么恢复这个数据库
首先为了防止干扰，执行 flush logs ，产生一个新binlog 文件。

  1. mysql> show master logs;
  2. +---------------+-----------+-----------+
  3. | Log_name      | File_size | Encrypted |
  4. +---------------+-----------+-----------+
  5. | binlog.000001 |      1594 | No        |
  6. +---------------+-----------+-----------+
  7. 1 row in set (0.00 sec)
  9. mysql> show master status;
  10. +---------------+----------+--------------+------------------+-------------------+
  11. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  12. +---------------+----------+--------------+------------------+-------------------+
  13. | binlog.000001 |     1594 |              |                  |                   |
  14. +---------------+----------+--------------+------------------+-------------------+
  15. 1 row in set (0.00 sec)
  17. mysql> flush logs;
  18. Query OK, 0 rows affected (0.08 sec)
  20. mysql> show master status;
  21. +---------------+----------+--------------+------------------+-------------------+
  22. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  23. +---------------+----------+--------------+------------------+-------------------+
  24. | binlog.000002 |      156 |              |                  |                   |
  25. +---------------+----------+--------------+------------------+-------------------+
  26. 1 row in set (0.00 sec)

恢复数据，首先要找到数据在哪里被删除了。

  1. mysqlbinlog binlog.000001 | grep -n  "drop database"
  2. 113:drop database test_liuhaolin_com

可以看到在 113 行的地方有个 删除语句。终可以找到两个地方

1.数据需要恢复的起始位置
2.数据需要恢复的结束位置
这里起始的位置就找 创建数据库的位置，结束的位置就找 删除数据库的位置。

  1. mysqlbinlog --set-charset=utf8  binlog.000001 > tmp.sql

可以在文件 tmp.sql 中知道 开始和结束位置

2b3a1d115c58db3ee2cf04911eff1d77.png

可以看到开始的地方是 233

084721cb7cd04e6cb6c2dd78aa889d9a.png

可以看到结束的位置在 1371 ，所以执行一下数据的恢复。

  1. mysqlbinlog -v  binlog.000001  --start-position=233  --stop-position=1371 | mysql

作为验证,执行前可以，看下 数据库是否存在。

  1. mysql> use test_liuhaolin_com;
  2. ERROR 1049 (42000): Unknown database 'test_liuhaolin_com'

执行恢复操作

  1. mysqlbinlog -v  binlog.000001  --start-position=233  --stop-position=1371 | mysql

再次检查，可以发现数据已经恢复

  1. mysql>
  2. mysql> use test_liuhaolin_com;
  3. Database changed

发表评论

表情：
评论列表 （有 0 条评论，221人围观）

还没有评论，来说两句吧...

相关阅读