SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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,

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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,

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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,

    Anoox search engine volunteer

    www.anoox.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

    Anoox search engine volunteer

    www.anoox.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    What suggestions please?
    go back to your backup .sql file and extract the CREATE TABLE and INSERT statements for the user table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

    Quote Originally Posted by r937 View Post
    go back to your backup .sql file and extract the CREATE TABLE and INSERT statements for the user table

    Anoox search engine volunteer

    www.anoox.com

  10. #10
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

    Anoox search engine volunteer

    www.anoox.com

  11. #11
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

    Anoox search engine volunteer

    www.anoox.com

  12. #12
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,337
    Mentioned
    192 Post(s)
    Tagged
    4 Thread(s)
    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.
    Don't be yourself. Be someone a little nicer. -Mignon McLaughlin, journalist and author (1913-1983)


    Git is for EVERYONE
    Literally, the best app for readers.
    Make Your P@ssw0rd Secure
    Leveraging SubDomains

  13. #13
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

    Quote Originally Posted by ParkinT View Post
    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.

    Anoox search engine volunteer

    www.anoox.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •