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]#