The Easy Way to Copy a MySQL Database

MySQL Database Copy If you have a web site of any kind, you probably have dealt with MySQL databases. Sometimes, you need to move your database, whether it’s to another server or back onto your existing server after performing an OS upgrade.

The advice you usually hear goes like this: Export the table structure and data using the mysql command line utility or with phpMyAdmin. Then reimport the exported data once your install/upgrade is done.

Well, that’s just a serious pain in the arse.

Lucky for you, there’s a very, very easy way to copy your DB over with a few copy commands…

Let’s take your average Linux server. MySQL stores the actual database in a series of files on your server’s harddrive. Big surprise there, eh? The files are usually found in /var/lib/mysql. The file listing should look something like this:

drwxr-xr-x 26 mysql mysql     4096 2009-10-18 16:27 .
drwxr-xr-x 33 root  root      4096 2009-10-17 23:12 ..
drwx------  2 mysql mysql     4096 2009-10-18 11:47 schmoozing
-rw-r--r--  1 root  root         0 2009-10-17 23:11 debian-5.0.flag
drwx------  2 mysql mysql     4096 2009-10-18 11:48 bad_news
drwx------  2 mysql mysql     4096 2009-10-18 11:48 big_bubba_site
drwx------  2 mysql mysql     4096 2009-10-18 11:48 pajama_collection
-rw-rw----  1 mysql mysql 18874368 2009-10-18 16:27 ibdata1
-rw-rw----  1 mysql mysql  5242880 2009-10-18 16:27 ib_logfile0
-rw-rw----  1 mysql mysql  5242880 2009-10-18 12:51 ib_logfile1
drwxr-xr-x  2 mysql root      4096 2009-10-18 11:39 mysql
-rw-------  1 root  root         7 2009-10-18 11:39 mysql_upgrade_info
drwx------  2 mysql mysql    12288 2009-10-18 11:48 web_site1
drwx------  2 mysql mysql     4096 2009-10-18 11:48 web_site2

Each directory will contain some files like so:

drwx------  2 mysql mysql    4096 2009-10-18 11:48 .
drwxr-xr-x 26 mysql mysql    4096 2009-10-18 16:27 ..
-rw-rw----  1 mysql mysql      61 2009-10-18 11:48 db.opt
-rw-rw----  1 mysql mysql    8722 2009-10-18 11:48 old_pjs.frm
-rw-rw----  1 mysql mysql    1000 2009-10-18 11:48 old_pjs.MYD
-rw-rw----  1 mysql mysql    2048 2009-10-18 11:48 old_pjs.MYI
-rw-rw----  1 mysql mysql    8642 2009-10-18 11:48 new_pjs.frm
-rw-rw----  1 mysql mysql     296 2009-10-18 11:48 new_pjs.MYD
-rw-rw----  1 mysql mysql    2048 2009-10-18 11:48 new_pjs.MYI
-rw-rw----  1 mysql mysql    8954 2009-10-18 11:48 pjs.frm
-rw-rw----  1 mysql mysql    8644 2009-10-18 11:48 users.frm
-rw-rw----  1 mysql mysql     460 2009-10-18 11:48 users.MYD
-rw-rw----  1 mysql mysql    2048 2009-10-18 11:48 users.MYI

Let’s ignore the fact that the database structure is thoroughly lame in this example. So, note that there are 3 types of files:

.frm
.MYD
.MYI

There are also some .frm files that do not have a matching .MYD or .MYI file. In the case of a lone .frm file, the storage engine being used is probably InnoDB. In the case of the 3 matching types listed above, the MyISAM engine is being used.

So now that you understand all that, here’s what you do: Stop mysql (/etc/init.d/mysql stop). Then just copy the contents of the /var/lib/mysql directory over to the same directory on your new installation. In our example, copy the green files/directories, and don’t copy the red ones:

drwxr-xr-x 26 mysql mysql     4096 2009-10-18 16:27 .
drwxr-xr-x 33 root  root      4096 2009-10-17 23:12 ..
drwx------  2 mysql mysql     4096 2009-10-18 11:47 schmoozing
-rw-r--r--  1 root  root         0 2009-10-17 23:11 debian-5.0.flag
drwx------  2 mysql mysql     4096 2009-10-18 11:48 bad_news
drwx------  2 mysql mysql     4096 2009-10-18 11:48 big_bubba_site
drwx------  2 mysql mysql     4096 2009-10-18 11:48 pajama_collection
-rw-rw----  1 mysql mysql 18874368 2009-10-18 16:27 ibdata1
-rw-rw----  1 mysql mysql  5242880 2009-10-18 16:27 ib_logfile0
-rw-rw----  1 mysql mysql  5242880 2009-10-18 12:51 ib_logfile1
drwxr-xr-x  2 mysql root      4096 2009-10-18 11:39 mysql
-rw-------  1 root  root         7 2009-10-18 11:39 mysql_upgrade_info
drwx------  2 mysql mysql    12288 2009-10-18 11:48 web_site1
drwx------  2 mysql mysql     4096 2009-10-18 11:48 web_site2

You’ll notice these are also green:

-rw-rw----  1 mysql mysql 18874368 2009-10-18 16:27 ibdata1
-rw-rw----  1 mysql mysql  5242880 2009-10-18 16:27 ib_logfile0
-rw-rw----  1 mysql mysql  5242880 2009-10-18 12:51 ib_logfile1

Remember those lone .frm files that indicated InnoDB? Well, the 3 files above are the storage location for the InnoDB table data. So, you’ll want to copy those 3 files over as well.

Change the file permissions with chmod, change the owner/group to “mysql” using chown/chgrp, and that’s it. Well, almost: Don’t forget to start mysql again (/etc/init.d/mysql start)!

When you mosey into phpMyAdmin or whatever, you should now see all your old data.

And you didn’t even have to do any fancy-schmancy exporting and importing of .sql files and all that miscellaneous horse excrement! Most excellent.