Mysqldump分库分表备份

分库备份：

#!/bin/bash
MYUSER=root
MYPASSWD=mysqld
SOCKET=/u01/my3306/run/mysql.sock
MYSQLCMD="mysql -u$MYUSER -p$MYPASSWD -S $SOCKET"
MYSQLDUMP="mysqldump -u$MYUSER -p$MYPASSWD -S $SOCKET"
for database in test test1
do
$MYSQLDUMP $database | gzip > /backup/bak/${database}_$(date +%F).sql.gz
done

分表备份：

#!/bin/bash
MYUSER=root
MYPASSWD=mysqld
SOCKET=/u01/my3306/run/mysql.sock
#　MYSQLCMD="mysql -u$MYUSER -P$MYPASSWD -S $SOCKET"
MYSQLCMD="/u01/my3306/bin/mysql -u${user} -p${pass}"
MYSQLDUMP="mysqldump -u$MYUSER -p$MYPASSWD -S $SOCKET"
for database in `$MYSQLCMD -e "show databases;" | sed '1,2d' | egrep -v "mysql|schema"`
do
   mkdir -p /backup/bak/${database}
   for table in `$MYSQLCMD -e "show tables from $database;" | sed '1d'`
   do
   $MYSQLDUMP $database $table | gzip > /backup/bak/${database}/${database}_${table}_$(date +%F).sql.gz
   done
done