Jak zmniejszyć / wyczyścić plik ibdata1 w MySQL

Używam MySQL w localhost jako „narzędzie zapytań” do wykonywania statystyk w języku R, to znaczy za każdym razem, gdy uruchamiam skrypt R, tworzę nową bazę danych (A), tworzę nową tabelę (B), importuję dane do B , prześlij zapytanie, aby uzyskać to, czego potrzebuję, a następnie upuszczam B i upuszczam A.

Działa dla mnie dobrze, ale zdaję sobie sprawę, że rozmiar pliku ibdata szybko rośnie, nic nie zapisałem w MySQL, ale plik ibdata1 już przekroczył 100 MB.

Używam mniej więcej domyślnego ustawienia MySQL do instalacji, czy istnieje sposób, aby automatycznie zmniejszyć / wyczyścić plik ibdata1 po ustalonym czasie?

To ibdata1nie kurczy się, jest szczególnie irytującą funkcją MySQL. ibdata1Plik nie może być rzeczywiście skurczył dopiero po usunięciu wszystkich baz danych, usunąć pliki i przeładować zrzutu.

Ale możesz skonfigurować MySQL, aby każda tabela, łącznie z jej indeksami, była przechowywana jako osobny plik. W ten sposób ibdata1nie będzie tak duży. Zgodnie z komentarzem Billa Karwina jest on domyślnie włączony od wersji 5.6.6 MySQL.

To było jakiś czas temu. Aby jednak skonfigurować serwer tak, aby używał osobnych plików dla każdej tabeli, musisz to zmienić my.cnf, aby to włączyć:

[mysqld]
innodb_file_per_table=1

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

Aby odzyskać miejsce ibdata1, musisz usunąć plik:

1. Wykonaj jedną mysqldumpze wszystkich baz danych, procedur, wyzwalaczy itp. Oprócz baz danych mysqliperformance_schema
2. Usuń wszystkie bazy danych oprócz powyższych 2 baz danych
3. Zatrzymaj mysql
4. Usuń ibdata1i ib_logpliki
5. Uruchom mysql
6. Przywróć ze zrzutu

Po uruchomieniu MySQL w kroku 5 pliki ibdata1i ib_logzostaną odtworzone.

Teraz możesz już iść. Po utworzeniu nowej bazy danych do analizy tabele będą znajdować się w osobnych ibd*plikach, a nie w ibdata1. Jak zwykle upuszczasz bazę danych wkrótce potem, ibd*pliki zostaną usunięte.

http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

Prawdopodobnie widziałeś to:
http://bugs.mysql.com/bug.php?id=1341

Za pomocą polecenia ALTER TABLE <tablename> ENGINE=innodblub OPTIMIZE TABLE <tablename>można wyodrębnić danych i stron indeksu z ibdata1 do oddzielnych plików. Jednak ibdata1 nie zmniejszy się, chyba że wykonasz powyższe kroki.

Jeśli chodzi o information_schema, nie jest to konieczne ani możliwe do upuszczenia. W rzeczywistości jest to tylko garść widoków tylko do odczytu, a nie tabele. I nie ma z nimi żadnych plików, nawet katalogu bazy danych. informations_schemaUżywa pamięci DB-silnik i jest odrzucany i regenerowane po stop / restart mysqld. Zobacz https://dev.mysql.com/doc/refman/5.7/en/information-schema.html .

Dodając do odpowiedzi John P za ,

W systemie linux kroki 1-6 można wykonać za pomocą następujących poleceń:

1. mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql
2. DROP DATABASE [database_name];
3. sudo /etc/init.d/mysqld stop
4. sudo rm /var/lib/mysql/ibdata1
sudo rm /var/lib/mysql/ib_logfile (i usuwać nawzajem ib_logfile które mogą być nazwane ib_logfile0, ib_logfile1etc ...)
5. sudo /etc/init.d/mysqld start
6. create database [database_name];
7. mysql -u [username]-p[root_password] [database_name] < dumpfilename.sql

Ostrzeżenie: te instrukcje spowodują utratę innych baz danych, jeśli masz inne bazy danych w tej instancji mysql. Upewnij się, że kroki 1,2 i 6,7 zostały zmodyfikowane tak, aby obejmowały wszystkie bazy danych, które chcesz zachować.

Kiedy usuwasz tabele innodb, MySQL nie zwalnia miejsca w pliku ibdata, dlatego ciągle rośnie. Te pliki prawie nigdy się nie kurczą.

Jak zmniejszyć istniejący plik ibdata:

http://dev.mysql.com/doc/refman/5.5/en/innodb-resize-system-tablespace.html

Możesz to zrobić i zaplanować uruchomienie skryptu po ustalonym czasie, ale dla opisanej powyżej konfiguracji wydaje się, że wiele obszarów tabel jest łatwiejszym rozwiązaniem.

Jeśli skorzystasz z opcji konfiguracji innodb_file_per_table , utworzysz wiele obszarów tabel. Oznacza to, że MySQL tworzy osobne pliki dla każdej tabeli zamiast jednego udostępnionego pliku. Te oddzielne pliki są przechowywane w katalogu bazy danych i są usuwane po usunięciu tej bazy danych. Powinno to wyeliminować potrzebę zmniejszania / usuwania plików ibdata w twoim przypadku.

Więcej informacji o wielu obszarach tabel:

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

Jeśli używasz silnika pamięci masowej InnoDB do (niektórych) tabel MySQL, prawdopodobnie napotkałeś już problem z jego domyślną konfiguracją. Jak można zauważyć w katalogu danych MySQL (w Debianie / Ubuntu - / var / lib / mysql) znajduje się plik o nazwie „ibdata1”. Przechowuje prawie wszystkie dane InnoDB (nie jest to dziennik transakcji) instancji MySQL i może stać się dość duży. Domyślnie ten plik ma początkowy rozmiar 10 Mb i automatycznie się rozszerza. Niestety, z założenia plików danych InnoDB nie można zmniejszyć. Dlatego DELETE, TRUNCATE, DROP itp. Nie odzyskają miejsca zajmowanego przez plik.

Myślę, że można tam znaleźć dobre wyjaśnienie i rozwiązanie:

http://vdachev.net/2007/02/22/mysql-reducing-ibdata1/

Szybko skryptuję procedurę akceptowanej odpowiedzi w bash:

#!/usr/bin/env bash
DATABASES="$(mysql -e 'show databases \G' | grep "^Database" | grep -v '^Database: mysql$\|^Database: binlog$\|^Database: performance_schema\|^Database: information_schema' | sed 's/^Database: //g')"
mysqldump --databases $DATABASES -r alldatabases.sql && echo "$DATABASES" | while read -r DB; do
    mysql -e "drop database \`$DB\`"
done && \
    /etc/init.d/mysql stop && \
    find /var/lib/mysql -maxdepth 1 -type f \( -name 'ibdata1' -or -name 'ib_logfile*' \) -delete && \
    /etc/init.d/mysql start && \
    mysql < alldatabases.sql && \
    rm -f alldatabases.sql

Zapisz jako purge_binlogs.shi uruchom jako root.

Wyklucza mysql, information_schema, performance_schema(i binlogkatalogu).

Zakłada się, że masz poświadczenia administratora /root/.my.cnfi że Twoja baza danych znajduje się w domyślnym /var/lib/mysqlkatalogu.

Możesz również wyczyścić dzienniki binarne po uruchomieniu tego skryptu, aby odzyskać więcej miejsca na dysku dzięki:

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

Jeśli Twoim celem jest monitorowanie wolnego miejsca w MySQL i nie możesz zatrzymać MySQL w celu zmniejszenia pliku ibdata, przeprowadź go przez polecenia statusu tabeli. Przykład:

MySQL> 5.1.24:

mysqlshow --status myInnodbDatabase myTable | awk '{print $20}'

MySQL <5.1.24:

mysqlshow --status myInnodbDatabase myTable | awk '{print $35}'

Następnie porównaj tę wartość z plikiem ibdata:

du -b ibdata1

Źródło: http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

W nowej wersji powyższych receptur na serwer mysql zniszczy bazę danych „mysql”. W starej wersji działa. W nowych niektórych tabelach przełącza się na typ INNODB, a przez to spowoduje ich uszkodzenie. Najprostszym sposobem jest:

• zrzuć wszystkie bazy danych
• odinstaluj serwer mysql,
• dodaj pozostało my.cnf:

    [mysqld]
    innodb_file_per_table=1

• wymaż wszystko w / var / lib / mysql
• zainstaluj serwer mysql
• przywrócić użytkowników i bazy danych

Jak już wspomniano, nie można zmniejszyć ibdata1 (aby to zrobić, należy zrzucić i przebudować), ale często nie ma też takiej potrzeby.

Używając autoextend (prawdopodobnie najczęściej używanego ustawienia rozmiaru) ibdata1 wstępnie przydziela pamięć, zwiększając się za każdym razem, gdy jest prawie pełna. Dzięki temu zapisy są szybsze, ponieważ miejsce jest już przydzielone.

Po usunięciu danych nie zmniejsza się, ale przestrzeń w pliku jest oznaczana jako nieużywana. Teraz, gdy wstawisz nowe dane, ponownie wykorzysta puste miejsce w pliku przed dalszym powiększaniem pliku.

Będzie więc nadal rósł, tylko jeśli faktycznie potrzebujesz tych danych. Jeśli nie potrzebujesz miejsca na inną aplikację, prawdopodobnie nie ma powodu, aby ją zmniejszać.