数据库备份实战

数据库备份实战

一、数据库备份实战

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

常用方法:cp、sqlhotcopy、主从同步复制、mysqldump、xtrabackup。

mysqldump:

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

基于mysqldump备份耗时会非常长,而且备份期间会锁表,锁表直接导致数据库只能访问select,不能执行insert、update等操作,进而导致部分Web应用无法写入新数据。如果是myisam引擎的数据表,可以执行参数 –lock-tables=false 禁用锁表,但是有可能造成数据信息不一致。

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

mysqldump -uroot -p1234 –all-databases –opt –single-transaction > 2018databases.sql

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

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

 

2、Percona-xtrabackup

MySQL冷备、mysqldump、MySQL热拷贝均不能实现对数据库进行增量备份。

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

xtrabackup 只能备份innoDB和xtraDB两种数据引擎的表,而不能备份MyISAM数据表,innobackupex 则封装了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进程,完成整个数据库的备份。

 

3、Percona-xtrabackup 备份实战

安装percona-xtrabackup:

#yum install percona-xtrabackup -y

##数据库完整备份

#innobackupex –user=root –password=1234 /data/backup/mysql/

##数据库恢复,恢复前先保证数据一致性,执行如下命令

#innobackupex –defaults-file=/etc/my.cnf –user=root –password=1234 –apply-log /data/backup/mysql/2018-11-14_14-11-20

通常数据库备份完成后,数据尚不能直接用于恢复操作,因为备份数据是一个过程,在备份过程中,有任务会写入数据,可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此此时数据文件仍处于不一致状态,基于–apply-log 可以通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态,方可进行恢复数据。

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

 

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

rm -rf /var/lib/mysql/*

innobackupex –defaults-file=/etc/my.cnf –user=mysql –password=1234 –copy-back /data/dackup/msyql/2018-11-14_14-11-20

chown -R mysql:mysql /var/lib/mysql/

 

5、innobackupex 增量备份

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

##增量备份之前必须执行完全备份:

#innobackupex –user=root –passsword=1234 –databases=wordpress /data/backup/mysql/

会生成/data/backup/mysql/2018-11-14_15-20-20/带有当此时间参数的目录。备份数据就在此目录下面。

##执行第一次增量备份:相对于之前生成的完整备份生成增量备份。

innobackupex –defaults-file=/etc/my.cnf –user=root –password=1234 –databases=wordpress –incremental /data/backup/mysql/ –incremental-basedir=/data/bakcup/mysql/2018-11-14_15-20-20/

会在/data/backup/mysql/下生成新的增量备份数据路径:2018-11-14_15-22-25/

##数据库插入新数据后

##执行第二次增量备份:相对于之前生成的增量备份生成新的增量备份。

innobackupex –defaults-file=/etc/my.cnf –user=root –password=1234 –databases=wordpress –incremental /data/backup/mysql/ –incremental-basedir=/data/backup/mysql/2018-11-14_15-22-25/

会在/data/backup/mysql/下生成新的增量备份数据路径:2018-11-14_15-25-30/

6、MySQL增量备份恢复

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

MySQL增量备份恢复方法步骤如下:

##基于apply-log确保数据一致性:

innobackupex –defaults-file=/etc/my.cnf –user=root –password=1234 –apply-log –redo-only /data/backup/mysql/2018-11-14_15-20-20/

##执行第一次增量数据恢复:

innobackupex –defaults-file=/etc/my.cnf –user=root –password=1234 –apply-log –redo-only /data/backup/mysql/2018-11-14_15-20-20/ –incremental-dir=/data/backup/mysql/2018-11-14_15-25-30/

##执行第二次增量数据恢复:

innobackupex –defaults-file=/etc/my.cnf –user=root –password=1234 –apply-log –redo-only /data/backup/mysql/2018-11-14_15-20-20/ –incremental-dir=/data/backup/mysql/2018-11-14_15-22-25/

##执行完整数据恢复:

innobackupex –defaults-file=/etc/my.cnf –user=root –password=1234 –copy-back /data/backup/msyql/2018-11-14_15-20-20/

##测试数据库是否恢复。