跳转至

7. 数据迁移

0. 环境

ip:     192.168.178.151
系统:   centos7.6

1. 分析

1.异构平台迁移,使用mongoexport、mongoimport命令
2.MySQL可以导出为csv格式的文件
3.mongodb可以导入csv文件

#案例:将MySQL中的world库下的city表导入到mongodb中

2. 准备MySQL数据库(略)

#1.准备二进制包
[root@151 ~]# mkdir /application
[root@151 ~]# cd /application/
[root@151 application]# ls
mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

#2.解压缩
[root@151 application]# tar -zxf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@151 application]# ls
mysql-5.7.20-linux-glibc2.12-x86_64  mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

#3.改名
[root@151 application]# mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql
[root@151 application]# ls
mysql  mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

#4.创建mysql用户
[root@151 application]# id mysql
id: mysql: no such user
[root@151 application]# useradd mysql -s /sbin/nologin -M
[root@151 application]# id mysql
uid=1000(mysql) gid=1000(mysql) groups=1000(mysql)

#5.创建数据目录
[root@151 application]# mkdir /application/mysql/data
[root@151 application]# ls /application/mysql
bin  COPYING  data  docs  include  lib  man  README  share  support-files


#6.更改目录权限
[root@151 application]# chown -R mysql:mysql /application
[root@151 application]#
[root@151 application]# ll
total 626104
drwxr-xr-x 9 mysql mysql       129 May 13 14:05 mysql
-rw-r--r-- 1 mysql mysql 641127384 May 13 14:04 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

#7.添加环境变量
[root@151 application]# echo "export PATH=/application/mysql/bin:$PATH" >>/etc/profile
[root@151 application]# source /etc/profile
[root@151 application]# which mysql
/application/mysql/bin/mysql

#8.初始化数据
[root@151 application]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
#报错了。。。
#安装依赖环境
[root@151 application]# yum install -y libaio
#再次初始化,成功!!!
[root@151 application]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
2021-05-13T06:16:50.793005Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-13T06:16:51.561551Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-13T06:16:51.759672Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-13T06:16:51.832643Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ceb31800-b3b2-11eb-8aab-000c299fbc6f.
2021-05-13T06:16:51.834690Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-13T06:16:51.843074Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

#9.配置文件创建
[root@151 application]# vim /etc/my.cnf
[root@151 application]# cat /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log_error=/var/log/mysql.log

[client]
socket=/tmp/mysql.sock

#10.启动
[root@151 application]# mysqld_safe &
[1] 47434
[root@151 application]# 2021-05-13T06:22:41.974969Z mysqld_safe Logging to '/var/log/mysql.log'.
2021-05-13T06:22:42.018585Z mysqld_safe Starting mysqld daemon with databases from /application/mysql/data

[root@151 application]#
[root@151 application]# netstat -tunlp|grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      47547/mysqld

#11.登录测试
[root@151 application]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> quit
Bye

#12.重启mysql
[root@151 application]# mysqladmin shutdown
[root@151 application]# 2021-05-13T06:29:44.513054Z mysqld_safe mysqld from pid file /application/mysql/data/151.pid ended

[1]+  Done                    mysqld_safe
[root@151 application]# netstat -tunlp|grep 3306
[root@151 application]# mysqld_safe &
[1] 50288
[root@151 application]# 2021-05-13T06:30:14.857976Z mysqld_safe Logging to '/var/log/mysql.log'.
2021-05-13T06:30:14.900169Z mysqld_safe Starting mysqld daemon with databases from /application/mysql/data

[root@151 application]#
[root@151 application]# netstat -tunlp|grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      50401/mysqld
[root@151 application]#
[root@151 application]#

3. MySQL开启安全路径

vim /etc/my.cnf
#添加如下一行
secure-file-priv=/tmp
[root@151 application]# vim /etc/my.cnf
[root@151 application]# cat /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log_error=/var/log/mysql.log
secure-file-priv=/tmp

[client]
socket=/tmp/mysql.sock

4. 重启MySQL

mysqladmin shutdown  #or   pkill mysqld
mysqld_safe &
netstat -tunlp |grep 3306
[root@151 application]# netstat -tunlp|grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      50401/mysqld
[root@151 application]#
[root@151 application]# pkill mysqld
[root@151 application]#
[root@151 application]# 2021-05-13T06:32:19.483803Z mysqld_safe mysqld from pid file /application/mysql/data/151.pid ended

[1]+  Done                    mysqld_safe
[root@151 application]#
[root@151 application]# netstat -tunlp|grep 3306
[root@151 application]#
[root@151 application]# mysqld_safe &
[1] 51245
[root@151 application]# 2021-05-13T06:32:30.440911Z mysqld_safe Logging to '/var/log/mysql.log'.
2021-05-13T06:32:30.481530Z mysqld_safe Starting mysqld daemon with databases from /application/mysql/data

[root@151 application]#
[root@151 application]# netstat -tunlp|grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      51370/mysqld
[root@151 application]#

5. 登录mysql,准备数据

mysql
source /root/world.sql
[root@151 application]# cd
[root@151 ~]#
[root@151 ~]# ls
anaconda-ks.cfg  world.sql
[root@151 ~]#
[root@151 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source /root/world.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
5 rows in set (0.00 sec)

mysql> exit
Bye

6. 查看mongodb导入导出的csv格式

#查看上一节导出的csv文件
cat /mongodb/log.csv

#发现
    1.有标题行
    2.每行的数据以“,”分隔
[root@151 ~]# head -5 /mongodb/log.csv
uid,name,age,date
0,mongodb,6,2021-05-12T04:10:44.840Z
1,mongodb,6,2021-05-12T04:10:44.845Z
2,mongodb,6,2021-05-12T04:10:44.845Z
3,mongodb,6,2021-05-12T04:10:44.846Z

7. MySQL导出city表到csv文件

mysql
    use world;
    select * from world.city into outfile '/tmp/city1.csv' fields terminated by ',';  #默认导出的csv是以空格分隔,指定以“,”分隔,与mongodb的csv格式一致!
[root@151 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use world;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from world.city into outfile '/tmp/city.csv';
Query OK, 4079 rows affected (0.00 sec)

mysql> select * from world.city into outfile '/tmp/city1.csv' fields terminated by ',';
Query OK, 4079 rows affected (0.01 sec)

mysql> exit
Bye
#1.不加最后几行参数的csv文件查看
[root@151 ~]# head -5 /tmp/city.csv
1       Kabul   AFG     Kabol   1780000
2       Qandahar        AFG     Qandahar        237500
3       Herat   AFG     Herat   186800
4       Mazar-e-Sharif  AFG     Balkh   127800
5       Amsterdam       NLD     Noord-Holland   731200

#2.不加最后几行参数的csv文件查看
[root@151 ~]# head -5 /tmp/city1.csv
1,Kabul,AFG,Kabol,1780000
2,Qandahar,AFG,Qandahar,237500
3,Herat,AFG,Herat,186800
4,Mazar-e-Sharif,AFG,Balkh,127800
5,Amsterdam,NLD,Noord-Holland,731200

8. 查看导出的csv文件

head /tmp/city1.csv
#发现没有第一行的列信息!
[root@151 ~]# head -5 /tmp/city1.csv
1,Kabul,AFG,Kabol,1780000
2,Qandahar,AFG,Qandahar,237500
3,Herat,AFG,Herat,186800
4,Mazar-e-Sharif,AFG,Balkh,127800
5,Amsterdam,NLD,Noord-Holland,731200

9. 查看city表的结构

mysql
desc world.city;

#得到列名
[root@151 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> desc world.city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> exit
Bye

10. 给csv文件添加第一行的列明信息

vim /tmp/city1.csv

ID,Name,CountryCode,District,Population
[root@151 ~]# vim /tmp/city1.csv
[root@151 ~]# head -5 /tmp/city1.csv
ID,Name,CountryCode,District,Population
1,Kabul,AFG,Kabol,1780000
2,Qandahar,AFG,Qandahar,237500
3,Herat,AFG,Herat,186800
4,Mazar-e-Sharif,AFG,Balkh,127800

11. 在mongodb中导入csv文件

su - mongod

#1.如果csv文件中没有列信息,则使用此命令
mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d world -c city --type=csv -f ID,Name,CountryCode,District,Population --file /tmp/city1.csv

#疑问:csv文件中已经有了列信息,也可以使用-f参数再次指定列吗?验证一下啊。  已验证!不能!

#1.如果csv文件中有列信息,则使用此命令!
mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d world -c city --type=csv --headerline --file /tmp/city1.csv
[root@151 ~]# su - mongod
Last login: Thu May 13 08:22:20 CST 2021 on pts/0
[mongod@151 ~]$
[mongod@151 ~]$ mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d world -c city --type=csv --headerline --file /tmp/city1.csv
2021-05-13T14:44:05.925+0800    connected to: localhost:27017
2021-05-13T14:44:06.141+0800    imported 4079 documents
[mongod@151 ~]$

12. 登录mongodb查看

mongo -uroot -proot123 admin
show dbs
use world
show tables
db.city.count()
db.city.findOne()
db.city.find({CountryCode:"CHN"})
[mongod@151 ~]$ mongo -uroot -proot123 admin
MongoDB shell version: 3.2.22
connecting to: admin
>
> show dbs
admin   0.000GB
app     0.000GB
local   0.000GB
oldboy  0.002GB
test    0.000GB
world   0.000GB
>
> use world
switched to db world
>
> show tables
city
>
> db.city.count()
4079
>
> db.city.findOne()
{
        "_id" : ObjectId("609ccab59a08c7926116c48e"),
        "ID" : 1,
        "Name" : "Kabul",
        "CountryCode" : "AFG",
        "District" : "Kabol",
        "Population" : 1780000
}
>
>
> db.city.find({CountryCode:"CHN"})
{ "_id" : ObjectId("609ccab69a08c7926116cbef"), "ID" : 1890, "Name" : "Shanghai", "CountryCode" : "CHN", "District" : "Shanghai", "Population" : 9696300 }
{ "_id" : ObjectId("609ccab69a08c7926116cbf0"), "ID" : 1891, "Name" : "Peking", "CountryCode" : "CHN", "District" : "Peking", "Population" : 7472000 }
{ "_id" : ObjectId("609ccab69a08c7926116cbf1"), "ID" : 1892, "Name" : "Chongqing", "CountryCode" : "CHN", "District" : "Chongqing", "Population" : 6351600 }
{ "_id" : ObjectId("609ccab69a08c7926116cbf2"), "ID" : 1893, "Name" : "Tianjin", "CountryCode" : "CHN", "District" : "Tianjin", "Population" : 5286800 }
{ "_id" : ObjectId("609ccab69a08c7926116cbf3"), "ID" : 1894, "Name" : "Wuhan", "CountryCode" : "CHN", "District" : "Hubei", "Population" : 4344600 }
{ "_id" : ObjectId("609ccab69a08c7926116cbf4"), "ID" : 1895, "Name" : "Harbin", "CountryCode" : "CHN", "District" : "Heilongjiang", "Population" : 4289800 }
{ "_id" : ObjectId("609ccab69a08c7926116cbf5"), "ID" : 1896, "Name" : "Shenyang", "CountryCode" : "CHN", "District" : "Liaoning", "Population" : 4265200 }
{ "_id" : ObjectId("609ccab69a08c7926116cbf6"), "ID" : 1897, "Name" : "Kanton [Guangzhou]", "CountryCode" : "CHN", "District" : "Guangdong", "Population" : 4256300 }
{ "_id" : ObjectId("609ccab69a08c7926116cbf7"), "ID" : 1898, "Name" : "Chengdu", "CountryCode" : "CHN", "District" : "Sichuan", "Population" : 3361500 }
{ "_id" : ObjectId("609ccab69a08c7926116cbf8"), "ID" : 1899, "Name" : "Nanking [Nanjing]", "CountryCode" : "CHN", "District" : "Jiangsu", "Population" : 2870300 }
{ "_id" : ObjectId("609ccab69a08c7926116cbf9"), "ID" : 1900, "Name" : "Changchun", "CountryCode" : "CHN", "District" : "Jilin", "Population" : 2812000 }
{ "_id" : ObjectId("609ccab69a08c7926116cbfa"), "ID" : 1901, "Name" : "Xi�an", "CountryCode" : "CHN", "District" : "Shaanxi", "Population" : 2761400 }
{ "_id" : ObjectId("609ccab69a08c7926116cbfb"), "ID" : 1902, "Name" : "Dalian", "CountryCode" : "CHN", "District" : "Liaoning", "Population" : 2697000 }
{ "_id" : ObjectId("609ccab69a08c7926116cbfc"), "ID" : 1903, "Name" : "Qingdao", "CountryCode" : "CHN", "District" : "Shandong", "Population" : 2596000 }
{ "_id" : ObjectId("609ccab69a08c7926116cbfd"), "ID" : 1904, "Name" : "Jinan", "CountryCode" : "CHN", "District" : "Shandong", "Population" : 2278100 }
{ "_id" : ObjectId("609ccab69a08c7926116cbfe"), "ID" : 1905, "Name" : "Hangzhou", "CountryCode" : "CHN", "District" : "Zhejiang", "Population" : 2190500 }
{ "_id" : ObjectId("609ccab69a08c7926116cbff"), "ID" : 1906, "Name" : "Zhengzhou", "CountryCode" : "CHN", "District" : "Henan", "Population" : 2107200 }
{ "_id" : ObjectId("609ccab69a08c7926116cc00"), "ID" : 1907, "Name" : "Shijiazhuang", "CountryCode" : "CHN", "District" : "Hebei", "Population" : 2041500 }
{ "_id" : ObjectId("609ccab69a08c7926116cc01"), "ID" : 1908, "Name" : "Taiyuan", "CountryCode" : "CHN", "District" : "Shanxi", "Population" : 1968400 }
{ "_id" : ObjectId("609ccab69a08c7926116cc02"), "ID" : 1909, "Name" : "Kunming", "CountryCode" : "CHN", "District" : "Yunnan", "Population" : 1829500 }
Type "it" for more
>
> exit
bye

至此,数据迁移成功!!!


最后更新: 2022-02-20 11:21:58