Movatterモバイル変換

In cases where you need to transfer databases between different architectures, you can usemysqldump to create a file containing SQL statements. You can then transfer the file to the other machine and feed it as input to themysql client.

Usemysqldump --help to see what options are available.

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:

mysqladmin -h 'other_hostname' createdb_namemysqldumpdb_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:

mysqladmin createdb_namemysqldump -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:

mysqldump --quickdb_name | gzip >db_name.gz

Transfer the file containing the database contents to the target machine and run these commands there:

mysqladmin createdb_namegunzip <db_name.gz | mysqldb_name

You can also usemysqldump andmysqlimport to transfer the database. For large tables, this is much faster than simply usingmysqldump. In the following commands,DUMPDIR represents the full path name of the directory you use to store the output frommysqldump.

First, create the directory for the output files and dump the database:

mkdirDUMPDIRmysqldump --tab=DUMPDIRdb_name

Then transfer the files in theDUMPDIR directory to some corresponding directory on the target machine and load the files into MySQL there:

mysqladmin createdb_name           # create databasecatDUMPDIR/*.sql | mysqldb_name   # create tables in databasemysqlimportdb_nameDUMPDIR/*.txt   # load data into tables

Do not forget to copy themysql database because that is where the grant tables are stored. You might have to run commands as the MySQLroot user on the new machine until you have themysql database in place.

After you import themysql database on the new machine, executemysqladmin flush-privileges so that the server reloads the grant table information.