VitePress

Appearance

Return to top
On this page

MongoDB 异构数据平台迁移

Mysql TO MongoDB

mysql —–>mongodb world数据库下city表进行导出，导入到mongodb

（1）mysql开启安全路径

vim /etc/my.cnf   --->添加以下配置
secure-file-priv=/tmp

--重启数据库生效
/etc/init.d/mysqld restart

vim /etc/my.cnf   --->添加以下配置
secure-file-priv=/tmp

--重启数据库生效
/etc/init.d/mysqld restart

（2）导出mysql的city表数据

mysql>select * from world.city into outfile '/tmp/city.csv' fields terminated by ',';

mysql>select * from world.city into outfile '/tmp/city.csv' fields terminated by ',';

（3）处理备份文件

desc world.city
 ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population

vim /tmp/city.csv   ----> 添加第一行列名信息

ID,Name,CountryCode,District,Population

desc world.city
 ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population

vim /tmp/city.csv   ----> 添加第一行列名信息

ID,Name,CountryCode,District,Population

(4)在mongodb中导入备份

mongoimport --port 28018 -d world  -c city --type=csv --headerline  --file  /tmp/city.csv

use world
db.city.find({CountryCode:"CHN"});

mongoimport --port 28018 -d world  -c city --type=csv --headerline  --file  /tmp/city.csv

use world
db.city.find({CountryCode:"CHN"});

思考：

world共100张表，全部迁移到mongodb

select * from world.city into outfile '/tmp/world_city.csv' fields terminated by ',';

select concat("select * from ",table_schema,".",table_name ," into outfile '/tmp/",table_schema,"_",table_name,".csv' fields terminated by ',';")
from information_schema.tables where table_schema ='world';

导入：
    提示，使用infomation_schema.columns + information_schema.tables

world共100张表，全部迁移到mongodb

select * from world.city into outfile '/tmp/world_city.csv' fields terminated by ',';

select concat("select * from ",table_schema,".",table_name ," into outfile '/tmp/",table_schema,"_",table_name,".csv' fields terminated by ',';")
from information_schema.tables where table_schema ='world';

导入：
    提示，使用infomation_schema.columns + information_schema.tables

mysql导出csv：

select * from test_info   
into outfile '/tmp/test.csv'   
fields terminated by ','　　　 ------字段间以,号分隔
optionally enclosed by '"'　　------字段用"号括起
escaped by '"'   　　　　　　 ------字段中使用的转义符为"
lines terminated by '\r\n';　　------行以\r\n结束

select * from test_info   
into outfile '/tmp/test.csv'   
fields terminated by ','　　　 ------字段间以,号分隔
optionally enclosed by '"'　　------字段用"号括起
escaped by '"'   　　　　　　 ------字段中使用的转义符为"
lines terminated by '\r\n';　　------行以\r\n结束

mysql导入csv：

load data infile '/tmp/test.csv'
into table test_info
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n';

load data infile '/tmp/test.csv'
into table test_info
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n';