- This topic has 1 reply, 1 voice, and was last updated 11 years, 8 months ago by
.
-
Posts
-
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:
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!
- You must be logged in to reply to this topic.