mysql - titel - title tag




Wie man die Datei ibdata1 in MySQL verkleinert/löscht (5)

Ich benutze MySQL in localhost als "Abfrage-Tool" zur Durchführung von Statistiken in R, dh jedes Mal wenn ich ein R-Skript ausführe, erstelle ich eine neue Datenbank (A), erstelle eine neue Tabelle (B), importiere die Daten in B. , reiche eine Anfrage ein, um zu bekommen, was ich brauche, und dann lasse ich B fallen und lasse A fallen.

Es funktioniert gut für mich, aber ich merke, dass die Größe der ibdata-Datei schnell zunimmt, ich habe nichts in MySQL gespeichert, aber die Datei ibdata1 hat bereits 100 MB überschritten.

Ich benutze mehr oder weniger Standard-MySQL-Einstellung für das Setup, gibt es eine Möglichkeit für ich kann die ibdata1-Datei nach einer bestimmten Zeit automatisch verkleinern / löschen?


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 in Ihrem MySQL-Datenverzeichnis (in Debian / Ubuntu - / var / lib / mysql) bemerkt haben, liegt eine Datei namens 'ibdata1'. Es enthält fast alle InnoDB-Daten (es ist kein Transaktionslog) der MySQL-Instanz und könnte ziemlich groß werden. Standardmäßig hat diese Datei eine Anfangsgröße von 10 MB und wird automatisch erweitert. Leider können InnoDB-Datendateien nicht verkleinert werden. Das ist der Grund, warum DELETEs, TRUNCATEs, 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/


Hinzufügen zu John P's Antwort ,

Für ein Linux-System können die Schritte 1-6 mit folgenden 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 lösche alle anderen ib_logfiles, die ib_logfile0 , ib_logfile1 usw. ib_logfile1 )
  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 auf dieser mysql-Instanz haben. Stellen Sie sicher, dass die Schritte 1, 2 und 6, 7 so geändert wurden, dass sie alle Datenbanken abdecken, die Sie behalten möchten.


Wenn Sie innodb -Tabellen löschen, gibt MySQL den Speicherplatz in der ibdata-Datei nicht frei, weshalb es weiter wächst. Diese Dateien schrumpfen kaum.

So verkleinern Sie eine vorhandene ibdata-Datei:

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

Sie können dies skripten und planen, dass das Skript nach einer bestimmten Zeit ausgeführt wird, aber für das oben beschriebene Setup scheint es, dass mehrere Tablespaces eine einfachere Lösung darstellen.

Wenn Sie die Konfigurationsoption innodb_file_per_table , 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 entfernen, ibdata-Dateien in Ihrem Fall zu verkleinern / zu entfernen.

Weitere Informationen zu mehreren Tablespaces:

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


Wenn es Ihr Ziel ist, den freien Speicherplatz von MySQL zu überwachen, und Sie können MySQL nicht daran hindern, Ihre ibdata-Datei zu verkleinern, dann holen Sie sich die Tabellenstatusbefehle. Beispiel:

MySQL> 5.1.24:

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

MySQL <5.1.24:

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

Dann vergleiche diesen Wert mit deiner ibdata-Datei:

du -b ibdata1

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


Dass ibdata1 nicht schrumpft, ist eine besonders nervige Eigenschaft von MySQL. Die ibdata1 Datei kann nicht wirklich geschrumpft werden, wenn Sie nicht alle Datenbanken löschen, die Dateien entfernen und einen Dump neu laden.

Sie können MySQL jedoch so konfigurieren, dass jede Tabelle einschließlich ihrer Indizes als separate Datei gespeichert wird. Auf diese Weise wird ibdata1 nicht so groß werden. Laut Bill Karwins Kommentar ist dies standardmäßig ab Version 5.6.6 von MySQL aktiviert.

Es ist eine Weile her, dass ich das gemacht habe. Um jedoch Ihren Server so einzurichten, dass er für jede Tabelle separate Dateien verwendet, müssen Sie my.cnf ändern, um dies zu aktivieren:

[mysqld]
innodb_file_per_table=1

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

Da Sie den Speicherplatz von ibdata1 zurückfordern möchten, ibdata1 Sie die Datei tatsächlich löschen:

  1. mysqldump Sie einen mysqldump aller Datenbanken, Prozeduren, Trigger usw. mit Ausnahme der Datenbanken mysql und performance_schema
  2. Löschen Sie alle Datenbanken außer den oben genannten 2 Datenbanken
  3. Stoppen Sie MySQL
  4. Löschen ib_log Dateien ibdata1 und ib_log
  5. Starten Sie MySQL
  6. Wiederherstellen von Dump

Wenn Sie MySQL in Schritt 5 ib_log Dateien ibdata1 und ib_log neu erstellt.

Jetzt bist du fit zu gehen. Wenn Sie eine neue Datenbank zur Analyse erstellen, befinden sich die Tabellen in separaten ibd* -Dateien, nicht in ibdata1 . Wenn Sie die Datenbank bald danach löschen, werden die ibd* -Dateien gelöscht.

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

Sie haben das wahrscheinlich schon gesehen:
http://bugs.mysql.com/bug.php?id=1341

Mit dem Befehl ALTER TABLE <tablename> ENGINE=innodb oder OPTIMIZE TABLE <tablename> kann man Daten und Indexseiten von ibdata1 zu separaten Dateien extrahieren. Allerdings wird ibdata1 nicht verkleinert, es sei denn, Sie führen die obigen Schritte aus.

Bezüglich des information_schema ist das nicht notwendig und auch nicht möglich. Es ist in der Tat nur eine Reihe von schreibgeschützten Ansichten, keine Tabellen. Und ihnen sind keine Dateien zugeordnet, nicht einmal ein Datenbankverzeichnis. Das informations_schema verwendet den Speicher db-engine und wird beim Stoppen / Neustart von mysqld gelöscht und neu generiert. Siehe https://dev.mysql.com/doc/refman/5.7/en/information-schema.html .





innodb