mysql共享表空間的擴容、收縮和遷移

本篇內容介紹了“mysql共享表空間的擴容、收縮和遷移”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

一.擴容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表空間遷移
原庫表結構:

點擊(此處)折疊或打開

1. CREATE TABLE `wwj`.`t1` (
   

2.   `id` INT NOT NULL,
   

3.   `name` VARCHAR(45) NULL,
   

4.   PRIMARY KEY (`id`));
   

5.   
   

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

7. 
   

8. insert into wwj.t1 values(1,'wwj');

1.在目標實例上創建一個相同的表

點擊(此處)折疊或打開

1. CREATE TABLE `wwj2`.`t1` (
   

2.   `id` INT NOT NULL,
   

3.   `name` VARCHAR(45) NULL,
   

4.   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共享表空間的擴容、收縮和遷移”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！