只有表结构和binlog二进制日志文件-如何恢复数据


项目案例:
这家客户有一个MySQL5.7的库,由于磁盘故障的原因,导致整个数据库目录文件损坏,但庆幸的是,BINLOG日志文件在另一个磁盘中,客户的需求是,想紧急恢复其中一个重要的表数据,其它的可以慢慢再做还原数据处理


现在的问题是,手上有一个当时的binlog日志文件,也知道这张表的表结构,如何恢复数据?


其实这种情况还是可以根据二进制日志进行恢复的,请听我慢慢讲来O(∩_∩)O哈!


1.配置一台跟线上同样版本的MySQL实例:
安装配置略
Connection id:          11
Current database:       nsy_scm
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Using delimiter:        ;
Server version:         5.7.36-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 30 min 15 sec


配置文件如下:
cat my.cnf
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
(其它配置省略)


查看二进制日志文件:
mysql> show binary logs;
+-----------+-----------+
| Log_name  | File_size |
+-----------+-----------+
| on.000001 |        317 |
| on.000002 |     54436 |
| on.000003 |       217 |
| on.000004 |       217 |
| on.000005 |       194 |
+-----------+-----------+
5 rows in set (0.00 sec)


注意了,重点来了哈
我手上有二进制日志文件,文件名为mysql-bin.038331
现在要骗mysql,将mysql-bin.038331文件,重命名为on.000001
先停止数据库:
systemctl stop mysqld
mv mysql-bin.038331  on.000001


在数据库目录把旧的on.000001删除,再把改名后的on.000001复制到数据目录下
mv on.000001 /var/lib/mysql/


再启动实例:
systemctl start mysqld


再次查看一下二进制日志文件列表:
mysql> show binary logs;
+-----------+-----------+
| Log_name  | File_size |
+-----------+-----------+
| on.000001 | 524288577 |
| on.000002 |     54436 |
| on.000003 |       217 |
| on.000004 |       217 |
| on.000005 |       194 |
+-----------+-----------+
看到了吗,新文件已被数据库识别到了哈

别忘记了把需要恢复数据的表结构在库中创建哈



下面的操作就简单了,通过binlog2sql反向解析出的DML语句
binlog2sql安装配置:
安装epel-release
[root@localhost ~]# yum -y install epel-release  
 安装python-pip
[root@localhost ~]# yum -y install python-pip
 升级pip
[root@localhost ~]# pip install --upgrade pip
 检查pip版本
[root@localhost ~]# pip --version
shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql


shell> pip install -r requirements.txt


以上就安装好了binlog2sql,现在就可以通过binlog2sql来解析出需要的SQL记录了


命令如下:
 python binlog2sql.py  -h172.16.12.46 -P3306 -uroot -p'123456' -dnsy_scm -tsupplier_spec_product_purchase  --start-file='on.000001' >./1.sql


cat 1.sql
UPDATE `nsy_scm`.`supplier_spec_product_purchase` SET `supplier_spec_product_purchase_id`=537221, `supplier_id`=520, `is_deleted`=0, `color_code`='1011', `update_date`='2023-06-14 13:52:10', `color`='Gray', `create_by`='供应商商品价格数据同步', `spec_sku`='MC255661-1011-2XL', `update_by`='GYXT-4012', `color_sku`='MC255661-1011', `version`=2121, `is_small_order_price`=1, `product_sku`='MC255661', `create_date`='2022-09-14 19:01:03', `supplier_product_id`=142169, `spec_id`=1495854, `purchase_price`=34.9900, `product_id`=181216 WHERE `supplier_spec_product_purchase_id`=537221 AND `supplier_id`=520 AND `is_deleted`=0 AND `color_code`='1011' AND `update_date`='2023-06-14 13:52:10' AND `color`='Gray' AND `create_by`='供应商商品价格数据同步' AND `spec_sku`='MC255661-1011-2XL' AND `update_by`='GYXT-4012' AND `color_sku`='MC255661-1011' AND `version`=2120 AND `is_small_order_price`=1 AND `product_sku`='MC255661' AND `create_date`='2022-09-14 19:01:03' AND `supplier_product_id`=142169 AND `spec_id`=1495854 AND `purchase_price`=34.9900 AND `product_id`=181216 LIMIT 1; #start 20832598 end 20857911 time 2023-06-14 13:52:10
UPDATE `nsy_scm`.`supplier_spec_product_purchase` SET `supplier_spec_product_purchase_id`=537222, `supplier_id`=520, `is_deleted`=0, `color_code`='1', `update_date`='2023-06-14 13:52:10', `color`='White', `create_by`='供应商商品价格数据同步', `spec_sku`='MC255661-1-S', `update_by`='GYXT-4012', `color_sku`='MC255661-1', `version`=2121, `is_small_order_price`=1, `product_sku`='MC255661', `create_date`='2022-09-14 19:01:03', `supplier_product_id`=142169, `spec_id`=1495855, `purchase_price`=34.9900, `product_id`=181216 WHERE `supplier_spec_product_purchase_id`=537222 AND `supplier_id`=520 AND `is_deleted`=0 AND `color_code`='1' AND `update_date`='2023-06-14 13:52:10' AND `color`='White' AND `create_by`='供应商商品价格数据同步' AND `spec_sku`='MC255661-1-S' AND `update_by`='GYXT-4012' AND `color_sku`='MC255661-1' AND `version`=2120 AND `is_small_order_price`=1 AND `product_sku`='MC255661' AND `create_date`='2022-09-14 19:01:03' AND `supplier_product_id`=142169 AND `spec_id`=1495855 AND `purchase_price`=34.9900 AND `product_id`=181216 LIMIT 1; #start 20832598 end 20858332 time 2023-06-14 13:52:10

以上就是只通过二进制日志加表结构,解析出具体的SQL的步骤哈

  



分割线
感谢打赏
江西数库信息技术有限公司
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS