Copying MySQL Databases to a New MySQL Installation

These instructions describe how to copy databases from an old MySQL installation to a new one. They assume you have a working installation, with users configured to have proper access to existing data.

First, create a user that has write access in the new installation. If the old installation is on a different machine than the new one, use the old machine's hostname. Keep the single-quotes in there, especially if your chosen password has special characters in it. Do this after logging into the new MySQL installation.

grant all on *.* to '[user]'@'[old_host]' identified by '[password]';

Test that the user works. Try logging into the new installation from the old machine. Specifying the "-p" flag makes the client prompt you for your password. Enter the password you assigned above.

mysql -u [user] -h [new_host] -p

Use mysqldump to copy databases from the old installation to the new one. Do this from the old machine.

mysqldump takes the same options as mysql for logging in (-u, -h, -p). Use the appropriate values to login to your old installation. The command then pipes the output of mysqldump to the connection that's logged into the new installation. Similarly, specify the appropriate options to allow you to login.

The "DB1 [DB2 ...]" arguments are the names of the databases you want to copy. I made sure not to copy the "information_schema" database, because I didn't know how that would function under a different version of the server. Also, I didn't copy the "mysql" database, because that contains all the users and their privileges, and I wasn't sure if I wanted to duplicate all the users on the new server. But that might be a good idea in many situations; it depends on what you're going to use the new installation for. The point is, don't just blindly use the "--all-databases" argument to mysqldump.

mysqldump -u [old_user] -h [old_host] --databases DB1 [DB2 ...] | mysql -u [user] -h [new_host] -p

To simplify the command line, first make sure the MySQL username and password are the same for both the old and new installations. (Just create the new user in the first step with the same credentials as the existing user on the old installation.) Second, make sure you have a .my.cnf file in your home directory that contains these credentials in the [client] section. The simplified command line becomes:

mysqldump --databases DB1 [DB2 ...] | mysql -h [new_host]

Note that you still must specify the new machine's hostname when opening the connection to the destination server. Otherwise, you will just try to insert all the old data into the old database, where it already exists. That would probably cause lots of problems. Don't do it.