主题

srebro2026-09-02

前言 ​

  • 逻辑备份 [mysqldump]
  • 物理备份 [Xtrabackup]

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）。在 MySQL 8.0.26 版本中，--master-data 选项已经被 --source-data 选项取代。
  • --single-transaction 选项，数据一致性：通过使用事务，--single-transaction 确保备份在整个过程中看到的数据是一致的；非锁定：与 --lock-tables 参数不同，--single-transaction 不会锁定数据库表，而是通过事务隔离来保证数据的一致性。
  • --force 或者 -f，忽略错误并继续备份表或数据库。

DANGER

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

bash
vim /home/.my.cnf

[mysqldump]
host=localhost
user=root
password='passwd'

[mysql]
host=localhost
user=root
password='passwd'

1.1 通用备份脚本 ​

  • 找出需要备份的数据库
  • 配置备份数据路径
  • 配置备份数据需要保留的天数
bash
[root@localhost bak]# cat /home/application/mysql/bak/back_full.sh
#!/bin/bash
source /etc/profile
DATE=`date '+%Y%m%d_%H%M'`
MYSQLDIR=/home/application/mysql/app/bin
BACKUP_PATH=/home/application/mysql/bak/backup_full

#找出需要备份的库名
name=`${MYSQLDIR}/mysql --defaults-extra-file=/home/.my.cnf -e "show databases;" | grep -Ev "^(Database|sys|information_schema|mysql|performance_schema)$" | tr "\n"  " "`

#备份数据，加上存储过程
${MYSQLDIR}/mysqldump --defaults-extra-file=/home/.my.cnf --routines --set-gtid-purged=OFF --single-transaction --databases $name > ${BACKUP_PATH}/full_${DATE}.sql 2>&1

# 使用 zip 压缩备份文件
cd ${BACKUP_PATH}
/usr/bin/zip full_${DATE}.sql.zip full_${DATE}.sql
rm ${BACKUP_PATH}/full_${DATE}.sql

#删除30天之前的备份
/usr/bin/find ${BACKUP_PATH} -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; 查看主从同步状态
  6. 查看主库和从库的数据库数量是否一致
bash
#找出需要备份的库名
name=`/home/application/mysql/app/bin/mysql --defaults-extra-file=/home/.my.cnf -e "show databases;" | grep -Ev "^(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 --single-transaction --databases $name > /home/application/mysql/bak/full_bak/full_`date +"%F"`.sql 2>&1

2、mysqldump 分库备份 ​

DANGER

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

bash
vim /home/.my.cnf

[mysqldump]
host=localhost
user=root
password='passwd'

[mysql]
host=localhost
user=root
password='passwd'

2.1 通用备份脚本 ​

  • 找出需要备份的数据库
  • 配置备份数据路径
  • 配置备份数据需要保留的天数
bash
mkdir -p /home/application/mysql/bak/
vim /home/application/mysql/bak/fenku.sh
bash
#!/bin/bash
source /etc/profile
DATE=$(date '+%Y%m%d-%H%M')
MYSQL_DIR=/home/application/mysql/app/bin
BACKUP_DIR=/home/application/mysql/bak/backup_fenku
BACKUP_DIR_DATE=${BACKUP_DIR}/${DATE}
LOGFILE=${BACKUP_DIR}/data_backup.log

# Ensure backup and log directories exist
mkdir -p "${BACKUP_DIR_DATE}"

# Initialize log file
echo " " >> $LOGFILE
echo " " >> $LOGFILE
echo "-----------------" >> $LOGFILE
echo "BACKUP DATE: $(date +"%Y-%m-%d %H:%M:%S")" >> $LOGFILE
echo "-----------------" >> $LOGFILE

# Retrieve databases to backup
databases=`$MYSQL_DIR/mysql --defaults-extra-file=/home/.my.cnf -e "show databases;" | grep -Ev "^(Database|sys|information_schema|mysql|performance_schema)$" | tr "\n"  " "`

backup_success=true

for dbname in ${databases}
do
  TIMESTAMP=$(date +"%Y-%m-%d %H:%M:%S")
  echo "Starting backup for database $dbname at $TIMESTAMP" >> $LOGFILE

  $MYSQL_DIR/mysqldump --defaults-extra-file=/home/.my.cnf --routines --single-transaction -f -B $dbname > ${BACKUP_DIR_DATE}/${dbname}_${DATE}.sql 2>> $LOGFILE

  if [[ $? != 0 ]]; then
    echo "Backup of database $dbname failed at $TIMESTAMP!" >> $LOGFILE
    backup_success=false
  else
    echo "Backup of database $dbname completed successfully at $TIMESTAMP" >> $LOGFILE
  fi

  echo "-----------------" >> $LOGFILE
done

if [[ $backup_success == true ]]; then
  echo "All backups completed successfully!" >> $LOGFILE
else
  echo "One or more database backups failed!" >> $LOGFILE
fi

echo "-----------------" >> $LOGFILE

# Compress backup directory
cd ${BACKUP_DIR}
zip -r ${DATE}.zip ${DATE}
rm -r ${DATE}

# Delete backups older than 30 days
/usr/bin/find ${BACKUP_DIR}/ -ctime +30 -exec rm -rf {} \;

3、Xtrabackup 备份 ​

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

  • 需要提前在数据库主机上安装好 xtrabackup 备份软件，安装文档：Percona XtraBackup 安装指南
  • 需要有免密环境（到远程主机上）
  • 采用流备份的方式，短时间内网络流量不会很高，数据库压力也不会太大
  • 删除远程主机上超过 30 天以上的备份文件
bash
[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=r123d$&*#!
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 30 day before
ssh $REMOTE_HOST "find $REMOTE_BAKDIR/$MSNAME* -mtime +30 -type f -maxdepth 1 | xargs rm -rf {}"
最近更新