Copy the MySQL database to another machine

#When the database needs to be transferred between different schemas, you can use mysqldump to create files. This file will contain SQL statements. File transfers can be made from one machine to another, and the file can be used as input to the "mysql" client.

Moving a database between two machines

One way to move a database between two machines is to execute the command on the machine where the database exists -

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

Through slow Copy the database from the remote computer over the slow network

If you need to copy the database from the remote computer over the slow network, you can execute the following command-

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

MySQL dump

The dump can be stored in the file, which can later be transferred to the target computer. The next step is to load the file into the database. Let's take an example where a database needs to be dumped to a compressed file on the source machine. As shown below -

mysqldump −−quick db_name | gzip > db_name.gz

The file containing the database content needs to be transferred from the destination computer to the source computer. You need to execute the following command -

mysqladmin create db_name
gunzip < db_name.gz | mysql db_name

In addition, you can also use "mysqldump" and "mysqlimport" to transfer the database content. For databases containing large amounts of data, 'mysqldump' and 'mysqlimport' can be used together to increase the speed of operations. "DUMPDIR" represents the full path name of the directory where the output of "mysqldump" is stored.

First, create a directory so that the output files and database contents can be dumped. This can be done using the following command -

mkdir DUMPDIR
mysqldump −−tab=DUMPDIR
db_name

The files can then be transferred from the DUMPDIR directory to any corresponding directory on the target machine. The next step is to load the file into MySQL. This can be done using the following 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

The above is the detailed content of Copy the MySQL database to another machine. For more information, please follow other related articles on the PHP Chinese website!