工作中有时会遇到数据库迁移的问题,如果是使用相同数据库的话,迁移方法比较简单,例如mysql数据库的迁移:
1、备份数据,使用mysqldump命令
mysqldump -u username -p password database > ./backup.sql
2、还原数据
mysql -u username -p password database < ./backup.sql
database事先创建好的数据库
mongo数据库内容迁移到mysql数据库,由于是不同类型的数据库,方法有所不同,基本步骤:
1、使用mongoexport命令导出mongo指定db的collection内容到csv格式文件,csv文件每行存储一条记录,并且使用用“,”分割字段。
mongoexport -h host -u username -p password -d database -c collect_name -q query -f name1,name2 --type=csv -o ./backup.csv
2、处理backup.csv文件,写入mysql指定table,
第一种方式 ,使用python语言写个脚本运行
#coding=utf-8
import MySQLdb
import csv
import time
cur.close()
conn.commit()
conn.close()
if __name__ == "__main__":
conn = MySQLdb.connect(host=localhost, port=3306, user=root, passwd='', db='Test', charset='utf8')
cur = conn.cursor()
csvfile = file('backup.csv', 'rb')
reader = csv.reader(csvfile)
sql = "INSERT INTO table1 (name1, name2) VALUES ('%s','%s)"
args = []
for line in reader:
print line
args.append(tuple(line))
try:
cur.executemany(sql, args)
except Exception as e:
print("exec error: %s" % e)
cur.close()
conn.commit()
conn.close()
csvfile.close()
注意:csv第一行是字段名称,需要去掉,不然运行出错
第二种方式,直接使用mysql命令load data infile方式导入文件数据
基本语法:help load data查看用法
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name,...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
根据提示:
$mysql -uroot
mysql> use test;
mysql> load data local infile './backup.csv'
-> into table `table1` character set utf8
-> fields terminated by ',' optionally enclosed by '"'
-> lines terminated by '\n'
-> ignore 1 lines;