mysql table backup scripts

#!/bin/sh

echo "DB create select Backup Job Start"

yday=`date --date '1 days ago' +%Y%m%d`

test=''

test_index=''

/bin/mkdir /data/mysqldump/$yday 

# Drop Table day write

echo "" >> /root/bin/logdb_backup_1_day_drop.sh

echo "$yday LogDB Drop Table" >> /root/bin/logdb_backup_1_day_drop.sh

echo "" >> /root/bin/logdb_backup_1_day_drop.sh

# LogDB Table create select

for job_table in accesslog aaaa bbbb cccc dddd;

do

test=$job_table\_$yday

test_index=idx_$test\_01

echo "`date '+%F %T'` backing up the previous $job_table: BEGIN" >> /tmp/logDB_back.out

echo "`date '+%F %T'` backing up the previous $job_table: BEGIN"

case $job_table in 

accesslog)

# /usr/local/mysql/bin/mysql -u root -p12345678 logs -e "create table $test select * from $job_table where sdate between subdate(curdate(), 1) and concat(subdate(curdate(),1), ' 23:59:59')"

# /usr/local/mysql/bin/mysql -u root -p12345678 logs -e "create index $teet_index on $test(gid, sdate, edate)"

;;

aaaa)

# /usr/local/mysql/bin/mysql -u root -p12345678 logs -e "create table $test select * from $job_table where axis_date between subdate(curdate(), 1) and concat(subdate(curdate(),1), ' 23:59:59')"

# /usr/local/mysql/bin/mysql -u root -p12345678 logs -e "create index $test_index on $test(gid, axis_date, axis_hour, client_ip)"

;;

bbbb)

# /usr/local/mysql/bin/mysql -u root -p12345678 logs -e "create table $test select * from $job_table where axis_date between subdate(curdate(), 1) and concat(subdate(curdate(),1), ' 23:59:59')"

# /usr/local/mysql/bin/mysql -u root -p12345678 logs -e "create index $test_index on $test(gid, video, axis_date, client_ip)"

;;

cccc)

# /usr/local/mysql/bin/mysql -u root -p12345678 logs -e "create table $test select * from $job_table where cdate between subdate(curdate(), 1) and concat(subdate(curdate(),1), ' 23:59:59')"

# /usr/local/mysql/bin/mysql -u root -p12345678 logs -e "create index $test_index on $test(gid, client_ip, axis_date)"

;;

dddd)

# /usr/local/mysql/bin/mysql -u root -p12345678 logs -e "create table $test select * from $job_table where cdate between subdate(curdate(), 1) and concat(subdate(curdate(),1), ' 23:59:59')"

# /usr/local/mysql/bin/mysql -u root -p12345678 logs -e "create index $test_index on $test(gid, cdate)"

;;

esac

sleep 5

 

echo "`date '+%F %T'` backing up the previous $job_table: END" >> /tmp/logDB_back.out

echo "Job End" >> /tmp/logDB_back.out

echo "`date '+%F %T'` $job_table Table Dump: BEGIN" >> /tmp/logDB_back.out

# /usr/local/mysql/bin/mysqldump -u root -p12345678 --single-transaction logs $test > /data/mysqldump/$yday/$test\_dump.sql

sleep 5

echo "`date '+%F %T'` Table Dump $job_table: END" >> /tmp/logDB_back.out

        echo "Job End" >> /tmp/logDB_back.out

echo "" >> /tmp/logDB_back.out

echo "" >> /tmp/logDB_back.out

# /usr/local/mysql/bin/mysql -u root -p12345678 logs -e "drop table $test"

# Drop Table query 

# echo "/usr/local/mysql/bin/mysql -u root -p12345678 logs -e \"drop table $test\"" >> /root/bin/logdb_backup_1_day_drop.sh

done

echo ""

echo "DB create select Backup Job End" >> /tmp/logDB_back.out

# Services Table Dump Start

echo "`date '+%F %T'` Services Table Dump BEGIN" >> /tmp/logDB_back.out

/usr/local/mysql/bin/mysqldump -u root -p12345678 --single-transaction --ignore-table=zzzz --ignore-table=xxxx --ignore-table=vvvv --ignore-table=nnnn --ignore-table=mmmm logs  > /data/mysqldump/$yday/services_table_$yday\_dump.sql

echo "`date '+%F %T'` Services Table Dump END" >> /tmp/logDB_back.out

echo "`date '+%F %T'` Dump table rsync Start" >> /tmp/lodDB_back.out

/usr/bin/rsync -a -e ssh --bwlimit=1024 /data/mysqldump/$yday/services_table_$yday\_dump.sql root@1.2.3.4:/data/logdb/services_table/services_table_$yday\_dump.sql

/bin/rm -f /data/mysqldump/$yday/services_table_*

/usr/bin/rsync -a -e ssh --bwlimit=1024 /data/mysqldump/$yday/ root@1.2.3.4:/data/logdb/$yday/

# 14 day ago table delete

for delete_table in accesslog axis_client_ip axis_video_client_ip play_log user_env_play_log;

do

        echo "`date '+%F %T'` Delete Table $delete_table: START" >> /tmp/logDB_back.out

case $delete_table in

aaaa)

/usr/local/mysql/bin/mysql -u root -pdevel9870 logs -e "delete from $delete_table where sdate < concat(subdate(curdate(),2), ' 00:00:00')"

;;

bbbb)

/usr/local/mysql/bin/mysql -u root -pdevel9870 logs -e "delete from $delete_table where axis_date < concat(subdate(curdate(),2), ' 00:00:00')"

;;

cccc)

/usr/local/mysql/bin/mysql -u root -pdevel9870 logs -e "delete from $delete_table where axis_date < concat(subdate(curdate(),2), ' 00:00:00')"

;;

dddd)

/usr/local/mysql/bin/mysql -u root -pdevel9870 logs -e "delete from $delete_table where cdate < concat(subdate(curdate(),2), ' 00:00:00')"

;;

eeee)

/usr/local/mysql/bin/mysql -u root -pdevel9870 logs -e "delete from $delete_table where cdate < concat(subdate(curdate(),2), ' 00:00:00')"

;;

esac

sleep 5

        echo "`date '+%F %T'` Delete Table $delete_table: END" >> /tmp/logDB_back.out

        echo "Delete Table End" >> /tmp/logDB_back.out

done

# Job end

echo "`date '+%F %T'` Dump table rsync END" >> /tmp/logDB_back.out

/bin/mv /tmp/logDB_back.out /data/mysqldump/log/logDB_back_$yday