方法二、使用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_blockWHERE  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_questionWHERE  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.sqlmysqldump -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