Solved

# Restore from MySQLDump

Posted on 2011-05-03
Medium Priority
619 Views
I have a service database with Access 2003 front-end (30Mb) and MySQL 5 back-end (200Mb).  A MySQL dump of the back-end is taken every night and retained for some months.

Occasionally, one of the users will accidentally delete a record from one of the tables and need to have it recovered from backup.

Can someone please recommend a GUI app which will allow me to restore a single table from the relevant MySQLDump and store it somewhere other than the current version of the database?  I just need to be able to link-in to that table and append the missing record back into the current version.
0
Question by:Bellone
• 9
• 8

LVL 24

Expert Comment

ID: 35511815
I don't think there's one.  A MySQL dump is really a plain text file containing SQL statements.  Some things you can do:

1.  After mysqldump is completed, immediately import the whole thing to the other database.  Your nightly backup script should look something like:
mysqldump --add-drop-table -u username --password=password databasename > dumpfilename

-h hostname above is optional of course if the backup database is on the same server.

Then when you need to restore a certain table, assuming the backup database is on the same server and is accessible via the same username, you can restore via the following commands within mysql command line:
truncate table_name;
insert into table_name select * from backupdatabasename.table_name;

If the backup database is not on the same server, then you can (not using --password here since I assume you're doing this by hand):
mysqldump --add-drop-table -u username -h hostname -p backupdatabasename table_name > tabledumpfile
mysql -u username -p databasename < tabledumpfile

0

LVL 24

Expert Comment

ID: 35511865
Oh you just need to just retrieve the row.  Well, it looks like you already know how to do that so disregard my second and third set of code above =)

Another option I was thinking of is opening the dumpfile and extracting the actual statements that recreate the table and its contents - but that could get really unwieldy, so I won't continue explaining it.

Is there only one or a few tables that users accidentally delete from?  Maybe, you can create a BEFORE DELETE trigger for those tables that copies the deleted row to a backup table.  This way you can easily restore from within the same database.
0

LVL 21

Expert Comment

ID: 35511943
0

LVL 5

Author Comment

ID: 35511959
Hi Johanntagle - thanks for quick reply.

Am I right in thinking that line 2 of your first code block will recreate the backup .sql database with a name of my choice?  If so, that would do.  I can then use Access to attach to the relevant table in that file and find the record I want. (Being new to MySQL, I am terrified of restoring a month-old backup on top of the current version!)

All files are on the same server.

Regards, Bellone
0

LVL 24

Expert Comment

ID: 35511975
Hi Bellone,

No you need to create the backup database beforehand before you can import into it.  Just execute in mysql prompt with admin user:

create database backupdatabasename;

Check out the link provided by theGhost too - it looks interesting.
0

LVL 5

Author Comment

ID: 35512269
Sorry to be dense, but I'm getting a syntax error.  I've created the new database with all rights for root.  My command line at the mysql prompt  for the restore operation was

Ought I to have some more quotes somewhere?  Also file localhost_2011-04-15_23-30-00.sql
is in a different drive from RestoreDB - do I need to insert paths?
0

LVL 24

Expert Comment

ID: 35512569
No you do that in the unix command line, not in mysql prompt.  And no quotes for password.  I would normally not use --password when I type the command myself, since people might see it.  I would use:

mysql -u root -p RestoreDB < localhost_2011-04-15_23-30-00.sql

I use --password when I run it from a script so the password has to be pre-entered.

Finally, you just run that wherever localhost_2011-04-15_23-30-00.sql is located, else do:

mysql -u root -p RestoreDB < /full/path/to/localhost_2011-04-15_23-30-00.sql
0

LVL 5

Author Comment

ID: 35512844
So sorry, still getting syntax probs.  Please confirm that there should be a leading slash before the filepath and that they are definitely forward slashes, not backslashes
B
0

LVL 24

Expert Comment

ID: 35512907
Oh sorry I missed the fact that you're on windows.  Then it should be something like c:\path\to\file
0

LVL 5

Author Comment

ID: 35513033
Hmm.  The actual command I am issuing at the mysql> prompt in the DOS window is:

-u root -p AVMRestore < f:\mysqldumps\helpdesk\helpdeskdat\localhost_2011-04-15_23-30-00.sql;

(AVMRestore being the destination database)  This is now giving me:
ERROR Unknown command '\m'.

Different, but no cigar!  Any further ideas please?

B
0

LVL 24

Expert Comment

ID: 35513157
As I said, don't issue it in the mysql prompt but your OS command line.

c:\mysql -u root -p AVMRestore < f:\mysqldumps\helpdesk\helpdeskdat\localhost_2011-04-15_23-30-00.sql;
0

LVL 5

Author Comment

ID: 35513627
NIGHTMARE!

'c:\mysql -u root -p AVMRestore < f:\mysqldumps\helpdesk\helpdeskdat\localhost_2011-04-15_23-30-00.sql;' HAS JUST RESTORED 15th APRIL OVER THE TOP OF TODAYS VERSION WHICH PEOPLE ARE WORKING ON.  JUST WHAT I DIDN'T WANT TO HAPPEN!!!!!
0

LVL 24

Expert Comment

ID: 35544739
Sorry, but I have no knowledge of how localhost_2011-0415_23-30-00.sql was created.  The instructions I gave were based on how I ran mysqldump.  You should have compared it with how your nightly script runs it before proceeding.  It will likely have the --databases option which adds a line in the dumpfile that forces the data to be imported to the database of the same name where it came from.  How about then you import from your latest backup?
0

LVL 5

Author Comment

ID: 35708316
Sorry for the delay - been licking my wounds and regaining my equanimity.  I suspect you are right, the backed-up sql file contains the lines:

CREATE DATABASE IF NOT EXISTS helpdeskdat DEFAULT CHARACTER SET latin1;
USE helpdeskdat;

Presumably this negates the command we were giving it to restore to a different database:

...AVMRestore < f:\mysqldumps\helpdesk\helpdeskdat\localhost_2011-04-15_23-30-00.sql;

B
0

LVL 24

Accepted Solution

johanntagle earned 1500 total points
ID: 35710050
Yes specifically the "USE helpdeskdat;" did it.  Remove that line and you should be able to restore to a different database.  But learning from this, before you try to restore again please first look at your nightly backup script that calls mysqldump.  Let's see what other options were used to create the backup sql file.
0

LVL 5

Author Comment

ID: 35719663
On the case.  I'll keep you posted...
0

LVL 5

Author Comment

ID: 35745880
Got it going now.  I am doing additinal back-ups by table, and am able to restore individual tables under a different name, when needed, by changing the table name in the MySQLDump file.  Many thanks for your advice and patience.

B
0

LVL 5

Author Closing Comment

ID: 35745901
Got there in the end, but not before I had caused 30 people to lose half a day's work by inadvertently restoring a month-old backup over the current version.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
###### Suggested Courses
Course of the Month16 days, 21 hours left to enroll