LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
Search this Thread
Old 04-27-2007, 05:35 AM   #1
mazzo
Member
 
Registered: Jun 2003
Location: Thames Valley, UK
Distribution: RedHat 7.3 & 9 Ubuntu, Puppy Linux, Ubuntu and CentOS
Posts: 135

Rep: Reputation: 15
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!!!
 
Old 04-27-2007, 06:58 AM   #2
zaichik
Member
 
Registered: May 2004
Location: Iowa USA
Distribution: CentOS
Posts: 419

Rep: Reputation: 30
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":
Code:
mysqldump -u username -p password database_name > snapshot.sql
At the end of the day, it can be restored just as easily:
Code:
mysql -u username -p password database_name < snapshot.sql
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.
 
Old 04-27-2007, 09:12 AM   #3
mazzo
Member
 
Registered: Jun 2003
Location: Thames Valley, UK
Distribution: RedHat 7.3 & 9 Ubuntu, Puppy Linux, Ubuntu and CentOS
Posts: 135

Original Poster
Rep: Reputation: 15
Brilliant. Thank you!

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

Thanks again.
 
Old 04-27-2007, 10:10 AM   #4
zaichik
Member
 
Registered: May 2004
Location: Iowa USA
Distribution: CentOS
Posts: 419

Rep: Reputation: 30
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.
 
Old 04-27-2007, 12:25 PM   #5
mazzo
Member
 
Registered: Jun 2003
Location: Thames Valley, UK
Distribution: RedHat 7.3 & 9 Ubuntu, Puppy Linux, Ubuntu and CentOS
Posts: 135

Original Poster
Rep: Reputation: 15
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
restoring mysql database MrSako Linux - Software 2 12-09-2006 03:42 PM
Restoring MSSQL database backup into PSQL mandrivaLE2005 Linux - Software 4 01-26-2006 10:35 AM
Restoring *.sql database from Linux Fedora's shell marciano Linux - Newbie 1 10-19-2005 07:05 PM
i am installing a php running board where is my mysql database Virtualhate Linux - Newbie 2 06-03-2004 02:33 AM
Restoring MYSQL database without SSH shell access hct224 Linux - Newbie 5 01-08-2004 07:14 AM


All times are GMT -5. The time now is 09:11 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration