数据库备份和还原详解
备份策略
1、全量 + 差异 + binlogs
2、全量 + 增量 + binlogs
备份手段
1、物理备份（直接复制数据文件进行备份）
2、逻辑备份 （通过mysqldump）
恢复还原
1、完全 + 每一次增量 + 二进制日志（根据时间点恢复）
2、完全 + 最后一次差异备份 + 二进制日志（根据时间点恢复）
热备、温备、冷备的介绍
热备：读写操作均可进行的状态下所做的备份
问题：会导致备份的数据时间点可能不一致，恢复后的数据时间点不一致，还有可能造成mysql拒绝恢复
温备：可读但不可写状态下进行的备份
冷备：读写操作均不可进行的状态下进行的备份
了解了以上后，我们会对数据库的备份和恢复有了一定的认知，我们在备份时需要考虑各种因数，例如备份时是否会丢失数据，备份数据要在多少时间内完成，会锁定资源多长时间，机器负载情况等等
mysqldump使用
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 | 表级别备份，不会自动创建数据库 mysqldump dbname 库级别备份，自动建库 mysqldump --databases dbname 全量备份（需要开启二进制日志） mysqldump -E -R --triggers --master-data=2 -F -l --single-transaction --all-databases > backup.sql 恢复数据（在mysql中恢复） source backup.sql 如果在全量备份后又进行了一些其他的操作，那么我们需要增量备份下这部分数据 cp /usr/local/mysql/data/mysql-bin.0000022 /backup 使用二进制日志恢复 mysqlbinlog mysql-bin.0000022 | mysql -x, --lock-all-tables 锁定'所有库的所有表'，读锁； -l, --lock-tables 锁定'指定库所有表'； -R, --routines 备份存储过程和存储函数； -E, --events 备份事件调度器 -F，--flush-logs 锁定表完成后，即进行日志刷新操作，让日志滚动； --triggers 备份触发器 --master-data[=#] ：记录备份开始时 binlog中 1：记录为CHANGE MASTER TO语句，此语句不被注释； 2：记录为CHANGE MASTER TO语句，此语句被注释； 'InnoDB存储引擎：支持温备和热备；' --single-transaction：'创建一个事务，基于此快照执行备份'； |
|---|
实例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 备份表结构： mysqldump -uroot -hlocalhost -p -d novel wechat_member > db.sql 备份单个库下所有表结构： mysqldump -uroot -hlocalhost -p -d -B novel > db.sql 备份单个表数据： mysqldump -uroot -hlocalhost -p novel wechat_member > db.sql 备份多个表数据： mysqldump -uroot -hlocalhost -p novel wechat_member admin_user customer_info > db.sql #多个表之间用空格分 备份整个库： mysqldump -uroot -hlocalhost -p -B novel > db.sql 或 mysqldump -uroot -hlocalhost -p --databases novel novel2 > db.sql 备份所有库： mysqldump -uroot -hlocalhost -p -A > db.sql 或 mysqldump -uroot -hlocalhost -p --all-databases > db.sql |
|---|
xtrabackup备份工具
官方下载地址：https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
Percona XtraBackup（简称PXB）是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具，支持 MySQl（Oracle）、Percona Server 和 MariaDB，mysqldump的备份是逻辑备份，备份出来的语句是sql语句，所以备份和恢复的时候很慢，当数据量超过10G时，xtrabackup就会快非常多了
2.4版本，innobackupex的功能已经完全整合到了xstrabackup中，innobackupex不再是perl脚本了，但是，为了兼容之前用户的使用习惯，官方保留了innobackupex，它作为了一个软连接，指向了xtrabackup，也就是说2.4版本中，已经没有了innobackupex命令，用的都是xtrabackup命令，用的都是C程序，但是原理上并没有什么不通。
2.3之前的版本包括两个主要的备份工具，innobackupex和xtrabackup，xtrabackup是一个C程序，innobackupex是一个perl脚本，它对xtrackup这个C程序进行了封装，在备份innodb表时，此脚本会调用xtrabackup这个C程序。
如果使用xtrabackup这个C程序进行备份，则只能备份innodb和xtradb表，不能备份myisam表。
如果使用innobackupex进行备份，则可以备份innodb或xtradb表，同时也能备份myisam表。
所以一般在使用xtrabackup备份工具进行数据备份时，通常会选择使用innobackupex命令进行备份。
XtraBackup的优点
1、可以快速可靠的完成数据备份（复制数据文件和追踪事务日志）
2、数据备份过程中不会中断事务的处理（热备份）
3、节约磁盘空间和网络带宽
4、自动完成备份鉴定
5、因更快的恢复时间而提高在线时间
安装
1 | yum -y install https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm |
|---|
常用参数介绍
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | --defaults-file：指定my.cnf参数文件的位置，如果配置文件里没有写数据文件位置，那么我们需要额外指定--datadir选项 --apply-log：在备份完成后，暂时不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态。--apply-log的作用是<span style="text-decoration: underline;"><span style="color: #ff0000; text-decoration: underline;">通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态</span>。</span> --apply-log-only：会让xtrabackup跳过回滚节点，只做“redo”步骤，当数据库需要应用增量备份时，需要指定这个参数 --copy-back：恢复还原，如果my.cnf文件里没有指定数据目录位置，这里需要加上--datadir=/usr/local/mysql/data --remote-host=HOSTNAME： 通过ssh将备份数据存储到进程服务器上 --stream=[tar]：备份文件输出格式, 该文件可在XtarBackup binary文件中获得. 在使用参数stream=tar备份的时候,你的xtrabackup_logfile可能会临时放在/tmp目录下,如果你备份的时候并发写入较大的话,xtrabackup_logfile可能会很大(5G+),很可能会撑满你的/tmp目录,可以通过参数--tmpdir指定目录来解决这个问题，目前支持的格式有tar和xbstream，如果指定了这个参数，后面需要接tmpdir目录作为处理流的一个中间目录 --tmpdir=DIRECTORY：当有指定--remote-host or --stream时, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir --redo-only：强制备份日志时只redo,跳过rollback,用于数据库的全备或合并增量备份 --use-memory=*：该参数在prepare的时候使用,控制prepare时innodb实例使用的内存 --databases=LIST：列出需要备份的databases,如果没有指定该参数,所有包含MyISAM和InnoDB表的database都会被备份 --slave-info：备份从库, 加上--slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容。类似于:CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0 --socket=SOCKET：指定mysql.sock所在位置，以便备份进程登录mysql. --incremental-basedir：以上一次全量或增量备份的路径，作为增量备份的基础。当指定这个参数时，需要配合--incremental参数 --incremental：创建增量备份，当指定这个参数时，应该指定--incremental-lsn或--incremental-basedir参数，否则将会备份到--incremental-basedir路径 --no-timestamp：备份时不创建带有时间格式的子文件夹。 --tables-file=FILE：这个参数会接受一个字符串，这个字符串指定了一个文件，这个文件包含了需要备份的表明，格式为：database.tabl |
|---|
备份全部库
1 | innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf /testdir |
|---|
还原：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 一定要先停止数据库，然后再清空数据目录下的所有数据（全量备份时） service mysql stop cd /usr/local/mysql rm -rf data/* 对数据进行准备工作，合成可用的一致的数据，--use-memory可以加速准备工作的完成 innobackupex --apply-log /data/mysql_backup/full_backup --use-memory=2G --user=root --password=123456 或者 innobackupex --apply-log /testdir/2018-02-12_17-33-22 完成上述工作，证明我们已经获得了一份可用的，数据一致的备份，下面就是数据还原操作了： innobackupex --copy-back /testdir/2018-02-12_17-33-22 或者 innobackupex --datadir=/usr/local/mysql/data --copy-back /testdir/2018-02-12_17-33-22 或者 innobackupex --defaults-file=/etc/my.cnf --copy-back /testdir/2018-02-12_17-33-22 chown mysql:mysql -R data/ /etc/init.d/mysqld restart |
|---|
指定数据库备份：
1 2 3 4 5 | innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --databases="novel" /testdir 这样会生成一个带有时间的目录，加上--no-timestamp不带时间 多个库时可以写成：--databases="db1 db2"，中间空格隔开 备份成压缩文件： innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --databases="novel" --no-timestamp --stream=tar ./ | gzip - > db.bz.tar.gz |
|---|
注意：这里指定库进行备份时有些问题，后面会研究下，目前仅作参考。
还原：
1 2 3 4 5 | 清空数据目录 innobackupex --apply-log /testdir/2018-02-12_17-33-22 innobackupex --copy-back /testdir/2018-02-12_17-33-22 chown -R mysql:mysql usr/local/mysql/data service mysql start |
|---|
增量备份及还原
增量备份是建立在完整备份的基础上的，所以首先确保已经完整备份了一次
1 | innobackupex --user=root --password=123456 --databases="dbname" --no-timestamp /testdir/2018-02-12_17-33-22 |
|---|
第一次增量备份：
1 2 | innobackupex --incremental /testdir/bkdata1 --no-timestamp --incremental-basedir=/testdir/2018-02-12_17-33-22 --user=root --password=root --defaults-file=/etc/my.cnf |
|---|
第二次增量备份：需要把–incremental-basedir指定到上次做增量备份的目录，这里应该知道/testdir/bkdata1，一次类推，每次的增量备份都是在上一次的基础上进行
1 2 | innobackupex --incremental /testdir/bkdata2 --no-timestamp --incremental-basedir=/testdir/bkdata1 --user=root --password=root --defaults-file=/etc/my.cnf |
|---|
选项–incremental是指定作增量备份 –incremental-basedir选项是指定上一次增量备份的目录（如果是第一次作增量备份，则为完整备份的目录）
实际上增量备份主要是对innodb而言，对myisam和其他存储引擎来说，仍然是全备份，增量备份过程主要是通过拷贝innodb中有变更的“页”（页指的是页的LSN大于xtrabackup_checkpoints中给定的LSN），增量备份是基于全备的，第一次的增备必须基于上一次的全备，第二次基于第一次的增备………依次类推，最终达到一致性的增备。
关于LSN：
执行全量备份后：
1 2 3 4 5 6 7 8 9 | #innobackupex --defaults-file=/etc/my.cnf --user=root --host=localhost --password=clickwise10050 --no-timestamp /testdir/bkdata_all [root@xs_test_bk 2018-02-12_19-05-57]# cat xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 5924993513 last_lsn = 5924993513 compact = 0 recover_binlog_info = 0 |
|---|
可以看出这是一次全量备份
增量备份LSN：
1 2 3 4 5 6 7 8 9 | #innobackupex --user=root --host=localhost --password=clickwise10050 --incremental /testdir/bkdata1 --incremental-basedir=/testdir/bkdata_all [root@xs_test_bk 2018-02-13_14-53-26]# cat xtrabackup_checkpoints backup_type = incremental from_lsn = 5924993513 to_lsn = 5925001708 last_lsn = 5925001708 compact = 0 recover_binlog_info = 0 |
|---|
还原：
增量备份恢复需要三个步骤：
1、恢复完全备份
1 | #innobackupex --apply-log --redo-only /testdir/bkdata_all |
|---|
2、恢复增量备份到完全备份（开始恢复的增量备份要添加–redo-only参数，到最后一次增量备份去掉–redo-only）
1 2 3 4 5 | #innobackupex --apply-log --redo-only /testdir/bkdata_all --incremental-dir=/testdir/bkdata1 有两个增量备份时： #innobackupex --apply-log --redo-only /testdir/bkdata_all --incremental-dir=/testdir/bkdata2 超过两个增量备份时： #innobackupex --apply-log /testdir/bkdata_all --incremental-dir=/testdir/bkdata3 |
|---|
3、对整体的完全备份进行恢复，回滚那些未提交的数据
1 | #innobackupex --apply-log /testdir/bkdata_all |
|---|
注意：在增量备份恢复时，需要将每一个增量备份的数据先应用到全量备份中去，除了最后一个增备，所有的增备需要加上–redo-only参数，目的是只应用xtrabackup日志中已提交的事物数据，不回滚还未提交的数据，最后一个增备数据在应用到全备时不需要加–redo-only，因为需要回滚xtrabackup日志中那些还未提交的数据。
所有数据一致后，往数据目录里放还原数据：
1 2 3 4 5 6 7 | 1、先停止数据库进程 2、清空数据目录，可以mv走 3、开始还原 innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /testdir/bkdata_all 4、重新给数据目录修改所属权限 5、启动数据库 6、进库验证 |
|---|