The best way to copy a MySQL database to another machine

After reading your story on  Copying MySQL databases,  I would like to know whether a MySQL database can be copied from one machine to another using the mysqldump command instead of taking a snapshot by copying the data directory.

The mysqldump command can be used to export MySQL database data for one database at a time or for all MySQL data.

According to the documentation, you should consider using the mysqlhotcopy instead if you are backing up a server. Unfortunately, mysqlhotcopy might not work very well on Windows systems, and it can only be run on the system that has the database directories.

To continue reading, register here and become an Insider. You'll get free access to premium content from CIO, Computerworld, CSO, InfoWorld, and Network World. See more Insider content or sign in.

After reading your story on  Copying MySQL databases,  I would like to know whether a MySQL database can be copied from one machine to another using the mysqldump command instead of taking a snapshot by copying the data directory.

The mysqldump command can be used to export MySQL database data for one database at a time or for all MySQL data.

According to the documentation, you should consider using the mysqlhotcopy instead if you are backing up a server. Unfortunately, mysqlhotcopy might not work very well on Windows systems, and it can only be run on the system that has the database directories.

The mysqldump command uses a syntax that looks like

mysqldump [options] db_name [tables]

 where entire databases will be dumped if you do not name any tables. You can replace the db_name and tables list with "-all-databases" to dump everything.

 For MySQL versions prior to 4.1, include the -opt option in your command; otherwise, the entire result set gets loaded into memory before anything is dumped.

Loading your dumped data into another MySQL system can be done by using the "mysqlimport" command.

Read more about software in Network World's Software section.