一:实验环境
--这里把主机名为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)