MySQL数据库分库备份

1.创建数据库

#!/bin/bash
#Date:2020-01-8
#Function:create database
PATH="/path/mysql/bin:$PATH"
myuser=root
mypass=123
mycmd="mysql -u$myuser -p$mypass"
for db_name in db1,db2,db3
do
    $mycmd -e "create database $db_name"
done

2.备份数据库

#!/bin/bash
#Date:2020-01-8
#Function:backup database
PATH="/path/mysql/bin:$PATH"
dbpath=/dbdata/backup
myuser=root
mypass=123
mycmd="mysql -u$myuser -p$mypass"
mydump="mysqldump -u$myuser -p$mypass"
[ ! -d "$dbpath" ] && mkdir -p $dbpath
for db_name in `$mycmd -e "show databases;"|sed '1,2d'|egrep -v "mysql|schema|sys"`
do
    $mydump $db_name|gzip >$dbpath/${db_name}_$(date +%F).sql.gz
done