Navigation

Transfering MySQL Data Easily

Introduction

This article describes a simple method of easily transfering data from one MySQL server to another. The technique described here is better suited for personal servers or smaller operations. It is not recommended to try this technique where the on larger servers or when data integrity is crucial. However, the simplicity of this technique makes it perfect to transfer data between two workstations, or between development or staging servers. The technique described here involves shutting down the MySQL servers, therefore be prepared to schedule some downtime for your users if you intend to use this technique. Of course, this technique requires administrator access to both the source and target MySQL servers.

Obtaining The Data

MySQL stores its data on disk on its data directory. The data directory is typically /var/lib/mysql under Linux and Unix systems, but it can vary from installation to installation. To find out what the data directory for your installation is, look at the datadir property of the MySQL configuration file, usually at /etc/my.cnf on Linux and Unix systems.

Each subdirectory on the MySQL data directory represents a MySQL database, inside those directories, there are a number of files with frm, MYD and MYI extensions. The file names of these files represent the tables in the MySQL database. Each table has a corresponding frm, MYI and MYD file. For example, if you have a database called mydb, and inside that database there are two tables, table1 and table2, the directory corresponding to the mydb database would look like this:


-rw-rw----  1 mysql mysql   65 Jul 21 20:52 db.opt
-rw-rw----  1 mysql mysql 8612 Jul 21 21:15 table1.frm
-rw-rw----  1 mysql mysql   20 Jul 21 21:17 table1.MYD
-rw-rw----  1 mysql mysql 2048 Jul 21 21:20 table1.MYI
-rw-rw----  1 mysql mysql 8612 Jul 21 21:15 table2.frm
-rw-rw----  1 mysql mysql   20 Jul 21 21:17 table2.MYD
-rw-rw----  1 mysql mysql 2048 Jul 21 21:20 table2.MYI

To copy the data to another server, simply create a zip or tar file of the directory corresponding to the data you would like to transfer, make sure to shut down your MySQL server before doing this. Once you have created the zip or tar file, transfer it to the target MySQL server via FTP (or SFTP, RCP, sneakernet, etc.), and extract it into its data directory. Again, make sure the target MySQL server isn't running while extracting the data.

Once the data has been successfully extracted, bring up the MySQL server and log in as administrator, make sure MySQL can access the data successfully by issuing the "use" command with the newly transferred database as a parameter, in the example above, the command to issue would be use mydb;. If MySQL reports no errors, issue a few test queries to make sure everything is fine, a select * from table1 query should suffice.

Once you are reasonably sure the data was transferred successfully, grant access to regular users as necessary. Your transfered data should be ready to use.

Conclusion

This article demonstrated a simple technique that can be used to transfer data between MySQL servers. The techniques described here involve some downtime, and are better suited for personal, staging or development servers. For production servers, proper backup procedures are recommended.