在 MySQL 或 MariaDB DBMS 中將資料從共享表空間移動到每個表格一個檔案的表空間

延伸所有 | 折疊所有

對於 MySQL 和 MariaDB DBMS，您可以透過以下方式儲存資料：

• 作為共享表空間，當所有使用者資料庫的資料都儲存在單一檔案中（通常在ibdata1檔案中）時。
• 作為每個表格一個檔案的表空間，當為每個表建立一個單獨的資料空間作為單獨的檔案時。

使用共享表空間會增加表空間碎片的風險。由於無法執行SHRINK操作，共用表空間中的一個檔案可能會佔用約 90% 的磁碟空間。這會對磁碟子系統的效能產生負面影響。因此，選擇每個表格一個檔案的表空間很有用。

本文介紹如何將資料從共享表空間移動到每個表格一個檔案的表空間。如果其他應用程式也使用 MySQL 或 MariaDB DBMS，則您必須將這些應用程式的資料庫移至每個表格一個檔案的表空間。

我們建議您僅在使用管理伺服器的 MySQL 或 MariaDB DBMS 時才執行此程序。
將資料從共享表空間移動到每個表格一個檔案的表空間可能會增加磁碟子系統的負載。

先決條件

在開始之前，請確保：

• 您透過執行資料備份工作或klbackup 公用程式建立了管理伺服器資料的備份副本。
• 您建立了其他應用程式的備份副本，這些應用程式的資料庫由 MySQL 或 MariaDB DBMS（如有）管理。
• 在安裝了 DBMS 的裝置上，在具有資料庫的分割區中，有可用磁碟空間。

所需磁碟空間是目前資料庫總大小（ ibdata1檔案的目前大小）的 150%。

過程

若要將資料從共享表空間移動到每個表格一個檔案的表空間：

1. 停止卡巴斯基安全管理中心管理伺服器服務和其他使用您要為其執行此程序的 DBMS 實例的應用程式。
2. 根據 DBMS 版本，以超級使用者身分登入您的 DBMS，然後在任何方便的應用程式中執行fix_tablespace.sql指令碼。

   您可以透過點擊這些連接來檢視fix_tablespace.sql 指令碼：

   檢視適用於 MySQL 5.7 和 MariaDB 10.1+ 的指令碼

   DROP PROCEDURE IF EXISTS upgd_alter_all_tables_for_all_databases_to_innodb;

   DELIMITER //

   CREATE PROCEDURE `upgd_alter_all_tables_for_all_databases_to_innodb` ()

   BEGIN

   DECLARE __st_FETCH_STATUS INT;

   DECLARE `schemaName` VARCHAR(192);

   DECLARE `tableName` VARCHAR(192);

   DECLARE `curIX` CURSOR FOR

   SELECT `TABLE_SCHEMA` , `TABLE_NAME`

   FROM information_schema.TABLES

   WHERE `TABLE_SCHEMA` NOT IN ('mysql', 'performance_schema', 'sys') AND TABLE_TYPE = 'BASE TABLE';

   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET __st_FETCH_STATUS = 1;

   OPEN `curIX`;

   SET `__st_FETCH_STATUS` = 0; FETCH `curIX` INTO `schemaName`, `tableName`;

   while1 : WHILE (0 = 0 AND __st_FETCH_STATUS = 0 ) DO

   SET @`strSQL` = CONCAT( 'ALTER TABLE `' , `schemaName`, '`.`',`tableName`, '` ENGINE=''InnoDB'';' );

   PREPARE stmt1 FROM @`strSQL`;

   EXECUTE stmt1;

   DEALLOCATE PREPARE stmt1;

   SET `__st_FETCH_STATUS` = 0; FETCH `curIX` INTO `schemaName`, `tableName`;

   END WHILE;

   CLOSE `curIX`;

   END

   //

   DELIMITER ;

   SET GLOBAL innodb_file_per_table = 1;

   CALL upgd_alter_all_tables_for_all_databases_to_innodb();

   DROP PROCEDURE IF EXISTS upgd_alter_all_tables_for_all_databases_to_innodb;

   檢視適用於 MySQL 8.0+ 的指令碼

   DROP PROCEDURE IF EXISTS upgd_alter_all_tables_for_all_databases_to_innodb;

   DELIMITER //

   CREATE PROCEDURE `upgd_alter_all_tables_for_all_databases_to_innodb` ()

   BEGIN

   DECLARE __st_FETCH_STATUS INT;

   DECLARE `schemaName` VARCHAR(192);

   DECLARE `tableName` VARCHAR(192);

   DECLARE `curIX` CURSOR FOR

   SELECT `TABLE_SCHEMA` , `TABLE_NAME`

   FROM information_schema.TABLES

   WHERE `TABLE_SCHEMA` NOT IN ('mysql', 'performance_schema', 'sys') AND TABLE_TYPE = 'BASE TABLE';

   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET __st_FETCH_STATUS = 1;

   OPEN `curIX`;

   SET `__st_FETCH_STATUS` = 0; FETCH `curIX` INTO `schemaName`, `tableName`;

   while1 : WHILE (0 = 0 AND __st_FETCH_STATUS = 0 ) DO

   SET @`strSQL` = CONCAT( 'ALTER TABLE `' , `schemaName`, '`.`',`tableName`, '` ENGINE=''InnoDB'';' );

   PREPARE stmt1 FROM @`strSQL`;

   EXECUTE stmt1;

   DEALLOCATE PREPARE stmt1;

   SET @`strSQL` = CONCAT( 'ALTER TABLE `' , `schemaName`, '`.`',`tableName`, '` TABLESPACE = innodb_file_per_table;' );

   PREPARE stmt2 FROM @`strSQL`;

   EXECUTE stmt2;

   DEALLOCATE PREPARE stmt2;

   SET `__st_FETCH_STATUS` = 0; FETCH `curIX` INTO `schemaName`, `tableName`;

   END WHILE;

   CLOSE `curIX`;

   END

   //

   DELIMITER ;

   SET GLOBAL innodb_file_per_table = 1;

   CALL upgd_alter_all_tables_for_all_databases_to_innodb();

   DROP PROCEDURE IF EXISTS upgd_alter_all_tables_for_all_databases_to_innodb;

   您不得停止指令碼執行。
   如果沒有發生 DBMS 錯誤，但您終止了指令碼執行，請再次執行指令碼，或停止執行此程序的步驟，重新啟動 DBMS 服務，然後從備份中還原管理伺服器資料。

3. 在 my.cnf 檔案的[mysqld]部分中，將innodb_file_per_table參數設為1。
4. 重新啟動 MySQL 或 MariaDB 服務。
5. 啟動卡巴斯基安全管理中心管理伺服器服務。

資料被從共享表空間移動到每個表格一個檔案的表空間。