Moving data from a shared tablespace to a file-per-table tablespace in a MySQL or MariaDB DBMS

Expand all | Collapse all

For a MySQL and MariaDB DBMS, you can store data in the following ways:

• As a shared tablespace, when the data of all user databases is stored in a single file (usually, in the ibdata1 file).
• As a file-per-table tablespace, when for each table a separate data space is created as a separate file.

Using a shared tablespace increases the risk of tablespace fragmentation. Since it is not possible to perform a SHRINK operation, a file in a shared tablespace may take up about 90% of the disk space. This negatively affects the disk subsystem performance. Thus, it is useful to opt for a file-per-table tablespace.

This article describes how to move data from a shared tablespace to a file-per-table tablespace. If the MySQL or MariaDB DBMSs are also used by other applications, you have to move the databases of these applications to the file-per-table tablespace.

We recommend performing this procedure only if you are using MySQL or MariaDB DBMSs for Administration Server.
Moving data from a shared tablespace to a file-per-table tablespace may increase the load on the disk subsystem.

Prerequisites

Before you start, make sure that:

• You created a backup copy of Administration Server data by running either a data backup task or the klbackup utility.
• You created a backup copy of other applications whose databases are managed by MySQL or MariaDB DBMS (if any).
• On the device with the DBMS installed, in the partition with database, there is free disk space.

The required disk space is 150% of the current total databases size (current size of the ibdata1 file).

Process

To move data from a shared tablespace to a file-per-table tablespace:

1. Stop the Kaspersky Security Center Administration Server service and other applications using the instance of the DBMS for which you want to perform this procedure.
2. Depending on the DBMS version, log in to your DBMS as a superuser, and then in any convenient application, run the fix_tablespace.sql script.

   You can view the fix_tablespace.sql scripts by clicking these links:

   View the script for MySQL 5.7 and 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;

   View the script for 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;

   You must not stop the script execution.
   If no DBMS errors occurred, but you terminated the script execution, either run the script again or stop performing the steps of this procedure, restart the DBMS service, and restore the Administration Server data from the backup.

3. In the [mysqld] section of the my.cnf file, set the innodb_file_per_table parameter to 1.
4. Restart the MySQL or MariaDB service.
5. Start the Kaspersky Security Center Administration Server service.

The data is moved from a shared tablespace to a file-per-table tablespace.