MongoDB是一个文档数据库,在存储小文件方面存在天然优势。随着业务求的变化,需要将线上MySQL数据库中的行记录,导入到MongoDB中文档记录。
一、场景:线上MySQL数据库某表迁移到MongoDB,字段无变化。
二、Python模块:
使用Python的torndb,pymongo和time模块。
*注释:首先安装setup.py,pip,MySQLdb
执行如下命令即可:
pip install torndb
pip install pymongo
三、脚本内容如下:
[root ~]#cat nmytomongo.py
#!/usr/bin/env python
#fielName: mytomongo.py
#Author:xkops
#coding: utf-8
import torndb,pymongo,time
# connect to mysql database
mysql = torndb.Connection(host='127.0.0.1', database='database', user='username', password='password')
#connect to mongodb and obtain total lines in mysql
mongo = pymongo.MongoClient('mongodb://ip').database
mongo.authenticate('username',password='password')
countlines = mysql.query('SELECT max(table_field) FROM table_name')
count = countlines[0]['max(table_field)']
#count = 300
print count
i = 0
j = 100
start_time = time.time()
#select from mysql to insert mongodb by 100 lines.
for i in range(0,count,100):
#print a,b
#print i
#print 'SELECT * FROM quiz_submission where quiz_submission_id > %d and quiz_submission_id <= %d' %(i,j)
submission = mysql.query('SELECT * FROM table_name where table_field > %d and table_field <= %d' %(i,j))
#print submission
if submission:
#collection_name like mysql table_name
mongo.collection_name.insert_many(submission)
else:
i +=100
j +=100
continue
i +=100
j +=100
end_time = time.time()
deltatime = end_time - start_time
totalhour = int(deltatime / 3600)
totalminute = int((deltatime - totalhour * 3600) / 60)
totalsecond = int(deltatime - totalhour * 3600 - totalminute * 60)
#print migrate data total time consuming.
print "Data Migrate Finished,Total Time Consuming: %d Hour %d Minute %d Seconds" %(totalhour,totalminute,totalsecond)
*注释:按照自己的需求更改上述代码中的数据库地址,用户,密码,库名,表名以及字段名等。
四、执行迁移脚本:
[root ~]#python nmytomongo.py &> /tmp/migratelog.txt &
脚本执行完成后查看/tmp/migratelog.txt数据迁移消耗的时间。
更多MongoDB相关教程见以下内容:
CentOS 编译安装 MongoDB与mongoDB的php扩展
CentOS 6 使用 yum 安装MongoDB及服务器端配置
Ubuntu 13.04下安装MongoDB2.4.3
《MongoDB 权威指南》(MongoDB: The Definitive Guide)英文文字版[PDF]
基于CentOS 6.5操作系统搭建MongoDB服务 uxidc.com/Linux/2014-11/108900.htm