UPDATED: A simple method of transferring MySQL Database(s) and Tables between servers

We’ve recently been working on a Web-Server migration for a regular client at work, part of which involved transferring the MySQL Databases (or strictly the schemas) for each of their websites from their old Web-Server to the shiny new replacement. In doing this, I rediscovered a really handy method which really simplifies transferring MySQL data between servers, it goes something like this:

mysqldump -u SOURCE_MYSQL_USER -pSOURCE_MYSQL_PASSWORD --opt SOURCE_DATABASE_NAME | mysql --host=DESTINATION_SERVER_IP -u DESTINATION_MYSQL_USER -pDESTINATION_MYSQL_PASSWORD -C DESTINATION_DATABASE_NAME

To use the above command, you need to shell in to the “Source” Web-Server and run the above shell command (replacing the items in capitals with the relevant details).
Make sure you don’t leave a space between the “-p” and the MySQL password otherwise it won’t work!

All the normal MySQL and MySQLdump parameters should work.

The basic principal is this: Perform a MySQL Dump (which outputs the database data as importable SQL queries) and use the UNIX pipe to redirect that output SQL to another MySQL command which logs in to the destination MySQL server and imports the SQL which was output by MySQL Dump.

UPDATE:
If you only have shell access to shell in to the destination server, the following is what you need:

mysqldump -u SOURCE_MYSQL_USER -pSOURCE_MYSQL_PASSWORD --host=SOURCE_SERVER_IP --opt SOURCE_DATABASE_NAME | mysql -u DESTINATION_MYSQL_USER -pDESTINATION_MYSQL_PASSWORD -C DESTINATION_DATABASE_NAME

Things to bear in mind:

• This will only work on Linux/UNIX/BSD type servers – but we don’t use Windows toy servers anyway do we?
• You will need to have mysqldump installed and available on the server you’re shelling in on
• This is specific to MySQL
• The MySQL users you use must have relevant permissions on each server (the “Source” user must have select permissions on the relevant database(s)/schema(s) and table(s), the “Destination” user must have create permissions). I usually use the root MySQL user or a user with equivalent permissions.
• Make sure you don’t leave a space between the “-p” and the password
• You’d be wise to ensure that the Database(s)/Schema(s)/Table(s) you are transferring do not exist on the “Destination” server as I believe by default they will be overwritten
• As ever…MAKE A BACKUP OF SOURCE AND DESTINATION BEFORE YOU START!!! I cannot emphasise that enough! Be safe or prepare to be in a world of pain!
• Also, I’d generally recommend that if you haven’t already, you should set a password for your MySQL root user – You can do this from the Linux/UNIX/BSD using:

  mysqladmin -u root password NEW_ROOT_PASSWORD

That’s it…Hopefully that’ll be useful to somebody