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