Restore from MySQLDump

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.
LVL 5
BelloneAsked:
Who is Participating?
 
johanntagleConnect With a Mentor Commented:
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
 
johanntagleCommented:
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
mysql -u username --password=password -h hostname backupdatabasename < dumpfilename

Open in new window

-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;

Open in new window

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

Open in new window

0
 
johanntagleCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
BelloneAuthor Commented:
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
 
johanntagleCommented:
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;
grant all on backupdatabasename.* to username@localhost identified by 'password';

Check out the link provided by theGhost too - it looks interesting.
0
 
BelloneAuthor Commented:
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

mysql> -u root --password="[password in quotes]"  RestoreDB < localhost_2011-04-15_23-30-00.sql

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
 
johanntagleCommented:
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

Then enter the password myself.

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
 
BelloneAuthor Commented:
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
 
johanntagleCommented:
Oh sorry I missed the fact that you're on windows.  Then it should be something like c:\path\to\file
0
 
BelloneAuthor Commented:
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
 
johanntagleCommented:
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
 
BelloneAuthor Commented:
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
 
johanntagleCommented:
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
 
BelloneAuthor Commented:
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
 
BelloneAuthor Commented:
On the case.  I'll keep you posted...
0
 
BelloneAuthor Commented:
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
 
BelloneAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.