Copying Databases to Another Server

The database backup techniques discussed earlier in this chapter are useful for moving or copying a database from one MySQL server to another. This section describes some methods for performing database transfers. For purpose of this discussion, I assume that the objective is to transfer a database from the server on the local host to a server on the remote host boa.snake.net. However, the two servers could just as well be running on the same host. Also, although the following discussion describes how to copy entire databases, you can adapt the techniques to copying individual tables.

There are two general methods for copying a database to another server:

• Make a backup of the database. This results in a file or set of files. Copy the files to the second server host and load them into the second MySQL server.

• Copy the database directly over the network from one server to the other. This avoids the need for any intermediate backup files.

The following discussion shows how to use both methods.

Copying Databases Using a Backup File

To copy a database using a backup file, create the file using mysqldump, copy it to the second server host, and load it into the MySQL server there. The following example illustrates how to copy the sampdb database with this procedure:

% mysqldump --opt --database sampdb > sampdb.sql

% scp sampdb.sql boa.snake.net:/tmp

% mysql < /tmp/sampdb.sql

Copying Databases Using a Network Transfer

The mysqldump technique shown in the previous section involves creating a dump file to be copied to the destination server host. If you write the output of mysqldump over the network, no intermediate file is needed. Dump the database over the network using a pipe so that mysql reads the output of mysqldump directly. For example, to copy the sampdb database from the local host to the server on boa.snake.net, do so like this:

% mysqldump --opt --database sampdb | mysql -h boa.snake.net

If you cannot access the remote MySQL server on boa.snake.net from the local host, but you can access it by logging in there, use ssh to invoke mysql remotely:

% mysqldump --opt --database sampdb | ssh boa.snake.net mysql

On a slow network, the --compress option can improve performance when copying a database to another machine because it reduces the number of bytes traveling over the network:

% mysqldump --opt sampdb | mysql --compress -h boa.snake.net sampdb

Notice that the --compress option is given for the program that communicates with the server on the remote host, not the one that communicates with the local server. Compression applies only to network traffic; it does not cause compressed tables to be created in the destination database.

Another approach involves copying database files directly from one host to the other. Suppose that the mydb database has only MyISAM tables. In this case, table information is contained entirely in the files in the mydb database directory. If the local data directory is /user/local/mysql/data and the remote database directory on boa.snake.net is /var/mysql/data, the following commands copy the mydb database directory to that host:

% cd /usr/local/mysql/data
% scp -r mydb boa.snake.net:/var/mysql/data

Copying database files to another host this way involves some additional constraints:

• Both machines must have the same hardware architecture, or the tables you're copying must all be of a binary-portable table type. The resulting tables on the second host may appear to have very strange contents otherwise. A direct database copy works for MyISAM tables because they are binary-portable. It would not work for ISAM tables unless both hosts are hardware compatible.

• You must prevent the servers on both hosts from attempting to change the tables while you're copying them. The safest approach is to stop both servers while you're working with the tables.