Recipe 10.16. 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 Section 10.15):

% 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:

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

ssh connects to other-host.example.com and launches mysql there. It then reads the mysqldump output from the pipe and passes it to the remote mysql process. 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 enables connections on the SSH port.

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!