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

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #2417
    DanutaStavishw
    Spectator

    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:

    #2418
    DanutaStavishw
    Spectator

    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!

Viewing 2 posts - 1 through 2 (of 2 total)
  • You must be logged in to reply to this topic.