1 #!/bin/bash
2 # mysqldump script...
3
4 # mysqldup备份语句使用说明：
5 # mysqldump -S 数据库套接字文件 -u用户名 -p用户密码 -h mysql主机 --skip-lock-tables 数据库名称 表名称 --where "查询条件" > 导出文件名.sql
6
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.log
15 ${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}.sql
16 echo "`date "+%Y%m%d_%H:%M:%S"` 完成备份 student_answer_block 表一个月前的数据..." >> ${tb_bak}/tb_bak.log
17 echo -e "================================\n================================" >> ${tb_bak}/tb_bak.log
18
19 sleep 3
20 echo "${t} 开始备份 student_question 表一个月前的数据..." >> ${tb_bak}/tb_bak.log
21 ${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}.sql
22 echo "`date "+%Y%m%d_%H:%M:%S"` 完成备份 student_question 表一个月前的数据..." >> ${tb_bak}/tb_bak.log
23 sleep 3
24
25 #还原到新的数据库里 192.168.0.232:3306 数据库：yeah100bakup
26 echo "${t} 开始还原 student_answer_block 表一个月前的数据..." >> ${tb_bak}/tb_load.log
27 mysql -S /data/mysqldata/3306/mysql.sock -uroot -pever2016 yeah100bakup < ${tb_bak}/student_answer_block_${d}.sql
28 echo "`date "+%Y%m%d_%H:%M:%S"` 完成还原 student_answer_block 表一个月前的数据..." >> ${tb_bak}/tb_load.log
29 echo -e "================================\n================================" >> ${tb_bak}/tb_load.log
30
31 sleep 3
32 echo "${t} 开始还原 student_question 表一个月前的数据..." >> ${tb_bak}/tb_load.log
33 mysql -S /data/mysqldata/3306/mysql.sock -uroot -pever2016 yeah100bakup < ${tb_bak}/student_question_${d}.sql
34 echo "`date "+%Y%m%d_%H:%M:%S"` 完成还原 student_question 表一个月前的数据..." >> ${tb_bak}/tb_load.log
