"MySQL数据库备份与恢复"

Date 一 25 八月 2014 Tags mysql

概述

MySQL的多存储引擎架构针对不同的存储引擎有不同的备份方法，数据量的大小和备份目的不同都会影响如何选择备份策略。

备份类型

冷备份、热备份、暖备份。

冷备份是在完全停止数据库进程的情况下备份其数据目录，常在例行维护割接升级可停机的时候采用。

热备份是在数据库服务运行时通过备份工具执行数据备份，是在保证不中断和阻塞读写请求的同时完成数据备份，常用于大数据量的例行备份。

暖备份是MySQL数据库针对非事务型存储引擎表（例如：MyISAM）在不停机的情况下利用mysqlhotcopy工具进行的物理备份，其备份过程中为保证数据一致性会有短暂的读锁，与热备相比会阻塞写请求，故称为暖备。

注：mysqlhotcopy工具在未GA的MySQL 5.7版本将会移除，系统mysql库也会变成InnoDB引擎。

备份方式

逻辑备份和物理备份。

逻辑备份是对数据库对象(如用户、表、存储过程等)利用mysqldump和select outfile工具从数据库中导出最终以CSV或SQL文本形式保存。特点是备份速度较慢、可读性好，便于不同平台迁移。常用于表结构备份，在数据量规模不大时可作为例行备份。

物理备份是对数据库实例在物理磁盘上的数据文件、重做日志、表结构定义文件、配置文件等拷贝。特点是备份速度较快。常用于数据量规模较大的场景下可考虑在线热备。

存储引擎

主要讨论InnoDB和NDB（MySQL Cluster）事务型存储引擎。

对于逻辑备份两者都使用mysqldump或select .. outfile方式导出数据。 对于物理备份InnoDB推荐使用MySQL Enterprise Backup(MEB)进行在线热备；NDB使用 start backup命令执行在线热备。

数据量规模

通常小于30G的数据可采用逻辑备份方式（mysqldump）在业务闲时备份，备份和恢复一般可以在30分钟内完成；大于30G的数据可考虑物理备份。备份速度还取决于物理磁盘的IO能力。

全量备份

全量备份是使用mysqldump或MEB工具对全部数据进行备份，数据量越大备份全量备份的间隔越长。

增量备份

增量备份是利用MySQL的BINLOG所记录数据库在线变更的功能，对其定时备份。任何存储引擎都可以使用此方式。MEB支持针对InnoDB数据页的增量备份，每次只备份变化的数据页，此方式只对InnoDB引擎有效。

备份方案

MySQL 单机版

场景1：数据量30G以内

备份方式：在线逻辑备份

备份周期：每天全备+每小时binlog备份

备份方法：

全量备份命令

mysqldump -uUSER -pPASSWORD --single-transaction DATABASE [TABLES] |gzip > DATABASE.sql.gz

Binlog备份脚本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
#!/bin/bash

MyUSER=        # username
MyPASS=        # password
DATADIR=       # data directory
BACKDIR=       # backup binlog directory

# Linux bin paths, change this if it can not be autodetected via which command
MYSQLADMIN="$(which mysqladmin)"

# read binlog index get binlog name
$MYSQLADMIN -u$MyUSER -p$MyPASS flush-logs

FILELIST=`cat $DATADIR/mysql-bin.index`

cd $DATADIR

COUNTER=0
for file in $FILELIST
do
COUNTER=`expr $COUNTER + 1 `
done
NextNum=0
for file in  $FILELIST
do
    BASE=`basename $file`
    NextNum=`expr $NextNum + 1`
    if [ $NextNum -eq $COUNTER ]
    then
        echo "skip lastest"
    else
        dest=$BACKDIR/$BASE
        if [ -e $dest ]
        then
            echo "skip exist $BASE"
        else
            echo "copying $BASE"
            cp $BASE $BACKDIR
        fi
    fi
done
echo "Backup MySQL BinLog OK !"

计划任务：

#crontab -l
0   3   *   *   *   全备
1   *   *   *   *   binlog备份

恢复步骤：

  1. 选取数据丢失前最近的全备文件

  2. 暂停业务访问数据库

  3. 导入最近的全备文件
gunzip < DATABASE.sql.gz | mysql -uUSER -pPASSWORD DATABASE
  1. 选取全备时间点的binlog备份，若因误操作删除造成数据丢失则stop-datetime为误操作前时间点
mysqlbinlog --start-datetime='最近全备时间' --stop-datetime='最终误操作前时间点' BINLOG_1 .. BINLOG_N | mysql -uUSER -pPASSWORD
  1. 验证数据
  2. 恢复业务访问

场景2：数据量大于30G

备份方式：在线热备

备份周期：每周全备+每天增备+每小时binlog备份

备份方法：使用MEB工具

全量备份命令

mysqlbackup -uUSER -pPASSWORD --backup-dir=FULL_BACKUP_DIR --with-timestamp  backup-and-apply-log

增量备份命令

mysqlbackup -uUSER -pPASSWORD --incremental-base=history:last_backup --incremental-backup-dir=INCREMENT_BACKUP_DIR --with-timestamp --incremental backup

计划任务：

#crontab -l
0   3   *   *   0   全备
0   3   *   *   *   增备
1   *   *   *   *   binlog备份

恢复步骤：

MEB数据恢复的流程分成prepare和copy-back 两个部分，prepare部分是将所有的增量备份与全量备份合并，从全备之后的第一个增量备份开始，逐个的将增量备份合并到全量备份， 然后将合并完成的全量备份copy-back回数据目录完成恢复。

  1. 选取数据丢失前最近的全备文件

  2. 暂停业务访问数据库

  3. 从第一个增量备份逐个开始合并到全备
 mysqlbackup -uUSER -pPASSWORD  --incremental-backup-dir= INCREMENTAL-DIR-N --backup-dir=FULL_BACKUP_DIR apply-incremental-backup
  1. 关闭MySQL实例
  2. 恢复全量备份
mysqlbackup --defaults-file=/FULL_BACKUP_DIR/backup-my.cnf --datadir=DATA_DIR  --backup-dir=FULL_BACKUP_DIR copy-back
  1. 选取全备时间点的binlog备份，若因误操作删除造成数据丢失则stop-datetime为误操作前时间点
mysqlbinlog --start-datetime='最近全备时间' --stop-datetime='最终误操作前时间点' BINLOG_1 .. BINLOG_N | mysql -uUSER -pPASSWORD
  1. 验证数据
  2. 恢复业务访问

MySQL Cluster

MySQL Cluster原生提供了在线热备命令start_backup,恢复命令ndb_restore。使用原生的备份方式可以在线同时对所有数据节点执行备份操作，也是MySQL Cluster推荐的备份方式。

MySQL Cluster也支持逻辑备份mysqldump，但为保证备份时数据一致性需进入单用户模式，此模式只有一个SQL节点可以访问数据，所以这种方式会影响业务访问，故不建议使用此方式作为日常的例行备份。

备份方式：在线热备

备份方法：使用ndb_mgm客户端工具连接到管理节点执行备份命令

ndb_mgm -c connectstring -e 'start backup'

恢复步骤：

  1. 选取数据丢失前每个数据节点最近的备份文件，传输到相应数据节点上

  2. 暂停业务访问数据库

  3. 在一个数据节点进行元数据恢复
ndb_restore -m -r -b BACKUP_ID --backup_path=BACKUP_DIR -n NODE_ID
  1. 在其余数据节点进行数据恢复
ndb_restore -r -b BACKUP_ID --backup_path=BACKUP_DIR -n NODE_ID
  1. 进入单用户模式,准备进行binlog恢复
ndb_mgm -c connectstring -e "ENTER SINGLE USER MODE <SQL ID>"
  1. 选取全备时间点的binlog备份，若因误操作删除造成数据丢失则stop-datetime为误操作前时间点
mysqlbinlog --start-datetime='最近全备时间' --stop-datetime='最终误操作前时间点' BINLOG_1 .. BINLOG_N | mysql -uUSER -pPASSWORD
  1. 验证数据
  2. 恢复业务访问

补充

  1. 本地数据备份完成最好是传输到独立的存储中集中管理
  2. 数据恢复前应写好整个恢复流程，然后按流程一步一步操作
  3. 定期在演练环境验证数据备份有效性
  4. 做好备份成败和磁盘空间监控

Comments

comments powered by Disqus