worldnews — 2012-03-06T11:12:13-05:00 — #1
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.
r937 — 2012-03-06T11:58:10-05:00 — #2
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 — 2012-03-06T12:06:13-05:00 — #3
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.
r937 — 2012-03-06T12:23:56-05:00 — #4
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 — 2012-03-06T12:50:17-05:00 — #5
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?
r937 — 2012-03-06T13:04:10-05:00 — #6
if you have the space on your desktop, that's great
restore backup to desktop
mysqldump the single table into its own .sql file
run the .sql file into the live database
worldnews — 2012-03-06T13:07:48-05:00 — #7
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 — 2012-03-06T13:25:31-05:00 — #8
go back to your backup .sql file and extract the CREATE TABLE and INSERT statements for the user table
worldnews — 2012-03-06T15:15:37-05:00 — #9
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.
worldnews — 2012-03-06T16:17:14-05:00 — #10
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?
worldnews — 2012-03-06T16:24:42-05:00 — #11
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.
So if you can recommend some MySQL Admin expert to take care of this fix remotely that would be best.
parkint — 2012-03-06T16:25:11-05:00 — #12
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 — 2012-03-08T11:53:13-05:00 — #13
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
mittineague — 2014-09-23T21:29:49-04:00 — #14
This topic is now archived. It is frozen and cannot be changed in any way.