在 MySQL 或 MariaDB DBMS 中将数据从共享表空间移动到每个表一个文件的表空间

扩展所有 | 折叠所有

对于 MySQL 和 MariaDB DBMS，您可以通过以下方式存储数据：

• 作为共享表空间，当所有用户数据库的数据存储在单个文件中（通常在ibdata1文件中）时。
• 作为每个表一个文件的表空间，为每个表创建一个单独的数据空间作为单独的文件。

使用共享表空间会增加表空间碎片的风险。由于无法执行SHRINK操作，共享表空间中的文件可能会占用大约 90% 的磁盘空间。这会对磁盘子系统的性能产生负面影响。因此，选择每个表一个文件的表空间很有用。

本文介绍如何将数据从共享表空间移动到每个表一个文件的表空间。如果其他应用程序也使用 MySQL 或 MariaDB DBMS，则必须将这些应用程序的数据库移动到每个表独享文件的表空间。

我们建议您仅在使用管理服务器的 MySQL 或 MariaDB DBMS 时才执行该过程。
将数据从共享表空间移动到每个表一个文件的表空间可能会增加磁盘子系统的负载。

先决条件

在开始之前，请确保：

• 您通过运行数据备份任务或klbackup 实用程序创建了管理服务器数据的备份副本。
• 您创建了其他应用程序的备份副本，这些应用程序的数据库由 MySQL 或 MariaDB DBMS（如有）管理。
• 在安装了 DBMS 的设备上，在带有数据库的分区中，有一个可用磁盘空间。

所需磁盘空间是当前数据库总大小（ ibdata1文件的当前大小）的 150%。

过程

要将数据从共享表空间移动到每个表一个文件的表空间：

1. 停止 Kaspersky Security Center 管理服务器服务和其他使用您要为其执行此过程的 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. 启动 Kaspersky Security Center 管理服务器服务。

数据被从共享表空间移动到每个表一个文件表空间。