• 周五. 12月 9th, 2022

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

mysqldump备份单表数据

admin

11月 28, 2021

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

SELECT
  * INTO OUTFILE '/root/student_answer_block.text'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
  LINES TERMINATED BY '
'
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 '
'
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

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注