前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >mysql误操作binlog恢复

mysql误操作binlog恢复

作者头像
doper
发布于 2022-09-26 17:44:34
6950
发布于 2022-09-26 17:44:34
举报
文章被收录于专栏：后台技术杂项笔记后台技术杂项笔记

mysql误操作binlog恢复步骤

记录具体步骤，方便日后回忆

一般正式环境使用数据库都会做全备份，但如果某个时刻数据库崩了，若利用全备份文件恢复，则在全备份到出现问题这段时间内的数据将会丢失，一般来说迫不得已也不会采取这种措施，更直接是采用binlog恢复。

实验环境

这里使用mysql镜像实验

docker

代码语言：javascript
复制
Client:
 Version:           20.10.12
 API version:       1.41
 Go version:        go1.17.5
 Git commit:        e91ed5707e
 Built:             Mon Dec 13 22:31:40 2021
 OS/Arch:           linux/amd64
 Context:           default
 Experimental:      true

Server:
 Engine:
  Version:          20.10.12
  API version:      1.41 (minimum version 1.12)
  Go version:       go1.17.5
  Git commit:       459d0dfbbb
  Built:            Mon Dec 13 22:30:43 2021
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          v1.5.9
  GitCommit:        1407cab509ff0d96baa4f0eb6ff9980270e6e620.m
 runc:
  Version:          1.1.0
  GitCommit:        v1.1.0-0-g067aaf85
 docker-init:
  Version:          0.19.0
  GitCommit:        de40ad0

mysql

代码语言：javascript
复制
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

1. 检查数据库是否开启binlog

使用show variables like '%log_bin%'检查是否开启

代码语言：javascript
复制
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)

若没开启，则可通过在配置文件my.cnf添加配置

代码语言：javascript
复制
log_bin=ON
log_bin_basename=/var/lib/mysql/binlog 
log_bin_index=/var/lib/mysql/binlog.index

2. 模拟误删除操作

  1. 创建实验数据库myDatabase以及实验数据表user 这里建表语句来自mybatis-plus官方文档(懒得自己写了)
代码语言：javascript
复制
mysql> create database myDatabase;
Query OK, 1 row affected (0.04 sec)

mysql> use myDatabase;
Database changed
mysql> DROP TABLE IF EXISTS user;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CREATE TABLE user
    -> (
    ->     id BIGINT(20) NOT NULL COMMENT 'ID',
    ->     name VARCHAR(30) NULL DEFAULT NULL COMMENT '',
    ->     age INT(11) NULL DEFAULT NULL COMMENT '',
    ->     email VARCHAR(50) NULL DEFAULT NULL COMMENT '',
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> DELETE FROM user;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT INTO user (id, name, age, email) VALUES
    -> (1, 'Jone', 18, 'test1@baomidou.com'),
    -> (2, 'Jack', 20, 'test2@baomidou.com'),
    -> (3, 'Tom', 28, 'test3@baomidou.com'),
    -> (4, 'Sandy', 21, 'test4@baomidou.com'),
    -> (5, 'Billie', 24, 'test5@baomidou.com');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+--------+------+--------------------+
| id | name   | age  | email              |
+----+--------+------+--------------------+
|  1 | Jone   |   18 | test1@baomidou.com |
|  2 | Jack   |   20 | test2@baomidou.com |
|  3 | Tom    |   28 | test3@baomidou.com |
|  4 | Sandy  |   21 | test4@baomidou.com |
|  5 | Billie |   24 | test5@baomidou.com |
+----+--------+------+--------------------+
5 rows in set (0.00 sec)
  1. 模拟误删除
代码语言：javascript
复制
mysql> drop table user;
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
Empty set (0.01 sec)

mysql>

3. 查找binlog

代码语言：javascript
复制
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000026 |      7815 | No        |
| binlog.000027 |     24952 | No        |
| binlog.000028 |      3231 | No        |
| binlog.000029 |       179 | No        |
| binlog.000030 |       479 | No        |
| binlog.000031 |      5013 | No        |
| binlog.000032 |       528 | No        |
| binlog.000033 |       179 | No        |
| binlog.000034 |       179 | No        |
| binlog.000035 |      1447 | No        |
| binlog.000036 |       179 | No        |
| binlog.000037 |      5566 | No        |
| binlog.000038 |       821 | No        |
| binlog.000039 |       854 | No        |
| binlog.000040 |      4509 | No        |
| binlog.000041 |       859 | No        |
| binlog.000042 |      2982 | No        |
| binlog.000043 |      8238 | No        |
| binlog.000044 |       179 | No        |
| binlog.000045 |       179 | No        |
| binlog.000046 |       179 | No        |
| binlog.000047 |       179 | No        |
| binlog.000048 |     11185 | No        |
+---------------+-----------+-----------+
23 rows in set (0.00 sec)

mysql> show master status\G;
*************************** 1. row ***************************
             File: binlog.000048
         Position: 11185
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

可以看到这里最近的binlog名字为binlog.000048，根据第一步开启binlog命令中的提示去/var/lib/mysql/下查找binlog

代码语言：javascript
复制
root@f5f55acbb59a:/var/lib/mysql# ls -a | grep binlog.
binlog.000026
binlog.000027
binlog.000028
binlog.000029
binlog.000030
binlog.000031
binlog.000032
binlog.000033
binlog.000034
binlog.000035
binlog.000036
binlog.000037
binlog.000038
binlog.000039
binlog.000040
binlog.000041
binlog.000042
binlog.000043
binlog.000044
binlog.000045
binlog.000046
binlog.000047
binlog.000048
binlog.index

4. 直接将binlog导出到sql文件

接着把binlog.000048拿出来，然后转为sql文件

代码语言：javascript
复制
root@f5f55acbb59a:/var/lib/mysql# cp binlog.000048 /home/
root@f5f55acbb59a:/var/lib/mysql# cd /home/
root@f5f55acbb59a:/home# mysqlbinlog -d myDatabase binlog.000048 > 000048bin.sql
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
root@f5f55acbb59a:/home# ls
000048bin.sql  binlog.000048

这里-d用来指定误操作的数据库，当然还有其他参数如--start-datetime，--stop-datetime， --start-position， --stop-position用来指定导出binlog哪些时间点

由于这里是使用了mysql容器，因此我将sql文件复制到宿主机修改然后再放回去，也可以直接在容器内修改但是我懒得安装相关编辑器。

代码语言：javascript
复制
doper@doper  /home  sudo docker cp mysql:/home/000048bin.sql /home

修改000048bin.sql文件，找到刚才的删除语句，并将其对应的at以下的内容全部删除

image-20220125111545962
image-20220125111545962

修改完后重新将sql文件重新放回容器

代码语言：javascript
复制
doper@doper  /home  sudo docker cp /home/000048bin.sql mysql:/home/

回放

代码语言：javascript
复制
root@f5f55acbb59a:/home# mysql -uroot -p123456 myDatabase < 000048bin.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1007 (HY000) at line 400: Can't create database 'myDatabase'; database exists

这里报错是因为myDatabase数据库已经存在，说明我们导出的binlog时间段太长，回放时冲突，因此我们可以利用刚才上面提到的导出时间参数选择正确的时间点，重新生成sql文件执行.

这种方式恢复的时间段不够精确，容易出现问题。

5. 根据binlog时间点提取sql文件

代码语言：javascript
复制
root@f5f55acbb59a:/var/lib/mysql# mysqlbinlog binlog.000048

使用mysqlbinlog+binlog日志名来读取binlog里面的内容，其中# at + 数字中的数字就代表对应的pos时间点编号，并且其下一行还有对应操作发生的具体时间

找到我们要数据回滚的时间点

image-20220125113249838
image-20220125113249838
image-20220125113029208
image-20220125113029208

使用参数start-position和stop-position来导出我们指定的时间点，这里导出不包括10967

代码语言：javascript
复制
root@f5f55acbb59a:/home# mysqlbinlog --start-position=10154 --stop-position=10967 -d myDatabase binlog.000048 > 000048bin.sql

cat sql文件的内容看下

代码语言：javascript
复制
root@f5f55acbb59a:/home# cat 000048bin.sql 
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 156
#220125  2:02:03 server id 1  end_log_pos 125 CRC32 0xae39ed97  Start: binlog v 4, server v 8.0.27 created 220125  2:02:03 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
G1rvYQ8BAAAAeQAAAH0AAAABAAQAOC4wLjI3AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAbWu9hEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBl+05rg==
'/*!*/;
# ================================		start-position		================================
# at 10154
#220125  3:02:28 server id 1  end_log_pos 10486 CRC32 0x891fbcfa        Query   thread_id=36    exec_time=0     error_code=0    Xid = 1639
use `myDatabase`/*!*/;
SET TIMESTAMP=1643079748/*!*/;
SET @@session.pseudo_thread_id=36/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE user
(
    id BIGINT(20) NOT NULL COMMENT 'ID',
    name VARCHAR(30) NULL DEFAULT NULL COMMENT '',
    age INT(11) NULL DEFAULT NULL COMMENT '',
    email VARCHAR(50) NULL DEFAULT NULL COMMENT '',
    PRIMARY KEY (id)
)
/*!*/;
# at 10486
#220125  3:02:35 server id 1  end_log_pos 10565 CRC32 0x27b2ef33        Anonymous_GTID  last_committed=31       sequence_number=32      rbr_only=yes original_committed_timestamp=1643079755409992    immediate_commit_timestamp=1643079755409992     transaction_length=481
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1643079755409992 (2022-01-25 03:02:35.409992 UTC)
# immediate_commit_timestamp=1643079755409992 (2022-01-25 03:02:35.409992 UTC)
/*!80001 SET @@session.original_commit_timestamp=1643079755409992*//*!*/;
/*!80014 SET @@session.original_server_version=80027*//*!*/;
/*!80014 SET @@session.immediate_server_version=80027*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 10565
#220125  3:02:35 server id 1  end_log_pos 10646 CRC32 0xe5bf9fe9        Query   thread_id=36    exec_time=0     error_code=0
SET TIMESTAMP=1643079755/*!*/;
BEGIN
/*!*/;
# at 10646
#220125  3:02:35 server id 1  end_log_pos 10714 CRC32 0x7c4eb4f8        Table_map: `myDatabase`.`user` mapped to number 114
# at 10714
#220125  3:02:35 server id 1  end_log_pos 10936 CRC32 0x0375a73e        Write_rows: table id 114 flags: STMT_END_F

BINLOG '
S2jvYRMBAAAARAAAANopAAAAAHIAAAAAAAEACm15RGF0YWJhc2UABHVzZXIABAgPAw8EeADIAA4B
AQACA/z/APi0Tnw=
S2jvYR4BAAAA3gAAALgqAAAAAHIAAAAAAAEAAgAE/wABAAAAAAAAAARKb25lEgAAABJ0ZXN0MUBi
YW9taWRvdS5jb20AAgAAAAAAAAAESmFjaxQAAAASdGVzdDJAYmFvbWlkb3UuY29tAAMAAAAAAAAA
A1RvbRwAAAASdGVzdDNAYmFvbWlkb3UuY29tAAQAAAAAAAAABVNhbmR5FQAAABJ0ZXN0NEBiYW9t
aWRvdS5jb20ABQAAAAAAAAAGQmlsbGllGAAAABJ0ZXN0NUBiYW9taWRvdS5jb20+p3UD
'/*!*/;
# ================================		end-position(不包括)		================================
# at 10936
#220125  3:02:35 server id 1  end_log_pos 10967 CRC32 0xe0d2259a        Xid = 1641
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
root@f5f55acbb59a:/home#

将sql文件重新执行

代码语言：javascript
复制
root@f5f55acbb59a:/home# mysql -uroot -p123456 myDatabase < 000048bin.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

重新进入数据库发现被删除的表已经恢复

代码语言：javascript
复制
mysql> use myDatabase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------+
| Tables_in_myDatabase |
+----------------------+
| user                 |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from user;
+----+--------+------+--------------------+
| id | name   | age  | email              |
+----+--------+------+--------------------+
|  1 | Jone   |   18 | test1@baomidou.com |
|  2 | Jack   |   20 | test2@baomidou.com |
|  3 | Tom    |   28 | test3@baomidou.com |
|  4 | Sandy  |   21 | test4@baomidou.com |
|  5 | Billie |   24 | test5@baomidou.com |
+----+--------+------+--------------------+
5 rows in set (0.00 sec)

mysql> exit
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2022-01-25，如有侵权请联系 cloudcommunity@tencent.com 删除
sql
数据库
云数据库 SQL Server
容器
python

本文分享自 作者个人站点/博客 前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

sql
数据库
云数据库 SQL Server
容器
python
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • mysql误操作binlog恢复步骤
  • 实验环境
  • 1. 检查数据库是否开启binlog
  • 2. 模拟误删除操作
  • 3. 查找binlog
  • 4. 直接将binlog导出到sql文件
  • 5. 根据binlog时间点提取sql文件
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
产品介绍产品文档
云数据库采购特惠，高性能、高可靠、高安全，超值优惠等你享！
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论