MySQL分库分表备份脚本

备份思想

1. 拿到库名列表

2. 循环数据库，分别备份

分库备份脚本

vim oldboy07_01.sh
#!/bin/bash
BAKPATH=/server/backup
MYUSER=root
MYPASS=123456
SOCKET=/data/3306/mysql.sock
MYCMD="mysql -u$MYUSER -p$MYPASS -S $SOCKET"
MYDUMP="mysqldump -u$MYUSER -p$MYPASS -S $SOCKET -x -B -F -R"
[ ! -d $BAKPATH ] && mkdir -p $BAKPATH
DBLIST=`$MYCMD -e "show databases;" | sed 1d | egrep -v "_schema|mysql"`
for dbname in $DBLIST
do
     $MYDUMP $dbname|gzip >/server/backup/${dbname}_$(date +%F).sql.gz
done

 分表备份脚本

cp oldboy07_01.sh oldboy08_01.sh
vim oldboy08_01.sh
#!/bin/bash
BAKPATH=/server/backup
MYUSER=root
MYPASS=123456
SOCKET=/data/3306/mysql.sock
MYCMD="mysql -u$MYUSER -p$MYPASS -S $SOCKET"
MYDUMP="mysqldump -u$MYUSER -p$MYPASS -S $SOCKET -x -F -R"
[ ! -d $BAKPATH ] && mkdir -p $BAKPATH
DBLIST=`$MYCMD -e "show databases;" | sed 1d | egrep -v "_schema|mysql"`
for dbname in $DBLIST
do
     TLIST=`$MYCMD -e "show tables from $dbname;"|sed 1d `
     for tname in $TLIST
     do
     mkdir -p $BAKPATH/$dbname
     $MYDUMP $dbname $tname|gzip >/server/backup/$dbname/${tname}_$(date +%F).sql.gz
     done
done