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

Po usunięciu tabel 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 wykonać skrypt i zaplanować uruchomienie skryptu po określonym czasie, ale dla konfiguracji opisanej powyżej wydaje się, że wiele przestrzeni tabletu jest łatwiejszym rozwiązaniem.

Jeśli używasz opcji konfiguracyjnej innodb_file_per_table, tworzysz wiele tablespaces. Oznacza to, że MySQL tworzy oddzielne pliki dla każdej tabeli zamiast jednego udostępnionego pliku. Te oddzielne pliki przechowywane w katalogu bazy danych i są usuwane po usunięciu tej bazy danych. Powinno to usunąć potrzebę zmniejszania / usuwania plików ibdata w Twoim przypadku.

Więcej informacji na temat wielu tablespaces:

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

Dodanie do odpowiedzi Jana P,

Dla systemu 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 usuń wszystkie inne pliki ib_logfile, które mogą być nazwane ib_logfile0, ib_logfile1 itd...)
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 na 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ć.

Jeśli używasz silnika InnoDB do (niektórych) tabel MySQL, prawdopodobnie napotkałeś już problem z jego domyślną konfiguracją. Jak zapewne zauważyłeś w katalogu danych MySQL (w Debian/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 10Mb i automatycznie się rozszerza. Niestety, według projektu Pliki danych InnoDB nie mogą być zmniejszane. Dlatego usuwa, obcina, krople itp. nie odzyskuje miejsca używanego przez plik.

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

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

Jeśli twoim celem jest monitorowanie wolnego miejsca w MySQL i nie możesz zatrzymać MySQL, aby zmniejszyć plik ibdata, pobierz go za pomocą poleceń statusu tabeli. Przykład:

MySQL > 5.1.24:

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

MySQL

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 mysql-serwer przepis powyżej zmiażdży bazę danych "mysql". W starej wersji działa. W nowych niektórych tabelach przełącza się na tabelę typu INNODB, a w ten sposób można je uszkodzić. Najprostszym sposobem jest zrzucenie wszystkich baz danych, odinstalowanie mysql-server, add in pozostał mój."cnf": {]}

[mysqld]
innodb_file_per_table=1


erase all in /var/lib/mysql
install mysql-server
restore users and databases

Szybko skrypt procedury zaakceptowanej 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.sh i uruchom jako root.

mysql, information_schema, performance_schema (i binlog katalog).

Zakłada, że masz w /root/.my.cnf credendials administratora i że Twoja baza danych znajduje się w domyślnym katalogu /var/lib/mysql.

Możesz również wyczyścić logi binarne po uruchomieniu tego skryptu, aby odzyskać więcej miejsca na dysku za pomocą:

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

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

Używanie autoextend (prawdopodobnie najczęstsze ustawienie rozmiaru) ibdata1 prealokuje pamięć masową, rosnącą za każdym razem, gdy jest prawie pełna. To sprawia, że zapisuje się szybciej, ponieważ miejsce jest już przydzielone.

Po usunięciu danych nie zmniejsza się, ale przestrzeń wewnątrz pliku jest oznaczona jako nieużywana. Teraz po wstawieniu nowych danych ponownie wykorzysta puste miejsce w pliku przed / align = "left" /

Więc będzie rosnąć tylko wtedy, gdy naprawdę potrzebujesz tych danych. Jeśli nie potrzebujesz miejsca na inną aplikację, prawdopodobnie nie ma powodu, aby ją zmniejszyć.