db1为原数据库,db2为要导出到的数据库,fromtable 是要导出的表名
成都创新互联成立于2013年,是专业互联网技术服务公司,拥有项目成都网站制作、成都做网站网站策划,项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命,1280元朝阳县做网站,已为上家服务,为朝阳县各地企业和个人服务,联系电话:18980820575
1.方法一:
登录导出到的数据库,执行
create table fromtable select * from db1.fromtable;
2.方法二:
在cmd下执行,mysqldump -u root -p db1 fromtable file=d:/fromtable.sql; 输入秘密,root为用户名
登录db2 执行 source d:/fromtable.sql;
3.方法三:
登录db1 执行 select * from fromtable into outfile "d:/fromtable .txt"; 导出纯数据格式
登录db2 执行 load data infile d:/fromtable .txt into table fromtable; 需要先建一张和原表结构一样的空表。
4.建一个odbc连接,先导出到access中,再导出到另一个库中。
1、使用软件Navicat就可迁移复制数据库,打开Navicat,右键点击左边空白的地方,点击New Connection下的MySQL,创建一个服务器的连接,下面将演示把本地的数据迁移到服务器:
2、在弹出的创建新连接的窗口里,输入服务器的IP,数据库账号,密码等,然后就可以连接数据库了:
3、创建好后们打开本地的数据库,点击“Data Transfer”(数据传输),接着弹出新的界面:
4、新窗口中在左边选择本地数据库的库,和需要转移的表,可以选择一个,或多个表:
5、然后在右边的目标里,选择服务器的连接,然后选择服务器上的数据库:
6、选择完成后,就开始进行数据转移了,数据量不是很大的,很快就会转移完成的。以上就是mysql中数据复制到另一个数据库的方法:
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'fgxkB9;Zq40^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'fgxkB9;Zq40^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 syntax; check 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 syntax; check 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服务 ,如果导入方原来的表有数据,可以把原来的数据用正常方式导出,然后在工具里面导入合并就可以了,
再有一个可行的方法就是自写代码一行一行的转移数据了
什么系统?两个库是不是在同一台机?
linux下个人做法:
1.同一台机
用mysqldump导出表数据(具体使用可以查一下)
mysqldump
-h
host
-p
port
-p
password
-u
user
database
--default-character-set=utf8
--add-drop-table
tablename
-r
/tmp/table.sql
再导入数据
mysqldump
-h
host
-p
port
-p
password
-u
user
database
tablename
/tmp/tablename.sql
或者在进入mysql后用source命令导入。
2.不同的机,就需要先把数据文件导出,然后复制到另外一台机,再进行1的导入操作。
windows下没试过,一般都直接用phpmyadmin来操作了,界面操作没什么说的。