Export of database with phpmyadmin fails


#1

We are currently planning to migrate our server to a new hoster and I have troubles with exporting piwik database. The exported dump is incomplete.

Since we do not have root access to the machines, we have to use phpmyadmin to do the export. gzip complains that the exported gz files are faulty. The tables are MyISAM and it seems that the problematic tables are piwik_log_link_visit_action and piwik_log_visit. They both have about 1.1 GB size. I tried several times but I can’t export them.

We have several thousand visitors a day and there is constantly traffic that leads to updates of those tables. I guess, this traffic simply is too much for the export feature of phpmyadmin.

I thought about disabling piwik for a short while and export during that hour or so. Would that help?

And would you recommend to convert piwik to innodb? (During import, after a successful export) We have about 6000 visitors per day, with a couple of pageviews per visitor.

Thanks for any tips.


#2

I tried to disable piwik database access now using:
[Tracker]
record_statistics = 0

But I still couldn’t export the database. I don’t understand why. Any ideas? Please note, I am stuck with phpmyadmin, no shell access.

To be more precise, I can’t export piwik_log_link_visit_action.
11,382,051 entries, type MyISAM, size 1,1 GiB

Thanks.


(Matthieu Aubry) #3

I’ve never tried to export 1 Gb DB with phpMyadmin but I suppose ti doesnt work well - please ask your webhost to send you a dump of the database. Using Piwik 1.8 you can also delete old reports to free some space. In the next version we will also work on space usage improvements, stay tuned


(Thomas Seifert) #4

phpmyadmin won’t work with such large databases. if you can’t get a real dump from your host, you could try mysqldumper ( http://www.mysqldumper.net/ ) which is made for exporting large database on limited hosting packages.


#5

Thanks alot for your help.

I’ve managed to export the db now using mysqldumper. Worked like a charm. Very nice tool! Got a 3 gigabyte dump. I guess, I could’ve asked my hoster, but I like it better if I am able to do it myself.

Question: Would you recommend MyISAM or InnoDB for a DB this size?. We started using piwik February 2011 and we can assume that traffic will still increase.