MySQL – How to restore selected records?

Home › Forums › Tech › Web Development › MySQL – How to restore selected records?

Tagged: , , , , , ,

This topic contains 1 reply, has 1 voice, and was last updated by  DanutaStavishw 11 years, 1 month ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • 2013/02/28 at 08:42 #2417

    DanutaStavishw
    Participant

    Here is a situation:

    If you accidentally deleted some records from MySQL database and need to restore them, but not the entire database, here are the steps. In my case, I delete a WordPress user with 9 posts.

    I did have a 3-day-old database backup. And I also have a backup/test server where the same, but older database is loaded.

    (1) First, I would load the database backup from the latest backup into my test site. This step is well documented elsewhere.

    (2) Then, I would export required records into a CSV file:

    SELECT * FROM wp_users WHERE ID=169
    INTO OUTFILE 'Q:\\T\\wp_users.csv'
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n';

    SELECT * FROM wp_usermeta WHERE user_id=169
    INTO OUTFILE 'Q:\\T\\wp_usermeta.csv'
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n';

    (3) Finally I would transfer generated CSV files to a production server and import them into designated tables:

    2013/02/28 at 19:53 #2418

    DanutaStavishw
    Participant

    LOAD DATA LOCAL INFILE 'X:\\T\\wp_users.csv'
    INTO TABLE wp_users
    COLUMNS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n';

    LOAD DATA LOCAL INFILE 'X:\\T\\wp_usermeta.csv'
    INTO TABLE wp_usermeta
    COLUMNS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n';

    Please note that you can even restore key fields, that are set to auto-increase, as long as you to do not attempt to create a duplicate records.  This simple method allowed me to restore a delete user, and later a was able to link her articles back to the original user.  Major PR disaster averted!

  • Author
    Posts
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.