Upgrade/move a MySql installation

It sometimes happens that you need to move a MySql database but you do not have a proper backup. It is quite possible to copy the raw dataabase file to a new instlalation, provided that you are careful.

Method 1

I assume that you are copying to a pristine instalation. If there are live databases in the target installation you should not do this: the step of copying the 'ib*' files could mess things up.

1. Make a list of all the databases and users that you want to move.
2. In the new server connect to the 'mysql' database as root and issue a 'create database' command for each DB that you want to move.
3. Issue a GRANT for each user, taking care to include the "@'localhost'" clause on the user-name, and the ''identified by' clause, e.g.

   grant all to 'myuser'@'localhost' identified by 'userpassword';

   Then issue 'flush privileges'.

   You have now primed the system.

4. Copy the 'ib*' files from the root of the old data directory. Don't worry about over-writing any existing files. Without this you will be unable to access INNODB tables.
5. Copy the old database directories on top of the newly created locations.
6. Check that everything is owned by mysql:mysql
7. You may like to restart the server, but it should not be necessary.

You should now be able to access the copied DBs. There should be no need to run 'mysql_upgrade' unless there have been major changes in the server.

Method 2

A cleaner, but slower, way to do this is:

1. Stop the old DB
2. Use "mysqldump" to dump the whole DB and the schema. This will write the whole DB as a SQL stream to a text file, which can be very large.
3. Load the dump file into the new DB