mysql-mysql跨机器数据迁移（命令方式）

一：实验环境

--这里把主机名为target_pc的机器当做源端，把主机名为source_pc的机器当做目标端。 

不要被主机名混淆了。

二：实验步骤

这里介绍迁移所有数据库和迁移单个数据库时的数据迁移步骤。

2.1：迁移所有数据库

2.1.1 迁移前环境

迁移前，源端有以下数据库：

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

迁移前，目标端的有以下数据库：

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

 

源端比目标端多一个dan数据库。

--目标端是刚安装好的mysql，默认就有这4个数据库。

2.1.2 在源端备份所有数据库 

[root@target_pc databasefile]# mysqldump -u root -p --all-databases > /backup/databasefile/all_databases_20150325.bak

2.2.2 拷贝备份文件到目标端

[root@target_pc databasefile]# scp all_databases_20150325.bak 192.168.8.225:/backup/databasefile/
The authenticity of host '192.168.8.225 (192.168.8.225)' can't be established.
RSA key fingerprint is ed:ee:f6:e6:f5:3b:76:ed:18:fa:2d:eb:73:83:0e:13.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.8.225' (RSA) to the list of known hosts.
 
 
 
 
root@192.168.8.225's password: 
all_databases_20150325.bak                                                                                                                                      100%  598KB 598.3KB/s   00:00    
[root@target_pc databasefile]# 

2.2.3 在目标端还原所有数据库 

[root@source_pc databasefile]# mysql -u root -p < all_databases_20150325.bak 

Enter password: 

2.2.4 验证

[root@source_pc databasefile]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.20 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2014, 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 |
| dan                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
 
 
mysql> use dan;
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> show tables;
+---------------+
| Tables_in_dan |
+---------------+
| t             |
| t2            |
+---------------+
2 rows in set (0.00 sec)
 
mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

--注意：当迁移所有数据库时，不用提前在目标端创建好所有数据库。

 

2.2：迁移某个数据库

2.2.1 准备测试数据

在源端新建一个数据库：

mysql> create database jiao;
Query OK, 1 row affected (0.01 sec)
 
mysql> use jiao;
Database changed
mysql> create table t(id int);
Query OK, 0 rows affected (0.05 sec)
 
mysql> insert into t(id) values(1);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t(id) values(2);
Query OK, 1 row affected (0.00 sec)
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

 

2.2.2 在源端备份新增的这个数据库

[root@target_pc databasefile]# mysqldump -u root -p jiao > /backup/databasefile/jiao_20150325.bak

Enter password: 

2.2.3 拷贝备份文件到目标端

[root@target_pc databasefile]# scp jiao_20150325.bak 192.168.8.225:/backup/databasefile/
root@192.168.8.225's password: 
jiao_20150325.bak                                                                                                                                             100% 1757     1.7KB/s   00:00    
[root@target_pc databasefile]# 
 

2.2.4 在目标端创建好该数据库

mysql> create database jiao;

Query OK, 1 row affected (0.00 sec)

 

2.2.5 在目标端还原该数据库

[root@source_pc databasefile]# mysql -u root -p < jiao_20150325.bak

Enter password: 

 

2.2.6 验证

 

[root@source_pc databasefile]# mysql -u root -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.20 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2014, 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 |
| dan                |
| jiao               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)
 
mysql> use jiao;
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 t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
 
mysql> 

--注意：当迁移某个数据库时，必须在目标端先创建好数据库才行。

 

----想用图形用户界面方式，进行mysql-mysql数据迁移，请参考文章：mysql-mysql跨机器数据迁移（图形用户界面方式-navicat）