数据库备份策略

1. 热备

• MySQL主从复制（1主2从，半同步）

2. 延时备份 （未进行）

• 延时同步3小时：利用pt-slave-delay对一台slave进行延时同步

3. 冷备（物理备份)

• 备份策略：

  • 每天增量备份，3天一个完整备份
  • 当天备份同步到备份中心
  • 本机保留最近7天备份

• 备份工具：Percona XtraBackup

• 备份脚本：

• #!/bin/bash -x
  
  . /etc/profile > /dev/null 2>&1 
  . /etc/init.d/functions > /dev/null 2>&1 
  ulimit -SHn 65000 
  
  cur_time=`date +'%Y%m%d%H%M%S'` 
  d=`date +%Y%m%d` 
  d7=`date -d'7 days ago' +%Y%m%d` 
  week=`date +%w` 
  week7=`date -d'7 days ago' +%w` 
  d3=`date -d'3 days ago' +%Y%m%d` 
  week3=`date -d'3 days ago' +%w` 
  d1=`date -d'1 days ago' +%Y%m%d` 
  week1=`date -d'1 days ago' +%w` 
  
  mysql_user='bak_user' 
  mysql_pass='bak_pass' 
  mysql_port='3306' 
  mysql_dir="/data/mysql" 
  mysql_data_dir="/data/mysqldata" 
  mysql_sock="/data/mysqldata/mysql.sock" 
  mysql_conf="/etc/my.cnf" 
  script_dir="/data/scripts/db_scripts" 
  backup_dir="/data/backup/mysql_data_backup/${mysql_port}" 
  lan_ip=`ifconfig eth0|grep 'inet addr:'|awk '{print $2}'|awk -F: '{print $2}'` 
  
  [ -d $backup_dir ] || mkdir -p $backup_dir 
  log_file="${backup_dir}/backup_log_${d}.log" 
  echo "" >$log_file 
  
  start_file="${backup_dir}/backup_innodb_start.txt" 
  mysql_cmd="mysql -u${mysql_user} -p${mysql_pass} -S ${mysql_sock} -e 'flush tables;show slave status\G'" 
  bak_comm="/usr/bin/innobackupex   --defaults-file=${mysql_conf} --user=${mysql_user} --password=${mysql_pass} --socket=${mysql_sock} --no-timestamp --slave-info" 
  
  mailFunc() 
  {
   status=$1 
   echo "bakdir: ${backup_dir}/${d}_${week}" >> $log_file 
   echo "this message is from $0 in $HOSTNAME $lan_ip ." >> $log_file 
   /usr/local/monitor-base/bin/sendEmail -s smtp.xiaoniu66.com -f xxxx@xiaoniu66.com  \ 
   -t wlkj.ops.list@xiaoniu66.com \ 
   -xu xxxxx -xp xxxxx          \ 
   -u "XGZ-DB backup from $HOSTNAME $lan_ip is $status" \ 
   -o message-file=$log_file 
  }
  
  # 读取上一次完整备份的时间，标记为：周1-7
  if [ -f $start_file ] 
  then
   start=`cat $start_file` 
  else
   echo "$week" > $start_file 
   start=`cat $start_file` 
  fi
  
  # 计算上一次完整备份的时间间隔
  if [ $start -le $week ] 
  then
   start_d=$[ $week - $start ] 
  else
   start_d=$[ $week + 7 - $start ] 
  fi
  
  # 如果间隔等0天或者3天，则执行完整备份，否则执行增量备份
  if [ $start_d -eq 3 -o $start_d -eq 0 ] 
  then
   back_type="full" 
   echo $week > $start_file 
  else
   back_type="incremental" 
  fi
  
  # 备份前评估磁盘剩余空间是否高于50G，不是则退出
  dataSize=`du -s --block-size=G $mysql_data_dir |awk '{print $1}'|sed 's/G$//'` 
  freeSize=`df -h --block-size=G $backup_dir |grep -v '^Filesystem'|awk '{print $4}'|sed 's/G$//'` 
  if [ $start -eq ${week} ] 
  then
   if [ $dataSize -gt `expr $freeSize - 50` ] 
   then 
  #        mailFunc "error: Disk space is not enough"
   exit 1 
   fi 
  else
   if [ $freeSize -lt 50 ] 
   then 
  #        mailFunc "error: Disk space is not enough"
   exit 1 
   fi 
  fi
  
  ## 开始进行备份
  case $back_type in 
   full) 
   # 完整备份 
   ${bak_comm} ${backup_dir}/${d}_${week}_${back_type} | tee -a ${log_file} 
   ;; 
   incremental) 
   # 增量备份 
   start_day=`date -d"$start_d days ago" +%Y%m%d` 
   ${bak_comm} --incremental ${backup_dir}/${d}_${week}_${back_type} --incremental-basedir=${backup_dir}/${start_day}_${start}_full | tee -a ${log_file} 
   ;; 
  esac
  
  # 检查是否备份成功
  if [ ! -f ${backup_dir}/${d}_${week}_${back_type}/xtrabackup_checkpoints ];then 
   echo "backup fail" >> ${log_file} 
  #       mailFunc error
   exit 1 
  fi
  
  cat ${backup_dir}/${d}_${week}_${back_type}/xtrabackup_slave_info >> ${log_file} 
  cat ${backup_dir}/${d}_${week}_${back_type}/xtrabackup_checkpoints >> ${log_file} 
  cp -a ${mysql_conf} ${backup_dir}/${d}_${week}_${back_type}/  
  echo "xtrabackup start time ${cur_time}" >> ${log_file} 
  echo "xtrabackup end time `date +'%Y%m%d%H%M%S'`" >> ${log_file} 
  mv ${log_file} ${backup_dir}/${d}_${week}_${back_type}/ 
  #    mailFunc ok
  
  #chown -R 99.99 $backup_dir
  
  #rsync -av  ${backup_dir}/${d}_${week} dscq@${backupsvr}::${rsync_dir}/${svr_id}_db/ --password-file=${rsync_pass}
  #echo "rsync finished `date`" >> ${backup_dir}/${d}_${week}/finished_date.txt
  
  # 本机保留最近7天备份，过期备份删除
  ## delete 7 days ago backup
  old_backup=` ls -d ${backup_dir}/${d7}_${week7}_*` 
  
  echo "rm -rf ${old_backup}" 
  rm -rf ${old_backup} && echo "rm -rf ${old_backup} OK" 

• 备份恢复步骤 
  
  • 恢复完整备份

  • # 恢复事务日志:
    # --use-memory=4G：指定恢复事务日志所使用内存，可以加快恢复速度
    innobackupex --apply-log --use-memory=4G ${backup_dir}/${d}_${week} 
     150806 01:01:57 InnoDB: Shutdown completed; log sequence number 1609228 
     150806 01:01:57 innobackupex: completed OK ! 
    # 恢复完整备份：
    # 命令会自动将${backup_dir}/${d}_${week}数据文件复制到/etc/my.cnf里制定的datadir
    # 要确保/etc/my.cnf里制定的datadi目录为空，否则会执行失败
    innobackupex --copy-back ${backup_dir}/${d}_${week} 
     innobackupex: Finished copying back files. 
     111225 01:08:13 innobackupex: completed OK! 
    # 修改恢复后的数据目录权限
    chown -R mysql:mysql $datadir 

• 恢复增量备份

# 恢复redo-log：
# 只有一个增量备份，只需要对完整备份执行redo-only，如果有多个增量备份，则除最后一个增量外，其它都都需要执行
innobackupex --apply-log --redo-only $datadir 
 120103 22:00:12 InnoDB: Shutdown completed; log sequence number 1291135 
 120103 22:00:12 innobackupex: completed OK! 
#恢复事务日志
innobackupex --apply-log $datadir --incremental-dir=$INCREMENTAL-DIR 
# 恢复完整备份
innobackupex --copy-back  $datadir 
 innobackupex: Finished copying back files. 
 111225 01:08:13 innobackupex: completed OK! 
#

4. 冷备（逻辑备份）

• mysqldump（单线程）

mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 --routines --events --triggers --databases db > db.sql

• mysqldbexport&mysqldbimport (MySQL官方多线程备份）

# 使用6线程导出数据，并且包含change master信息，以便直接用于生成slave
mysqldbexport --server=root:root@localhost:3306 --all --bulk-insert --export=both --rpl=master --rpl-user=rpl:rpl --multiprocess=6 --output-file=data.sql 
#导入数据
mysqldbimport --server=root@localhost --import=both --bulk-insert --multiprocess=6 --format=sql data.sql  

• Mydumper（多线程备份）

#备份数据库
#!/bin/sh
DIR_BACKUP=/backup-directory 
DB_NAME=database_name 
DB_USER=user 
DB_PASS=password 
DOW=`date "+%a"` 
DB_DUMP=$DIR_BACKUP/dbdump.$DB_NAME.$DOW 
mydumper \
 --database=$DB_NAME \ 
 --host=$DB_HOST \ 
 --user=$DB_USER \ 
 --password=$DB_PASS \ 
 --outputdir=$DB_DUMP \ 
 --rows=500000 \ 
 --compress \ 
 --build-empty-files \ 
 --threads=6 \ 
 --compress-protocol 
### 恢复备份
myloader \
 --database=$DB_NAME \ 
 --directory=$DB_DUMP \ 
 --queries-per-transaction=50000 \ 
 --threads=6 \ 
 --compress-protocol \ 
 --verbose=3