MySQL Cookbook

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 you want to copy the states table from the cookbook database on the local host to the cb database on the host other-host.com. One way to do this is to dump the output into a file (as described in Recipe 10.16):

% mysqldump cookbook states > dump.txt

Then copy dump.txt to other-host.com and run the following command there to import the table into that server’s cb database:

% mysql cb < dump.txt

Another way to accomplish this without using an intermediary file is to send the output of mysqldump directly over the network to the remote MySQL server. If you can connect to both servers from the host where the cookbook database resides, use this command:

% mysqldump cookbook states | mysql -h other-host.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.com. It reads the pipe for input and sends each statement to the other-host.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.com:

% mysqldump cookbook states | ssh other-host.com ...