MySQL三部曲备份【逻辑备份+物理备份】脚本，生产实践

前言：

  • 逻辑备份【mysqldump】
  • 物理备份【Xtrbackup】

1、mysqldump全备

建议手动制定库，不要选择–all ，容易在数据恢复时发生问题

  • --set-gtid-purged=OFF 选项，在备份时会记录binlog日志，如果不加，不记录binlog日志，所以在我们做主从用了gtid时，用mysqldump备份时就要加--set-gtid-purged=OFF，否则你在主上导入恢复了数据，主没有了binlog日志，同步则不会被同步。
  • --routines 用于在MySQL数据库备份时包含存储过程（Stored Procedures）和存储函数（Stored Functions）。
  • --master-data 选项，如果，遇到需要恢复从库的场景，需要使用 --master-data选项，参数会自动在导出的SQL文件中插入一个特殊的注释或者命令（CHANGE MASTER TO）。这个命令包含了备份时刻主服务器的二进制日志文件名（LOG_FILE）和位置（LOG_POS）。

备份前准备：需要创建一个.my.cnf 配置文件用来指定备份用户

vim /home/.my.cnf
[mysqldump]
host=localhost
user=root
password=XXXXXXX
 
[mysql]
host=localhost
user=root
password=XXXXXXX

1.1通用备份脚本：

  • 找出需要备份的数据库
  • 配置备份数据路径
  • 配置备份数据需要保留的天数
[root@localhost bak]# cat /home/application/mysql/bak/back_full.sh
 
#找出需要备份的库名
name=`/home/application/mysql/app/bin/mysql --defaults-extra-file=/home/.my.cnf -e "show databases;"|grep -Evi "database|sys|information_schema|mysql|performance_schema" | tr "\n"  " "`
 
#备份数据，加上存储过程
/home/application/mysql/app/bin/mysqldump --defaults-extra-file=/home/.my.cnf --routines --set-gtid-purged=OFF --databases $name > /home/application/mysql/bak/full_bak/full_`date +"%F"`.sql 2>&1
  
#删除30天之前的备份
/usr/bin/find /home/application/mysql/bak/full_bak/ -name "full_*" -ctime +30 | xargs rm -rf

1.2 主从恢复备份脚本：

恢复步骤大致如下：

  1. mysqldump 备份主库数据，加上--master-data选项
  2. 登录从库，停止 主从同步，stop slave;
  3. source XXX.sql; 恢复数据
  4. start slave; 启动主从同步
  5. show slave status\G; 查看主从同步状态
#找出需要备份的库名
name=`/home/application/mysql/app/bin/mysql --defaults-extra-file=/home/.my.cnf -e "show databases;"|grep -Evi "database|sys|information_schema|mysql|performance_schema" | tr "\n"  " "`

#备份数据，加上存储过程,加上备份时刻主服务器的二进制日志文件名（LOG_FILE）和位置（LOG_POS）。
/home/application/mysql/app/bin/mysqldump --defaults-extra-file=/home/.my.cnf --master-data --routines --set-gtid-purged=OFF --databases $name > /home/application/mysql/bak/full_bak/full_`date +"%F"`.sql 2>&1

2、mysqldump分库备份

:::danger
备份前准备：需要创建一个.my.cnf 配置文件用来指定备份用户
:::

vim /home/.my.cnf

[mysqldump]
host=localhost
user=root
password=XXXXXXX
 
[mysql]
host=localhost
user=root
password=XXXXXXX

2.1 通用备份脚本：

  • 找出需要备份的数据库
  • 配置备份数据路径
  • 配置备份数据需要保留的天数
#!/bin/bash
source /etc/profile
DATE=`date '+%Y%m%d-%H%M'`
BACKUP_DIR=/srebro/mysql/backup/mysql_fenku/$DATE
LOGFILE=/srebro/mysql/backup/mysql_fenku/$DATE/data_backup.log
name=`/srebro/mysql/app/bin/mysql --defaults-extra-file=/home/.my.cnf -e "show databases;"|grep -Evi "database|sys|information_schema|mysql|performance_schema"`
  
if [ ! -d $BACKUP_DIR ]
  then
        mkdir -p "$BACKUP_DIR"
fi
  
echo " " >> $LOGFILE
echo " " >> $LOGFILE
echo "-----------------" >> $LOGFILE
echo "BACKUP DATE:" $(date +"%y-%m-%d %H:%M:%S") >> $LOGFILE
echo "----------------" >> $LOGFILE
  
for dbname in ${name}
do
 /srebro/mysql/app/bin/mysqldump  --defaults-extra-file=/home/.my.cnf --routines --set-gtid-purged=OFF --single-transaction -B $dbname > ${BACKUP_DIR}/${dbname}_${DATE}.sql 2>&1
#如果使用socket 的方式 /srebro/mysql/app/bin/mysqldump  --defaults-extra-file=/home/.my.cnf -S /srebro/mysql/mysql.socket --set-gtid-purged=OFF --single-transaction -B $dbname > ${BACKUP_DIR}/${dbname}_${DATE}.sql 2>&1
 
done
  
if [[ $? == 0 ]]
  then
    echo "Backup Successful!" >> $LOGFILE
else
    echo "Database Backup Fail!" >> $LOGFILE
fi
#删除30天之前的备份
/usr/bin/find /srebro/mysql/backup/mysql_fenku/ -ctime +30 | xargs rm -rf

3、Xtrbackup备份

备注： --include 指定需要备份的数据库，多个用'|'隔开，比如'srebro_auth|srebro_base|srebro_alarm'

[root@master ~]# vim /home/data/backup/xtrbackup_full.sh
#!/bin/bash
#Author: srebro.cn
####################################################
##
## xtrabackup script
##      backup data at remote host
##      you should config ssh trust
##
## Usage:
##   1.You must check and modify the variable
##     in variable section
##
##
####################################################
 
 
MSNAME=yz
BAKDIR=/home/data/backup/xtrbackup
BAKFILE=$MSNAME-`date +%Y%m%d%H%M`
LOGFILE=$MSNAME-`date +%Y%m%d%H%M`.log
ROOTPWD=r1xxx
REMOTE_HOST=vmuser@10.114.0.123
REMOTE_BAKDIR=/data/xtrbackup
MYCNF=/etc/my.cnf
 
 
 
/usr/bin/innobackupex --defaults-file=$MYCNF --user=root --password=$ROOTPWD --host=127.0.0.1 \
--lock-wait-threshold=40 --lock-wait-query-type=all --lock-wait-timeout=180 --kill-long-queries-timeout=20 --kill-long-query-type=all \
--stream=tar $BAKDIR 2>$BAKDIR/$LOGFILE | ssh $REMOTE_HOST  "cat - > $REMOTE_BAKDIR/$BAKFILE.tar"
 
 
# check backup log
CHECKOK=`tail -1 $BAKDIR/$LOGFILE | grep "completed OK\!" | wc -l`
if [ $CHECKOK -ne 1 ]
then
  echo "[ WARNING ] Backup failed!"
  exit
fi
 
 
# clean backup piece, delete 3 day before
ssh $REMOTE_HOST "find $REMOTE_BAKDIR/$MSNAME* -mtime +30 -type f -maxdepth 1 | xargs rm -rf {}"