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.) See Section 13.1, “The MyISAM
Storage Engine”.
The MySQL ISAM
data and index files
(.ISD
and *.ISM
,
respectively) are dependent upon the architecture and, in some
cases, the operating system. If you want to move applications to
another machine having a different architecture or operating
system than that of the 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 creates a file containing SQL statements. You can then transfer the file to the other machine and use it as input to the mysql client.
Try mysqldump --help to see what options are
available. Before MySQL 4.1, if you are moving the data to a newer
version of MySQL, you should add the
--opt
option to the
mysqldump commands shown here, to take
advantage of any optimizations that result in a dump file that is
smaller and can be processed faster.
(--opt
is enabled by default as
of MySQL 4.1.)
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 '
shell>other_hostname
' createdb_name
mysqldump
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
shell>db_name
mysqldump -h '
other_hostname
' --compressdb_name
| mysqldb_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
shell>db_name
gunzip <
db_name
.gz | mysqldb_name
You can also use mysqldump and
mysqlimport to transfer the database. For very
large tables, this is much faster than simply using
mysqldump. In the following commands,
DUMPDIR
represents the full path name
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
shell>DUMPDIR
mysqldump --tab=
DUMPDIR
db_name
Then transfer the files in the DUMPDIR
directory to a directory on the target machine and load the files
into MySQL there:
shell>mysqladmin create
shell>db_name
# create databasecat
shell>DUMPDIR
/*.sql | mysqldb_name
# create tables in databasemysqlimport
db_name
DUMPDIR
/*.txt # load data into tables
Do not 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 information.
User Comments
Add your own comment.