Copying MySQL Databases to another Machine

When databases need to be transferred between different architectures, the ‘mysqldump’ can be used to create a file. This file would contain SQL statements. The file transfers can be done from one machine to another, and this file can be used as an input to the ‘mysql’ client.

Move Database between two machines

One way to move database between two machines is to execute the commands on the machine on which database is present −

mysqladmin −h 'other_hostname' create db_name
mysqldump db_name | mysql −h 'other_hostname' db_name

Copy a database from a remote machine over a slow network

If database needs to be copied from a remote network over a network that is slow, the below commands can be executed −

mysqladmin create db_name
mysqldump −h 'other_hostname' −−compress db_name | mysql db_name

MySQL dump

The dump can be stored in a file, and later it can be transferred to the target machine. The next step is to load the file into the database. Let us take an example where it is required to dump a database to a compressed file on the source machine. It has been shown below −

mysqldump −−quick db_name | gzip > db_name.gz

The file that contains the database contents need to be transferred from the target machine to the source machine. The below commands need to be executed −

mysqladmin create db_name
gunzip < db_name.gz | mysql db_name

Apart from this, ‘mysqldump’ and ‘mysqlimport’ can also be sued to transfer the database contents. For databases that contain large amount of data, ‘mysqldump’ and ‘mysqlimport’ can be used together to increase the speed of the operation. ‘DUMPDIR’ represents the full path name of the directory where the output of ‘mysqldump’ is stored.

First, a directory is created so that output files and database contents can be dumped. This can be done using the below commands −

mkdir DUMPDIR
mysqldump −−tab=DUMPDIR
db_name

Then, the files can be transferred from the DUMPDIR directory to any corresponding directory on the target machine. The next step is to load the files into the MySQL. This can be done using the below command −

mysqladmin create db_name # create database
cat DUMPDIR/*.sql | mysql db_name # create tables in database
mysqlimport db_name
   DUMPDIR/*.txt # load data into tables