So verkleinern / bereinigen Sie die ibdata1-Datei in MySQL

Hinzufügen zu Antwort von John P ,

Für ein Linux-System können die Schritte 1-6 mit diesen Befehlen ausgeführt werden:

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 (und alle anderen ib_log-Dateien löschen, die den Namen ib_logfile0, ib_logfile1 usw. haben)
5. Sudo /etc/init.d/mysqld start
6. create database [database_name];
7. mysql -u [username]-p[root_password] [database_name] < dumpfilename.sql

Warnung: Diese Anweisungen führen dazu, dass Sie andere Datenbanken verlieren, wenn Sie andere Datenbanken in dieser mysql-Instanz haben. Stellen Sie sicher, dass die Schritte 1, 2 und 6, 7 geändert werden, um alle Datenbanken abzudecken, die Sie behalten möchten.

Wenn Sie innodb-Tabellen löschen, gibt MySQL den Speicherplatz in der ibdata-Datei nicht frei. Diese Dateien werden kaum verkleinert.

So verkleinern Sie eine vorhandene ibdata-Datei:

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

Sie können dies skripten und die Ausführung des Skripts nach einem festgelegten Zeitraum einplanen. Für das oben beschriebene Setup scheinen jedoch mehrere Tablespaces eine einfachere Lösung zu sein.

Wenn Sie die Konfigurationsoption innodb_file_per_table verwenden, erstellen Sie mehrere Tablespaces. Das heißt, MySQL erstellt separate Dateien für jede Tabelle anstelle einer gemeinsamen Datei. Diese separaten Dateien werden im Verzeichnis der Datenbank gespeichert und beim Löschen dieser Datenbank gelöscht. Dies sollte die Notwendigkeit beseitigen, ibdata-Dateien in Ihrem Fall zu verkleinern/zu bereinigen.

Weitere Informationen zu mehreren Tablespaces:

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

Wenn Sie die InnoDB-Speicher-Engine für (einige) Ihrer MySQL-Tabellen verwenden, sind Sie wahrscheinlich bereits auf ein Problem mit der Standardkonfiguration gestoßen. Wie Sie vielleicht bemerkt haben, befindet sich in Ihrem MySQL-Datenverzeichnis (in Debian/Ubuntu -/var/lib/mysql) eine Datei mit dem Namen 'ibdata1'. Es enthält fast alle InnoDB-Daten (es ist kein Transaktionsprotokoll) der MySQL-Instanz und kann ziemlich groß werden. Standardmäßig hat diese Datei eine Anfangsgröße von 10 MB und wird automatisch erweitert. Unglücklicherweise können InnoDB-Datendateien nicht verkleinert werden. Das ist der Grund, warum DELETE, TRUNCATE, DROPs usw. den von der Datei belegten Speicherplatz nicht zurückfordern.

Ich denke, Sie können dort eine gute Erklärung und Lösung finden:

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

Skriptete schnell die Prozedur der akzeptierten Antwort in 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

Speichern Sie als purge_binlogs.sh und führen Sie als root aus.

Schließt mysql, information_schema, performance_schema (und binlog -Verzeichnis) aus.

Angenommen, Sie haben Administratorrechte in /root/.my.cnf und Ihre Datenbank befindet sich im Standardverzeichnis /var/lib/mysql.

Sie können auch Binärprotokolle löschen, nachdem Sie dieses Skript ausgeführt haben, um mehr Speicherplatz freizugeben.

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

Wenn Ihr Ziel darin besteht, den freien MySQL-Speicherplatz zu überwachen, und Sie MySQL nicht stoppen können, um Ihre ibdata-Datei zu verkleinern, dann rufen Sie sie über Tabellenstatusbefehle ab. Beispiel:

MySQL> 5.1.24:

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

MySQL <5.1.24:

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

Dann vergleichen Sie diesen Wert mit Ihrer ibdata-Datei:

du -b ibdata1

Quelle: http://dev.mysql.com/doc/refman/5.1/de/show-table-status.html

In einer neuen Version von mysql-server werden die obigen Rezepte die "mysql" -Datenbank zerstören. In der alten Version funktioniert es. In neuen Tabellen wird auf den Tabellentyp INNODB umgeschaltet, und Sie beschädigen sie dadurch. Am einfachsten ist es, alle Datenbanken zu sichern, mysql-server zu deinstallieren und die verbleibende my.cnf hinzuzufügen:

[mysqld]
innodb_file_per_table=1


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

Wie bereits erwähnt, können Sie ibdata1 nicht verkleinern (um dies zu tun, müssen Sie einen Speicherauszug erstellen und neu erstellen), aber dies ist häufig auch nicht unbedingt erforderlich.

Mit autoextend (wahrscheinlich die gebräuchlichste Größeneinstellung) reserviert ibdata1 den Speicher vor und wächst jedes Mal, wenn er fast voll ist. Dadurch werden Schreibvorgänge beschleunigt, da bereits Speicherplatz zugewiesen ist.

Wenn Sie Daten löschen, werden diese nicht verkleinert, aber der Speicherplatz in der Datei wird als nicht verwendet markiert. Wenn Sie nun neue Daten einfügen, wird der leere Speicherplatz in der Datei wiederverwendet, bevor die Datei weiter vergrößert wird.

Es wächst also nur weiter, wenn Sie diese Daten tatsächlich benötigen. Sofern Sie nicht tatsächlich den Speicherplatz für eine andere Anwendung benötigen, gibt es wahrscheinlich keinen Grund, ihn zu verkleinern.