备份

小数据库用mysql自带的mysqldump就可以完成备份，写一个简单的适用于linux下跑的脚本。

策略

每天凌晨1点全备，保留7天

备份脚本dbbak.sh

#!/bin/bash  
export PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/mysql/bin:/root/bin:/usr/local/xtrabackup/bin

bak_host_IP=xxx.xxx.xxx.xxx
DATE=$(date +%Y-%m-%d)
backup_home=/home/dbbak/${bak_host_IP}
logpath=$backup_home/${DATE}
logfile=$logpath/mysqldump-${DATE}_${bak_host_IP}.log
mkdir -p $logpath

# Logging function
function log_info() {
    if [ "$verbose" == "no" ] ; then
        printf "%s --> %s\n" "$(date +%Y-%m-%d-%T)" "$*" >> "$logfile"
    else
        printf "%s --> %s\n" "$(date +%Y-%m-%d-%T)" "$*" | tee -a "$logfile"
    fi
}

#======================================================================
#You have to modify them to the correct information
#####mysql主机信息配置#########

#last_day=$(date -d "1 days ago" +%Y-%m-%d)  
backup_dir=$logpath
shell_dir=$(cd $(dirname $0); pwd)
#filename=$(find $backup_home -name "$last_day*" -print|awk -F / '{print $NF}')

bak_port=3306
bak_db=--all-databases
bak_env=/home/apps/app/mysql
#bak_config=/etc/my.cnf
bak_config=$bak_env/my.cnf
bak_socket=$bak_env/tmp/mysql.sock
#bak_socket=/var/lib/mysql/mysql.sock
#export rsync_logfile=/app/innobackupex/log/${DATE}/rsync_log/rsync-${DATE}_${bak_host_IP}.log
bakuser='root'
bakpasswd='xxxxxxxxx'

#mysql=/usr/bin/mysql
mysql=$(command -v $bak_env/bin/mysql)
mysqlcommand="$mysql"
mysqlcommand=$mysqlcommand" -u ${bakuser}"
mysqlcommand=$mysqlcommand" -p${bakpasswd}"
mysqlcommand=$mysqlcommand" -h ${bak_host_IP}"
mysqlcommand=$mysqlcommand" -S ${bak_socket}"
[ -n "$bak_port" ] && mysqlcommand=$mysqlcommand" -P $bak_port"
mysqlcommand=$mysqlcommand" -Bse "

$mysqlcommand "show master status"  >> "$logfile"

# Begin script
# we trap control-c
trap sigint INT

# Check for xtrabackup
yhop_mysqldump=$bak_env/bin/mysqldump
#yhop_mysqldump=/usr/bin/mysqldump
if command -v mysqldump >/dev/null; then
    mysqldump=$(command -v mysqldump)
elif
    command -v $yhop_mysqldump >/dev/null; then
	mysqldump=$(command -v ${yhop_mysqldump})
else
    log_info "mysqldump does not appear to be installed. Please install and try again."
    exit 1
fi

#echo "nohup $mysqldump -h $bak_host_IP -P $bak_port -u $bakuser -p$bakpasswd -S $bak_socket --all-databases --single-transaction --set-gtid-purged=OFF --quick > $backup_dir/${bak_host_IP}_$DATE.sql  &"
nohup $mysqldump -h $bak_host_IP -P $bak_port -u $bakuser -p$bakpasswd -S $bak_socket --all-databases --single-transaction --quick > $backup_dir/${bak_host_IP}_$DATE.sql  &


if pgrep -x "mysqldump" > /dev/null
then
    echo "有 mysqldump 进程在运行。"
	log_info "full_Backup is running! at $(date +%Y%m%d%H%M)"  
else
    log_info "mysqldump not running ! at $(date +%Y%m%d%H%M)"  
fi


###清理备份日志，保留七天内的
DAYS_TO_KEEP=7

# 获取当前日期的Unix时间戳
CURRENT_TIME=$(date +%s)

# 计算5天前的Unix时间戳
CUTOFF_TIME=$((CURRENT_TIME - 86400 * DAYS_TO_KEEP))

# 遍历目录下的所有子目录
for dir in "$backup_home"/*; do
    # 检查是否为目录
    if [ -d "$dir" ]; then
        # 提取目录名（假设为日期格式）
        dir_name=$(basename "$dir")
        
        # 尝试将目录名解析为Unix时间戳
        dir_time=$(date -d "$dir_name" +%s 2>/dev/null)
        
        # 如果解析成功并且目录创建时间早于截止时间，删除目录
        if [ $? -eq 0 ] && [ $dir_time -lt $CUTOFF_TIME ]; then
            echo "删除过期目录: $dir"
            rm -rf "$dir"
        fi
    fi
done
log_info "Backup Process Done"