mysqldump备份单表数据

方法二、使用MySQL的SELECT INTO OUTFILE 备份语句（推荐）
在下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。

SELECT
  * INTO OUTFILE '/root/student_answer_block.text'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
  LINES TERMINATED BY '\n'
FROM
  student_answer_block
WHERE
  examination_id IN (
    SELECT
      ID
    FROM
      examinations
    WHERE
      STATISTIC_TRIGGERED = 'Y'
    AND STATISTIC_DATE < DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
    AND ORG_NO IS NOT NULL
    ORDER BY
      STATISTIC_DATE DESC
  );





SELECT
  * INTO OUTFILE '/root/student_question.text'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
  LINES TERMINATED BY '\n'
FROM
  student_question
WHERE
  examination_id IN (
    SELECT
      ID
    FROM
      examinations
    WHERE
      STATISTIC_TRIGGERED = 'Y'
    AND STATISTIC_DATE < DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
    AND ORG_NO IS NOT NULL
    ORDER BY
      STATISTIC_DATE DESC
  );

 

 

方法三、使用mysqldump
很奇妙的是我发现了mysqldump其实有个很好用的参数“—w”
帮助文档上说明：
-w, --where=name Dump only selected records. Quotes are mandatory.
Defaults to on; use --skip-lock-tables to disable

 

备份一个月前的数据：
mysqldump -S /data/mysqldata/3307/mysql.sock -uroot -p --skip-lock-tables yeah100 student_answer_block --where "examination_id IN ( SELECT ID FROM examinations WHERE STATISTIC_TRIGGERED = 'Y' AND STATISTIC_DATE < DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND ORG_NO IS NOT NULL ORDER BY STATISTIC_DATE DESC )" > /tmp/student_answer_block.sql



mysqldump -S /data/mysqldata/3307/mysql.sock -uroot -p --skip-lock-tables yeah100 student_question --where "examination_id IN ( SELECT ID FROM examinations WHERE STATISTIC_TRIGGERED = 'Y' AND STATISTIC_DATE < DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND ORG_NO IS NOT NULL ORDER BY STATISTIC_DATE DESC )" > /tmp/student_question.sql

 

 

还原数据库方法：

mysql -S /data/mysqldata/3306/mysql.sock -uroot -p yeah100bakup < ./student_question.sql