MySQL Magic und datadir Wechsel

Manchmal hat man es mit merkwürdigen Verhaltnsweisen einer Datenbank wie MySQL zu tun.

So zum Beispeil wenn die Engine InnoDB genutzt wird. Diese ist total toll und auch transaktionssicher. Nur brauchen die wenigsten Anwendungen diese Sicherheit und wollen lieber schneller und/oder schlanker sein.

An dieser Stelle wird man bei InnoDB mit großen Dateigrößen überrascht und bekommt mit unter Performanceschwierigkeiten. Ich habe mir viele Gedanken dazu wie man etwas derartiges Lösen kann. Fest steht, dass die Datenbanken und mehrere Faktoren größer sind und man nur bedingt die Möglichkeit bestitzt hier zu tunen ohne die Hardware anzupassen (zumindest ab einer gewissen Größenordnung).

Normalerweise sollte der mysqlcheck Befehl dabei helfen die Geschwindigkeit und „Ordentlichkeit“ zumindest ein kleines Stück weit bezubehalten.

Nicht immer ist das aber ausreichend um die Dateigröße und auch die Geschwindigkeit zu steigern. Letztendlich habe ich keinen anderen Weg gefunden als die komplette Datenbank zu exportieren und anschließend wieder zu importieren. Das Ergebnis vor weg ist eine um den Faktor 13 kleinere Datenbank geworden.

Zunächst sollte man sich vor Augen führen, wie große die Datenbanken wirklich sind:

mysql -u root -p -e ’select table_schema „Database“,round(sum(data_length+index_length)/1024/1024,4) „Size (MB)“ from information_schema.tables group by table_schema;‘ > /root/databasesize.txt

In der Datei databasesize.txt stehen alle Datenbanken mit Namen und der entsprechenden Größe.

Zunächst einmal werden alle Datenbanken gecheckt:

  • mysqlcheck –all-databases -u root -p –optimize –auto-repair

Anschließend wird die Datenbank exportiert

  • mysqlcheck –all-databases -u root -p –all-databases > /root/Sicherung.sql

Ist die Sicherung erledigt fährt man den MySQL dienst herunter

/etc/init.d/mysql stop

und kann man sich (vielleicht auch zur Sicherheit) einen neuen Speicherort für die Datenbanken aussuchen. Diese stellt man in der Datei /etc/mysql/my.cnf ein. Dort legt man sich einfach einen neuen Eintrag für das datadir an.

#datadir = /var/lib/mysql
datadir = /neuer/speicherort/lib/mysql

Möchte man bei InnoDB bleiben, so kann es interessant sein den Eintrag

innodb_file_per_table = 1

in der Sektion [mysqld] hinzuzufügen.

Wichtig ist, dass der neue Speicherort auch von dem Benutzer und der Gruppe mysql genutzt werden kann  (chmod mysql:mysql /neuer/speicherort/lib/mysql). Dorthin kopiert man die vorhandene MySQL Datenbank

cp /var/lib/mysql/mysql /neuer/speicherort/lib/mysql

Somit kann man sich sicher sein, dass man sich auch wieder einloggen kann. Nun kann man den den Dienst wieder starten

/etc/init.d/mysql start

und die Datenbank einlesen:

mysql -u root -p < /root/Sicherung.sql

Man kann nun zuschauen, wie die Datenbankan erneut angelegt werden.

Da ich hier beschreibe wie man von zum Beispiel InnoDB auf MyISAM kommt, fehlt noch die Konvertierung der Tabelle auf das gewünscht Format. Dazu lassen wir uns von MySQL ein Skript generieren:

mysql -u root -p -e ’select concat(‚alter table ‚,table_schema,‘.‘,table_name,‘ engine=MyISAM;‘) from information_schema.tables where engine = ‚InnoDB“ > /root/altertabletomyisam.sql

und lesen es erneut ein:

mysql -u root -p < /root/altertabletomyisam.sql

Nun rattert MySQL die ganzen Tabellen durch und erzeugt uns feine MyISAM Tabellen.

Wenn man genug Platz hat, dann kannn man den letzten Schritt schon vor dem Export der Datenbank machen, denn es werden neue Tabellen erzeugt und die alten nicht gelöscht.

Es gibt natürlich noch andere Wege. Einen habe ich hier gefunden.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.