Restoring a running mysql database
 

Hi

Our web designers (yes - I have had to get an external company in) have told me that they cannot easily add a feature that I have requested.

We have a demo site with our online system which we can show to customers. We can create accounts and watch the whole thing work. All I wanted was there to be a way of restoring the database back to a saved snapshot, so that it does not become littered with all the new test accounts. I thought this could be at the end of a day - or by a "restore" button on our site admin page.

They have said they can't do it. It would mean shutting down mysql for all users of their server and it would be too expensive.

I've done similar in the past albeit on a stand alone server running our company's CRM package on a LAN. I would take daily snapshots and wrote a script that would replace the current data with the desired back-up. It too was running on a lamp.

Are they right? Would they have to stop mysql for everyone or can it be just for one database? I would have used a cron job to do it - they are talking about using some php scripting!

That's the problem with a little knowledge, I now think anything is possible!!!

You typically don't want to restore a database on a live system when there are users accessing it. However, this particular scenario makes it sound like this database is not in a multiuser environment, and when you are ready to restore the snapshot, then no one is using it (specifically, no one is adding vital data to it).

If I understand the scenario correctly, you can use mysqldump to dump the database "snapshot":
At the end of the day, it can be restored just as easily:
Your web designers were likely thinking of restoring by saving the actual datafiles and then replacing those. In that case, they would definitely want mysql shut down.

Brilliant. Thank you!

The last one is mysql rather than mysqldump? I probably need to read up some more on this.

Thanks again.

Just confirming that the last one is, indeed, mysql rather than mysqldump.

In a nutshell, mysqldump creates a script of all the SQL statements needed to recreate the tables in the database and populate them with the records that are in those tables at the time of the dump.

The problem with mysqldump is that it can lead to inconsistent data in some situations. For example, assume there are two tables, table_1 and table_3, that have related records. mysqldump is run to get a snapshot of the database. Imagine the following sequence of events:

1. mysqldump creates the SQL for table_1
2. A user updates the database, adding a record to table_1 and the related record in table_3
3. mysqldump creates the SQL for table_2
4. mysqldump creates the SQL for table_3

At this point, the database itself is consistent, but the snapshot is not. This seems unlikely to happen in the scenario you have described. However, if such a course of events *is* possible, you might want to look into mysqlhotcopy instead. This has the advantage of locking all the tables, creating a copy of the database, and then unlocking the tables. Thus, the user would be unable to perform Step 2 until the copy was complete.

Yet again, I am grateful for your help.

I used mysqlhotcopy on the company database I administer for that exact reason. In fact I actually suggested it to the web developers before they came back with the "it's not possible" stuff.

The situation you have described (ie the database is not in constant use) is spot on. I only want one snapshot - if you like the demo database with a few dummy companies and transactions already entered. Then I can show a potential customer how easy it is to use our system by adding a new dummy account and various transactions. They can watch it do it's stuff (like invoicing etc). Each night the original snapshot over-writes that days "demoing" and voila - it's back to normal again!

I thought it was an easy thing to do - and basically if it is not in use, you have confirmed that it is.

Thanks again - I really value your help.