####################################
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)
近几天在抓Wiki的数据，爬虫没控制好导致数据量过大，原数据储存位置在C盘差点爆了，在网上查询了更换存储路径的方式如下：
1. 在任务管理器-服务里找到MySQL的启动项-右键点属性，查看配置文件的路径，我的原有路径为：C:/ProgramData/MySQL/MySQL Server 8.0/my.ini
2. 在服务中停止MySQL服务，查看my.ini文件中datadir项，进入该目录下将data文件夹copy至新设定的文件目录下，我挪到了D:/MySQLData。因为涉及数据最好是复制，等复制完毕再删除原数据。 同时复制一份my.ini文件至该目录下，并将my.ini文件中datadir更新至你新设定的路径D:/MySQLData/data
3. win + R打开运行窗口，regedit打开注册表，在HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\路径下找到MySQL启动项,名字跟服务中一致,双击ImagePath,打开d窗，把数值数据中my.ini的路径改为D:\MySQLData\my.ini，确定。
4. 到服务中启动MySQL，能正常启动并且能正常连接则迁移完毕。
1、首先在桌面上双击打开“此电脑”软件。
2、然后在打开的此电脑页面中，找到并双击打开C盘。
3、选中需要移动的文件，再右击选中的文件，从d出的快捷菜单中选择“剪切”命令。
4、在窗口中双击打开D盘或E盘。
5、最后鼠标右击窗口空白处，从d出的快捷菜单中选择“粘贴”命令即可。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）