脚本将阿里云 mongodb数据转成mysql-CSDN博客

思路：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