Déplacement de données d'un tablespace partagé vers un tablespace fichier par table dans les SGBD MySQL ou MariaDB

Tout développer | Tout réduire

Pour les SGBD MySQL et MariaDB, vous pouvez stocker les données comme suit :

• En tant que tablespace partagé, lorsque les données de toutes les bases de données utilisateur sont stockées dans un fichier unique (généralement, dans le fichier ibdata1).
• En tant que tablespace fichier par table, lorsqu'un espace de données distinct est créé pour chaque table en tant que fichier distinct.

L'utilisation d'un tablespace partagé augmente le risque de fragmentation du tablespace. L'opération SHRINK étant impossible, un fichier du tablespace partagé peut occuper environ 90 % de l'espace disque. Cela affecte négativement les performances du sous-système de disque. Il est donc utile d'opter pour un tablespace fichier par table.

Cet article explique comment déplacer des données d'un tablespace partagé vers un tablespace fichier par table. Si les SGBD MySQL ou MariaDB sont également utilisés par d'autres applications, vous devez déplacer les bases de données de ces applications dans le tablespace fichier par table.

Nous vous recommandons d'effectuer cette procédure uniquement si vous utilisez le SGBD pour Serveur d'administration MySQL ou MariaDB.
Le déplacement de données d'un tablespace partagé vers un tablespace fichier par table peut augmenter la charge sur le sous-système de disque.

Prérequis

Avant de commencer, assurez-vous que :

• Vous avez créé une copie de sauvegarde des données du Serveur d'administration en exécutant une tâche de sauvegarde des données ou l'utilitaire klbackup.
• Vous avez créé une copie de sauvegarde des autres applications dont les bases de données sont gérées par le SGBD MySQL ou MariaDB (le cas échéant).
• Sur l'appareil sur lequel le SGBD est installé, il y a de l'espace libre sur la partition avec la base de données.

L'espace disque requis représente 150 % de la taille totale actuelle des bases (taille actuelle du fichier ibdata1).

Processus

Pour déplacer des données d'un tablespace partagé vers un tablespace fichier par table, procédez comme suit :

1. Arrêtez le service du Serveur d'administration de Kaspersky Security Center et les autres applications utilisant l'instance du SGBD pour lequel vous souhaitez exécuter cette procédure.
2. En fonction de la version du SGBD, connectez-vous à votre SGBD en tant que superutilisateur, puis, dans n'importe quelle application pratique, exécutez le script fix_tablespace.sql.

   Vous pouvez consulter les scripts fix_tablespace.sql en cliquant sur les liens suivants :

   Consultez le script pour MySQL 5.7 et MariaDB 10.1 ou toute version ultérieure

   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;

   Consultez le script pour 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;

   Vous ne devez pas arrêter l'exécution du script.
   Si aucune erreur de SGBD ne s'est produite, mais que vous avez interrompu l'exécution du script, soit exécutez à nouveau le script, soit arrêtez d'exécuter les étapes de cette procédure, redémarrez le service SGBD et restaurez les données du Serveur d'administration à partir de la sauvegarde.

3. Dans la section [mysqld] du fichier my.cnf, définissez le paramètre innodb_file_per_table sur 1.
4. Redémarrez le service MySQL ou MariaDB.
5. Relancez le service du Serveur d'administration de Kaspersky Security Center.

Les données sont déplacées d'un tablespace partagé vers un tablespace fichier par table.