Restore individual MySQL records with SELECT INTO OUTFILE and LOAD DATA INFILE

Filed under

Every now and then, a single CMS post may vanish or a forum user's posts are accidentally deleted. No worries, right? That's why we make backups. If you have an active site with lots of users posting content, loading a backup is probably not practical. So, load up the latest incremental backup and select the records you need, and do it easily with MySQL's SELECT INTO OUTFILE and LOAD DATA INFILE.

As the MySQL documentation says, these commands are meant to work in pairs. Here's a simple example of restoring all fields for a specific collection of records.

SELECT * INTO OUTFILE '/tmp/data-out.csv' 
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
  LINES TERMINATED BY '\n' 
  FROM my_table 
  WHERE id = N;

Move the CSV file to your production server and load it. Be sure to specify which fields you selected.

LOAD DATA INFILE '/tmp/data-out.csv' INTO TABLE my_table 
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
  LINES TERMINATED BY '\n' 
  (field1, field2, field3, field4);

Each of these commands is capable of more. See the MySQL docs for complete details.

Share