{"id":3182,"date":"2011-01-10T17:46:21","date_gmt":"2011-01-10T16:46:21","guid":{"rendered":"http:\/\/kochsiek.org\/blog\/?p=3182"},"modified":"2011-09-25T14:32:52","modified_gmt":"2011-09-25T12:32:52","slug":"piwik-datenbanken-splitten","status":"publish","type":"post","link":"http:\/\/kochsiek.org\/blog\/2011\/01\/10\/piwik-datenbanken-splitten\/","title":{"rendered":"Piwik &#8211; Datenbanken splitten"},"content":{"rendered":"<p>Aus aktuellem Anlass will ich mal versuchen, hier eine Anleitung zum Splitten von <a title=\"Webseitenanalyse mit Piwik\" href=\"http:\/\/piwik.org\" target=\"_blank\">Piwik<\/a> Datenbanken festzuhalten. Vielleicht ist es ja f\u00fcr den ein oder andern mal ganz n\u00fctzlich.<\/p>\n<p>Hintergrund der ganzen Aktion ist, dass ich f\u00fcr 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\u00fcgung hatte, habe ich das Tracking der Besucher seiner Seiten \u00fcber eine Piwik-Installation in meinem Webhosting laufen lassen. Piwik hat ja nun die Eigenschaft, die genutzte MySQL Datenbank recht schnell ziemlich gro\u00df werden zu lassen.<br \/>\nNun hat der Hoster meines Bekannten aber <a title=\"All-Inkl.com\" href=\"http:\/\/kochsiek.org\/blog\/2011\/01\/04\/neue-tarife-bei-all-inkl-com\/\" target=\"_blank\">seine Tarife ge\u00e4ndert<\/a>, und so stehen jetzt statt nur einem GB ganze 10 GB zur Verf\u00fcgung. Das Tracking der Besucher mit Piwik kann also nun \u00fcber seinen eigenen Server laufen.<\/p>\n<p>Sch\u00f6n w\u00e4re es jetzt nat\u00fcrlich, wenn alle bisher in der Vergangenheit aufgelaufenen Daten, die bisher auf meinem Server liegen, auch auf seinem Server wieder zur Verf\u00fcgung stehen w\u00fcrden.<!--more--><\/p>\n<p><strong>Disclaimer: Obwohl hier \u00c4nderungen an Datenbanken gemacht werden, spielt sich der Gro\u00dfteil in einer tempor\u00e4ren Arbeits-DB ab. Hier kann eigentlich nicht viel kaputt gehen. Dennoch sollte man wissen, was man tut! Datensicherungen sind sicherlich von Vorteil \ud83d\ude42 Ich \u00fcbernehme keine Gew\u00e4hr oder Haftung f\u00fcr zerschossene Datenbanken und das damit einhergehende Leid der Betroffenen!<\/strong><\/p>\n<p>Wir haben also folgende Ausgangssituation:<\/p>\n<p>Server A (alt)<\/p>\n<ul>\n<li>MySQL Datenbank A<\/li>\n<li>Piwik-Installation A (Version 1.1.1)<\/li>\n<li>ID der beiden Webseiten, die exportiert werden sollen: 6 und 11<\/li>\n<\/ul>\n<p>Server B (neu)<\/p>\n<ul>\n<li>eigene, neue MySQL Datenbank B<\/li>\n<li>eigene, neue, leere Piwik-Installation B (Version 1.1.1)<\/li>\n<li>ID der beiden Webseiten, die importiert werden sollen: 1 und 2<\/li>\n<li>Der Tracking-Code auf den Webseiten verweist auf die Piwik-Installation auf Server A<\/li>\n<\/ul>\n<p>Die beiden Piwik-Installationen auf Server A und B m\u00fcssen nat\u00fcrlich in der selben Version vorliegen.<br \/>\nIn Piwik B sind die beiden zu trackenden Webseiten bereits angelegt worden und haben die IDs 1 und 2.<\/p>\n<p>Ich habe vor, zun\u00e4chst alle Daten zu kopieren, dann aus dieser Kopie die Daten rauszul\u00f6schen, die nicht auf Server B \u00fcbernommen werden sollen, die IDs der Webseiten in Piwik zu \u00e4ndern, und am Ende diese bearbeiteten Daten dann auf Server B einzuspielen.<\/p>\n<p>F\u00fcr alles, was mit Datenbanken, Tabellen, SQL-Abfragen usw. zu tun hat, nutze ich den <a title=\"MySQLDumper\" href=\"http:\/\/mysqldumper.de\" target=\"_blank\">MySQLDumper<\/a>.<\/p>\n<p>Kurz noch etwas zur Gr\u00f6\u00dfe der Ausgangsdatenbank auf Server A:<\/p>\n<ul>\n<li>Piwik installiert seit 17.01.2010<\/li>\n<li>Insgesamt 11 Webseiten zum Tracken angelegt, 2 davon sollen jetzt exportiert werden<\/li>\n<li>Eine der zu exportierenden Websites in Piwik wurde am 18.01.2010 angelegt<\/li>\n<li>Die andere wurde am 17.09.2010 angelegt<\/li>\n<li>Datens\u00e4tze in piwik_log_action: ca. 16.000<\/li>\n<li>Datens\u00e4tze in piwik_log_link_visit_action: ca. 252.000<\/li>\n<li>Datens\u00e4tze in piwik_log_visit: ca. 115.000<\/li>\n<li>Gr\u00f6\u00dfe dieser 3 Tabellen insgesamt: ca. 50 MB<\/li>\n<\/ul>\n<p>Als erstes lege ich nun auf dem Server A eine neue, leere MySQL Datenbank an. Diese wird als Arbeits-DB fungieren.<\/p>\n<p>Mit Hilfe des MySQLDumpers sichere ich nun folgende Tabellen meiner Piwik-Datenbank A<\/p>\n<ul>\n<li>piwik_goal<\/li>\n<li>piwik_log_action<\/li>\n<li>piwik_log_conversion<\/li>\n<li>piwik_log_link_visit_action<\/li>\n<li>piwik_log_visit<\/li>\n<li>piwik_pdf<\/li>\n<li>piwik_site<\/li>\n<li>piwik_site_url<\/li>\n<\/ul>\n<p>Die beiden Tabellen piwik_goal und piwik_log_conversion werden nur in dem Fall ben\u00f6tigt, wenn auch Ziele in Piwik (f\u00fcr die beiden zu \u00fcbernehmden Seiten) definiert sind.<\/p>\n<p><strong>Achtung:<\/strong> Aktuell scheint die \u00dcbernahme von Zielen noch nicht so ganz richtig zu funktionieren. Wenn ich herausgefunden habe, woran das liegt, werde ich das hier aktualisieren!<\/p>\n<p>Die so gesicherten Tabellen spiele ich in meiner Arbeits-DB auf Server A wieder ein (wie das genau funktioniert, erkl\u00e4rt sich bei Nutzung des MySQLDumpers eigentlich von selbst &#8211; falls nicht wird im <a title=\"MySQLDumper - Support Forum\" href=\"http:\/\/forum.mysqldumper.de\" target=\"_blank\">Support-Forum des Dumpers<\/a> gerne geholfen.).<\/p>\n<p>In dieser Arbeits-DB mache ich jetzt weiter. Ich will ja nur die Stammdaten- und Log-Eintr\u00e4ge f\u00fcr die beiden Seiten mit den IDs 6 und 11 \u00fcbernehmen. Also l\u00f6schen wir aus den entsprechenden Tabellen alles andere raus. Zus\u00e4tzlich 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.<\/p>\n<p>Mit dem SQL-Browser des MySQLDumpers f\u00fchre ich jetzt nacheinander folgende SQL-Statements aus. Jedes Statement einzeln!<\/p>\n<h5 style=\"text-align: left;\">DELETE FROM `piwik_site` WHERE `idsite` &lt;&gt; 6 AND `idsite` &lt;&gt; 11<br \/>\nUPDATE `piwik_site` SET `idsite` = 1 WHERE `idsite` = 6<br \/>\nUPDATE `piwik_site` SET `idsite` = 2 WHERE `idsite` = 11<\/h5>\n<h5 style=\"text-align: left;\">DELETE FROM `piwik_site_url` WHERE `idsite` &lt;&gt; 6 AND `idsite` &lt;&gt; 11<br \/>\nUPDATE `piwik_site_url` SET `idsite` = 1 WHERE `idsite` = 6<br \/>\nUPDATE `piwik_site_url` SET `idsite` = 2 WHERE `idsite` = 11<\/h5>\n<h5 style=\"text-align: left;\">DELETE FROM `piwik_goal` WHERE `idsite` &lt;&gt; 6 AND `idsite` &lt;&gt; 11<br \/>\nUPDATE `piwik_goal` SET `idsite` = 1 WHERE `idsite` = 6<br \/>\nUPDATE `piwik_goal` SET `idsite` = 2 WHERE `idsite` = 11<\/h5>\n<h5 style=\"text-align: left;\">DELETE FROM `piwik_log_conversion` WHERE `idsite` &lt;&gt; 6 AND `idsite` &lt;&gt; 11<br \/>\nUPDATE `piwik_log_conversion` SET `idsite` = 1 WHERE `idsite` = 6<br \/>\nUPDATE `piwik_log_conversion` SET `idsite` = 2 WHERE `idsite` = 11<\/h5>\n<h5 style=\"text-align: left;\">DELETE FROM `piwik_pdf` WHERE `idsite` &lt;&gt; 6 AND `idsite` &lt;&gt; 11<br \/>\nUPDATE `piwik_pdf` SET `idsite` = 1 WHERE `idsite` = 6<br \/>\nUPDATE `piwik_pdf` SET `idsite` = 2 WHERE `idsite` = 11<\/h5>\n<h5 style=\"text-align: left;\">DELETE FROM `piwik_log_visit` WHERE `idsite` &lt;&gt; 6 AND `idsite` &lt;&gt; 11<br \/>\nUPDATE `piwik_log_visit` SET `idsite` = 1 WHERE `idsite` = 6<br \/>\nUPDATE `piwik_log_visit` SET `idsite` = 2 WHERE `idsite` = 11<\/h5>\n<h5 style=\"text-align: left;\">DELETE FROM `piwik_log_link_visit_action` WHERE `idvisit` NOT IN (SELECT `idvisit` FROM `piwik_log_visit`)<br \/>\nUPDATE `piwik_log_link_visit_action` SET `idsite` = 1 WHERE `idsite` = 6<\/h5>\n<p>Das war soweit ganz einfach. Jetzt wird es etwas komplizierter. Das liegt an der Struktur von Piwik.<\/p>\n<p>In der Tabelle <em>piwik_log_link_visit_action<\/em> gibt es 3 Spalten, in denen jeweils Verweise auf einen Wert f\u00fcr <em>idaction<\/em> aus der Tabelle <em>piwik_log_action<\/em> drinstehen. Ich will jetzt jeden Eintrag aus der <em>piwik_log_action<\/em> rausl\u00f6schen, der nicht irgendwo in der <em>piwik_log_link_visit_action<\/em> verkn\u00fcpft ist.<\/p>\n<p>Dazu erstelle ich mir erst mal 2 tempor\u00e4re Tabellen<\/p>\n<h5 style=\"text-align: left;\">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`) )<\/h5>\n<h5 style=\"text-align: left;\">CREATE TABLE `piwik_temp2`( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `idaction` int(10) unsigned NOT NULL, PRIMARY KEY (`id`) )<\/h5>\n<p>Jetzt schreib ich in meine Tabelle <em>piwik_temp<\/em> alle <em>idaction<\/em> Werte aus der <em>piwik_log_link_visit_action<\/em> rein, die dort in 3 verschiedenen Spalten stehen. Am Ende habe ich dann in der <em>piwik_temp<\/em> alle diese Werte in nur einer Spalte.<\/p>\n<h5 style=\"text-align: left;\">INSERT INTO `piwik_temp` (`idlink_va`, `idaction`) SELECT `idlink_va`, `idaction_url` FROM `piwik_log_link_visit_action` WHERE `idaction_url` IS NOT NULL<\/h5>\n<h5 style=\"text-align: left;\">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<\/h5>\n<h5 style=\"text-align: left;\">INSERT INTO `piwik_temp` (`idlink_va`, `idaction`) SELECT `idlink_va`, `idaction_name` FROM `piwik_log_link_visit_action` WHERE `idaction_name` IS NOT NULL<\/h5>\n<p>Da ich jetzt aber in meiner tempor\u00e4ren Tabelle jede Menge doppelte Werte f\u00fcr <em>idaction<\/em> habe, schreibe ich in meine zweite Temp-Tabelle nun nur noch die eindeutigen Werte rein.<\/p>\n<h5 style=\"text-align: left;\">INSERT INTO `piwik_temp2` (`idaction`) SELECT DISTINCT `idaction` FROM `piwik_temp`<\/h5>\n<p>Jetzt kann ich aus der Tabelle <em>piwik_log_action<\/em> alle Zeilen l\u00f6schen, deren <em>idaction<\/em> nicht in meiner k\u00fcrzeren \u00dcbersicht in der zweiten Temp-Tabelle vorkommen.<\/p>\n<h5 style=\"text-align: left;\">DELETE FROM `piwik_log_action` WHERE `idaction` NOT IN (SELECT `idaction` FROM `piwik_temp2`)<\/h5>\n<p>Dieses L\u00f6schen kann etwas l\u00e4nger 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\u00e4ge drinstehen. MySQL muss nun f\u00fcr jeden der ca. 16.000 Eintr\u00e4ge aus der <em>piwik_log_action<\/em> pr\u00fcfen, ob die <em>idaction<\/em> in der <em>piwik_temp2<\/em> vorkommt. 16.000 * 5.000 = 80 Millionen Pr\u00fcfungen, die zwar jeweils nur Mili- oder Nanosekunden brauchen, aber in der Summe k\u00f6nnen da schon ein paar Minuten bei rauskommen. MySQL f\u00fchrt 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\u00dfen, einen Kaffee trinken gehen und den MySQLDumper neu aufrufen.<\/p>\n<p>Dieses Vorgehen ist sicher nicht der Weisheit letzter Schluss. Wenn jemand, der SQL spricht, denkt und tr\u00e4umt den ein oder anderen Verbesserungsvorschlag hat, bin ich daf\u00fcr nat\u00fcrlich sehr dankbar und werde das hier auch kundtun.<\/p>\n<p>Am Ende l\u00f6sche ich noch die beiden tempor\u00e4ren Tabellen.<\/p>\n<h5 style=\"text-align: left;\">DROP TABLE `piwik_temp`<br \/>\nDROP TABLE `piwik_temp2`<\/h5>\n<p>Die so bearbeiteten Tabellen sichere ich jetzt wieder mit dem MySQLDumper und \u00fcbertrage die erzeugte Datei auf den neuen Server B. Dort w\u00e4hle 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 &#8211; bis auf die <em>piwik_site*<\/em> eh&#8216; leer).<\/p>\n<p>Nun rufe ich Piwik auf dem neuen Server B \u00fcber das Web-Interface ganz normal auf. Sobald ich einen entsprechenden Zeitraum ausgew\u00e4hlt habe, werden die Tabellen <em>piwik_archive_*<\/em> neu erzeugt. Das kann beim ersten Aufruf nat\u00fcrlich etwas l\u00e4nger dauern. Man sollte sich einmal in der Tages-Ansicht soweit in die Vergangenheit klicken, wie man w\u00fcnscht, Auswertungen schneller zur Verf\u00fcgung zu haben.<\/p>\n<p>Was jetzt nat\u00fcrlich noch gemacht werden muss ist, den Tracking-Code auf den zu trackenden Webseiten gegen den aus der neuen Piwik-Installation auszutauschen. Ansonsten w\u00fcrden neue Besucher nat\u00fcrlich weiterhin in die Statistik auf dem alten Server reinlaufen.<\/p>\n<p>Das einzige Manko bei dieser \u00dcbernahme ist momentan noch, dass Ziele (Tabelle <em>piwik_goal<\/em> und <em>piwik_log_conversion<\/em>) aus mir nicht bekannten Gr\u00fcnden nicht mehr angezeigt werden. Wenn ich das rausbekommen habe, werde ich das hier nat\u00fcrlich aktualisieren.<\/p>\n<p>\u00dcber Anmerkungen in den Kommentaren w\u00fcrde ich mich nat\u00fcrlich sehr freuen.<\/p>\n<p>HTH<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Aus aktuellem Anlass will ich mal versuchen, hier eine Anleitung zum Splitten von Piwik Datenbanken festzuhalten. Vielleicht ist es ja f\u00fcr den ein oder andern mal ganz n\u00fctzlich. Hintergrund der ganzen Aktion ist, dass ich f\u00fcr einen Bekannten mal eine &hellip; <a href=\"http:\/\/kochsiek.org\/blog\/2011\/01\/10\/piwik-datenbanken-splitten\/\">Weiterlesen <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[8,935,1586],"tags":[1644,616,615,614,1438,1402],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/kochsiek.org\/blog\/wp-json\/wp\/v2\/posts\/3182"}],"collection":[{"href":"http:\/\/kochsiek.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/kochsiek.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/kochsiek.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/kochsiek.org\/blog\/wp-json\/wp\/v2\/comments?post=3182"}],"version-history":[{"count":26,"href":"http:\/\/kochsiek.org\/blog\/wp-json\/wp\/v2\/posts\/3182\/revisions"}],"predecessor-version":[{"id":3655,"href":"http:\/\/kochsiek.org\/blog\/wp-json\/wp\/v2\/posts\/3182\/revisions\/3655"}],"wp:attachment":[{"href":"http:\/\/kochsiek.org\/blog\/wp-json\/wp\/v2\/media?parent=3182"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/kochsiek.org\/blog\/wp-json\/wp\/v2\/categories?post=3182"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/kochsiek.org\/blog\/wp-json\/wp\/v2\/tags?post=3182"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}