mysql 表空间迁移_MySQL【表空间迁移大表】

mysql> mysql> show tables;

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

| Tables_in_ops2 |

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

| tt2 |

| tt3 |

| tt7 |

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

3 rows in set (0.00 sec)

mysql>

mysql>

mysql>

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>

mysql>

mysql>

mysql>

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql>

mysql>

mysql>

mysql>

mysql>

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>

mysql>

mysql>

mysql>

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)