如何复制MySQL数据库或表到另外一台服务器？

使用这种方法前，我们需要先下载一个MySQL客户端工具SqlYog。点击这里下载并安装\x0d\x0a\x0d\x0a下面我们开始复制数据库：\x0d\x0a1、打开SqlYog community Edition，分别在不同的选项卡中打开源数据库服务器与目标数据库服务器，这一点很重。\x0d\x0a\x0d\x0a在源数据库服务器选项卡中你将看到所有数据库列表。\x0d\x0a2、在需要复制迁移的数据库上右击，在d出菜单中选择“Copy Database to Different Host/Database”\x0d\x0a3、在d出对话框中，我们能看到源数据库服务器及目标服务器，在左边，通过勾选复选框来选择需要复制迁移的对象，如表、函数、触发器等，也可以选择所有对象。\x0d\x0a4、在右边选择需要迁移的目标服务器或数据库\x0d\x0a5、根据你的需要选择复制类型：“Structure and Data”或“Structure only”，即“结构和数据”或“仅结构”。\x0d\x0a6、选择结束后点击“Copy”按钮开始复制，知道数据迁移结束。

0. 目标端必须有同名表，没有则建一个空表；

####################################

1、 源端文件准备

源端： 

   flush tables t for export 

复制 

t.ibd, t.cfg到目标端。 

###############################

flush tables tt7   for export 

cp  tt7*   ../ops

2、 目标端<存在同样的表则>丢弃原来的数据文件

目标端： 

 alter table tt7  discard tablespace

3、 目标端加载新的数据文件 t.ibd

alter table tt7 import tablespace 

4、源端释放锁

源端： 

unlock tables 

过程中主要异常处理：

#####################################################

SELECT  * FROM   ops2.tt7   

SELECT  * FROM   ops.tt7   

import tablespace报错：

mysql>alter table tt7 import tablespace 

ERROR 1812 (HY000): Tablespace is missing for table ops.tt7.

确认再相应的目录存在两个文件

确认属主和权限

#####################################################

过程

[root@qaserver120 ops]# ll

total 80

drwxr-xr-x 2 root  root      36 Dec  2 21:42 000

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

[root@qaserver120 ops]# 

[root@qaserver120 ops]# 

[root@qaserver120 ops]# 

[root@qaserver120 ops]# cp 000

[root@qaserver120 ops]# ll

drwxr-xr-x 2 root  root      36 Dec  2 21:42 000

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r----- 1 root  root     627 Dec  2 21:45 tt7.cfg

-rw-r----- 1 root  root  114688 Dec  2 21:45 tt7.ibd

[root@qaserver120 ops]# chown mysql.mysql tt7*

[root@qaserver120 ops]# 

[root@qaserver120 ops]# ll

drwxr-xr-x 2 root  root      36 Dec  2 21:42 000

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r----- 1 mysql mysql    627 Dec  2 21:45 tt7.cfg

-rw-r----- 1 mysql mysql 114688 Dec  2 21:46 tt7.ibd

[root@qaserver120 ops]# 

#####################################################

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

| tt7           |

+---------------+

2 rows in set (0.00 sec)

mysql>select * from tt7

ERROR 1814 (HY000): Tablespace has been discarded for table 'tt7'

mysql>alter table tt7 import tablespace 

ERROR 1812 (HY000): Tablespace is missing for table ops.tt7.

mysql> 

mysql>alter table tt7 import tablespace

Query OK, 0 rows affected (0.08 sec)

mysql> 

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql>

###############################################

################################################

mysql>mysql>show tables

+----------------+

| Tables_in_ops2 |

+----------------+

| tt2            |

| tt3            |

| tt7            |

+----------------+

3 rows in set (0.00 sec)

mysql> 

mysql>use ops

Database changed

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

+---------------+

1 row in set (0.00 sec)

mysql> 

mysql>use ops2

Database changed

mysql>select * from tt7

+--------+------+

| x      | y    |

+--------+------+

| BBBBBB | NULL |

+--------+------+

1 row in set (0.00 sec)

mysql> 

mysql> 

mysql>insert into tt7 select * from tt3

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql>insert into tt7 select * from tt3

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql> 

mysql>commit

Query OK, 0 rows affected (0.00 sec)

mysql> 

mysql>exit

Bye

[root@qaserver120 pkg]# cd /data/mysql/ops2

[root@qaserver120 ops2]# ll

total 240

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt3.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:36 tt7.ibd

[root@qaserver120 ops2]# 

[root@qaserver120 ops2]# 

[root@qaserver120 ops2]# 

[root@qaserver120 ops2]# mysql -u'root'  -p'fgxkB9Zq40^MFQUi$PJ'        -A

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with or \g.

Your MySQL connection id is 56

Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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 ops2

Database changed

mysql> 

mysql>flush tables tt7   for export 

Query OK, 0 rows affected (0.00 sec)

mysql>show tables

+----------------+

| Tables_in_ops2 |

+----------------+

| tt2            |

| tt3            |

| tt7            |

+----------------+

3 rows in set (0.01 sec)

mysql>exit

Bye

[root@qaserver120 ops2]# ll

total 240

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt3.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:36 tt7.ibd

[root@qaserver120 ops2]# ll

total 240

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt3.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:36 tt7.ibd

[root@qaserver120 ops2]# pwd

/data/mysql/ops2

[root@qaserver120 ops2]# cd  cd /data/mysql 

-bash: cd: cd: No such file or directory

[root@qaserver120 ops2]#  cd /data/mysql/ops2

[root@qaserver120 ops2]# ll

total 240

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:17 tt3.ibd

-rw-r----- 1 mysql mysql 114688 Dec  2 21:36 tt7.ibd

[root@qaserver120 ops2]# ll -al

total 244

drwxr-x---  2 mysql mysql     51 Dec  2 21:38 .

drwxr-xr-x 12 mysql mysql   4096 Dec  2 21:17 ..

-rw-r-----  1 mysql mysql 114688 Dec  2 21:17 tt2.ibd

-rw-r-----  1 mysql mysql 114688 Dec  2 21:17 tt3.ibd

-rw-r-----  1 mysql mysql 114688 Dec  2 21:36 tt7.ibd

[root@qaserver120 ops2]# pwd

/data/mysql/ops2

[root@qaserver120 ops2]# mysql -u'root'  -p'fgxkB9Zq40^MFQUi$PJ'        -A

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with or \g.

Your MySQL connection id is 57

Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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 ops2

Database changed

mysql>show tables

+----------------+

| Tables_in_ops2 |

+----------------+

| tt2            |

| tt3            |

| tt7            |

+----------------+

3 rows in set (0.00 sec)

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql>flush tables tt7   for export 

Query OK, 0 rows affected (0.00 sec)

mysql>use ops

Database changed

mysql>ll

    ->

ERROR 1064 (42000): You have an error in your SQL syntaxcheck the manual that corresponds to your MySQL server version for the right syntax to use near 'll' at line 1

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

+---------------+

1 row in set (0.00 sec)

mysql> 

mysql> 

mysql>alter table tt7 import tablespace 

ERROR 1100 (HY000): Table 'tt7' was not locked with LOCK TABLES

mysql> 

mysql> 

mysql>use ops2

Database changed

mysql>show tables

+----------------+

| Tables_in_ops2 |

+----------------+

| tt2            |

| tt3            |

| tt7            |

+----------------+

3 rows in set (0.00 sec)

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql>unlock tables

Query OK, 0 rows affected (0.00 sec)

mysql>show create table  tt7

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                         |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| tt7   | CREATE TABLE `tt7` (

  `x` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs DEFAULT NULL,

  `y` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>use ops

Database changed

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

+---------------+

1 row in set (0.01 sec)

mysql>  CREATE TABLE `tt7` (

    ->   `x` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs DEFAULT NULL,

    ->   `y` int(11) DEFAULT NULL

    ->) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs

Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> 

mysql> 

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

| tt7           |

+---------------+

2 rows in set (0.00 sec)

mysql>select * from tt7

Empty set (0.00 sec)

mysql> 

mysql>alter table tt7  discard tablesapce 

ERROR 1064 (42000): You have an error in your SQL syntaxcheck the manual that corresponds to your MySQL server version for the right syntax to use near 'tablesapce' at line 1

mysql>alter table tt7  discard tablespace

Query OK, 0 rows affected (0.03 sec)

mysql> 

mysql> 

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

| tt7           |

+---------------+

2 rows in set (0.00 sec)

mysql>select * from tt7

ERROR 1814 (HY000): Tablespace has been discarded for table 'tt7'

mysql> 

mysql> 

mysql> 

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

| tt7           |

+---------------+

2 rows in set (0.00 sec)

mysql>select * from tt7

ERROR 1814 (HY000): Tablespace has been discarded for table 'tt7'

mysql> 

mysql> 

mysql>alter table tt7 import tablespace 

ERROR 1812 (HY000): Tablespace is missing for table `ops`.`tt7`.

mysql> 

mysql> 

mysql> 

mysql>alter table tt7 import tablespace

Query OK, 0 rows affected (0.08 sec)

mysql> 

mysql> 

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql>SELECT  * FROM   ops2.tt7   

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql>SELECT  * FROM   ops.tt7   

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql> 

mysql> 

mysql> 

mysql>unlock tables 

Query OK, 0 rows affected (0.00 sec)

mysql>unlock tables 

Query OK, 0 rows affected (0.00 sec)

mysql>use ops

Database changed

mysql>show tables

+---------------+

| Tables_in_ops |

+---------------+

| tt2           |

| tt7           |

+---------------+

2 rows in set (0.00 sec)

mysql> 

mysql> 

mysql> 

mysql>use ops2

Database changed

mysql> 

mysql> 

mysql>show tables

+----------------+

| Tables_in_ops2 |

+----------------+

| tt2            |

| tt3            |

| tt7            |

+----------------+

3 rows in set (0.01 sec)

mysql> 

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

mysql> 

mysql>use ops

Database changed

mysql> 

mysql> 

mysql>select * from tt7

+--------------+------+

| x            | y    |

+--------------+------+

| BBBBBB       | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

| AAAAAA       | NULL |

| BBBBBB       | NULL |

| 555555555555 | NULL |

+--------------+------+

7 rows in set (0.00 sec)

把双方的mysql服务都停掉，直接把导出方的数据表文件(就是和数据库表名对应的.frm.myd.myi文件）拷到导入方（注意一个表有三个文件)，然后改一下数据表文件名成你要导入的表名，然后启动mysql服务

，如果导入方原来的表有数据，可以把原来的数据用正常方式导出，然后在工具里面导入合并就可以了，

再有一个可行的方法就是自写代码一行一行的转移数据了

---