526互联

数据库导出指定的时间段

发布时间 2023-07-14 09:51:12作者: Gshelldon
#!/bin/bash
export PATH='/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin'
USER=admin2
PASS=L4bAWUye2A4JNN!
DB_NAME=ng_kboss_personnel

# 导出

mysql -u$USER --password="$PASS" $DB_NAME -Ne "SELECT * FROM ng_attendance_sign WHERE DATE_FORMAT(create_time,'%Y-%m-%d') = DATE_SUB(CURDATE(),interval 1 day);" > /backup/ng_attendance_sign
mysql -u$USER --password="$PASS" $DB_NAME -Ne "SELECT * FROM ng_attendance_schedule WHERE DATE_FORMAT(create_time,'%Y-%m-%d') = DATE_SUB(CURDATE(),interval 1 day);" >/backup/ng_attendance_schedule
mysql -u$USER --password="$PASS" $DB_NAME -Ne "SELECT * FROM ng_attendance_approval WHERE DATE_FORMAT(create_time,'%Y-%m-%d') = DATE_SUB(CURDATE(),interval 1 day);" > /backup/ng_attendance_approval
mysql -u$USER --password="$PASS" $DB_NAME -Ne "SELECT * FROM ng_clocking_evection WHERE DATE_FORMAT(create_time,'%Y-%m-%d') = DATE_SUB(CURDATE(),interval 1 day);" > /backup/ng_clocking_evection
mysql -u$USER --password="$PASS" $DB_NAME -Ne "SELECT * FROM ng_clocking_forget WHERE DATE_FORMAT(create_time,'%Y-%m-%d') = DATE_SUB(CURDATE(),interval 1 day);" > /backup/ng_clocking_forget
mysql -u$USER --password="$PASS" $DB_NAME -Ne "SELECT * FROM ng_clocking_goout WHERE DATE_FORMAT(create_time,'%Y-%m-%d') = DATE_SUB(CURDATE(),interval 1 day);" > /backup/ng_clocking_goout
mysql -u$USER --password="$PASS" $DB_NAME -Ne "SELECT * FROM ng_clocking_leaveflow WHERE DATE_FORMAT(create_time,'%Y-%m-%d') = DATE_SUB(CURDATE(),interval 1 day);" > /backup/ng_clocking_leaveflow
mysql -u$USER --password="$PASS" $DB_NAME -Ne "SELECT * FROM ng_clocking_schedulesign WHERE DATE_FORMAT(create_time,'%Y-%m-%d') = DATE_SUB(CURDATE(),interval 1 day);" > /backup/ng_clocking_schedulesign
mysql -u$USER --password="$PASS" $DB_NAME -Ne "SELECT * FROM ng_clocking_workhome WHERE DATE_FORMAT(create_time,'%Y-%m-%d') = DATE_SUB(CURDATE(),interval 1 day);" > /backup/ng_clocking_workhome
mysql -u$USER --password="$PASS" $DB_NAME -Ne "SELECT * FROM ng_clocking_workover WHERE DATE_FORMAT(create_time,'%Y-%m-%d') = DATE_SUB(CURDATE(),interval 1 day);" > /backup/ng_clocking_workover
mysql -u$USER --password="$PASS" $DB_NAME -Ne "SELECT * FROM ng_attendance_total WHERE belongmonth IN (date_format(CURDATE(), '%m'), date_format(CURDATE(), '%m') -1 );" > /backup/ng_attendance_total

# 导入
cd /backup && \

for i in `ls -1 ng_*`;do
    mysql -uroot -p'qwer1234' -h192.168.21.112 ng_kboss_attendance -e "LOAD DATA INFILE /backup/$i INTO TABLE $i;"
done