1.分库备份,库名手动提供
#!/bin/bash
MYUSER=root
MYPASS=123456
MYCMD="mysql -u$MYUSER -$MYPASS"
MYDUMP="mysqldump -u$MYUSER -p$MYPASS"
for database in beijing shanghai shenzhen
do
$MYDUMP $database |gzip > /tmp/backup/${database}_$(date +%F).sql.gz
done
2.分库备份,库名自动查询自动备份
#!/bin/bash
MYUSER=root
MYPASS=123456
MYCMD="mysql -u$MYUSER -p$MYPASS"
MYDUMP="mysqldump -u$MYUSER -p$MYPASS"
for database in `$MYCMD -e "show databases;"|sed '1,2d'|egrep -v 'mysql|test'`
do
$MYDUMP $database |gzip > /tmp/backup/${database}_$(date +%F).sql.gz
done
3.分表备份,库名表明均自动查询自动备份
#!/bin/bash
MYUSER=root
MYPASS=123456
MYCMD="mysql -u$MYUSER -p$MYPASS"
MYDUMP="mysqldump -u$MYUSER -p$MYPASS"
for database in `$MYCMD -e "show databases;"|sed '1,2d'|egrep -v 'mysql|test'`
do
for table in `$MYCMD -e "show tables from $database;"|sed '1d'`
do
mkdir -p /tmp/backup/$database/
$MYDUMP $database $table |gzip > /tmp/backup/$database/${database}_${table}_$(date +%F).sql.gz
done
done
转载于:https://blog.51cto.com/utrace/1386088