MySql - zmiana innodb_file_per_table dla bazy danych na żywo

Mam dużą bazę danych MySql (150 GB) i dopiero teraz zauważyłem, że innodb_file_per_tablejest ustawiona na, offktóra powoduje, że cała baza danych jest hostowana w jednym pliku ( ibdata1). Chcę aktywować innodb_file_per_tablei pozwolić, aby z mocą wsteczną podzieliłem bazę danych na kilka plików, jaki jest najlepszy sposób to zrobić?

Jest naprawdę tylko jeden sposób, aby to osiągnąć. Będziesz musiał wyeksportować dane za pomocą mysqldumps, upuść wszystkie bazy danych, zamknij mysqld, usuń ib_logfile0, usuń ib_logfile1, usuń ibdata1, dodaj innodb_file_per_tablepod [mysqld]nagłówkiem, uruchom mysql.

Tę odpowiedź opublikowałem w StackOverflow w październiku 2010 roku

Oto kroki wymienione pionowo:

Krok 01) MySQL Zrzuć wszystkie bazy danych do pliku tekstowego SQL (nazwij go SQLData.sql)

Krok 02) Usuń wszystkie bazy danych (oprócz schematu mysql)

Krok 03) Zamknij mysql

PRZESTROGA : Aby całkowicie usunąć niezatwierdzone transakcje z plików InnoDB, uruchom to

mysql -uroot -p... -Ae"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop

Krok 04) Dodaj następujące wiersze do /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Uwaga: Niezależnie od tego, jaki zestaw masz dla innodb_buffer_pool_size, upewnij się, że innodb_log_file_size to 25% innodb_buffer_pool_size.

Krok 05) Usuń ibdata1, ib_logfile0 i ib_logfile1

W tym momencie powinien istnieć tylko schemat mysql w / var / lib / mysql

Krok 06) Uruchom ponownie mysql

Spowoduje to ponowne utworzenie ibdata1 przy 10 MB, ib_logfile0 i ib_logfile1 przy 1G każdy

Krok 07) Załaduj ponownie SQLData.sql do mysql

ibdata1 wzrośnie, ale będzie zawierał tylko metadane tabeli

Każda tabela InnoDB będzie istniała poza ibdata1

Załóżmy, że masz tabelę InnoDB o nazwie mydb.mytable. Jeśli przejdziesz do / var / lib / mysql / mydb, zobaczysz dwa pliki reprezentujące tabelę

• mytable.frm (nagłówek silnika pamięci masowej)
• mytable.ibd (Strona główna danych tabeli i indeksów tabel dla mydb.mytable)

ibdata1 nigdy nie będzie już zawierał danych i indeksów InnoDB.

Dzięki opcji innodb_file_per_table w /etc/my.cnf możesz uruchomić OPTIMIZE TABLE mydb.mytable, a plik /var/lib/mysql/mydb/mytable.ibd faktycznie się zmniejszy.

Robiłem to wiele razy w mojej karierze jako MySQL DBA

W rzeczywistości, kiedy pierwszy raz to zrobiłem, zwinąłem 50 GB pliku ibdata1 do 500 MB.

Spróbuj. Jeśli masz dodatkowe pytania, napisz do mnie. Zaufaj mi. Będzie to działać w krótkim okresie i na dłuższą metę. !!!

Istnieje alternatywa, która wyodrębni tabelę InnoDB bez zmniejszania ibdata1.

Krok 01) Dodaj następujące wiersze do /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Krok 02) service mysql restart

Krok 03) Aby wyodrębnić pojedynczą tabelę InnoDB o nazwie mydb.mytable, wykonaj następujące czynności:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

Spowoduje to utworzenie jednego pliku z zachowaniem oryginalnego pliku struktury

• /var/lib/mysql/mydb/mytable.frm
• /var/lib/mysql/mydb/mytable.ibd

Możesz to zrobić dla każdej tabeli InnoDB. Niestety, ibdata1 pozostanie 150 GB.

Jeśli chcesz odzyskać przestrzeń ibdata, zrzut / przywrócenie jest twoim jedynym wyborem, jak podkreśla Rolando . Jest to również prawdopodobnie najlepsza wydajność.

Jeśli jednak chcesz po prostu zmniejszyć straty i utracić 150 GB na dysku twardym, możesz po prostu włączyć plik innodb_file_per_tablemy.cnf i ponownie uruchomić serwer.

Następnie dla każdej tabeli wydaj:

ALTER TABLE x DISABLE KEYS;
ALTER TABLE x ENGINE=InnoDB;
ALTER TABLE x ENABLE KEYS; 

Problem polega na tym, że duże przestrzenie tabel zajmą trochę czasu.

Sugeruję, aby skonfigurować Slave swojego db db, uruchomić konwersję na Slave, a następnie albo wyłączyć master / Slave i skopiować nową przestrzeń danych do Master, lub wypromować Slave jako Master, gdy już go dogoni .

Będziesz miał trudności z wprowadzeniem tej zmiany bez żadnych przestojów.