MySQL8数据迁移大表捷径【表空间迁移】
0. 目标端必须有同名表，没有则建一个空表；
创新互联公司是专业的兴宾网站建设公司，兴宾接单;提供成都网站设计、做网站、成都外贸网站建设公司,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行兴宾网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!
####################################
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数据库里面的表及数据 复制到oracle
1、可以用PB、PLSQL Developer等软件，把表的数据倒成SQL文件，在MySql中执行！
2、也可以尝试写数据迁移程序，大概步骤是这样：
1）、根据双方数据库的数据字典，罗列出需要的字段
2）、编写相应的迁移程序，主要注意的地方就是Oracle的数据类型要做相应的转换，转移成DB2里有的，删除倒出时一些无用的数据，使导入的数据具有意义！
3）、抽取数据做验证！
3、如果数据量不大的话，完成可以用把表的数据倒成SQL文件再执行！
mysql怎么把表转移到另外一个数据库
可以在另外一个数据库中先创建相同的数据库结构，然后再将数据库内容复制到另一个数据库中即可。
mysql5.1的数据库如何迁移至mysql5.7？
安全的办法是在旧环境导出表数、在新环境建立数据库后导入表，可以使用SQL或者TXT为中间文件。
本文题目：怎么把mysql的表迁移 oracle表迁移到mysql
本文网址：
http://cdkjz.cn/article/hgegjj.html
