mysql共享表空间的扩容、收缩和迁移

55次阅读

共计 2870 个字符，预计需要花费 8 分钟才能阅读完成。

本篇内容介绍了“mysql 共享表空间的扩容、收缩和迁移”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

一. 扩容 innodb 文件
1. 关闭 mysql db
# /usr/local/mysql/bin/mysqladmin -S /tmp/mysql3307.sock shutdown

2. 打开参数文件
innodb_data_file_path = ibdata1:512M:autoextend
将 ibdata1 调整至接近实际大小，并在后面追加新的文件:
innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend

3. 启动数据库
# /usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my3307.cnf –user=mysql

二. 缩小表空间
共享表空间文件, 一旦扩展无法自动缩小, 需要通过手工缩小
1. 导出全部数据库
# /usr/local/mysql/bin/mysqldump -uroot -p -A -S /tmp/mysql3307.sock /tmp/3307all.sql
Enter password:

2. 关闭 mysql db
# /usr/local/mysql/bin/mysqladmin -S /tmp/mysql3307.sock shutdown

3. 删除 mysql 的数据目录
rm -rf /home/mysql3307/mysql3307/*

4. 初始化 mysql
/usr/local/mysql/bin/mysqld –defaults-file=/etc/my3307.cnf –initialize-insecure –basedir=/usr/local/mysql –datadir=/home/mysql3307/mysql3307 –user=mysql

5. 启动 mysql
/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my3307.cnf –user=mysql

6. 导入数据
source /tmp/3307all.sql

三.innodb 表空间迁移
原库表结构:

点击 (此处) 折叠或打开

CREATE TABLE `wwj`.`t1` (

`id` INT NOT NULL,

`name` VARCHAR(45) NULL,

PRIMARY KEY (`id`));

alter table wwj.t1 add index idx_name (name) ;

insert into wwj.t1 values(1, wwj

1. 在目标实例上创建一个相同的表

点击 (此处) 折叠或打开

CREATE TABLE `wwj2`.`t1` (

`id` INT NOT NULL,

`name` VARCHAR(45) NULL,

PRIMARY KEY (`id`));

2. 在目标库上执行 ALTER TABLE t DISCARD TABLESPACE;
ALTER TABLE t1 DISCARD TABLESPACE;
– discard 的意思就是从数据库 detached, 会删除 ibd 文件，保留 frm 文件。
– 也就意味着，你可以对 frm 文件操作，比如：rename table,drop table，但是不能对 ibd 文件操作，比如：dml

3. 在源库上执行 FLUSH TABLES t FOR EXPORT; 生成.cfg 文件
flush tables t1 for export;

-rw-r—–. 1 mysql mysql 67 Mar 24 06:59 db.opt
-rw-r—–. 1 mysql mysql 467 Mar 24 18:32 t1.cfg
-rw-r—–. 1 mysql mysql 8586 Mar 24 06:59 t1.frm
-rw-r—–. 1 mysql mysql 114688 Mar 24 06:59 t1.ibd

此时，.cfg 文件在 InnoDB 的 data directory 中
flush tables .. for export 会加锁，这时候，千万不能退出终端或 session，否则加锁无效且.cfg 文件自动删除。

4. 讲.ibd 文件和.cfg 文件拷贝到目标实例
[root@mysql5 wwj]# cp t1.cfg /home/mysql3306/mysql3306/wwj2
[root@mysql5 wwj]# cp t1.ibd /home/mysql3306/mysql3306/wwj2

修改权限

5. 在源库执行 unlock tables;
mysql UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

6. 在目标库执行 ALTER TABLE t IMPORT TABLESPACE;

第一次执行: 在目标库追加 index 后成功
mysql alter table t1 import tablespace;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 3
Current database: wwj2

ERROR 1808 (HY000): Schema mismatch (Number of indexes don t match, table has 1 indexes but the tablespace meta-data file has 2 indexes)

innodb 可传输表空间注意事项
—————————–
必须开启 innodb_file_per_table
当这个表处于 quiesced 状态, 甚至不能被 select
两边实例的 page size 一致
5.7 版本之前，不支持分区表 transport
外键相关的表，必须设置 foreign_key_checks=0 才能成功
ALTER TABLE … IMPORT TABLESPACE 不需要.cfg metadata file . 但是，这样的话，MySQL 就不会对 schema 进行 verificate
5.6 以及更高版本，import export 版本必须在同一个 series
在 replication 环境中，master slave 都必须开启 innodb_file_per_table
对于 InnoDB general tablespace，不支持 discard import tablespace
如果两边服务器的 table row_format 设置的不一样，会导致 schema mismatch error
加密过的 InnoDB tablespace 必须要拷贝.cfp 文件

四.MyISAM 表空间迁移
1. flush table with read lock
2. 直接复制数据文件和表结构文件

“mysql 共享表空间的扩容、收缩和迁移”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！

正文完