Copying MySQL databases

Current Job Listings

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 offers more information.

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.
Now read: Getting grounded in IoT