mysql备份脚本

定时任务:

09 00 * * * /bin/bash /data/scripts/dbbak.sh >> /backup/backup_$(date +\%Y\%m\%d\%H\%M\%S).log 2>&1

dbbak.sh:

#!/bin/bash
# 设置备份相关参数
BACKUP_DIR="/backup/dbbak"
MYSQL_USER="root"
MYSQL_PASSWORD="xxxxx"
MYSQL_SOCKET="/var/lib/mysql/mysql.sock"
MAX_BACKUP_COUNT=3

# 创建备份目录
mkdir -p "$BACKUP_DIR"

# 备份当前时间戳
TIMESTAMP=$(date +%Y%m%d%H%M%S)

# 执行数据库备份
innobackupex --defaults-file=/etc/my.cnf --user="$MYSQL_USER" --password="$MYSQL_PASSWORD" --socket="$MYSQL_SOCKET" --no-timestamp "$BACKUP_DIR/$TIMESTAMP"

echo "######apply-log###############################################"

# 备份一致性检查
innobackupex --defaults-file=/etc/my.cnf --user="$MYSQL_USER" --password="$MYSQL_PASSWORD" --socket="$MYSQL_SOCKET" --apply-log "$BACKUP_DIR/$TIMESTAMP"

echo "######del-bak###############################################"
# 删除历史备份
BACKUP_COUNT=$(ls -1 "$BACKUP_DIR" | wc -l)
if [ "$BACKUP_COUNT" -gt "$MAX_BACKUP_COUNT" ]; then
DELETE_COUNT=$((BACKUP_COUNT - MAX_BACKUP_COUNT))
OLDEST_BACKUPS=$(ls -1tr "$BACKUP_DIR" | head -n $DELETE_COUNT)
for BACKUP in $OLDEST_BACKUPS; do
echo "$BACKUP_DIR/$BACKUP delete"
rm -rf "$BACKUP_DIR/$BACKUP"
done
fi
echo "#############game over######################"

 

 

=====================================================================================================================

mysql 恢复

 innobackupex --defaults-file=/etc/my.cnf --copy-back  /backup/dbbak/20210704061201

stop slave;
reset master;
reset slave all;

set global gtid_purged='xxxxx:1-775276171';#xtraback_info中的信息
change master to master_host='主库ip',master_port=3306,master_user='repl',master_password='12345',MASTER_AUTO_POSITION = 1;
start slave;
show slave status\G;

 

 

=================================================================================================================

mysql跳过错误

stop slave sql_thread;
set gtid_next='xxxx:1304694';
begin;commit;
set gtid_next=automatic;
start slave sql_thread;
show slave status\G;