How to Copy an MYSQL Database from One Computer to Another

If you want to transfer a database to another system with a different architecture, try mysqldump to create a file with all the statements from your database. To see what options are available, type mysqldump --help. In addition, you can use mysql --opt command if you move the database to a newer version of MySQL. Using this argument, the dump file will be smaller and can be imported quickly.

One of the easiest ways to move a database is to run the following commands on the computer where the database is: shell> mysqladmin -h 'other_hostname' create db_name shell> mysqldump --opt db_name | mysql -h 'other_hostname' db_name

If your network is slow and you want to copy the database from a remote computer, use the following commands: shell> mysqladmin create db_name shell> mysqldump -h 'other_hostname' --opt --compress db_name | mysql db_name

Another way is to use the mysqldump command to dump the database into a file, copy it to the other computer and load the file from there. The following command shows how to dump the database to a compressed file: shell> mysqldump --quick db_name | gzip > db_name.gz

Copy the database file to the target computer and run the following commands: shell> mysqladmin create db_name shell> gunzip < db_name.gz | mysql db_name

For larger tables you can use mysqlimport command which is much faster. To use it, create the directory for the output files and dump the database: shell> mkdir DUMPDIR shell> mysqldump --tab=DUMPDIR db_name

DUMPDIR represents the full pathname of the directory you use to store the output from mysqldump.

After you create the directory and dump the database, move the files from DUMPDIR to the directory from the target computer where you will load the files into MYSQL: shell> mysqladmin create db_name shell> cat DUMPDIR/*.sql | mysql db_name shell> mysqlimport db_name DUMPDIR/*.txt

The last step is to reload the grant table information using mysqladmin flush-privileges command.