This blog is turning 5 years old this summer. We've tried to target our articles towards businesses or people who want to understand computing and software enough to make smart software-adjacent decisions. We're not really a coding or programming blog trying so we don’t try to show how the sausage is made.

All of that said, this week we are peeking a little under then hood about a specific technical "how-to" primarily since the general issue of cloning your database is a useful one.


What? - Backing Up A Database

You might not find anybody in computing more paranoid than me about backups. I've had a web host flat-out lose five sites worth of files and data about 15 years ago. Never forget. The only thing that makes me feel more secure than three redundant backups is having five redundant backups.

There's no substitute for backing up in a variety of spaces. We regularly download snapshot copies of all our and our clients' databases on periodic local backups. We make nightly server backups of those databases. Our server itself has a backup system.

But what happens if you want to copy a database snapshot of your "real" data and use it to troubleshoot or harmlessly test your code in a "staging" or safe sandbox environment? That situation calls for an automated process to take everything from one production database and copy it somewhere else.


Why? - Methods Of Backups; Backing Up vs. Cloning

There are a variety of server scripts to be used or written to simply backup a database. This mysqldump method is pretty reliable.

But that is for backup purposes. What we're talking about here is replicating a production environment for a staging or testing environment. Developers need these ecosystems to safely work with real-world data while writing and testing code.

To clone a database, we like our method below.


Where - In PHP

Since this particular project is in PHP, we're writing the cloning code with its MySql calls with PHP code. But this could just as easily be done with MySQL queries in Javascript, ASP.net, C# or some database plugins that communicate with databases. And most traditionally, stored procedures would be a common place to execute the dropping and copying of tables.

What we particularly like about our employed method here is that we clone the production database to the staging database nightly. We call it via a cron job scheduler, which executes at 2am local time. Since any time the page is called it will perform the cloning operations, not only do we call it overnight with the cron job but we can just load the page at any time to re-clone the database on demand.

That way, while we've been manipulating the data in the testing environment to troubleshoot or try things, at any point we can reset by re-cloning and get the production data back with one click.

Additionally, it allows any users to whom we have granted PHP cloning-page access to perform a clone by just accessing the page or clicking a link; instead of training or ensuraing that these users know how to access the database directly and execute a stored procedure.


How - Our Code


This is pretty self-explanatory code as you can see above.

The main thing that happens here is that we are dropping our tables in the testing/staging database, then forming them on the fly by doing a create table / select query from all records in the same table in the production database.


The Takeaway

Sure, this isn't reinventing the wheel or doing rocket surgery. But we hope you found it a useful and limber approach towards backup up MySQL database data.