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帮助信息
[[email protected] ~]$ 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时间窗口预计值，可以使用以下命令：
[[email protected] ~]$ 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 环境准备
[[email protected] ~]# su - mongod [[email protected] ~]$ mongod -f /usr/local/mongodb/27017/conf/mongod.yml [[email protected] ~]$ mongod -f /usr/local/mongodb/27018/conf/mongod.yml [[email protected] ~]$ mongod -f /usr/local/mongodb/27019/conf/mongod.yml
1.4.2.2 创建备份目录
[[email protected] ~]$ mkdir -p /usr/local/mongodb/backup
1.4.2.3 模拟数据插入
[[email protected] ~]$ 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 在数据插入时进行全库备份
[[email protected] ~]$ 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 模拟误删除操作
[[email protected] ~]$ 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信息
[[email protected] ~]$ 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 查看误操作的时间戳
[[email protected] ~]$ 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 还原误删除操作之前的状态
[[email protected] ~]$ 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 查看还原结果
[[email protected] ~]$ 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帮助信息
[[email protected] ~]$ mongoexport --help
- 参数说明：
- -h：指明数据库宿主机的IP
- -u：指明数据库的用户名
- -p：指明数据库的密码
- -d：指明数据库的名字
- -c：指明collection的名字
- -f：指明要导出那些列
- -o：指明到要导出的文件名
- -q：指明导出数据的过滤条件
- --authenticationDatabase admin
1.5.2 mongoimport帮助信息
[[email protected] ~]$ 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）
[[email protected] ~]$ 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）
[[email protected] ~]$ 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）
[[email protected] ~]$ 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）
[[email protected] ~]$ 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 创建外部数据
[[email protected] ~]$ 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 导入数据
[[email protected] ~]$ 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 修改配置文件以对目录有写入的权限
[[email protected] ~]# vim /etc/my.cnf [mysqld] secure-file-priv=/tmp
1.7.2.2 导出CSV数据
[[email protected] ~]# 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 查看导出的数据
[[email protected] ~]# cat /tmp/test.csv 1,"leon",18 2,"shadow",20 3,"shuaige",26 4,"meinv",19 5,"wangzi",28
1.7.2.4 导入到Mongodb
[[email protected] ~]$ 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 检查导入的数据
[[email protected] ~]$ 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文件
[[email protected] ~]# 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)
我的微信
如果有技术上的问题可以扫一扫我的微信
