URGENT: how to restore only a SINGE Table from a MySQL backup

[WorldNews]

Hello,

One of our new guys by mistake DELETED our main users Table. Oooooooh

Thanks goodness we have a recent backup of the Database that contains this Table,
which backup was made a few days ago.

So my question is how does one just restore a SINGE Table from a MySQL backup?
Since we do not want to overwrite the other Tables that are up to date and Ok.

Thank you for your attention to this very URGENT matter.

Regards,

[r937]

open the backup file in a text editor

find the CREATE TABLE statement for the users table, along with all the INSERT statements

copy them out and run them

[WorldNews]

What!

1st, the MySQL backup file s1_backup.sql is HUGE! It is like 1 GB.
So I doubt many Text editors could handle that.

But assuming I can open this .sql file in a Text editor, what does it mean:
"find the CREATE TABLE statement for the users table, along with all the INSERT statements
copy them out and run them"

FYI, the MySQL back up file that we have, has backup of many Databases in it, and not just the DB from which DB we
want to restore 1 Table to the site.

So what I am looking for is a command which command would restore just 1 Table from a MySQL dump that contains many Databases
to the working Database containing the effected Table.

Regards,

[r937]

So I doubt many Text editors could handle that.

i'm sure if you search hard, you can find one

my text editor (♥ ultraedit ♥) can handle files up to 4GB

as for how to find a string of text, that's trivial

[WorldNews]

Actually can we just to this:

1- Restore the backed up databases to my desktop

2- Then just copy the corrupted Table files, that is .frm .MYD .MYI, of the Table that we want to restore, from my
desktop to the server where the production DB is at.

Would this work to restore the Table that has been deleted by mistake?

Regards,

[r937]

if you have the space on your desktop, that's great

1. restore backup to desktop

2. mysqldump the single table into its own .sql file

3. run the .sql file into the live database

[WorldNews]

Actually I am getting Error messages when trying to restore the backed up .sql file of the dump of all databases

I am getting Error messages regarding Tables that are old and not important!

What suggestions please?

[r937]

What suggestions please?

go back to your backup .sql file and extract the CREATE TABLE and INSERT statements for the user table

[WorldNews]

Hi,

Not sure how that is going to help.

We are entering super critical here, since it is 3PM and we have still no restored the User table.
I think best thing is we have a MySQL Admin expert restore this Table for a fee. OTN, can you recommend 1 or 2
good MySQL Admin expert (or firms) that can do such fix? And hopefully at reasonable rates.

Regards,
Dean

go back to your backup .sql file and extract the CREATE TABLE and INSERT statements for the user table

[WorldNews]

Hi,

So I can see the (deleted) Table in the UltraEdit that you suggested. 1st thanx for that suggestion.

So can you please tell me exactly, steps by step, how I restore this 1 Table from this backup of multiple MySQL databases
to the production MySQL server where all the Databases are seating?

Regards,
dean

[WorldNews]

No, sorry, take back the last message.
I cannot see the Table in question in UltraEdit. I guess UltraEdit was able to open the MySQL up to a certain point and not after.

Ayayayay

So if you can recommend some MySQL Admin expert to take care of this fix remotely that would be best.
ThanX.

[ParkinT]

All SQL statements [should] end with a semicolon.
So, if you begin at the CREATE TABLE statement and follow all the parameters (which are separated by commas) to the end (most likely a semicolon), that is the text you want to PRESERVE. I recommend you copy and paste that into another text file. Yes, you can open a simple text file and paste that in.

Then do the same with any INSERT statements that mention that same table.

Once you have completed these steps, simply save the text file and rename it to a .sql extension. This is really not even necessary but will simplify the operation in your mind.

IMPORT that new .sql file into MySQL and you have just SAVED THE DAY.

Grab a coffee and go home.

[WorldNews]

ThanX I took care off this.
Moral of this lesson: do not backup multiple Databases, but backup 1 DB at a time.
Good day all

All SQL statements [should] end with a semicolon.
So, if you begin at the CREATE TABLE statement and follow all the parameters (which are separated by commas) to the end (most likely a semicolon), that is the text you want to PRESERVE. I recommend you copy and paste that into another text file. Yes, you can open a simple text file and paste that in.

Then do the same with any INSERT statements that mention that same table.

Once you have completed these steps, simply save the text file and rename it to a .sql extension. This is really not even necessary but will simplify the operation in your mind.

IMPORT that new .sql file into MySQL and you have just SAVED THE DAY.

Grab a coffee and go home.