一:实验环境
![](https://img-blog.csdn.net/20150326095008807?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQveWFiaW5nc2hpX3RlY2g=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)
--这里把主机名为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>