How Copy a MySQL Database

Wouldn’t it be nice if it was as simple as “copy database1 to database2”? Or something like that? But that would be too easy! Although to be fair, copying a MySQL database is easier than many other databases. But the caveat is that if you do it wrong, it is easy to accidentally export the database names, and importing an improperly exported database potentially overwrite production data if you’re not careful.

The following steps require console access to the machine, in question.

To get a proper database copy, try these steps

1. use mysqldump to dump the source database into file.sql
   
   mysqldump my_source_db -u[user] -p[password] >file.sql
   
   
   
2. create the target database
   
   mysql -u[user] -p[password] -e "create database my_target_db"
   
   
   
3. Pipe the exported file into the new database
   
   mysql my_target_db -u[user] -p[password] <file.sql
   
   

Pitfalls

If you don’t use this exact syntax, opting instead to use the –databases argument, the database will only import upon itself… you won’t be able to copy it into a database of a different name! Beware! Doing it wrong will potentially destroy data in your production environment!

My Personal Gripes

No blog post of mine would be complete without some “colorful” language. So, I just gotta say, that, although this process is possibly easier than dealing with some other competing databases’ processes out there, this process is kinda… “crap”. The more steps there are to remember and get right, and the more ambiguity there is in the process, the more there is potential for you to destroy your production data… leading to catastrophe for you and your business. I absolutely would not recommend copying a production database to a second database on your production server, due to the distinct possibility that someone accidentally exported the database with the database names included in the output file. The way to double-check this is to look near the top of the exported file for a “USE” statement, e.g. “USE my_database”. If there’s a “use” statement near the top of the exported file do not import it onto a production server, as it will overwrite your production data (assuming it was exported from said database). Since these exported files can be very big, you’ll have trouble opening it in a typical text editor. In that case you can check the file with the following commands

Windows
type mydump.txt|more

Linux
cat mydump.txt|more

The above commands work for text files of any size and once you’ve read the first few pages, you can Ctrl+C to cancel out.

But don’t learn this lesson the hard way. Keep your production vs. beta vs. dev environments separate! And tightly control who has privileges to potentially overwrite your production data!