Mover datos desde un espacio de tabla compartido a un espacio de tabla por archivo en un DBMS MySQL o MariaDB

Expandir todo | Contraer todo

Para un DBMS MySQL y MariaDB, puede almacenar datos de las siguientes maneras:

• Como espacio de tabla compartido, cuando los datos de todas las bases de datos de usuarios se almacenan en un solo archivo (generalmente, en el archivo ibdata1).
• Como espacio de tabla de archivo por tabla, cuando para cada tabla se crea un espacio de datos separado como un archivo separado.

El uso de un espacio de tabla compartido aumenta el riesgo de fragmentación del espacio de tabla. Debido a que no es posible realizar una operación SHRINK, un archivo en un espacio de tabla compartido puede ocupar aproximadamente el 90 % del espacio en disco. Esto afecta negativamente al rendimiento del subsistema de disco. Por lo tanto, es útil optar por un espacio de tabla de archivo por tabla.

En este artículo se describe cómo mover datos de un espacio de tabla compartido a un espacio de tabla de archivo por tabla. Si otras aplicaciones también utilizan el DBMS MySQL o MariaDB, debe mover las bases de datos de estas aplicaciones al espacio de tabla de archivo por tabla.

Le recomendamos realizar el procedimiento solo si está utilizando DBMS MySQL o MariaDB para el Servidor de administración.
Mover datos de un espacio de tabla compartido a un espacio de tabla de archivo por tabla puede aumentar la carga en el subsistema de disco.

Requisitos previos

Antes de comenzar, asegúrese de lo siguiente:

• Ha creado una copia de seguridad de los datos del Servidor de administración ejecutando una tarea de copia de seguridad de datos o la utilidad klbackup.
• Ha creado una copia de seguridad de otras aplicaciones cuyas bases de datos están administradas por DBMS MySQL o MariaDB (si corresponde).
• En el dispositivo con el DBMS instalado, en la partición con la base de datos, hay espacio libre en disco.

El espacio en disco requerido es el 150 % del tamaño total actual de las bases de datos (tamaño actual del archivo ibdata1).

Proceso

Para mover datos de un espacio de tabla compartido a un espacio de tabla de archivo por tabla:

1. Detenga el servicio del Servidor de administración de Kaspersky Security Center y otras aplicaciones que utilicen la instancia del DBMS para la que desea realizar este procedimiento.
2. Según la versión del DBMS, inicie sesión en el DBMS como superusuario y luego, en cualquier aplicación conveniente, ejecute el script fix_tablespace.sql.

   Puede ver el script fix_tablespace.sql si hace clic en estos enlaces:

   Ver el script para MySQL 5.7 y 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;

   Ver el 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;

   No debe detener la ejecución del script.
   Si no se produjeron errores en el DBMS, pero finalizó la ejecución del script, vuelva a ejecutar el script o deje de realizar los pasos de este procedimiento, reinicie el servicio del DBMS y restaure los datos del Servidor de administración desde la copia de seguridad.

3. En la sección [mysqld] del archivo my.cnf, establezca el parámetro innodb_file_per_table en 1.
4. Reinicie el servicio MySQL o MariaDB.
5. Inicie el servicio del Servidor de administración de Kaspersky Security Center.

Los datos se mueven de un espacio de tabla compartido a un espacio de tabla de archivo por tabla.