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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.