2.10.8 Copying MySQL Databases to Another Machine
If you are using MySQL 3.23 or later, 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.) `MyISAM' storage engine MyISAM storage
The MySQL `ISAM' data and index files (`.ISD' and `*.ISM',
respectively) are architecture dependent and in some cases operating
system dependent. If you want to move your applications to another
machine that has a different architecture or operating system than your
current machine, you should not try to move a database by simply copying
the files to the other machine. Use `mysqldump' instead.
By default, `mysqldump' will 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.
Try `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 faster.
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:
shell> mysqladmin create DB_NAME
shell> mysqldump -h 'OTHER_HOSTNAME' --opt --compress DB_NAME | mysql DB_NAME
You can also store the result in a file, then transfer the file to the
target machine and load the file into the database there. For example,
you can dump a database to a file on the source machine like this:
shell> mysqldump --quick DB_NAME | gzip > DB_NAME.CONTENTS.gz
(The file created in this example is compressed.) Transfer the file
containing the database contents to the target machine and run these
shell> mysqladmin create DB_NAME
shell> gunzip < DB_NAME.CONTENTS.gz | mysql DB_NAME
You can also use `mysqldump' and `mysqlimport' to transfer the database.
For big 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
Also, don't forget to copy the `mysql' database because that is where
the `user', `db', and `host' 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
automatically generated byinfo2html