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