Spostamento dei dati da un tablespace condiviso a un tablespace file per tabella in un DBMS MySQL e MariaDB

Espandi tutto | Comprimi tutto

Per i DBMS MySQL e MariaDB, è possibile archiviare i dati nei seguenti modi:

• Come tablespace condiviso, quando i dati di tutti i database utente sono archiviati in un singolo file (in genere, nel file ibdata1).
• Come tablespace file per tabella, quando per ogni tabella viene creato uno spazio dati separato come file separato.

L'utilizzo di un tablespace condiviso aumenta il rischio di frammentazione del tablespace. Poiché non è possibile eseguire un'operazione SHRINK, un file in un tablespace condiviso potrebbe occupare circa il 90% dello spazio su disco. Ciò influisce negativamente sulle prestazioni del sottosistema del disco. Pertanto, è utile optare per un tablespace file per tabella.

Questo articolo descrive come spostare i dati da un tablespace condiviso a un tablespace file per tabella. Se i DBMS MySQL o MariaDB vengono utilizzati anche da altre applicazioni, è necessario spostare i database di queste applicazioni nel tablespace file per tabella.

È consigliabile eseguire questa procedura solo se si utilizzano DBMS MySQL o MariaDB per Administration Server.
Lo spostamento dei dati da un tablespace condiviso a un tablespace file per tabella può aumentare il carico sul sottosistema del disco.

Prerequisiti

Prima di iniziare, assicurarsi che:

• Sia stata creata una copia di backup dei dati di Administration Server eseguendo un'attività di backup dei dati o l'utilità klbackup.
• Sia stata creata una copia di backup di altre applicazioni i cui database sono gestiti dal DBMS MySQL o MariaDB (se presente).
• Nel dispositivo in cui è installato il DBMS, nella partizione con il database sia disponibile spazio su disco.

Lo spazio su disco richiesto corrisponde al 150% della dimensione totale corrente dei database (dimensione corrente del file ibdata1).

Processo

Per spostare i dati da un tablespace condiviso a un tablespace file per tabella:

1. Arrestare il servizio Kaspersky Security Center Administration Server e le altre applicazioni che utilizzano l'istanza del DBMS per cui si desidera eseguire questa procedura.
2. In base alla versione del DBMS, accedere al DBMS come utente con privilegi avanzati, quindi eseguire lo script fix_tablespace.sql in qualsiasi applicazione utile.

   È possibile visualizzare gli script fix_tablespace.sql facendo clic su questi collegamenti:

   Visualizzare lo script per MySQL 5.7 e 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;

   Visualizzare lo script per 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;

   Non è necessario arrestare l'esecuzione dello script.
   Se non si sono verificati errori del DBMS, ma l'esecuzione dello script è stata terminata, eseguire nuovamente lo script o interrompere l'esecuzione dei passaggi di questa procedura, riavviare il servizio DBMS e ripristinare i dati di Administration Server dal backup.

3. Nella sezione [mysqld] del file my.cnf, impostare il parametro innodb_file_per_table su 1.
4. Riavviare il servizio MySQL o MariaDB.
5. Riavviare il servizio Kaspersky Security Center Administration Server.

I dati vengono spostati da un tablespace condiviso a un tablespace file per tabella.