MySQL Cookbook, 2nd Edition

Copying Tables or Databases to Another Server

Problem

You want to copy tables or databases from one MySQL server to another.

Solution

Use mysqldump and mysql together, connected by a pipe.

Discussion

SQL-format output from mysqldump can be used to copy tables or databases from one server to another. Suppose that you want to copy the states table from the cookbook database on the local host to the cb database on the host other-host.example.com. One way to do this is to dump the output into a file (as described in Exporting Table Contents or Definitions in SQL Format):

%mysqldump cookbook states > states.txt

Now copy states.txt to other-host.example.com, and run the following command there to import the table into that MySQL server’s cb database:

%mysql cb < states.txt

To accomplish this without using an intermediary file, send the output of mysqldump directly over the network to the remote MySQL server. If you can connect to both servers from your local host, use this command:

%mysqldump cookbook states | mysql -h other-host.example.com cb

The mysqldump half of the command connects to the local server and writes the dump output to the pipe. The mysql half of the command connects to the remote MySQL server on other-host.example.com. It reads the pipe for input and sends each statement to the other-host.example.com server.

If you cannot connect directly to the remote server using mysql from your local host, send the dump output into a pipe that uses ssh to invoke mysql remotely on other-host.example.com ...