Moving data from shared tablespace to file-per-table tablespace in MySQL and MariaDB DBMSs

For MySQL and MariaDB DBMSs, 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 MySQL or MariaDB DBMSs are also used by other applications, you have to move the databases of these applications to file-per-table tablespace.

We recommend that you perform the procedure only if you are using MySQL or MariaDB DBMSs for Administration Server.
Moving data from shared tablespace to 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 my MySQL or MariaDB DBMS (if any).
• On the device with the DBMS installed, in the partition with database, there is a 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 DBMS for which you want to perform this procedure.
2. 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 script by clicking this link:

   View the script

   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;

   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.