Przenoszenie danych ze współdzielonej przestrzeni tabel do przestrzeni tabel typu plik-tabela w systemach DBMS MySQL lub MariaDB

Rozwiń wszystko | Zwiń wszystko

W przypadku systemów DBMS MySQL i MariaDB dane można przechowywać w następujący sposób:

• Jako współdzielona przestrzeń tabel, gdy dane wszystkich baz danych użytkownika są przechowywane w jednym pliku (zwykle w pliku ibdata1).
• Jako przestrzeń tabel typu plik-tabela, gdy dla każdej tabeli tworzona jest oddzielna przestrzeń danych jako oddzielny plik.

Korzystanie ze współdzielonej przestrzeni tabel zwiększa ryzyko fragmentacji przestrzeni tabel. Ponieważ nie da się wykonać operacji SHRINK, plik w przestrzeni tabel współdzielonych może zająć około 90% miejsca na dysku. Ma to negatywny wpływ na wydajność podsystemu dyskowego. Dlatego też warto zdecydować się na przestrzeń tabel typu plik-tabela.

W tym artykule opisano sposób przenoszenia danych ze współdzielonej przestrzeni tabel do przestrzeni tabel typu plik-tabela. Jeśli systemy DBMS MySQL lub MariaDB są używane również przez inne aplikacje, należy przenieść bazy danych tych aplikacji do przestrzeni tabel typu plik-tabela.

Zalecamy wykonanie tej procedury tylko wtedy, gdy w charakterze Serwera administracyjnego używany jest system DBMS MySQL lub MariaDB.
Przenoszenie danych z współdzielonej przestrzeni tabel do przestrzeni tabel typu plik-tabela może zwiększyć obciążenie podsystemu dyskowego.

Wymagania wstępne

Zanim zaczniesz, upewnij się, że:

• Utworzyłeś kopię zapasową danych Serwera administracyjnego, uruchamiając zadanie tworzenia kopii zapasowej danych lub narzędzie klbackup.
• Utworzyłeś kopię zapasową innych aplikacji, których bazy danych są zarządzane przez system DBMS MySQL lub MariaDB (jeśli dotyczy).
• Na urządzeniu z zainstalowanym systemem DBMS, na partycji z bazą danych, znajduje się wolna przestrzeń dyskowa.

Wymagana ilość miejsca na dysku wynosi 150% bieżącego całkowitego rozmiaru bazy danych (bieżący rozmiar pliku ibdata1).

Proces

Aby przenieść dane ze współdzielonej przestrzeni tabel do przestrzeni tabel typu plik-tabela:

1. Zatrzymaj usługę Serwer administracyjny Kaspersky Security Center i inne aplikacje korzystające z instancji systemu DBMS, dla którego chcesz wykonać tę procedurę.
2. Zaloguj się do systemu DBMS jako superużytkownik, a następnie w dowolnej dogodnej aplikacji uruchom skrypt fix_tablespace.sql.

   Możesz wyświetlać skrypty fix_tablespace.sql klikając te odnośniki:

   Wyświetl skrypt dla MySQL 5.7 i 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;

   Wyświetl skrypt dla 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;

   Nie wolno zatrzymywać wykonywania skryptu.
   Jeśli nie wystąpiły żadne błędy systemu DBMS, ale przerwałeś wykonywanie skryptu, uruchom skrypt ponownie lub przerwij wykonywanie kroków tej procedury, uruchom ponownie usługę DBMS i przywróć dane Serwera administracyjnego z kopii zapasowej.

3. W sekcji [mysqld] pliku my.cnf ustaw parametr innodb_file_per_table na 1.
4. Uruchom ponownie usługę MySQL lub MariaDB.
5. Uruchom ponownie usługę Serwera administracyjnego Kaspersky Security Center.

Dane są przenoszone ze współdzielonej przestrzeni tabel do przestrzeni tabel typu plik-tabela.