Aus aktuellem Anlass will ich mal versuchen, hier eine Anleitung zum Splitten von Piwik Datenbanken festzuhalten. Vielleicht ist es ja für den ein oder andern mal ganz nützlich.
Hintergrund der ganzen Aktion ist, dass ich für einen Bekannten mal eine Webseite gemacht habe (ok, eigentlich waren es zwei Seiten). Da er in seinem Webhosting-Paket nur eine begrenzte Anzahl an MySQL Datenbanken und auch nur begrenzten Speicherplatz zur Verfügung hatte, habe ich das Tracking der Besucher seiner Seiten über eine Piwik-Installation in meinem Webhosting laufen lassen. Piwik hat ja nun die Eigenschaft, die genutzte MySQL Datenbank recht schnell ziemlich groß werden zu lassen.
Nun hat der Hoster meines Bekannten aber seine Tarife geändert, und so stehen jetzt statt nur einem GB ganze 10 GB zur Verfügung. Das Tracking der Besucher mit Piwik kann also nun über seinen eigenen Server laufen.
Schön wäre es jetzt natürlich, wenn alle bisher in der Vergangenheit aufgelaufenen Daten, die bisher auf meinem Server liegen, auch auf seinem Server wieder zur Verfügung stehen würden.
Disclaimer: Obwohl hier Änderungen an Datenbanken gemacht werden, spielt sich der Großteil in einer temporären Arbeits-DB ab. Hier kann eigentlich nicht viel kaputt gehen. Dennoch sollte man wissen, was man tut! Datensicherungen sind sicherlich von Vorteil 🙂 Ich übernehme keine Gewähr oder Haftung für zerschossene Datenbanken und das damit einhergehende Leid der Betroffenen!
Wir haben also folgende Ausgangssituation:
Server A (alt)
- MySQL Datenbank A
- Piwik-Installation A (Version 1.1.1)
- ID der beiden Webseiten, die exportiert werden sollen: 6 und 11
Server B (neu)
- eigene, neue MySQL Datenbank B
- eigene, neue, leere Piwik-Installation B (Version 1.1.1)
- ID der beiden Webseiten, die importiert werden sollen: 1 und 2
- Der Tracking-Code auf den Webseiten verweist auf die Piwik-Installation auf Server A
Die beiden Piwik-Installationen auf Server A und B müssen natürlich in der selben Version vorliegen.
In Piwik B sind die beiden zu trackenden Webseiten bereits angelegt worden und haben die IDs 1 und 2.
Ich habe vor, zunächst alle Daten zu kopieren, dann aus dieser Kopie die Daten rauszulöschen, die nicht auf Server B übernommen werden sollen, die IDs der Webseiten in Piwik zu ändern, und am Ende diese bearbeiteten Daten dann auf Server B einzuspielen.
Für alles, was mit Datenbanken, Tabellen, SQL-Abfragen usw. zu tun hat, nutze ich den MySQLDumper.
Kurz noch etwas zur Größe der Ausgangsdatenbank auf Server A:
- Piwik installiert seit 17.01.2010
- Insgesamt 11 Webseiten zum Tracken angelegt, 2 davon sollen jetzt exportiert werden
- Eine der zu exportierenden Websites in Piwik wurde am 18.01.2010 angelegt
- Die andere wurde am 17.09.2010 angelegt
- Datensätze in piwik_log_action: ca. 16.000
- Datensätze in piwik_log_link_visit_action: ca. 252.000
- Datensätze in piwik_log_visit: ca. 115.000
- Größe dieser 3 Tabellen insgesamt: ca. 50 MB
Als erstes lege ich nun auf dem Server A eine neue, leere MySQL Datenbank an. Diese wird als Arbeits-DB fungieren.
Mit Hilfe des MySQLDumpers sichere ich nun folgende Tabellen meiner Piwik-Datenbank A
- piwik_goal
- piwik_log_action
- piwik_log_conversion
- piwik_log_link_visit_action
- piwik_log_visit
- piwik_pdf
- piwik_site
- piwik_site_url
Die beiden Tabellen piwik_goal und piwik_log_conversion werden nur in dem Fall benötigt, wenn auch Ziele in Piwik (für die beiden zu übernehmden Seiten) definiert sind.
Achtung: Aktuell scheint die Übernahme von Zielen noch nicht so ganz richtig zu funktionieren. Wenn ich herausgefunden habe, woran das liegt, werde ich das hier aktualisieren!
Die so gesicherten Tabellen spiele ich in meiner Arbeits-DB auf Server A wieder ein (wie das genau funktioniert, erklärt sich bei Nutzung des MySQLDumpers eigentlich von selbst – falls nicht wird im Support-Forum des Dumpers gerne geholfen.).
In dieser Arbeits-DB mache ich jetzt weiter. Ich will ja nur die Stammdaten- und Log-Einträge für die beiden Seiten mit den IDs 6 und 11 übernehmen. Also löschen wir aus den entsprechenden Tabellen alles andere raus. Zusätzlich haben meine beiden Seiten in der neuen Piwik-Installation B jetzt ja andere ID Nummern bekommen. Also wird die ID 6 zu ID 1 und aus ID 11 wird ID 2.
Mit dem SQL-Browser des MySQLDumpers führe ich jetzt nacheinander folgende SQL-Statements aus. Jedes Statement einzeln!
DELETE FROM `piwik_site` WHERE `idsite` <> 6 AND `idsite` <> 11
UPDATE `piwik_site` SET `idsite` = 1 WHERE `idsite` = 6
UPDATE `piwik_site` SET `idsite` = 2 WHERE `idsite` = 11
DELETE FROM `piwik_site_url` WHERE `idsite` <> 6 AND `idsite` <> 11
UPDATE `piwik_site_url` SET `idsite` = 1 WHERE `idsite` = 6
UPDATE `piwik_site_url` SET `idsite` = 2 WHERE `idsite` = 11
DELETE FROM `piwik_goal` WHERE `idsite` <> 6 AND `idsite` <> 11
UPDATE `piwik_goal` SET `idsite` = 1 WHERE `idsite` = 6
UPDATE `piwik_goal` SET `idsite` = 2 WHERE `idsite` = 11
DELETE FROM `piwik_log_conversion` WHERE `idsite` <> 6 AND `idsite` <> 11
UPDATE `piwik_log_conversion` SET `idsite` = 1 WHERE `idsite` = 6
UPDATE `piwik_log_conversion` SET `idsite` = 2 WHERE `idsite` = 11
DELETE FROM `piwik_pdf` WHERE `idsite` <> 6 AND `idsite` <> 11
UPDATE `piwik_pdf` SET `idsite` = 1 WHERE `idsite` = 6
UPDATE `piwik_pdf` SET `idsite` = 2 WHERE `idsite` = 11
DELETE FROM `piwik_log_visit` WHERE `idsite` <> 6 AND `idsite` <> 11
UPDATE `piwik_log_visit` SET `idsite` = 1 WHERE `idsite` = 6
UPDATE `piwik_log_visit` SET `idsite` = 2 WHERE `idsite` = 11
DELETE FROM `piwik_log_link_visit_action` WHERE `idvisit` NOT IN (SELECT `idvisit` FROM `piwik_log_visit`)
UPDATE `piwik_log_link_visit_action` SET `idsite` = 1 WHERE `idsite` = 6
Das war soweit ganz einfach. Jetzt wird es etwas komplizierter. Das liegt an der Struktur von Piwik.
In der Tabelle piwik_log_link_visit_action gibt es 3 Spalten, in denen jeweils Verweise auf einen Wert für idaction aus der Tabelle piwik_log_action drinstehen. Ich will jetzt jeden Eintrag aus der piwik_log_action rauslöschen, der nicht irgendwo in der piwik_log_link_visit_action verknüpft ist.
Dazu erstelle ich mir erst mal 2 temporäre Tabellen
CREATE TABLE `piwik_temp`( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `idlink_va` int(10) unsigned NOT NULL, `idaction` int(10) unsigned NOT NULL, PRIMARY KEY (`id`) )
CREATE TABLE `piwik_temp2`( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `idaction` int(10) unsigned NOT NULL, PRIMARY KEY (`id`) )
Jetzt schreib ich in meine Tabelle piwik_temp alle idaction Werte aus der piwik_log_link_visit_action rein, die dort in 3 verschiedenen Spalten stehen. Am Ende habe ich dann in der piwik_temp alle diese Werte in nur einer Spalte.
INSERT INTO `piwik_temp` (`idlink_va`, `idaction`) SELECT `idlink_va`, `idaction_url` FROM `piwik_log_link_visit_action` WHERE `idaction_url` IS NOT NULL
INSERT INTO `piwik_temp` (`idlink_va`, `idaction`) SELECT `idlink_va`, `idaction_url_ref` FROM `piwik_log_link_visit_action` WHERE `idaction_url_ref` IS NOT NULL
INSERT INTO `piwik_temp` (`idlink_va`, `idaction`) SELECT `idlink_va`, `idaction_name` FROM `piwik_log_link_visit_action` WHERE `idaction_name` IS NOT NULL
Da ich jetzt aber in meiner temporären Tabelle jede Menge doppelte Werte für idaction habe, schreibe ich in meine zweite Temp-Tabelle nun nur noch die eindeutigen Werte rein.
INSERT INTO `piwik_temp2` (`idaction`) SELECT DISTINCT `idaction` FROM `piwik_temp`
Jetzt kann ich aus der Tabelle piwik_log_action alle Zeilen löschen, deren idaction nicht in meiner kürzeren Übersicht in der zweiten Temp-Tabelle vorkommen.
DELETE FROM `piwik_log_action` WHERE `idaction` NOT IN (SELECT `idaction` FROM `piwik_temp2`)
Dieses Löschen kann etwas länger dauern. Eventuell sieht es auch so aus, als ob der MySQLDumper sich in einer Endlosschleife befindet. In meinem Fall hatte ich in der zweiten Temp-Tabelle am Ende ca. 5.000 Einträge drinstehen. MySQL muss nun für jeden der ca. 16.000 Einträge aus der piwik_log_action prüfen, ob die idaction in der piwik_temp2 vorkommt. 16.000 * 5.000 = 80 Millionen Prüfungen, die zwar jeweils nur Mili- oder Nanosekunden brauchen, aber in der Summe können da schon ein paar Minuten bei rauskommen. MySQL führt dies auch aus, es kann halt nur sein, dass das Web-Interface des Dumpers da nicht mitmacht. In dem Fall einfach den Web-Browser schließen, einen Kaffee trinken gehen und den MySQLDumper neu aufrufen.
Dieses Vorgehen ist sicher nicht der Weisheit letzter Schluss. Wenn jemand, der SQL spricht, denkt und träumt den ein oder anderen Verbesserungsvorschlag hat, bin ich dafür natürlich sehr dankbar und werde das hier auch kundtun.
Am Ende lösche ich noch die beiden temporären Tabellen.
DROP TABLE `piwik_temp`
DROP TABLE `piwik_temp2`
Die so bearbeiteten Tabellen sichere ich jetzt wieder mit dem MySQLDumper und übertrage die erzeugte Datei auf den neuen Server B. Dort wähle ich die neue, leere Piwik-Datenbank aus und stelle die soeben gesicherten Tabellen in die neue Datenbank wieder her. Die dort bestehenden Tabellen werden dabei ersetzt (waren aber – bis auf die piwik_site* eh‘ leer).
Nun rufe ich Piwik auf dem neuen Server B über das Web-Interface ganz normal auf. Sobald ich einen entsprechenden Zeitraum ausgewählt habe, werden die Tabellen piwik_archive_* neu erzeugt. Das kann beim ersten Aufruf natürlich etwas länger dauern. Man sollte sich einmal in der Tages-Ansicht soweit in die Vergangenheit klicken, wie man wünscht, Auswertungen schneller zur Verfügung zu haben.
Was jetzt natürlich noch gemacht werden muss ist, den Tracking-Code auf den zu trackenden Webseiten gegen den aus der neuen Piwik-Installation auszutauschen. Ansonsten würden neue Besucher natürlich weiterhin in die Statistik auf dem alten Server reinlaufen.
Das einzige Manko bei dieser Übernahme ist momentan noch, dass Ziele (Tabelle piwik_goal und piwik_log_conversion) aus mir nicht bekannten Gründen nicht mehr angezeigt werden. Wenn ich das rausbekommen habe, werde ich das hier natürlich aktualisieren.
Über Anmerkungen in den Kommentaren würde ich mich natürlich sehr freuen.
HTH
