mongodb转mysql_脚本 将阿里云 mongodb数据转成mysql

思路：mongodb是阿里云上，有定时备份，用python脚本去下载备份文件，恢复到我的环境 中，用脚本查出数据转换成sql插入mysql.

我的环境python是2.7.5。注：mysql端要预先建设 好对应的表，表名和mongodb上的表同名。

api:

https://help.aliyun.com/document_detail/26226.html?spm=a2c4g.11186623.6.1308.77201b113JWEGG

pip install aliyun-python-sdk-core

pip install aliyun-python-sdk-rds

pip install aliyun-python-sdk-dds

cat mongo_dload_backup.py

#!/usr/bin/env python

#coding=utf-8

import sys,os

from aliyunsdkcore.client import AcsClient

from aliyunsdkcore.acs_exception.exceptions import ClientException

from aliyunsdkcore.acs_exception.exceptions import ServerException

from aliyunsdkdds.request.v20151201.DescribeBackupsRequest import DescribeBackupsRequest

# AccessKeyID,AccessKeySecret 从阿里账号上查看 。

client = AcsClient('AccessKeyID', 'AccessKeySecret', 'cn-hangzhou')

request = DescribeBackupsRequest()

request.set_accept_format('json')

request.set_NodeId("d-bp1ecd91ebdbb234")

request.set_DBInstanceId("dds-xxxxxx")#mongodb实例ID

#request.set_EndTime("2019-03-28T17:13Z")

#request.set_StartTime("2019-03-28T17:09Z")

request.set_EndTime(sys.argv[2])

request.set_StartTime(sys.argv[1])

response = client.do_action_with_exception(request)

resp = eval(response)

#print(resp.get('Backups')['Backup'][0]['BackupDownloadURL'])

ur = resp.get('Backups')['Backup'][0]['BackupDownloadURL']

for i in ur.split('/'):

if 'tar.gz' in i:

for ii in i.split('?'):

if 'tar.gz' in ii:

tar_name=ii

#print(str(response, encoding='utf-8'))

os.system('wget -c \'%s\' -O %s' %(ur,tar_name))

cat mongo.sh

#!/bin/bash

python mongo_dload_backup.py '2019-03-29T17:09Z' '2019-03-29T17:13Z'

ls hins*

pidd=$(ps -ef|grep mongo|grep mongod|grep -v auto|awk -F' ' '{print $2}')

kill -2 "$pidd"

rm -fr /data/mongodb/data/*

file=$(ls /data/mongodb/data/)

echo $file

sleep 5

if [ ! -n "$file" ]; then

fname=$(ls hins*|grep `date +%Y%m%d`)

tar xvf "$fname" -C /data/mongodb/data/

fi

/usr/local/mongodb406/bin/mongod --bind_ip 0.0.0.0 -f /data/mongodb/conf/mongod.conf &

sleep 3

tab=`mongo 127.0.0.1:27017/user << EOF 2>/dev/null

show tables;

EOF`

#echo $tab

tabs=$(echo $tab|cut -d':' -f7|awk -F' ' '{$1="";print $0}'|sed 's/^ *//')

#echo $tabs

for t in $tabs;do

if [ "$t" == "bye" ];then

break

fi

echo ' '

mongo 127.0.0.1:27017/user << EOF 2>/dev/null|grep -vE 'version|Implicit session|connecting to|for more|bye'|sed -r 's#ObjectId##g'|sed -r 's#ISODate##g'|sed 's#(##g'|sed 's#)##g'|sed -r 's#null#"null"#g'|sed -r 's#""null#"null#g'|sed -r 's#null""#null"#g' >/tmp/mongo/$t

DBQuery.shellBatchSize = 100000;

db.$t.find();

EOF

sed -i '/100000/d' /tmp/mongo/$t

done

cat pm.py

#!/usr/bin/python

#! _*_coding:utf-8_*_

import json

import sys

for line in open("/tmp/mongo/%s" %(sys.argv[1])):

# b = json.loads(line)

b = eval(line)

tem = ''

tems = ''

for key in b:

st = str(key)+'|'+str(b[key])

st2 = st.split('|')

tem = tem +str(st2[0]) + ','

ttt = st2[1:]

for i in ttt:

if '-' in i and 'T' in i:

ii = i.replace('T',' ')

iii = ii.replace('Z','')

iiii = iii.replace('\'','')

tems = tems +'\''+str(iii)+ '\''','

else:

ii = i.replace('\'','')

tems = tems +'\''+str(ii)+ '\''','

print 'insert into '+sys.argv[1]+'('+tem+') values('+tems+');'

print ' '

#f.close()

cat sql.sh

#!/bin/bash

tab=`ls /tmp/mongo/`

mv /tmp/sql.sql /tmp/sql_`date +%m%d`.sql

for i in $tab;do

echo "truncate table "$i";" >> /tmp/sql.sql

python pm.py "$i"|sed -r 's#,\)#\)#g' >>/tmp/sql.sql

echo ' ' >>/tmp/sql.sql

done

#mysql -p123456 user < /tmp/sql.sql

mysql -uroot -pxxxx -P 4407 -h 47.98.xxx.xxx user < /tmp/sql.sql

yt=$(echo "`date +%m%d`-1"|bc)

mysql -uroot -pxxxx -P 4408 -h 47.98.xx.xx user < `ls /tmp/*.sql|grep $yt`

定时任务

0 2 * * * sh /data/mongodb/mon_to_mysql/mongo.sh

8 2 * * * sh /data/mongodb/mon_to_mysql/sql.sh