Перенос данных из общего табличного пространства в табличное пространство file-per-table в СУБД MySQL или MariaDB

Для СУБД MySQL и MariaDB вы можете хранить данные следующими способами:

• В качестве общего табличного пространства, когда данные всех пользовательских баз данных хранятся в одном файле (обычно в файле ibdata1).
• В качестве табличного пространства file-per-table, когда для каждой таблицы создается отдельное пространство данных в виде отдельного файла.

Использование общего табличного пространства увеличивает риск фрагментации табличного пространства. Поскольку выполнить операцию SHRINK невозможно, файл в общем табличном пространстве может занимать около 90% дискового пространства. Это отрицательно сказывается на производительности дисковой подсистемы. Поэтому стоит выбрать табличное пространство file-per-table.

В этой статье описывается, как переместить данные из общего табличного пространства в табличное пространство file-per-table. Если СУБД MySQL или MariaDB также используются другими приложениями, вам необходимо переместить базы данных этих приложений в табличное пространство file-per-table.

Рекомендуется выполнять перенос только в том случае, если вы используете СУБД MySQL или MariaDB для Сервера администрирования.
Перемещение данных из общего табличного пространства в табличное пространство file-per-table может увеличить нагрузку на дисковую подсистему.

Предварительные требования

Перед тем как начать, убедитесь в следующем:

• Вы создали резервную копию данных Сервера администрирования, запустив либо задачу резервного копирования данных, либо утилиту klbackup.
• Вы создали резервную копию других приложений, базы данных которых управляются СУБД MySQL или MariaDB (если есть).
• На устройстве с установленной СУБД в разделе с базой данных есть свободное место на диске.

Требуемое дисковое пространство составляет 150% от текущего общего размера баз данных (текущего размера файла ibdata1).

Процесс

Чтобы переместить данные из общего табличного пространства в табличное пространство file-per-table:

1. Остановите службу Сервера администрирования Kaspersky Security Center и другие приложения, использующие экземпляр СУБД, для которого вы хотите выполнить эту процедуру.
2. В зависимости от версии СУБД войдите в свою СУБД как суперпользователь, а затем в любом приложении запустите скрипт 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;

   Не останавливайте выполнение скрипта.
   Если ошибок СУБД не было, но вы прервали выполнение скрипта, либо запустите скрипт повторно, либо прекратите выполнение шагов этой процедуры, перезагрузите службу СУБД и восстановите данные Сервера администрирования из резервной копии.

3. В разделе [mysqld] файла my.cnf установите для параметра innodb_file_per_table значение 1.
4. Перезагрузите службу MySQL или MariaDB.
5. Запустите службу Сервера администрирования Kaspersky Security Center.

Данные будут перемещены из общего табличного пространства в табличное пространство file-per-table: