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.txtThen 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.txtAnother 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 cbThe 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 ...