主题

srebro2026-09-02

1、mysqldump 全备 ​

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

备份前准备：需要创建一个 .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

#删除30天之前的备份
/usr/bin/find ${BACKUP_PATH} -name "full_*" -ctime +30 | xargs rm -rf

2、mysqldump 分库备份 ​

备份前准备：需要创建一个 .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 --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

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