企业级数据库备份实战案例

在日常的运维工作中，数据是公司非常重要的资源，尤其是数据库 的相关信息，如果数据丢了，少则损失几千元，高则损失上千万。所以 在运维工作中要及时注意网站数据的备份，要对数据库进行不定期的备 份。

企业中如果数据量达到TB级别，维护和管理是非常复杂的，尤其 是对数据库进行备份操作。

一、 数据库备份方法及策略

企业中MySQL数据库备份最常用的方法如下：

• 直接cp备份；
• sqlhotcopy备份；
• 主从同步复制；
• Mysqldump备份；
• xtrabackup备份。

mysqldump和xtrabackup均可以备份MySQL数据，以下为mysqldump 工具使用方法。

通常小于100GB的MySQL数据库可以使用默认mysqldump备份工具 进行备份，如果是超过100GB的大数据，由于mysqldump备份方式是采 用的逻辑备份，最大的缺陷是备份和恢复速度较慢。

基于mysqldump备份耗时会非常长，而且备份期间会锁表，锁表直 接导致数据库只能访问select，不能执行insert、update等操作，进而导致 部分Web应用无法写入新数据。

如果是myisam引擎表，当然也可以执行参数--lock-tab les＝false禁 用锁表，但是有可能造成数据信息不一致。

如果是支持事务的表，例如innoDB和BDB，--single-transaction参数 是一个更好的选择，因为它不锁定表，具体应用如下：

mysqldump -uroot -pl23456 --al1-databases --opt --single-transaction >2017all.sq1

其中--opt快捷选项，等同于添加--add-drop-tables--add-locking--create- option--disable-keys--extended-insert--lock-tables--quick--set-charset选项。 该选项能让mysqldump很快地导出数据，并且导出的数据能很快导回。 该选项默认开启，但可以用--skip-opt禁用。

如果运行mysqldump没有指定--quick或--opt选项，则会将整个结果 集中放在内存中。如果导出大数据库的话可能会导致内存溢出而异常退 出。

二、xtrabackup备份原理

MySQL冷备、mysqldump、MySQL热拷贝均不能实现对数据库进 行增量备份。在实际环境中增量备份非常的实用，如果数据量小于 100GB，存储空间足够，可以每天进行完整备份，如果每天产生的数据 量大，需要定制数据备份策略。例如每周日使用完整备份，周一到周六使用增量备份，或者每周六完整备份，周日到周五使用增量备份。

Percona-xtrabackup是为实现增量备份而生的一款主流备份工具， xtrabackup有两个主要工具，分别为xtrabackup、innobackupex。

Percona-xtrabackup是Percona公司开发的一个用于MySQL数据库物 理热备的备份工具。支持MySQL、Percona server及MariaDB，开源免 费，是目前互联网数据库备份最主流的工具之一。

xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备 份MyISAM数据表，innobackupex 1.5.1则封装了xtrabackup，是一个封装 好的脚本，使用该脚本能同时备份处理innoDB和MyISAM，但在处理 MyISAM时需要加一个读锁。

xtrabackup备份原理：innobackupex在后台线程不断追踪innoDB的 日志文件，然后复制innoDB的数据文件。数据文件复制完成之后，日志 的复制线程也会结束。这样就得到了不在同一时间点的数据副本和开始 备份以后的事务日志。完成上面的步骤之后，就可以使用innoDB崩溃恢 复代码执行事务日志(redo log)，以达到数据的一致性。其备份优点如下：

• 备份速度快，物理备份更加可靠；
• 备份过程不会打断正在执行的事务，无须锁表；
• 能够基于压缩等功能节约磁盘空间和流量；
• 自动备份校验；还原速度快；可以流传将备份传输到另外一台机器上；
• 节约磁盘空间和网络带宽。

innobackupex工具的备份过程原理，如图所示。

innobackupex备份过程中首先启动xtrabackup_log后台检测的进程， 实时检测MySQL redo的变化，一旦发现redo有新的日志写入，立刻将日 志写入到日志文件xtrabackup_log中，并复制innoDB的数据文件和系统 表空间文件idbdata1到备份目录。

innode引擎表备份完之后，执行flush table with read lock操作进行 MyLSAM表备份。复制.frm.myd.myi文件，并且在这一时刻获得binary log的位置，将表进行解锁unlock tables，停止xtrabackup_log进程，完成 整个数据库的备份。

三、Percona-xtrabackup备份实战

基于Percona-xtrabackup备份，需要以下几个步骤。

（1）官网下载Percona-xtrabackup，Percona官方wiki使用帮助如 下。

http://www. percona. com/docs/wiki/percona-xtrabackup:start

[root@wangxuejin-test195 opt]# wget http://od.mrtom.club/LINUX/xtrabackup/percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz 

（2）Percona-xtrabackup软件安装方法，cp innobackupex、 xtrabackup、xtrabackup_51、xtrabackup_55工具到/usr/bin目录下，代码如下：

[root@wangxuejin-test195 opt]# tar xf percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz 
[root@wangxuejin-test195 opt]# cp percona-xtrabackup-2.4.9-Linux-x86_64/bin/innobackupex /usr/bin/
[root@wangxuejin-test195 opt]# cp percona-xtrabackup-2.4.9-Linux-x86_64/bin/xtrabackup /usr/bin

（3）MySQL数据库全备份，代码如下，详情如图所示。

[root@wangxuejin-test195 mysql-backup]# innobackupex  --user=root --password=123456  --socket=/var/lib/mysql/mysql.sock --database=wangxuejin  /data/mysql-backup

查看备份

（4）innobackupex数据库恢复，恢复前先保证数据一致性，执行如 下命令，详情如图所示。

[root@wangxuejin-test195 mysql-backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --apply-log /data/mysql-backup/2020-05-18_15-09-51/

通常数据库备份完成后，数据尚不能直接用于恢复操作，因为备份 数据是一个过程，在备份过程中，有任务会写入数据，可能会包含尚未 提交的事务或已经提交但尚未同步至数据文件中的事务。

因此此时数据文件仍处于不一致状态，基于--apply-log可以通过回 滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致 性状态，方可进行恢复数据。

apply-log过程可以在任何机器上运行，没有强制在线上或者备份库 上运行，可以把备份复制在闲置的服务器上去运行，以此来降低备份库 的压力，但必须保证backup和apply-log所使用的mysqlbackup的版本要一 致。

（5）删除原数据目录/var/lib/mysql数据，使用参数--copy-back恢复 完整数据，授权MySQL用户给所有的数据库文件，代码如下

[root@wangxuejin-test195 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --copy-back /data/mysql-backup/2020-05-18_15-09-51/
[root@wangxuejin-test195 ~]# chown mysql.mysql -R /var/lib/mysql/

查看数据库是否恢复

可以看到数据库完全恢复

四、innobackupex增量备份

增量备份仅能应用于innoDB或xtraDB表，对于MyISAM表而言，执 行增量备份时实际上进行的是完全备份。

（1）增量备份之前必须执行完全备份，代码如下，详情如图所示。

[root@wangxuejin-test195 ~]# innobackupex  --user=root --password=123456  --socket=/var/lib/mysql/mysql.sock --databases=wangxuejin  /data/mysql-backup

（2）执行第一次增量备份，代码如下：

[root@wangxuejin-test195 mysql-backup]# innobackupex  --user=root --password=123456  --socket=/var/lib/mysql/mysql.sock --databases=wangxuejin --incremental /data/mysql-backup/ --incremental-basedir=/data/mysql-backup/2020-05-18_15-35-32

增量备份完后，会在/data/backup/mysql/目录下生成新的备份目录，

（4）执行第二次增量备份，代码如下

[root@wangxuejin-test195 mysql-backup]# innobackupex  --user=root --password=123456  --socket=/var/lib/mysql/mysql.sock --databases=wangxuejin --incremental /data/mysql-backup/ --incremental-basedir=/data/mysql-backup/2020-05-18_15-38-33

五、MySQL增量备份恢复

删除原数据库中表及数据记录信息

MySQL增量备份数据恢复方法步骤如下。

（1）基于apply-log确保数据一致性，代码如下：

[root@wangxuejin-test195 ~]# innobackupex  --user=root --password=123456  --socket=/var/lib/mysql/mysql.sock --apply-log --redo-only /data/mysql-backup/2020-05-18_15-35-32 

（2）执行第一次增量数据恢复，代码如下：

[root@wangxuejin-test195 ~]# innobackupex  --user=root --password=123456  --socket=/var/lib/mysql/mysql.sock --apply-log --redo-only /data/mysql-backup/2020-05-18_15-35-32 --incremental-dir=/data/mysql-backup/2020-05-18_15-43-45

（3）执行第二次增量数据恢复，代码如下：

[root@wangxuejin-test195 ~]# innobackupex  --user=root --password=123456  --socket=/var/lib/mysql/mysql.sock --apply-log --redo-only /data/mysql-backup/2020-05-18_15-35-32 --incremental-dir=/data/mysql-backup/2020-05-18_15-38-33

（4）执行完整数据恢复，代码如下：

[root@wangxuejin-test195 lib]# mv mysql/ mysql_bak
[root@wangxuejin-test195 lib]# mkdir mysql

[root@wangxuejin-test195 ~]# innobackupex  --user=root --password=123456  --socket=/var/lib/mysql/mysql.sock --databases=wangxuejin --copy-back /data/mysql-backup/2020-05-18_15-35-32/

注意：如果不把mysql目录改名字会报Original data directory /var/lib/mysql is not empty!错误，大致意思是 /var/lib/mysql 不是空的，感觉有点鸡肋，恢复单个数据库不应该这样，所以只能做如下操作了。

 [root@wangxuejin-test195 lib]# ls mysql
 ibdata1  wangxuejin  xtrabackup_info
 [root@wangxuejin-test195 lib]# mv mysql/* mysql_backup/
 mv：是否覆盖"mysql_backup/ibdata1"？ y
 [root@wangxuejin-test195 lib]# rm -rf mysql
 [root@wangxuejin-test195 lib]# mv mysql_backup/ mysql
 [root@wangxuejin-test195 lib]# chown -R mysql. mysql/
 [root@wangxuejin-test195 lib]# systemctl restart mariadb

（5）测试数据库已完全恢复