MySQL备份之分库分表备份脚本

1、分库备份:#！/bin/shMYUSER=rootMYPASS=123456SOCKET=/data/3306/mysql.sockMYLOGIN="mysql -u$MYUSER -p$MYPASS -S $SOCKET"MYDUMP="mysqldump -u$MYUSER -p$MYPASS -S$SOCKET -B"DATABASE="$（$MYLOGIN -e "show databases;"|egrep -vi "Data|_schema|mysql"）" for dbname in $DATABASE do MYDIR=/server/backup/$dbname [ ！ -d $MYDIR ] && mkdir -p $MYDIR $MYDUMP $dbname|gzip >$MYDIR/${dbname}_$（date +%F）.sql.gzdone查看备份明细:[root@server001 ~]#tree /server/backup//server/backup/├── opuser26│ ├──opuser26_2015-12-29.sql.gz│ ├──opuser26_2015-12-30.sql.gz│ └──opuser26_2015-12-31.sql.gz├── opuser_users26│ ├──opuser_users26_2015-12-29.sql.gz│ ├──opuser_users26_2015-12-30.sql.gz│ └──opuser_users26_2015-12-31.sql.gz└── users26 ├──users26_2015-12-29.sql.gz ├──users26_2015-12-30.sql.gz └──users26_2015-12-31.sql.gz 3 directories, 9 files[root@server001 ~]#2、分库分表备份:#！/bin/shUSER=rootPASSWD=passw0rdSOCKET=/data/3306/mysql.sockMYLOGIN="mysql -u$USER -p$PASSWD -S$SOCKET"MYDUMP="mysqldump -u$USER -p$PASSWD -S$SOCKET"DATEBASE="$（$MYLOGIN -e "show databases;"|egrep -vi "Data|_schema|mysql"）" for dbname in $DATEBASEdo TABLE="$（$MYLOGIN -e "use $dbname;show tables;"|sed "1d"）" for tname in $TABLE doMYDIR=/server/backup/$dbname/${dbname}_$（date +%F） [ ！ -d $MYDIR ] && mkdir -p $MYDIR $MYDUMP $dbname $tname |gzip >$MYDIR/${dbname}_${tname}_$（date +%F）.sql.gz donedone 查看备份明细:[root@server002 scripts]# tree /server/backup//server/backup/├── opuser26│ ├──opuser26_2015-12-30│ │├──opuser26_opuser_test_2015-12-30.sql.gz│ │└──opuser26_opuser_test2_2015-12-30.sql.gz│ └──opuser26_2015-12-31│ ├── opuser26_opuser_test_2015-12-31.sql.gz│ └── opuser26_opuser_test2_2015-12-31.sql.gz├── opuser_products26│ ├──opuser_products26_2015-12-30│ │├──opuser_products26_cloudstack_2015-12-30.sql.gz│ │├──opuser_products26_dashborad_2015-12-30.sql.gz│ │└── opuser_products26_student_2015-12-30.sql.gz│ └──opuser_products26_2015-12-31│ ├──opuser_products26_cloudstack_2015-12-31.sql.gz│ ├──opuser_products26_dashborad_2015-12-31.sql.gz│ └──opuser_products26_student_2015-12-31.sql.gz└── products26 ├──products26_2015-12-30 │├──products26_openstack_2015-12-30.sql.gz │├──products26_saltstack_2015-12-30.sql.gz │├──products26_server_2015-12-30.sql.gz │└──products26_zabbix_2015-12-30.sql.gz └──products26_2015-12-31 ├──products26_openstack_2015-12-31.sql.gz ├──products26_saltstack_2015-12-31.sql.gz ├──products26_server_2015-12-31.sql.gz └──products26_zabbix_2015-12-31.sql.gz9 directories, 18 files[root@server002 scripts]#本文永久更新链接地址