NexusDB

These days most CMS, store solutions, forums, etc run with MySQL databases - so do our solutions (Drupal, VBForums, Mantis, MediaWiki). Thus an integral part of relocating services is to get the databases across to the new server.

One tends to think: "No issues. MySQL has simple backup or dump functionality". Correct, it has. And they work fine. Until you actually try to restore your data. Then you suddenly realize that something is just not quite right.

Running a restore of the full dump of all our databases ends in a fatal error after about 15k lines of SQL. The dump file has a total of 130k lines, so there we have a big problem. Tried to dump again, restore again -> same issue. Ok, different approach.

I tried the MySQL administrator and create a backup file of all databases. A very long time later we were ready for restore. Peng! Similar issue, this time after just 3% of restore. Hm, maybe mysql simply can't handle the 1.2 gigabyte file???

Next idea: back up and restore individual databases. First database (wiki) works fine. Great! On to the web site. Kaboooom. Same issue again. Tried forums, mantis - same problem.

This was really becoming a major issue. How the heck should we get the databases over to the new server save of creating our own backup / restore solution?

Well, got pretty desperate. Stopped MySQL, tried a simply file copy of the full data directory including the InnoDB store etc. After a lengthy copy (InnoDB is HUGE!) we finally had everything on the new server.

Next thing is starting MySQL on the new server. Error - can't start. Ouch. A bit of investigating later it turns out that MySQL chokes on the InnoDB log files (there are one or more of them depending on several things). Nothing to lose anyway, just deleted them and tried to restart.

Dada! We've lift off. MySQL is up and running and a review of ISAM and InnoDB tables shows that they are ok, even though the MySQL is muttering something of "possible database corruption". To be safe we did a repair and optimize of all databases/tables and now have all databases fully up and running on the new server. Great. On to new adventures.