Problem: I needed to automatically reset a MySQL database back to a fresh install every so often.
This method works for PHP/MySQL installations (and possibly any online database… although I’ve only tested MySQL)
I found myself in the situation where I wanted to offer my clients a free demo of open source software. Even though open source software is free to download and install, it often proves difficult for the less tech savvy. In these cases I offer clients a one time installation fee where I install it for them. But before they hand over their money, I offer them a fully functional live demo before I go ahead and install it. This prevents refunds and makes the whole process transparent because they know exactly what they will get.
The Solution (there may be better alternatives… but I found this one the easiest to implement)
Reset the database back to its original installation state be setting a cron to delete and reinstall the database every hour. This leaves the “reset” process completely automated.
- Install the application (ie any php/MySQL application) on a sub domain.
- Use generic usernames and passwords. Don’t use any personally identifying information.
- Set it up so that its fully functional.
- Using phpMyAdmin, export the database. Upload the exported SQL file to the application root folder.
- Create a php script (see sample below) to reinstall the database (ie the one you just downloaded). Upload it to the root of the sub domain you created.
- Set up a cron to run the script at regular intervals. If your server doesn’t have a cron, I use a cron service called cronless to trigger the script to run.
Download and install the application (ie wordpress, osTicket, SugarCRM etc…) according to the installation documentation. Use a sub domain. This way it won’t affect the performance of your main site. Use a host where your account is managed so that if for some reason the server is breached, the hosting company will have professional technicians to isolate and manage the breach. Don’t install it on your own unmanaged VPS. A security breach could mean downtime that you cannot afford.
Keep everything generic. Try to keep login information relatively secure. Use an email opt in form to provide access. You don’t want every spammer in the universe logging in and using the email function.
Ensure that the application is functional. This way it will be reset to its functional state.
Once the application has been setup, export the database using phpMyAdmin. To do this, login to phpMyadmin and open the applicable database.
Exporting the Database
In the phpMyAdmin menu, select Export
Set up the export options.
Next, set the format Options
Next Set the Object Creation Options
Next set the Dump Options
Save the database somewhere you can find it. RENAME the database if its difficult to remember.
UPLOAD the SQL file you just created to the root folder using a FTP program such as FileZilla.
Creating the PHP script.
Copy and paste the code below into a text document. Save it as dbreset.php. Substitute the values that correspond to your database.
dsn ='mysql:host=sql5.mysqlhost.com;dbname=sugarcrm_db'; $username = 'sugarcrm'; $password = 'password'; $db = new PDO($dsn, $username , $password); $sql = file_get_contents('sugarcrm_db.sql'); $qr = $db->exec($sql); ?>
Upload the dbreset.php file to the root folder using your FTP program.
Test the Database Reset Script
Log into the application that you installed. Make some changes to it and save them. Run the dbreset.php script by simply typing it into the address bar of your web browser to trigger the script eg http://subdomain.mysite.com/dbreset.php
If there are errors in the script (eg a spelling mistake) php page errors will appear. If all goes well… the page will be blank. Log back into your application. If the changes you made just a moment ago are erased it means that the script successfully reinstalled the database to its original state.
Now we need to automate the process.
Setting up the Cron
Most hosting control panels allow the use of crons to schedule tasks. They are easy to use. Set up the task to call the php script you just created eg subdomain.mydomain.com/dbreset.php
Set the Cron to run at intervals eg each hour, each day… whatever you need.
Sometimes, your web host may not offer crons. In these cases, you can use a cron service such as cronless. Follow the directions below.
Login to cronless (setup a free account if you don’t have one)
Create a new Job and fill in the details…
The above job will reset the database every hour.
Your database reset is complete and fully automated.
The above technique completely erases and “resets” your database for which ever PHP/MySQL application. The most common open source systems that this can be used for include
- CMS systems such as WordPress, Joomla, Drupal
- Shopping cart systems such as Magento, Zencart, prestashop etc…
- CRM systems: SugarCRM, vTiger
- Supoort Desks: osTicket
- Learning Systems: Moodle
… and probably a whole lot more!
I used this technique for Demos. It could also be used for workshops where installations need to be reset each day after students explore systems and add data etc… Again, there are loads more ways this could be used.
This technique overcame my issue of providing live demos and erasing data. It is simple and easy to implement but it’s not without its risks. This method allows anyone to take a demo which means there are some potential security issues. I tend to reset my data every hour which gives the user enough time to look around whilst reducing the time for ill intended people to do anything significant. Even though I have never had a security breach (yet), it’s also a good idea to implement some security precautions too.
- Harden your PHP as much as you can.
- Use security plugins where you can at the site level to secure the site. Do this at setup so that they are reset to their original state and hide the plugins where you can.
- Don’t allow search engines to crawl the demo site.
- Ban users by IP if they hit the site too often.
- Use an opt in form to deliver demo site login details.
- Use a reverse proxy such as Cloud Flare to weed out known hackers and spammers.
I hope this helps someone. It’s certainly made a difference in offering my clients live demos of different software before they commit.
Your comments and ideas to improve this solution are very welcome.