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

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

1、分库备份:

#!/bin/sh

MYUSER=root

MYPASS=123456

SOCKET=/data/3306/mysql.sock

MYLOGIN="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.gz

done

查看备份明细:

[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/sh

USER=root

PASSWD=passw0rd

SOCKET=/data/3306/mysql.sock

MYLOGIN="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 $DATEBASE

do

 TABLE="$($MYLOGIN -e "use $dbname;show tables;"|sed '1d')"

  for tname in $TABLE

  do

MYDIR=/server/backup/$dbname/${dbname}_$(date +%F)

    [ ! -d $MYDIR ] && mkdir -p $MYDIR

 $MYDUMP $dbname $tname |gzip >$MYDIR/${dbname}_${tname}_$(date +%F).sql.gz

    done

done

 

查看备份明细:

[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.gz

9  directories, 18 files

[root@server002 scripts]#

本文永久更新链接地址：