从MySQL迁移到MongoDB

前言

       生产上有部分MySQL业务需要迁移到MongoDB，为确保数据的一致性故采用离线迁移的方式。MySQL端导出为CSV格式，然后MongoDB端通过mongoimport方式导入。

MySQL端-导出

导出表数据为CSV格式，列与列之间以“,”分隔。

 

编辑mysql_export.sh脚本

[root]# vi mysql_export.sh
#!/bin/bash
MYSQL_SCHEMA="china"
for TABLE_NAME in `mysql -se "select table_name from information_schema.tables where table_schema='$MYSQL_SCHEMA';"`
do
  mysql $MYSQL_SCHEMA -e "select * from $TABLE_NAME into outfile '/tmp/$TABLE_NAME.csv' fields terminated by ',';"
done

 

执行脚本导出数据

[root]# sh mysql_export.sh

 

查看导出生成的CSV文件

[root]# ls -rtl /tmp/*.csv
-rw-rw-rw- 1 mysql mysql 8363 Jan 13 14:52 /tmp/city.csv
-rw-rw-rw- 1 mysql mysql  714 Jan 13 14:52 /tmp/provincial.csv

 

在MySQL端生成mongoimport.sh然后传输到MongoDB端：

编辑gene_mongoimport.sh脚本

[root]# vi gene_mongoimport.sh
#!/bin/bash

MYSQL_SCHEMA="china"
for TABLE_NAME in `mysql -se "select table_name from information_schema.tables where table_schema='$MYSQL_SCHEMA';"`
do
  echo "echo '----- $TABLE_NAME ------'" >> mongoimport.sh
  mysql -se "SELECT concat('mongoimport -u mysql -p Gaoyu@029 -d mysql -c ',table_name,' --fields ',GROUP_CONCAT(COLUMN_NAME SEPARATOR ','),' --file=/tmp/',table_name,'.csv --type=csv') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '$MYSQL_SCHEMA' and TABLE_NAME='$TABLE_NAME';" >> mongoimport.sh
done

 

执行脚本生成mongoimport.sh脚本

[root]# sh gene_mongoimport.sh
[root]# cat mongoimport.sh
echo '----- city -----'
mongoimport -u mysql -p Gaoyu@029 -d mysql -c city --fields cid,city,postcode,pid --file=/tmp/city.csv --type=csv


echo '----- provincial -----'
mongoimport -u mysql -p Gaoyu@029 -d mysql -c provincial --fields pid,provincial,postcode --file=/tmp/provincial.csv --type=csv

 

将mongoimport.sh脚本和CSV文件传输到MongoDB端。

MongoDB端-导入

创建mysql库，创建mysql用户

[root]# mongo -u admin -p Passwd admin
> use mysql
> db.createUser(
   {
     user: "mysql",
     pwd: "Gaoyu@029",
     roles: [ { role: "readWrite", db: "mysql" } ]
   }
   )

 

执行mongoimport.sh脚本导入数据

[root]# sh mongoimport.sh
----- city -----
2022-01-13T15:01:56.351+0800	connected to: mongodb://localhost/
2022-01-13T15:01:56.359+0800	391 document(s) imported successfully. 0 document(s) failed to import.
----- provincial -----
2022-01-13T15:01:56.379+0800	connected to: mongodb://localhost/
2022-01-13T15:01:56.387+0800	34 document(s) imported successfully. 0 document(s) failed to import.

数据比对

MySQL端查看数据条数：

(root@localhost) [mysql]> select table_name,table_rows from information_schema.tables where table_schema='china';
+------------+------------+
| table_name | table_rows |
+------------+------------+
| city       |        391 |
| provincial |         34 |
+------------+------------+

 

MongoDB查看数据条数：

[root]# mongo -u mysql -p Gaoyu@029 mysql
> db.city.count()
391
> db.provincial.count()
34

 

对比MySQL端与MongoDB端数据是否有差异：

TABLE_NAME	MySQL	MongoDB	差异条数
city	391	391	0
provincial	34	34	0

 

数据迁移完成。