Copying Tables or Databases to Another Server

10.17.1 Problem

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

10.17.2 Solution

Use mysqldump and mysql together, connected by a pipe.

10.17.3 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 mysql cb

ssh connects to other-host.com and launches mysql there. Then it reads the mysqldump output from the pipe and passes it to the remote mysql process. Using ssh can be useful when you want to send a dump over the network to a machine that has the MySQL port blocked by a firewall but that allows connections on the SSH port.

If you don't have access to ssh, you may be able to use rsh instead. However, rsh is insecure, so ssh is much preferred.

To copy multiple tables over the network, name them all following the database argument of the mysqldump command. To copy an entire database, don't specify any table names after the database name. mysqldump will dump all the tables contained in the database.

If you're thinking about invoking mysqldump with the --all-databases option to send all your databases to another server, consider that the output will include the tables in the mysql database that contains the grant tables. If the remote server has a different user population, you probably don't want to replace that server's grant tables!