Mover dados de um espaço de tabela compartilhado para um espaço de tabela individual nos DBMSs MySQL e MariaDB

Expandir tudo | Recolher tudo

Em um DBMS MySQL e MariaDB, é possível armazenar dados das seguintes formas:

• Como um espaço de tabela compartilhado, quando os dados de todos os bancos de dados do usuário forem armazenados em um único arquivo (geralmente no arquivo ibdata1).
• Como um espaço de tabela individual, quando um espaço para dados separado é criado como um arquivo separado.

Usar um espaço de tabela compartilhado aumenta o risco de fragmentação. Como não é possível executar uma operação SHRINK, um arquivo em um espaço de tabela compartilhado pode ocupar cerca de 90% do espaço em disco. Isso prejudica o desempenho do subsistema de disco. Portanto, é útil optar por um espaço de tabela individual.

Este artigo descreve como mover dados de um espaço de tabela compartilhado para um espaço de tabela individual. Se os DBMSs MySQL ou MariaDB também forem utilizados por outros aplicativos, será necessário mover os bancos de dados desses aplicativos para o espaço de tabela individual.

Recomendamos que o procedimento seja executado somente se você estiver utilizando DBMSs MySQL ou MariaDB para o Servidor de Administração.
Mover dados de um espaço de tabela compartilhado para um espaço de tabela individual pode aumentar a carga no subsistema de disco.

Pré-requisitos

Antes de começar, certifique-se de que:

• Uma cópia de backup dos dados do Servidor de Administração foi criada por meio da execução de uma tarefa de backup de dados ou do utilitário klbackup.
• Uma cópia de backup de outros aplicativos foi criada, cujos bancos de dados são gerenciados por um DBMS MySQL ou MariaDB (se houver).
• No dispositivo com o DBMS instalado, há um espaço livre em disco na partição com o banco de dados.

O espaço em disco necessário corresponde a 150% do tamanho total atual dos bancos de dados (tamanho atual do arquivo ibdata1).

Processar

Para mover dados de um espaço de tabela compartilhado para um espaço de tabela individual:

1. Pare o serviço do Servidor de Administração do Kaspersky Security Center e outros aplicativos por meio da instância do DBMS para a qual você deseja executar este procedimento.
2. Dependendo da versão do DBMS, faça login no seu DBMS como superusuário e então, em qualquer aplicativo conveniente, execute o script fix_tablespace.sql.

   É possível visualizar os scripts fix_tablespace.sql clicando nestes links:

   Visualizar o script para 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;

   Visualizar o script para 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;

   A execução do script não deve ser interrompida.
   Se nenhum erro de DBMS ocorreu, mas você interrompeu a execução do script, execute o script novamente ou pare de executar as etapas deste procedimento, reinicie o serviço DBMS e restaure os dados do Servidor de Administração a partir do backup.

3. Na seção [mysqld] do arquivo my.cnf, defina o parâmetro innodb_file_per_table como 1.
4. Reinicie o serviço MySQL ou MariaDB.
5. Reinicie o serviço do Servidor de Administração do Kaspersky Security Center.

Os dados serão movidos de um espaço de tabela compartilhado para um espaço de tabela individual.