mysql单表dump_mysqldump备份单表数据的脚本

1 #!/bin/bash2 # mysqldump script...3

4 # mysqldup备份语句使用说明：5 # mysqldump -S 数据库套接字文件 -u用户名 -p用户密码 -h mysql主机 --skip-lock-tables 数据库名称 表名称 --where "查询条件" >导出文件名.sql6

7 tb_bak="/root/yeah100_tables"

8 my_socket="/data/mysqldata/3307/mysql.sock"

9 my_dump="/usr/local/mysql/bin/mysqldump"

10 d=`date "+%Y%m%d"`11 t=`date "+%Y%m%d_%H:%M:%S"`12

13 # 备份 student_answer_block 和 student_question 表一个月前的数据14 echo "${t} 开始备份 student_answer_block 表一个月前的数据..." >> ${tb_bak}/tb_bak.log15 ${my_dump} -S ${my_socket} -uroot -pever2016 --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 )" > ${tb_bak}/student_answer_block_${d}.sql16 echo "`date"+%Y%m%d_%H:%M:%S"` 完成备份 student_answer_block 表一个月前的数据..." >> ${tb_bak}/tb_bak.log17 echo -e "================================\n================================" >> ${tb_bak}/tb_bak.log18

19 sleep 3

20 echo "${t} 开始备份 student_question 表一个月前的数据..." >> ${tb_bak}/tb_bak.log21 ${my_dump} -S ${my_socket} -uroot -pever2016 --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 )" > ${tb_bak}/student_question_${d}.sql22 echo "`date"+%Y%m%d_%H:%M:%S"` 完成备份 student_question 表一个月前的数据..." >> ${tb_bak}/tb_bak.log23 sleep 3

24

25 #还原到新的数据库里 192.168.0.232:3306数据库：yeah100bakup26 echo "${t} 开始还原 student_answer_block 表一个月前的数据..." >> ${tb_bak}/tb_load.log27 mysql -S /data/mysqldata/3306/mysql.sock -uroot -pever2016 yeah100bakup < ${tb_bak}/student_answer_block_${d}.sql28 echo "`date"+%Y%m%d_%H:%M:%S"` 完成还原 student_answer_block 表一个月前的数据..." >> ${tb_bak}/tb_load.log29 echo -e "================================\n================================" >> ${tb_bak}/tb_load.log30

31 sleep 3

32 echo "${t} 开始还原 student_question 表一个月前的数据..." >> ${tb_bak}/tb_load.log33 mysql -S /data/mysqldata/3306/mysql.sock -uroot -pever2016 yeah100bakup < ${tb_bak}/student_question_${d}.sql34 echo "`date"+%Y%m%d_%H:%M:%S"` 完成还原 student_question 表一个月前的数据..." >> ${tb_bak}/tb_load.log