分库备份mysql 5.6

发表评论
3,242

A+

最近无聊~~~~~~~~~~~~写的~~~~~~~~~~~~~~

备份后目录/data/dbback/备份当天日期/备份当天日期.tar.gz ，里面包含各个数据库的sql

#!/bin/bash
#

mysql_bin_dir=/usr/local/mysql/bin
back_dir=/data/dbback
backup_user=#mysql备份账号
backup_password=#mysql密码
date_day=`date +%F`
if [ -d $back_dir/$date_day ];then
        exit 1
else
        mkdir -pv $back_dir/$date_day
fi

for db_name in `$mysql_bin_dir/mysql -u$backup_user -p$backup_password -e "show databases;"|grep -Ev "Database|information_schema|performance_schema"`;do
        $mysql_bin_dir/mysqldump -u$backup_user -p$backup_password --master-data=2 --set-gtid-purged=off --hex-blob --single-transaction --events --routines --skip-add-locks  $db_name>$back_dir/$date_day/$db_name.sql
done
cd $back_dir/$date_day
tar -zcf $date_day.tar.gz *.sql
rm -f *.sql

#!/bin/bash

mysql_bin_dir=/usr/local/mysql/bin

back_dir=/data/dbback

backup_user=#mysql备份账号

backup_password=#mysql密码

date_day=`date +%F`

if [ -d $back_dir/$date_day ];then

exit 1

else

mkdir -pv $back_dir/$date_day

for db_name in `$mysql_bin_dir/mysql -u$backup_user -p$backup_password -e "show databases;"|grep -Ev "Database|information_schema|performance_schema"`;do

$mysql_bin_dir/mysqldump -u$backup_user -p$backup_password --master-data=2 --set-gtid-purged=off --hex-blob --single-transaction --events --routines --skip-add-locks $db_name>$back_dir/$date_day/$db_name.sql

done

cd $back_dir/$date_day

tar -zcf $date_day.tar.gz *.sql

rm -f *.sql