MySQL分库分表备份脚本
MySQL分库备份脚本
#脚本详细内容
1 [root@db02 scripts]# cat /server/scripts/Store_backup.sh 2 #!/bin/sh 3 MYUSER=root 4 MYPASS=yangliheng 5 SOCKET=/data/3306/mysql.sock 6 MYLOGIN="mysql -u$MYUSER -p$MYPASS -S $SOCKET" 7 MYDUMP="mysqldump -u$MYUSER -p$MYPASS -S$SOCKET -B" 8 DATABASE="$($MYLOGIN -e "show databases;"|egrep -vi "Data|_schema|mysql")" 9 10 for dbname in $DATABASE 11 do 12 MYDIR=/server/backup/$dbname 13 [ ! -d $MYDIR ] && mkdir -p $MYDIR 14 $MYDUMP $dbname|gzip >$MYDIR/${dbname}_$(date +%F).sql.gz 15 done
#执行后的结果
1 [root@db02 /]# tree /server/backup/ 2 /server/backup/ 3 ├── oldboy28 4 │ ├──oldboy28_2016-07-29.sql.gz 5 │ ├──oldboy28_2016-07-30.sql.gz 6 │ └──oldboy28_2016-07-31.sql.gz 7 ├── oldboy_yangliheng28 8 │ ├──oldboy_yangliheng28_2016-07-29.sql.gz 9 │ ├──oldboy_yangliheng28_2016-07-30.sql.gz 10 │ └──oldboy_yangliheng28_2016-07-31.sql.gz 11 └── yangliheng28 12 ├──yangliheng28_2016-07-29.sql.gz 13 ├──yangliheng28_2016-07-30.sql.gz 14 └── yangliheng28_2016-07-31.sql.gz 15 16 3 directories, 9 files
MySQL分库分表备份脚本
#脚本详细内容
1 [root@db02 scripts]# cat mysql_table.sh 2 #!/bin/sh 3 USER=root 4 PASSWD=yangliheng 5 SOCKET=/data/3306/mysql.sock 6 MYLOGIN="mysql -u$USER -p$PASSWD -S$SOCKET" 7 MYDUMP="mysqldump -u$USER -p$PASSWD -S$SOCKET" 8 DATEBASE="$($MYLOGIN -e "show databases;"|egrep -vi "Data|_schema|mysql")" 9 10 for dbname in $DATEBASE 11 do 12 TABLE="$($MYLOGIN -e "use $dbname;show tables;"|sed '1d')" 13 for tname in $TABLE 14 do 15 MYDIR=/server/backup/$dbname/${dbname}_$(date +%F) 16 [ ! -d $MYDIR ] && mkdir -p $MYDIR 17 $MYDUMP $dbname $tname |gzip >$MYDIR/${dbname}_${tname}_$(date +%F).sql.gz 18 done 19 done
#查看执行后的结果
1 [root@db02 scripts]# tree /server/backup/ 2 /server/backup/ 3 ├── oldboy28 4 │ ├──oldboy28_2016-07-30 5 │ │├──oldboy28_oldboy_test_2016-07-30.sql.gz 6 │ │└──oldboy28_oldboy_test2_2016-07-30.sql.gz 7 │ └──oldboy28_2016-07-31 8 │ ├── oldboy28_oldboy_test_2016-07-31.sql.gz 9 │ └── oldboy28_oldboy_test2_2016-07-31.sql.gz 10 ├── oldboy_yangliheng28 11 │ ├──oldboy_yangliheng28_2016-07-30 12 │ │├──oldboy_yangliheng28_cloudstack_2016-07-30.sql.gz 13 │ │├──oldboy_yangliheng28_dashborad_2016-07-30.sql.gz 14 │ │└── oldboy_yangliheng28_student_2016-07-30.sql.gz 15 │ └──oldboy_yangliheng28_2016-07-31 16 │ ├──oldboy_yangliheng28_cloudstack_2016-07-31.sql.gz 17 │ ├──oldboy_yangliheng28_dashborad_2016-07-31.sql.gz 18 │ └──oldboy_yangliheng28_student_2016-07-31.sql.gz 19 └── yangliheng28 20 ├──yangliheng28_2016-07-30 21 │├──yangliheng28_openstack_2016-07-30.sql.gz 22 │├──yangliheng28_saltstack_2016-07-30.sql.gz 23 │├──yangliheng28_server_2016-07-30.sql.gz 24 │└──yangliheng28_zabbix_2016-07-30.sql.gz 25 └──yangliheng28_2016-07-31 26 ├──yangliheng28_openstack_2016-07-31.sql.gz 27 ├──yangliheng28_saltstack_2016-07-31.sql.gz 28 ├──yangliheng28_server_2016-07-31.sql.gz 29 └──yangliheng28_zabbix_2016-07-31.sql.gz 30 31 9 directories, 18 files
