MySQL Back Up and Restore

Question

I trying to migrate my Expression Engine site from one sever to another.

I was using MySQL version 5.1 but on my new server I’m using version 5.5.

I've just copied over the Database from the Data Directory on the old server to the new and my site wouldn't function correctly.

When I restore my Database with a MySQL Dump file from the old server, the site works.

Is there an issue with copying and pasting a MySQL database from one server to another and why is the MySQL Dump restore not effected by the same issue?

I'm trying to use this information to provide a good backup solution.

Answer 1

I haven't looked into the details, but the MySQL data files are binary files and may not be directly compatible between versions. The files produced by MySQLdump are simply a list of SQL instructions to recreate the database, so they will be more compatible between versions, though not necessarily backwards compatible, so you may have trouble going from 5.5 -> 5.1 if you ever had to.

Answer 2

The best backup solution is to dump the SQL rather than copying files.

mysqldump utility provides a full dump of all of the data that is compatible (IIRC) all the way down to MySQL 4.0 using it's conditional statements.

I have a feeling that the reason copying data directories between 5.1 and 5.5 is because the engines may have changed the way that they store their data. I had an issue like this related to InnoDB and it was a pain in the behind. Long story short I never got the data back and unfortunately didn't have an SQL Dump (the hard drive failed!).

Anyway at the end of the day, mysqldump is the best tool to use as it creates compatible SQL statements which should work across a whole plethora of MySQL Versions, both past and future.

Answer 3

The dump and restore method works because the dump file is basically just a bunch of "CREATE TABLE" and "INSERT" Statements. The dump and restore rebuilds the database from scratch using basic SQL statements. Copy and pasting doesn't work because you are moving between different versions of the database with different data formats. If you are moving between machines with the same version of MySQL and the same configuration, then simply copying the data directory can work, assuming you do it properly. Using the dump / restore method is the most reliable way, but is often very time consuming on large (think 50 GB or even much larger) databases.