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 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 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 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 It reads the pipe for input and sends each statement to the 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

% mysqldump cookbook states | ssh mysql cb

ssh connects to 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!

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois © 2008-2020.
If you may any questions please contact us: