2.10.2. Copying MySQL Databases to Another Machine
You can copy the .frm,
.MYI, and .MYD files
for MyISAM tables between different
architectures that support the same floating-point format.
(MySQL takes care of any byte-swapping issues.) See
Section 14.1, “The MyISAM Storage Engine”.
In cases where you need to transfer databases between different
architectures, you can use mysqldump to
create a file containing SQL statements. You can then transfer
the file to the other machine and feed it as input to the
mysql client.
Use mysqldump --help to see what options are
available. If you are moving the data to a newer version of
MySQL, you should use mysqldump --opt to take
advantage of any optimizations that result in a dump file that
is smaller and can be processed more quickly.
The easiest (although not the fastest) way to move a database
between two machines is to run the following commands on the
machine on which the database is located:
shell> mysqladmin -h 'other_hostname' create db_name
shell> mysqldump --opt db_name | mysql -h 'other_hostname' db_name
If you want to copy a database from a remote machine over a slow
network, you can use these commands:
shell> mysqladmin create db_name
shell> mysqldump -h 'other_hostname' --opt --compress db_name | mysql db_name
You can also store the dump in a file, transfer the file to the
target machine, and then load the file into the database there.
For example, you can dump a database to a compressed file on the
source machine like this:
shell> mysqldump --quick db_name | gzip > db_name.gz
Transfer the file containing the database contents to the target
machine and run these commands there:
shell> mysqladmin create db_name
shell> gunzip < db_name.gz | mysql db_name
You can also use mysqldump and
mysqlimport to transfer the database. For
large tables, this is much faster than simply using
mysqldump. In the following commands,
DUMPDIR represents the full pathname
of the directory you use to store the output from
mysqldump.
First, create the directory for the output files and dump the
database:
shell> mkdir DUMPDIR
shell> mysqldump --tab=DUMPDIR db_name
Then transfer the files in the
DUMPDIR directory to some
corresponding directory on the target machine and load the files
into MySQL there:
shell> mysqladmin create db_name # create database
shell> cat DUMPDIR/*.sql | mysql db_name # create tables in database
shell> mysqlimport db_name DUMPDIR/*.txt # load data into tables
Do not forget to copy the mysql database
because that is where the grant tables are stored. You might
have to run commands as the MySQL root user
on the new machine until you have the mysql
database in place.
After you import the mysql database on the
new machine, execute mysqladmin
flush-privileges so that the server reloads the grant
table information.
