下面一起来了解下MySQL 与 xtrabackup如何实现备份与恢复，相信大家看完肯定会受益匪浅，文字在精不在多，希望Mysql 与 xtrabackup如何实现备份与恢复这篇短内容是你想要的。
10年积累的网站制作、做网站经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先做网站后付款的网站建设流程，更有道外免费网站建设让你可以放心的选择与我们合作。
Mysql 备份恢复与 xtrabackup备份
1.1 备份的原因
备份是数据安全的最后一道防线，对于任何数据丢失的场景，备份虽然不一定能恢复百分之百的数据(取决于备份周期)，但至少能将损失降到最低。衡量备份恢复有两个重要的指标：恢复点目标(RPO)和恢复时间目标(RTO)，前者重点关注能恢复到什么程度，而后者则重点关注恢复需要多长时间。
1.1.1 备份的目录
做灾难恢复：对损坏的数据进行恢复和还原
需求改变：因需求改变而需要把数据还原到改变以前
测试：测试新功能是否可用
1.1.2 备份中需要考虑的问题
可以容忍丢失多长时间的数据；
恢复数据要在多长时间内完；
恢复的时候是否需要持续提供服务；
恢复的对象，是整个库，多个表，还是单个库，单个表。
1.1.3 备份的类型
热备份：
这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。
冷备份：
这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。
温备份：
这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。
1.2 备份的方式
1.2.1 冷备份
最简单的备份方式就是，关闭MySQL云服务器，然后将data目录下面的所有文件进行拷贝保存，需要恢复时，则将目录拷贝到需要恢复的机器即可。这种方式确实方便，但是在生产环境中基本没什么作用。因为所有的机器都是要提供服务的，即使是Slave有时候也需要提供只读服务，所以关闭MySQL停服备份是不现实的。与冷备份相对应的一个概念是热备份，所谓热备份是在不影响MySQL对外服务的情况下，进行备份。
冷备份及停止业务进行备份。
1.2.2 快照备份
首先要介绍的热备份是快照备份，快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的原理是将所有的数据库文件放在同一分区中，然后对该分区执行快照工作，对于Linux而言，需要通过LVM(Logical Volumn Manager)来实现。LVM使用写时复制(copy-on-write)技术来创建快照，例如，对整个卷的某个瞬间的逻辑副本，类似于数据库中的innodb存储引擎的MVCC，只不过LVM的快照在文件系统层面，而MVCC在数据库层面，而且仅支持innodb存储引擎。
LVM有一个快照预留区域，如果原始卷数据有变化时，LVM保证在任何变更写入之前，会复制受影响块到快照预留区域。简单来说，快照区域内保留了快照点开始时的一致的所有old数据。对于更新很少的数据库，快照也会非常小。
对于MySQL而言，为了使用快照备份，需要将数据文件，日志文件都放在一个逻辑卷中，然后对该卷快照备份即可。由于快照备份，只能本地，因此，如果本地的磁盘损坏，则快照也就损坏了。快照备份更偏向于对误操作防范，可以将数据库迅速恢复到快照产生的时间点，然后结合二进制日志可以恢复到指定的时间点。基本原理如下图：
1.2.3 逻辑备份（文本表示：SQL 语句）
冷备份和快照备份由于其弊端在生产环境中很少使用，使用更多是MySQL自带的逻辑备份和物理备份工具，这节主要讲逻辑备份，MySQL官方提供了Mysqldump逻辑备份工具，虽然已经足够好，但存在单线程备份慢的问题。在社区提供了更优秀的逻辑备份工具mydumper，它的优势主要体现在多线程备份，备份速度更快。
1.2.4 其他常用的备份方式
物理备份（数据文件的二进制副本）
全量备份概念
全量数据就是数据库中所有的数据（或某一个库的全部数据）；
全量备份就是把数据库中所有的数据进行备份。
mysqldump会取得一个时刻的一致性数据.
增量备份（刷新二进制日志）
增量数据就是指上一次全量备份数据之后到下一次全备之前数据库所更新的数据
对于mysqldump,binlog就是增量数据.
1.2.5 备份工具的介绍
1、mysqldump: mysql原生自带很好用的逻辑备份工具
2、mysqlbinlog: 实现binlog备份的原生态命令
3、xtrabackup: precona公司开发的性能很高的物理备份工具
1.3 mysqldump备份介绍
备份的基本流程如下：
1.调用FTWRL(flush tables with read lock)，全局禁止读写 2.开启快照读，获取此时的快照(仅对innodb表起作用) 3.备份非innodb表数据(*.frm,*.myi,*.myd等) 4.非innodb表备份完毕后，释放FTWRL锁 5.逐一备份innodb表数据 6.备份完成。
整个过程，可以参考我同事的一张图，但他的这张图只考虑innodb表的备份情况，实际上在unlock tables执行完毕之前，非innodb表已经备份完毕，后面的t1,t2和t3实质都是innodb表，而且5.6的mysqldump利用保存点机制，每备份完一个表就将一个表上的MDL锁释放，避免对一张表锁更长的时间。
1.3.1 mysqldump备份流程
1.3.2 常用的备份参数
参数 参数说明
-A 备份全库
-B 备某一个数据库下的所有表
-R, --routines 备份存储过程和函数数据
--triggers 备份触发器数据
--master-data={1|2} 告诉你备份后时刻的binlog位置如果等于1，则将其打印为CHANGE MASTER命令; 如果等于2，那么该命令将以注释符号为前缀。
--single-transaction 对innodb引擎进行热备
-F, --flush-logs 刷新binlog日志
-x, --lock-all-tables 锁定所有数据库的所有表。这是通过在整个转储期间采用全局读锁来实现的。
-l, --lock-tables 锁定所有表以供读取
-d 仅表结构
-t 仅数据
--compact 减少无用数据输出(调试)
一个完整的备份语句： innodb引擎的备份命令如下：mysqldump -A -R --triggers --master-data=2 --single-transaction |gzip >/opt/all$(date +%F).sql.gz 适合多引擎混合（例如：myisam与innodb混合）的备份命令如下： mysqldump -A -R --triggers --master-data=2 |gzip >/opt/all$(date +%F).sql.gz
1.3.3 -A 参数
备份全库，备份语句
mysqldump -uroot -p123 -A > /backup/full.sql
1.3.4 -B 参数
备某一个数据库下的所有表
增加建库（create）及“use库”的语句，可以直接接多个库名，同时备份多个库* -B 库1 库2
mysqldump -uroot -p123 -B world > /backup/worldb.sql
备份语句：
create database if not 存在 use db1 drop table create table insert into
不加-B备份数据库时，只是备份数据库下的所有表，不会创建数据库
只能备份单独的数据库（一般用于备份单表时使用） mysqldump -uroot -p123 world > /backup/world.sql 备份单表 mysqldump -uroot -p123 world city > /backup/world_city.sql 对于单表备份的粒度，再恢复数据库数据时速度最快。 备份多个表 mysqldump 库1 表1 表2 表3 >库1.sql mysqldump 库2 表1 表2 表3 >库2.sql
分库备份:for循环
mysqldump -uroot -p'mysql123' -B mysql ... mysqldump -uroot -p'mysql123' -B mysql_utf8 ... mysqldump -uroot -p'mysql123' -B mysql ... ......
分库备份
for name in `mysql -e "show databases;"|sed 1d` do mysqldump -uroot -p'mysql123' -B $name done
1.3.5 --master-data={1|2}参数
告诉你备份后时刻的binlog位置
2为注释 1为非注释，要执行的(主从复制)
[root@db02 logs]# sed -n '22p' /opt/t.sql CHANGE MASTER TO MASTER_LOG_FILE='clsn-bin.000005', MASTER_LOG_POS=344; [root@db02 logs]# mysqldump -B --master-data=2 clsn >/opt/t.sql
1.3.6 --single-transaction 参数
对innodb引擎进行热备
只支持innodb引擎 使用该参数会单独开启一个事务进行备份，利用事务的快照技术实现。
基于事务引擎:不用锁表就可以获得一致性的备份.
体现了ACID四大特性中的隔离性，生产中99% 使用innodb事务引擎.
虽然支持热备，并不意味着你可以再任意时间点进行备份，特别是业务繁忙期，不要做备份策略，一般夜里进行备份。
innodb引擎的备份命令如下：
mysqldump -A -B -R --triggers --master-data=2 --single-transaction |gzip >/opt/all.sql.gz
1.3.7 --flush-logs参数/-F
刷新binlog日志
每天晚上0点备份数据库
mysqldump -A -B -F >/opt/$(date +%F).sql [root@db02 ~]# ll /application/mysql/logs/ -rw-rw---- 1 mysql mysql 168 Jun 21 12:06 clsn-bin.000001 -rw-rw---- 1 mysql mysql 168 Jun 21 12:06 clsn-bin.000002 -rw-rw---- 1 mysql mysql 210 Jun 21 12:07 clsn-bin.index 提示:每个库都会刷新一次.
1.3.8 压缩备份
压缩备份命令:
mysqldump -B --master-data=2 clsn|gzip >/opt/t.sql.gz
解压:
zcat t.sql.gz >t1.sql gzip -d t.sql.gz #删压缩包 gunzip alL_2017-12-22.sql.gz
一个完整的备份语句
innodb引擎的备份命令如下： mysqldump -A -R --triggers --master-data=2 --single-transaction |gzip >/opt/all.sql.gz 适合多引擎混合（例如：myisam与innodb混合）的备份命令如下： mysqldump -A -R --triggers --master-data=2 |gzip >/opt/alL_$(date +%F).sql.gz
1.3.9 使用Mysqldump备份进行恢复实践
备份innodb引擎数据库clsn并压缩：
mysqldump -B -R --triggers --master-data=2 clsn|gzip >/opt/all_$(date +%F).sql.gz
人为删除clsn数据库：
[root@db02 opt]# mysql -e “drop database clsn;” [root@db02 opt]# mysql -e “show databases;”
恢复数据库：
使用gzip解压 gzip -d xxx.gz shell> mysql set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> source /backup/alL_2017-12-22.sql
验证数据：
[root@db02 opt]# mysql -e “use clsn;select * from test;”
1.4 【模拟】增量恢复企业案例
1.4.1 前提条件:
1.具备全量备份（mysqldump）。
2.除全量备份以外，还有全量备份之后产生的的所有binlog增量日志。
1.4.2 环境准备
(1)准备环境：
drop database clsn; CREATE DATABASE clsn; USE `clsn`; CREATE TABLE `test` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; INSERT INTO `test` VALUES (1,'clsn'),(2,'znix'),(3,'inca'),(4,'zuma'),(5,'kaka');
查看创建好的数据
mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | clsn | | 2 | znix | | 3 | inca | | 4 | zuma | | 5 | kaka | +----+------+ 5 rows in set (0.00 sec)
(2)模拟环境：
mkdir /data/backup -p date -s "2017/12/22"
全备份:
mysqldump -B --master-data=2 --single-transaction clsn|gzip>/data/backup/clsn_$(date +%F).sql.gz
模拟增量:
mysql -e "use clsn;insert into test values(6,'haha');" mysql -e "use clsn;insert into test values(7,'hehe');" mysql -e "select * from clsn.test;"
(3)模拟误删数据：
date -s "2017/12/22 11:40" mysql -e "drop database clsn;show databases;"
出现问题10分钟后,发现问题,删除了数据库了.
1.4.3 恢复数据准备
(1)采用iptables防火墙屏蔽所有应用程序的写入。
[root@clsn ~]# iptables -I INPUT -p tcp --dport 3306 ! -s 172.16.1.51 -j DROP #<==非172.16.1.51禁止访问数据库3306端口。 或采用mysql 配置参数，但是需要重启数据库 --skip-networking 复制二进制日志文件 cp -a /application/mysql/logs/clsn-bin.* /data/backup/ 截取日志 zcat clsn_2017-12-22.sql.gz >clsn_2017-12-22.sql sed -n '22p' clsn_2017-12-22.sql mysqlbinlog -d clsn --start-position=339 clsn-bin.000008 -r bin.sql
需要恢复的日志:
1.clsn_2017-12-22.sql 2.bin.sql grep -i drop bin.sql sed -i '/^drop.*/d' bin.sql
1.4.4 进行数据恢复
恢复数据
重庆分公司
重庆分公司
