MySQL – How to restore selected records?

[DanutaStavishw]

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:

[DanutaStavishw]

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