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

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.

Comments

Restoring a data from the

Restoring a data from the database is one functional item in a system. It is very important to restore at some point. -buy real instagram comments

 If you have an active site

 If you have an active site with lots of users posting content, loading a backup is probably not practical. duct cleaning

Post new comment

The content of this field is kept private and will not be shown publicly. If you have a Gravatar account associated with the e-mail address you provide, it will be used to display your avatar.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <p> <span> <div> <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <codeblock> <blockquote> <h1> <h2> <h3> <h4> <h5> <h6> <img> <fieldset> <legend>
  • Lines and paragraphs break automatically.
  • You may link to webpages through the weblinks registry
  • Syntax highlight code surrounded by the {syntaxhighlighter SPEC}...{/syntaxhighlighter} tags, where SPEC is a Syntaxhighlighter options string or "class="OPTIONS" title="the title".

More information about formatting options

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
copyright © 2011, 2 tablespoons | Privacy Policy