Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.5Mb
PDF (A4) - 39.5Mb
PDF (RPM) - 38.7Mb
HTML Download (TGZ) - 11.0Mb
HTML Download (Zip) - 11.0Mb
HTML Download (RPM) - 9.7Mb
Man Pages (TGZ) - 217.1Kb
Man Pages (Zip) - 326.8Kb
Info (Gzip) - 3.6Mb
Info (Zip) - 3.6Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Copy a Database from one Server to Another

7.4.5.2 Copy a Database from one Server to Another

On Server 1:

shell> mysqldump --databases db1 > dump.sql

Copy the dump file from Server 1 to Server 2.

On Server 2:

shell> mysql < dump.sql

Use of --databases with the mysqldump command line causes the dump file to include CREATE DATABASE and USE statements that create the database if it does exist and make it the default database for the reloaded data.

Alternatively, you can omit --databases from the mysqldump command. Then you will need to create the database on Server 2 (if necessary) and specify it as the default database when you reload the dump file.

On Server 1:

shell> mysqldump db1 > dump.sql

On Server 2:

shell> mysqladmin create db1
shell> mysql db1 < dump.sql

You can specify a different database name in this case, so omitting --databases from the mysqldump command enables you to dump data from one database and load it into another.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Charles Peterson on August 31, 2017
here is what I do for a quick dump to another remote server...

-- dump-to-server.sh --
#!/bin/bash

if [[ -z "$1" || -z "$2" ]]; then
echo "--------- usage ---------";
echo "./dump-to-server.sh schema_name root@remote.server.net";
echo "";
else
mysqldump --opt "$1" | gzip -c | ssh "$2" "gunzip -c | mysql $1"
fi