Copying MySQL databases

How do I copy a MySQL database from one system to another?

MySQL provides replication features that support master/slave database server relationships.

Establishing an account on the master with the replication, slave, super, reload and select privileges will configure the master server to allow slave servers to replicate the database by issuing the 'load data from master' command.

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.

How do I copy a MySQL database from one system to another?

MySQL provides replication features that support master/slave database server relationships.

Establishing an account on the master with the replication, slave, super, reload and select privileges will configure the master server to allow slave servers to replicate the database by issuing the 'load data from master' command.

Before replicating the database with that command, you will need to execute a 'flush tables with read lock' command.

Another method is to make an archive of the database directories with a Zip or Tar utility, and copy that archive into the data directory on the other MySQL server.

Copying database directories from the MySQL 'data' directory from one server to another also works across operating systems. Don't overwrite the 'mysql' database, as it contains the system user information and passwords. Copying the database from one system to another with this method doesn't establish the master/slave replication relationships needed to keep the two copies synchronized.

The MySQL manual at ww.mysql.com/documentation/mysql offers more information.

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