mongodb异机做时间点恢复(基于时间范围查询导出oplog)

环境:
OS:Centos 7
DB:mongodb 5
原环境:1主2从,异机恢复的环境:单机(oplog.rs是否开启都可以,开启可以参考:https://www.cnblogs.com/hxlasky/p/17979922)

 

1.模拟写入数据
连接主库模拟写入数据

/usr/local/services/mongodb/bin/mongo 192.168.56.101:29001
use admin
db.auth("test","test123");
myrepl:PRIMARY> use db_pushmsg;
switched to db db_pushmsg
myrepl:PRIMARY> for (var i=0;i<1000;i++){ db.user.save({"userid":i})}
myrepl:PRIMARY> db.user.count();
1000

 

2.备份并记录备份完成时间
在主库上进行备份,备份整个库

[root@dsc1 bak]# /opt/mongodb-database-tools/bin/mongodump -h 192.168.56.101:29001 -u test -p test123 --authenticationDatabase admin --db=db_pushmsg -o /tmp/bak
2024-04-28T09:22:43.579+0800    writing db_pushmsg.tb_test to /tmp/bak/db_pushmsg/tb_test.bson
2024-04-28T09:22:43.584+0800    writing db_pushmsg.user to /tmp/bak/db_pushmsg/user.bson
2024-04-28T09:22:43.588+0800    done dumping db_pushmsg.tb_test (1 document)
2024-04-28T09:22:43.591+0800    done dumping db_pushmsg.user (1000 documents)

这里完成备份的时间点是:2024-04-28T09:22:43,转换成utc时间为:1714267363
后面我们需要从这个时间点开始导出oplog日志

 

3.模拟删除表

db.user.insert({"userid" :1000})
db.user.insert({"userid" :1001})
db.user.insert({"userid" :1002})
db.user.insert({"userid" :1003})
db.user.insert({"userid" :1004})
db.user.insert({"userid" :1005})

这个时候删除表,等会恢复我们需要恢复到1005这个时候的数据.
myrepl:PRIMARY> db.user.drop();

然后继续写入
db.user.insert({"userid" :1006})
db.user.insert({"userid" :1007})
db.user.insert({"userid" :1008})
db.user.insert({"userid" :1009})
db.user.insert({"userid" :1010})

 

4.找到删除表的时间点
下面两个方法都可以查询删除表的日志

db.oplog.rs.find({"o" : { "drop" : "user" }})
db.oplog.rs.find({"o.drop": {$exists: true}}).sort({$natural: -1}).limit(1);

myrepl:PRIMARY> use local;
switched to db local
myrepl:PRIMARY> db.oplog.rs.find({"o.drop": {$exists: true}}).sort({$natural: -1}).limit(1);
{ "op" : "c", "ns" : "db_pushmsg.$cmd", "ui" : UUID("97085f8f-29a3-460e-a705-100a7b741979"), "o" : { "drop" : "user" }, "o2" : { "numRecords" : 1006 }, "ts" : Timestamp(1714268052, 1), "t" : NumberLong(3), "v" : NumberLong(2), "wall" : ISODate("2024-04-28T01:34:12.031Z") }

myrepl:PRIMARY> db.oplog.rs.find({"o" : { "drop" : "user" }})
{ "op" : "c", "ns" : "db_pushmsg.$cmd", "ui" : UUID("97085f8f-29a3-460e-a705-100a7b741979"), "o" : { "drop" : "user" }, "o2" : { "numRecords" : 1006 }, "ts" : Timestamp(1714268052, 1), "t" : NumberLong(3), "v" : NumberLong(2), "wall" : ISODate("2024-04-28T01:34:12.031Z") }

 

这里删除的时间点是:1714268052

5.导出oplog.rs
用于异机数据恢复
[root@dsc1 bin]# mkdir /tmp/oplog
基于时间范围查询导出:
/opt/mongodb-database-tools/bin/mongodump -h 192.168.56.101:29001 -u test -p test123 --authenticationDatabase admin --db=local -c oplog.rs --query '{"ts":{"$gt": {"$timestamp":{"t":1714267363, "i":1}},"$lt": {"$timestamp":{"t":1714268052, "i":1}}},"ns":{"$regex":"user"}}' -o /tmp/oplog

 

也可以在数据库查询下,查看日志情况
db.oplog.rs.find({ts:{$gt:Timestamp(1714267363, 1),$lt: Timestamp(1714268052, 1)},"ns":{"$regex":"user"}})

 

查看导出的文件内容:
/opt/mongodb-database-tools/bin/bsondump /tmp/oplog/local/oplog.rs.bson>/tmp/oplog.txt

 

6.把备份文件和oplog文件拷贝到需要恢复的服务器

[root@dsc1 tmp]# tar -cvf bak.tar ./bak
[root@dsc1 tmp]# tar -cvf oplog.tar ./oplog

[root@dsc1 tmp]#scp bak.tar root@192.168.56.103:/tmp/
[root@dsc1 tmp]#scp oplog.tar root@192.168.56.103:/tmp/

 

7.异机解压备份文件

[root@localhost tmp]# cd /tmp
[root@localhost tmp]# tar -xvf bak.tar
[root@localhost tmp]# tar -xvf oplog.tar

 

8.恢复
异机当前是没有需要恢复的库的

/opt/mongodb/bin/mongo 192.168.56.103:27001
> use admin
switched to db admin
> db.auth("test","test123");
1
> show dbs
admin   0.000GB
config  0.000GB
local   0.000GB

 

恢复数据库
[root@localhost ~]# /opt/mongodb-database-tools/bin/mongorestore -h 192.168.56.103:27001 -u test -p test123 --authenticationDatabase admin -d db_pushmsg /tmp/bak/db_pushmsg

 

9.恢复oplog
/opt/mongodb-database-tools/bin/mongorestore -h 192.168.56.103:27001 -u test -p test123 --authenticationDatabase admin --oplogReplay /tmp/oplog/local/oplog.rs.bson
这里恢复需要指定具体的文件名:/tmp/oplog/local/oplog.rs.bson,而不是目录

 

可以看到数据已经恢复

> db.user.find().sort({_id: -1}).limit(5)
{ "_id" : ObjectId("662da76002272b15d9bab6bb"), "userid" : 1005 }
{ "_id" : ObjectId("662da75b02272b15d9bab6ba"), "userid" : 1004 }
{ "_id" : ObjectId("662da75702272b15d9bab6b9"), "userid" : 1003 }
{ "_id" : ObjectId("662da75202272b15d9bab6b8"), "userid" : 1002 }
{ "_id" : ObjectId("662da74d02272b15d9bab6b7"), "userid" : 1001 }