MongoDB进阶（三）—备份与恢复

2017年12月7日19:51:19 发表评论 1,074 views

1.1 备份恢复方式

1.1.1 文档级工具（逻辑）

1.1.1.1 mongoexport/mongoimport

导入/导出的是JSON格式或者CSV格式（数据行，便于查看），适用于跨版本、跨架构的备份恢复、迁移（如：mysql迁移到mongodb）。

1.1.1.2 mongodump/mongorestore

导入/导出的是BSON格式，适用于常规的备份恢复。

1.1.1.3 JSON和BSON的区别

JSON可读性强但体积较大，BSON则是二进制文件，体积小但对人类几乎没有可读性。
在一些mongodb版本之间，BSON格式可能会随版本不同而有所不同，所以不同版本之间用mongodump/mongorestore可能不会成功，具体要看版本之间的兼容性。当无法使用BSON进行跨版本的数据迁移的时候，使用JSON格式即mongoexport/mongoimport是一个可选项。
跨版本的mongodump/mongorestore个人并不推荐，实在要做请先检查文档看两个版本是否兼容（大部分时候是的）。

1.1.2 文件系统级

物理
拷贝文件卷/磁盘快照

1.2 mongodump和mongorestore介绍

mongodump能够在Mongodb运行时进行备份，它的工作原理是对运行的Mongodb做查询，然后将所有查到的文档写入磁盘。但是存在的问题时使用mongodump产生的备份不一定是数据库的实时快照，如果我们在备份时对数据库进行了写入操作，则备份出来的文件可能不完全和Mongodb实时数据相等。另外在备份时可能会对其它客户端性能产生不利的影响。

1.2.1 mongodump帮助信息

[mongod@db01 ~]$ mongodump --help

参数说明：

-h：指明数据库宿主机的IP
-u：指明数据库的用户名
-p：指明数据库的密码
-d：指明数据库的名字
-c：指明collection的名字
-o：指明到要导出的文件名
-q：指明导出数据的过滤条件
-j，--numParallelCollections= number of collections to dump in parallel (4 by default)
--oplog：备份的同时备份oplog

1.3 mongodump和mongorestore单节点操作（很少使用）

1.3.1 单库备份

$ mongodump -h  10.0.0.21:30000 -uroot -proot --authenticationDatabase admin  -d test -o /mongodb/backup/

1.3.2 单集合备份

$ mongodump -h  10.0.0.21:30000 -uroot -proot --authenticationDatabase admin  -d test -c vast -o /mongodb/backup/

1.3.3 压缩备份

$ mongodump -h  10.0.0.21:30000 -uroot -proot --authenticationDatabase admin  -d test -o /mongodb/backup/ --gzip
$ mongodump -h  10.0.0.21:30000 -uroot -proot --authenticationDatabase admin  -d test -c vast -o /mongodb/backup/ --gzip

1.3.4 单库恢复

$ mongorestore -h  10.0.0.21:30000 -uroot -proot --authenticationDatabase admin -d test /home/mongod/backup/test/

1.3.5 单集合恢复

$ mongorestore -h  10.0.0.21:30000 -uroot -proot --authenticationDatabase admin -d test -c vast /home/mongod/backup/test/vast.bson

1.3.6 先删除再恢复

$ mongorestore -h  10.0.0.21:30000 -uroot -proot --authenticationDatabase admin -d test --drop /home/mongod/backup/test/
$ mongorestore -h  10.0.0.21:30000 -uroot -proot --authenticationDatabase admin -d test -c vast --drop /home/mongod/backup/test/vast.bson

参数说明：

--drop：表示恢复的时候把之前的集合drop掉

1.4 mongodump和mongorestore高级企业应用

注意：这是replica set或者master/slave模式专用操作：

--oplog： use oplog for taking a point-in-time snapshot

1.4.1 oplog介绍

在replica set中oplog是一个定容集合（capped collection），它的默认大小是磁盘空间的5%（可以通过--oplogSizeMB参数修改）。位于local库的db.oplog.rs，其中记录的是整个mongod实例一段时间内数据库的所有变更（插入/更新/删除）操作，当空间用完时新记录自动覆盖最老的记录，其覆盖范围被称作oplog时间窗口。需要注意的是：因为oplog是一个定容集合，所以时间窗口能覆盖的范围会因为单位时间内的更新次数不同而变化。

查看当前的oplog时间窗口预计值，可以使用以下命令：

[mongod@db01 ~]$ mongo --host=10.0.0.41 --port=29021
sh1:PRIMARY> rs.printReplicationInfo()
configured oplog size:   2048MB
log length start to end: 499secs (0.14hrs)
oplog first event time:  Fri Dec 01 2017 15:31:30 GMT+0800 (CST)
oplog last event time:   Fri Dec 01 2017 15:39:49 GMT+0800 (CST)
now:                     Fri Dec 01 2017 16:13:31 GMT+0800 (CST)

1.4.2 oplog企业级应用（备份时使用--oplog选项实现热备）

1.4.2.1 环境准备

[root@db01 ~]# su - mongod
[mongod@db01 ~]$ mongod -f /usr/local/mongodb/27017/conf/mongod.yml
[mongod@db01 ~]$ mongod -f /usr/local/mongodb/27018/conf/mongod.yml
[mongod@db01 ~]$ mongod -f /usr/local/mongodb/27019/conf/mongod.yml

1.4.2.2 创建备份目录

[mongod@db01 ~]$ mkdir -p /usr/local/mongodb/backup

1.4.2.3 模拟数据插入

[mongod@db01 ~]$ mongo 10.0.0.41:27018/test
repl:PRIMARY>for(var i = 0; i < 1000000; i++) {
    db.foo.insert({a: i});
}

1.4.2.4 在数据插入时进行全库备份

[mongod@db01 ~]$ mongodump --port 27018 --oplog -o /usr/local/mongodb/backup/
2017-12-01T11:45:27.069+0800    writing test.foo to
2017-12-01T11:45:27.795+0800    done dumping test.foo (125645 documents)
2017-12-01T11:45:27.799+0800    writing captured oplog to
2017-12-01T11:45:27.881+0800        dumped 957 oplog entries

1.4.2.5 模拟误删除操作

[mongod@db01 ~]$ mongo 10.0.0.41:27018/test
repl:PRIMARY> db.foo.count()
100000
repl:PRIMARY> db.foo.findOne()
{ "_id" : ObjectId("5a20d180091cc855741500ab"), "a" : 0 }
repl:PRIMARY> db.foo.remove({"a":0})
WriteResult({ "nRemoved" : 1 })
repl:PRIMARY> db.foo.findOne()
{ "_id" : ObjectId("5a20d180091cc855741500ac"), "a" : 1 }
repl:PRIMARY> db.foo.count()
99999

1.4.2.6 备份oplog信息

[mongod@db01 ~]$ mongodump --port 27018 -d local -c oplog.rs -o /usr/local/mongodb/backup/
2017-12-01T11:57:45.672+0800    writing local.oplog.rs to
2017-12-01T11:57:47.833+0800    done dumping local.oplog.rs (425798 documents)

1.4.2.7 查看误操作的时间戳

[mongod@db01 ~]$ bsondump /usr/local/mongodb/backup/local/oplog.rs.bson | grep "\"op\":\"d\"" | head
2017-12-01T12:01:34.601+0800    425798 objects found
{"ts":{"$timestamp":{"t":1512100483,"i":1}},"t":{"$numberLong":"1"},"h":{"$numberLong":"7078003606994129005"},"v":2,"op":"d","ns":"test.foo","o":{"_id":{"$oid":"5a20d180091cc855741500ab"}}}

1.4.2.8 还原误删除操作之前的状态

[mongod@db01 ~]$ mongorestore --port 27018 --oplogReplay --oplogLimit "1512100483:1" /usr/local/mongodb/backup/
...省略部分输出内容...
2017-12-01T12:17:10.226+0800    finished restoring local.oplog.rs (425798 documents)
2017-12-01T12:17:10.226+0800    replaying oplog
2017-12-01T12:17:10.248+0800    done

1.4.2.9 查看还原结果

[mongod@db01 ~]$ mongo 10.0.0.41:27018/test
repl:PRIMARY> db.foo.count()
1000000
repl:PRIMARY> db.foo.find({a:0})
{ "_id" : ObjectId("5a20e46f091cc8557416874b"), "a" : 0 }

1.5 mongoexport和mongoimport介绍

Mongodb中的mongoexport工具可以把一个collection导出成JSON格式或CSV格式的文件，可以通过参数指定导出的数据项，也可以根据指定的条件导出数据。

Mongodb中的mongoimport工具可以把一个特定格式文件中的内容导入到指定的collection中。该工具可以导入JSON格式数据，也可以导入CSV格式数据。

1.5.1 mongoexport帮助信息

[mongod@db01 ~]$ mongoexport --help

参数说明：

-h：指明数据库宿主机的IP
-u：指明数据库的用户名
-p：指明数据库的密码
-d：指明数据库的名字
-c：指明collection的名字
-f：指明要导出那些列
-o：指明到要导出的文件名
-q：指明导出数据的过滤条件
--authenticationDatabase admin

1.5.2 mongoimport帮助信息

[mongod@db01 ~]$ mongoimport --help

参数说明：

-h：指明数据库宿主机的IP
-u：指明数据库的用户名
-p：指明数据库的密码
-d：指明数据库的名字
-c：指明collection的名字
-f：指明要导入那些列
-j，--numInsertionWorkers=<number> ：number of insert operations to run concurrently
--headerline：指明第一行是列名，不需要导入

1.6 mongoexport和mongoimport基本操作

1.6.1 全库备份（JSON）

[mongod@db01 ~]$ mongoexport -h 10.0.0.41:27018 -d test -c vast -o /usr/local/mongodb/backup/vasts.dat
2017-12-01T16:47:51.780+0800    connected to: 10.0.0.41:27018
2017-12-01T16:47:51.922+0800    exported 200 records

提示：备份文件的名字可以自定义，默认导出了JSON格式的数据。

1.6.2 全库备份（CSV）

[mongod@db01 ~]$ mongoexport -h 10.0.0.41:27018 -d test -c vast --type=csv -f id,name -o /usr/local/mongodb/backup/vasts.csv
2017-12-01T16:51:07.136+0800    connected to: 10.0.0.41:27018
2017-12-01T16:51:07.148+0800    exported 200 records

1.6.3 还原数据库（JSON）

[mongod@db01 ~]$ mongoimport -h 10.0.0.41:27018 -d test -c vast /usr/local/mongodb/backup/vasts.dat
2017-12-01T16:55:06.106+0800    connected to: 10.0.0.41:27018
2017-12-01T16:55:06.146+0800    imported 200 documents

1.6.4 还原数据库（CSV）

[mongod@db01 ~]$ mongoimport -h 10.0.0.41:27018 -d test -c vast --type=csv --headerline --file /usr/local/mongodb/backup/vasts.csv
2017-12-01T16:57:28.606+0800    connected to: 10.0.0.41:27018
2017-12-01T16:57:28.642+0800    imported 200 documents

1.6.5 导入外部数据

1.6.5.1 创建外部数据

[mongod@db01 ~]$ vim /usr/local/mongodb/backup/P_T_HISTXN_20171201.csv
"2015-07-01 00:00:00","154221","11000255653101","2015-07-01 00:00:00","0523753","EB0523753","PAZF00","07010032044026","0305","EB052",,"0305","银行北京支行","0305","11000255653101","北京立信科技发展有限公司",,"北京上地支行","308100005416","6226090110740251","徐雨阳","200000","1","0","RMB",,"D","PAZ15","3090","借款","554345.5","0","WEB","BPAY-UPP",,,"B","ZFA","0000","00000002","Y",,,"2015-07-01 00:00:00","554345.5","554345.5","Y","1",,,,,"WEB","网银","EB","0","Y",,,,"0","0","1","11",,,,"13","11000255653101","N"
"2015-07-01 00:00:00","092012","11000256450102","2015-07-01 00:00:00","0159509","EB0159509","PAZF00","07010031074886","9998","EB015",,,"中国农业银行常州分行营业部","103304061015","6228480419360850277","徐卫","0338","平安银行南京河西支行","9998","11000256450102","江苏文化实业有限公司","35000","0","0","RMB",,"C","PAZ14","3090","书款","28389004.82","0","Z14",,,,"B","ZFA","0000","00000001","Y",,,"2015-07-01 00:00:00","28389004.82","28389004.82","N","1",,,,,"Z14",,"EB","0",,,,,"0","0","1","11",,,,"17","11000256450102","N"
"2015-07-01 00:00:00","101255","11000256450102","2015-07-01 00:00:00","0224494","EB0224494","PABOCP","07011232352900","0338","EB022",,"0338","平安银行南京河西支行","0338","11000256450102","江苏文化实业有限公司",,,"000000000000",,"南京有限公司第一分公司","31053","0","0","RMB",,"D","PAZ02","3090",,"28357951.82","0","106542        B08",,,,"B","ZFA","0000","00000002","Y",,,"2015-07-01 00:00:00","28357951.82","28357951.82","Y","1",,,,,"B08","影像票交提回","EB","0",,,"2001","30106542","0","0","0","11",,,,"17","11000256450102","N"
"2015-07-01 00:00:00","102924","11000256450102","2015-07-01 00:00:00","0245050","EB0245050","PAZF00","07010031223297","9998","EB024",,,"中国建设银行湖北省分行","105521000012","42001258139059005570","武汉市育才幼儿园","0338","平安银行南京河西支行","9998","11000256450102","江苏文化实业有限公司","14040","0","0","RMB",,"C","PAZ12","3090","货款","28371991.82","0","Z12","BPAY-UPP",,,"B","ZFA","0000","00000003","Y",,,"2015-07-01 00:00:00","28371991.82","28371991.82","N","1",,,,,"Z12",,"EB","0","Y",,,,"0","0","1","11",,,,"17","11000256450102","N"
"2015-07-01 00:00:00","103918","11000256450102","2015-07-01 00:00:00","0256580","EB0256580","PAZF00","07010031248738","9998","EB025",,,"中国农业银行常州分行","103304061015","6228480419360850277"

1.6.5.2 导入数据

[mongod@db01 ~]$ mongoimport -h 10.0.0.41:27018 --db=test --collection=tx --numInsertionWorkers=4 --type=csv --fields=TDT,TTM,ACC,ADT,TRC,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,AMT,F23,F24,CCY,F26,F27,F28,F29,F30,F31,F32,F33,F34,F35,F36,F37,F38,F39,SEQ,F41,F42,F43,F44,F45,F46,F47,F48,F49,F50,F51,F52,F53,F54,F55,F56,F57,F58,F59,F60,F61,F62,F63,F64,F65,F66,F67,F68,F69,F70 --file=/usr/local/mongodb/backup/P_T_HISTXN_20171201.csv
2017-12-01T17:05:07.644+0800    connected to: 10.0.0.41:27018
2017-12-01T17:05:07.727+0800    imported 5 documents

1.6.5.3 查看数据

repl:PRIMARY> db.tx.findOne()
{
    "_id" : ObjectId("5a211b436638f1d1cb92d3da"),
    "TDT" : "2015-07-01 00:00:00",
    "TTM" : 154221,
    "ACC" : NumberLong("11000255653101"),
    "ADT" : "2015-07-01 00:00:00",
    "TRC" : 523753,
    "F6" : "EB0523753",
    "F7" : "PAZF00",
    "F8" : NumberLong("7010032044026"),
    "F9" : 305,
    "F10" : "EB052",
    "F11" : "",
    "F12" : 305,
    "F13" : "银行北京支行",
    "F14" : 305,
    "F15" : NumberLong("11000255653101"),
    "F16" : "北京立信科技发展有限公司",
    "F17" : "",
    "F18" : "北京上地支行",
    "F19" : NumberLong("308100005416"),
    "F20" : NumberLong("6226090110740251"),
    "F21" : "徐雨阳",
    "AMT" : 200000,
    "F23" : 1,
    "F24" : 0,
    "CCY" : "RMB",
    "F26" : "",
    "F27" : "D",
    "F28" : "PAZ15",
    "F29" : 3090,
    "F30" : "借款",
    "F31" : 554345.5,
    "F32" : 0,
    "F33" : "WEB",
    "F34" : "BPAY-UPP",
    "F35" : "",
    "F36" : "",
    "F37" : "B",
    "F38" : "ZFA",
    "F39" : 0,
    "SEQ" : 2,
    "F41" : "Y",
    "F42" : "",
    "F43" : "",
    "F44" : "2015-07-01 00:00:00",
    "F45" : 554345.5,
    "F46" : 554345.5,
    "F47" : "Y",
    "F48" : 1,
    "F49" : "",
    "F50" : "",
    "F51" : "",
    "F52" : "",
    "F53" : "WEB",
    "F54" : "网银",
    "F55" : "EB",
    "F56" : 0,
    "F57" : "Y",
    "F58" : "",
    "F59" : "",
    "F60" : "",
    "F61" : 0,
    "F62" : 0,
    "F63" : 1,
    "F64" : 11,
    "F65" : "",
    "F66" : "",
    "F67" : "",
    "F68" : 13,
    "F69" : NumberLong("11000255653101"),
    "F70" : "N"
}

1.7 mongodb和mysql迁移数据

1.7.1 数据库迁移

1.7.1.1 迁移的方式

同构迁移：数据量大，但是版本差距不是很大的情况下，如：只是将数据库迁移到好机器上时
异构迁移：版本差距较大，特别是存储引擎差距较大时，如：1、5.5、5.6、5.7、8.0等情况时，建议使用物理迁移的方式（xtrabackup）。

1.7.1.2 迁移注意事项

要考虑申请停机的时间，即业务不能访问的时间：
业务割接时间：割接时最后那部分binlog的恢复时间（涉及到原服务器的业务停止）
失败回退的时间
应用IP的切换时间
备份时间：有些情况下不需要，比如热备情况
恢复时间：可以参考备份的时间
预迁移测试：应用测试服务器测试迁移数据

1.7.1.3 windows和linux系统之间的迁移

只能使用逻辑迁移（mysqldump等），不能使用物理迁移的方式（xtrabackup等）。

提示：使用mysqldump作为逻辑备份工具时，要考虑使用的字符集要一致

1.7.2 MySQL迁移到Mongodb

1.7.2.1 修改配置文件以对目录有写入的权限

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp

1.7.2.2 导出CSV数据

[root@db01 ~]# mysql -uroot -p123456
mysql> use leon;

mysql> SELECT * FROM test
    -> INTO OUTFILE '/tmp/test.csv'
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> ESCAPED BY '"'
    -> LINES TERMINATED BY '\r\n';
Query OK, 5 rows affected (0.00 sec)

命令说明：

FIELDS TERMINATED BY ',' ：字段间以“,”号分隔
OPTIONALLY ENCLOSED BY '"' ：字段用“"”号括起
ESCAPED BY '"' ：字段中使用的转义符为“"”
LINES TERMINATED BY '\r\n'; ：行以“\r\n”结束

1.7.2.3 查看导出的数据

[root@db01 ~]# cat /tmp/test.csv
1,"leon",18
2,"shadow",20
3,"shuaige",26
4,"meinv",19
5,"wangzi",28

1.7.2.4 导入到Mongodb

[mongod@db01 ~]$ mongoimport -h 10.0.0.41:27017 -d test -c test --type=csv -f id,name,age --file /tmp/test.csv
2017-12-01T18:40:43.562+0800    connected to: 10.0.0.41:27017
2017-12-01T18:40:43.662+0800    imported 5 documents

1.7.2.5 检查导入的数据

[mongod@db01 ~]$ mongo --port 27017
MongoDB shell version: 3.2.8
connecting to: 127.0.0.1:27017/test
repl:PRIMARY> show tables;
test
repl:PRIMARY> db.test.find()
{ "_id" : ObjectId("5a2131abdb10bf4ad3f2c73c"), "id" : 1, "name" : "leon", "age" : 18 }
{ "_id" : ObjectId("5a2131abdb10bf4ad3f2c73d"), "id" : 3, "name" : "shuaige", "age" : 26 }
{ "_id" : ObjectId("5a2131abdb10bf4ad3f2c73e"), "id" : 4, "name" : "meinv", "age" : 19 }
{ "_id" : ObjectId("5a2131abdb10bf4ad3f2c73f"), "id" : 5, "name" : "wangzi", "age" : 28 }
{ "_id" : ObjectId("5a2131abdb10bf4ad3f2c740"), "id" : 2, "name" : "shadow", "age" : 20 }

1.7.3 Mongodb迁移到MySQL

1.7.3.1 Mongodb导出CSV文件

过程参照本文上面的命令。

1.7.3.2 MySQL导入CSV文件

[root@db01 ~]# mysql -uroot -p123456
mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> create table test (                      # 导入之前mysql中需要有相应的表
    -> id int(4) not null auto_increment,
    -> name varchar(20),                
    -> age int(3),                      
    -> primary key (id)                  
    -> );                               
Query OK, 0 rows affected (0.03 sec)

mysql> LOAD DATA INFILE '/tmp/test.csv'         # 导入数据
    -> INTO TABLE test
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> ESCAPED BY '"'
    -> LINES TERMINATED BY '\r\n';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from test;                      # 查看导入的数据
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | leon    |   18 |
|  2 | shadow  |   20 |
|  3 | shuaige |   26 |
|  4 | meinv   |   19 |
|  5 | wangzi  |   28 |
+----+---------+------+
5 rows in set (0.00 sec)

我的微信

如果有技术上的问题可以扫一扫我的微信

1. 本网站名称：Leon的博客
2. 本站永久网址：https://blog.leonshadow.cn
3. 本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长QQ632113590进行删除处理。
4. 本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5. 本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
6. 本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

2023年 9月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30