MySQL三部曲备份【逻辑备份+物理备份】脚本,生产实践
本文最后更新于 2024-09-19,文章内容可能已经过时。
前言:
- 逻辑备份[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)。
- --single-transaction 选项,数据一致性:通过使用事务,
--single-transaction
确保备份在整个过程中看到的数据是一致的。这意味着备份的数据不会被插入、更新或删除操作影响,从而避免了在备份过程中数据的不一致性问题;非锁定:与--lock-tables
参数不同,--single-transaction
不会锁定数据库表,而是通过事务隔离来保证数据的一致性。这有助于减少对数据库的锁定,允许读写操作继续进行,不会对数据库的正常使用产生过多影响。 - --force 或者 -f , 忽略错误并继续备份表或数据库。
:::danger
备份前准备:需要创建一个.my.cnf 配置文件用来指定备份用户
:::
vim /home/.my.cnf
[mysqldump]
host=localhost
user=root
password='passwd'
[mysql]
host=localhost
user=root
password='passwd'
1.1通用备份脚本:
- 找出需要备份的数据库
- 配置备份数据路径
- 配置备份数据需要保留的天数
[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 主从恢复备份脚本:
恢复步骤大致如下:
- mysqldump 备份主库数据,加上--master-data选项
- 登录从库,停止 主从同步,stop slave;
- source XXX.sql; 恢复数据
- start slave; 启动主从同步
- show slave status\G; 查看主从同步状态
- 查看主库 和 从库的 数据库 数量是否一致!!!
#找出需要备份的库名
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 配置文件用来指定备份用户
:::
vim /home/.my.cnf
[mysqldump]
host=localhost
user=root
password='passwd'
[mysql]
host=localhost
user=root
password='passwd'
2.1 通用备份脚本:
- 找出需要备份的数据库
- 配置备份数据路径
- 配置备份数据需要保留的天数
#!/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 --set-gtid-purged=OFF --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 (Uncomment if needed)
cd ${BACKUP_DIR}
zip -r ${DATE}.zip ${DATE}
rm -r ${DATE}
# Delete backups older than 30 days (Uncomment if needed)
/usr/bin/find ${BACKUP_DIR}/ -ctime +30 -exec rm -rf {} \;
3、Xtrbackup备份
备注: --include 指定需要备份的数据库,多个用'|'隔开,比如'srebro_auth|srebro_base'
- 需要提前在数据库主机上安装好xtrabackup备份软件,安装文档: https://docs.percona.com/percona-xtrabackup/innovation-release/yum-repo.html#install-percona-xtrabackup-from-percona-yum-repository
- 需要有免密环境(到远程主机上)
- 采用流备份的方式,短时间内网络流量不会很高,数据库压力也不会太大
- 删除远程主机上超过30天以上的备份文件
[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 3 day before
ssh $REMOTE_HOST "find $REMOTE_BAKDIR/$MSNAME* -mtime +30 -type f -maxdepth 1 | xargs rm -rf {}"
- 感谢你赐予我前进的力量
赞赏者名单
因为你们的支持让我意识到写文章的价值🙏
本文是原创文章,采用 CC BY-NC-ND 4.0 协议,完整转载请注明来自 运维小弟
评论
匿名评论
隐私政策
你无需删除空行,直接评论以获取最佳展示效果