 |
|
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.
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.
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.
