Easiest way to copy a MySQL database?

Question

Does anyone know of an easy way to copy a database from one computer to a file, and then import it on another computer?

Answer 1

Here are a few options:

mysqldump

The easiest, guaranteed-to-work way to do it is to use mysqldump. See the manual pages for the utility here:

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Basically, it dumps the SQL scripts required to rebuild the contents of the database, including creation of tables, triggers, and other objects and insertion of the data (it's all configurable, so if you already have the schema set up somewhere else, you can just dump the data, for example).

Copying individual MyISAM table files

If you have a large amount of data and you are using the MyISAM storage engine for the tables that you want to copy, you can just shut down mysqld and copy the .frm, .myd, and .myi files from one database folder to another (even on another system). This will not work for InnoDB tables, and may or may not work for other storage engines (with which I am less familiar).

mysqlhotcopy

If you need to dump the contents of a database while the database server is running, you can use mysqlhotcopy (note that this only works for MyISAM and Archive tables):

http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html

Copying the entire data folder

If you are copying the entire database installation, so, all of the databases and the contents of every database, you can just shut down mysqld, zip up your entire MySQL data directory, and copy it to the new server's data directory.

This is the only way (that I know of) to copy InnoDB files from one instance to another. This will work fine if you're moving between servers running the same OS family and the same version of MySQL; it may work for moving between operating systems and/or versions of MySQL; off the top of my head, I don't know.