将MySQL数据迁移到MongoDB

时间:2024-03-19 15:02:07

Mongodb中的mongoexport工具可以把一个collection导出成JSON格式或CSV格式的文件。MySQL支持导出CSV格式的数据以至于可以把MySQL的数据迁移至MongoDB中。
实践演示:

  1. mysql开启安全路径

vim /etc/my.cnf 添加以下配置
secure-file-priv=
重启数据库生效
/etc/init.d/mysqld restart

  1. 导出book库下所有表

SELECT CONCAT(“select * from “,table_schema,”.”,table_name,
" into outfile ‘/bak/",table_schema,".",table_name,".csv’ fields terminated by ‘,’
optionally enclosed by ‘""’ escaped by ‘""’
lines terminated by ‘\r\n’;")
FROM information_schema.tables WHERE table_schema =‘book’;
不加双引号
SELECT CONCAT(“select * from “,table_schema,”.”,table_name,
" into outfile ‘/bak/",table_schema,".",table_name,".csv’ fields terminated by ‘,’ ;")
FROM information_schema.tables WHERE table_schema =‘book’;

将MySQL数据迁移到MongoDB

将MySQL数据迁移到MongoDB
查看表的字段

select COLUMN_NAME from information_schema.COLUMNS where table_name=‘books’ and table_schema = ‘book’;
将MySQL数据迁移到MongoDB
编辑CSV文件并在第一行添加以上字段信息,用,隔开

  1. 导入CSV文件到MongoDB

mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d book -c yourtablename --headerline --type=csv [-f ID,Name,Population] //添加了字段信息不用加该选项 --file /bak/yourtablename.csv

MySQL导出CSV

select * from book
into outfile '/tmp/test.csv
fields terminated by ‘,’  
optionally enclosed by ‘"’ 
escaped by ‘"’      
lines terminated by ‘\r\n’;

参数说明:
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’;