Restore from MySQLDump

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

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

Select allOpen 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

Select allOpen in new window

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.

Try this:
http://kedar.nitty-witty.com/blog/mydumpsplitter-extract-tables-from-mysql-dump-shell-script

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

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.

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?

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

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

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

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

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;

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

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?

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

On the case.  I'll keep you posted...

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

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.