将MySQL数据迁移到MongoDB

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

1. mysql开启安全路径

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

2. 导出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';

查看表的字段

select COLUMN_NAME from information_schema.COLUMNS where table_name=‘books’ and table_schema = ‘book’;

编辑CSV文件并在第一行添加以上字段信息，用,隔开

3. 导入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’;