数据库技术：数据库学习之十一--mysql备份恢复

作者:cuiyongchao007更新于： 2020-05-18 09:44:02

　　备份恢复是最简单的方式为您和您的公司获得随时随地获取重要的是你的一切-从任何一台计算机，设备，服务器，或云应用。

　　十一、MySQL备份恢复

　　课程大纲

　　1、备份的原因

　　2、备份的方式

　　3、备份的工具

　　4、mysqldump备份工具的详解

　　5、mysqldump+mysqlbinlog实现增量备份

　　6、企业级备份策略及恢复案例

　　7、xtrabackup备份恢复实战

　　运维工作的核心简单概括就两件事：

　　第一个是保护公司的数据。

　　第二个是让网站能7*24小时提供服务(用户体验)。

　　1、备份的类型

　　冷备份：关闭数据、停止业务

　　温备份：枷锁备份

　　热备份：在线备份，不会影响业务。

　　2、备份方式

　　逻辑备份：

　　基于sql语句的备份：

　　①mysqldump--》建库，建表，数据插入

　　②基于二进制日志：数据库的所有变化类的操作。

　　③基于复制的备份：将二进制日志实时传递到另一台机器并且恢复。

　　物理备份：

　　①xtrabackup进行物理备份

　　②拷贝数据文件（冷备）

　　3、备份工具：

　　①mysqldump

　　mysql原生自带很好用的逻辑备份工具

　　②mysqlbinlog（根据始末position位置进行截取备份）

　　实现binlog备份的原生态命令

　　③xtrabackup

　　percona公司开发的性能很高的物理备份工具

　　mysqldump备份工具优缺点：

　　优点：逻辑备份工具，都是sql语句，都是文本格式，便于查看和编辑，便于压缩。

　　缺点：备份较慢，效率低。

　　mysqldump参数介绍：

　　-u-p-S-h-P

　　-A,全库备份

　　例子：

　　[root@centos6-kvm3~]#mysqldump-uroot-poldboy123-A>/backup/mysqlfull.sql

　　单库备份：

　　-B,增加建库（create）及’use库‘的语句，在将来恢复时，不需要手工建库和use库。

　　-B选项还可以实现，同时备份多个库，备份到同一个文件中。（空格隔开）

　　[root@centos6-kvm3~]#mysqldump-uroot-poldboy123-Blufei>/backup/mysqllufei.sql

　　[root@centos6-kvm3~]#mysqldump-uroot-poldboy123lufei>/backup/mysqllufei1.sql

　　不加-B，恢复时候需要先创建库，use库下再进行恢复。

　　不加-B,去备份他库下的一个单表的意思。

　　生成环境下，也要加的额外参数：

　　-R:备份存储过程和函数数据。

　　--triggers:备份触发器数据。

　　-F，--flush-logs：刷新binlog日志，为了方便将来二进制日志截取时的起点。

　　mysqldump-uroot-poldboy-A-F>/backup/mysqlfull.sql

　　--master-data={1|2}:告诉你备份时刻的binlog位置，一般我们选择2，以注释的方式记录二进制日志的位置。

　　mysql>showmasterstatus;

　　锁表：适合所有引擎（myisam,innodb）

　　-x,--lock-all-tables

　　-l,--lock-tables

　　--single-transaction对innodb引擎进行热备

　　[root@centos6-kvm3~]#mysqldump-uroot-poldboy123-A--master-data=2--single-transaction>/backup/mysqlfull.sql

　　通过快照的方式实现热备。

　　压缩备份：

　　[root@centos6-kvm3~]#mysqldump-uroot-poldboy123-A-R--triggers--master-data=2--single-transaction|gzip>/backup/mysqlfull_$(date+%F).sql

　　4、mysqldump+binlog企业恢复实战

　　使用source命令进行恢复

　　setsql_log_bin=0;（临时关闭二进制日志，防止恢复操作记录到二进制日志中）

　　source/opt/xxx.sql;

　　企业实例：

　　背景环境：

　　正在运行的网站系统，mysql数据库，数据量25G，日业务增量10-15M。

　　备份方式：

　　每天23:00点，计划任务调用mysqldump执行全备脚本。

　　故障时间点：

　　上午10点，误删除了一个表。

　　如何恢复？

　　思路：

　　①断开业务，防止对数据库二次伤害，挂出维护页面。

　　②搭建备用库，恢复全备。

　　③截取昨天晚上23:00之后到上午10点误删除操作之前的二进制日志。

　　④恢复到备用库，验证数据可用性和完整性。

　　⑤两种恢复前端应用。

　　 5.1备用库导出误删除的表，导入到生产库，开启业务。

　　 5.2直接将应用切割刀备用库，替代生产库，开启业务。

　　模拟故障并恢复：

　　1、原始数据：

　　mysql>createdatabaseoldboy;

　　mysql>useoldboy

　　mysql>createtablet1(idint,namevarchar(20));

　　mysql>insertintot1values(1,'zhang3');

　　mysql>insertintot1values(2,'li4');

　　mysql>insertintot1values(3,'wang5');

　　mysql>commit;

　　2、模拟前一天晚上23:00全备

　　mysqldump-A-R--triggers--master-data=2--single-transaction|gzip>/backup/all_$(date+%F).sql.gz

　　3、模拟白天（23:00-10:00）业务对数据的修改

　　mysql>insertintot1values(4,'zhang33');

　　mysql>insertintot1values(5,'li44');

　　mysql>insertintot1values(6，'wang54');

　　mysql>commit;

　　4、模拟故障

　　droptablet1;

　　5、恢复

　　（1）准备全备，并获取到备份文件中的binlog的截取起点

　　gunzipall_2018-04-04.sql.gz

　　 --CHANGEMASTERTOMASTER_LOG_FILE='my-bin.000004',MASTER_LOG_POS=731;

　　（2）截取二进制日志

　　mysqlbinlog--start-position=731--stop-position=1126/data/binlog/my-bin.000004>/backup/binlog.sql

　　-----

　　showbinlogeventsin'my-bin.000004';----》drop之前的position为1126

　　-----

　　(3)恢复全备+binlog

　　setsql_log_Bin=0;

　　source/backup/all_2018-04-04.sql;

　　source/backup/binlog.sql

　　5、xtrabackup介绍

　　Xtrabackup物理备份工具

　　percona公司的备份工具，性能比较高。物理备份工具。

　　特点：

　　物理备份工具，在同级数据量基础上，都要比逻辑备份性能要好的多。

　　特别是在数据量比较大的时候，体现的更加明显。

　　备份方式：

　　 1、拷贝数据文件

　　 2、拷贝数据页

　　备份原理（innodb）：

　　 1、对于innodb表，可以实现热备

　　 （1）在数据还有修改操作的时刻，直接将数据文件中的数据页备份

　　 此时，备份走的数据对于当前mysql来讲是不一致。

　　 （2）将备份过程中的redo和undo一并备走。

　　 （3）为了恢复的时候，只要保证备份出来的数据页LSN能和redoLSN匹配，

　　 将来恢复的就是一致的数据。redo应用和undo的应用。

　　 2、对与myisam表，实现自动锁表拷贝文件。

　　Xtrabackup软件安装：

　　1、安装

　　wget-O/etc/yum.repos.d/epel.repohttp://mirrors.aliyun.com/repo/epel-6.repo

　　yum-yinstallperlperl-devellibaiolibaio-develperl-Time-HiResperl-DBD-MySQL

　　wgethttps://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

　　yum-yinstallpercona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

　　2、全备备份并恢复

　　mkdir/server/backup-p

　　--user=

　　--password=

　　--socket=

　　--no-timestamp

　　全备例子：

　　innobackupex/server/backup/

　　innobackupex--no-timestamp/server/backup/full

　　全备恢复例子：

　　1、恢复数据前的准备(合并xtabackup_log_file和备份的物理文件)

　　innobackupex--apply-log--use-memory=32M/server/backup/full/

　　2、模拟故障

　　停库:

　　pkillmysqld

　　破坏数据：

　　cd/application/mysql/data

　　\\rm-rf*

　　3、恢复

　　cp-a/server/backup/full/*/application/mysql/data

　　或者

　　innobackupex--copy-back/server/backup/full/

　　注意：恢复时，要确认数据路径是空的，并且数据库是停掉的

　　chown-Rmysql.mysql/application/mysql/data

　　启动：

　　/etc/init.d/mysqldstart

　　mysql-e"select*fromoldboy.t1"

　　6、xtrabackup实现增量备份及故障恢复

　　xtrabackup实现增量备份及故障恢复

　　周日全备，周一到周六做增量

　　1、周日全备：

　　mkdir/backup/full

　　innobackupex--user=root--password=123--no-timestamp/backup/full/

　　2、模拟数据变化（周一数据变化）

　　。

　　。

　　。

　　3、第一增量（周一晚上增量）：

　　innobackupex--user=root--password=123--incremental--no-timestamp--incremental-basedir=/backup/full//backup/inc1

　　4、模拟数据变化（周二数据变化）

　　。

　　。

　　。

　　5、第二次增量（周二晚上增量）：

　　innobackupex--user=root--password=123--incremental--no-timestamp--incremental-basedir=/backup/inc1/backup/inc2

　　6、模拟数据损坏

　　n多的操作。。。。。

　　周三上午10:00时刻,删除t1表

　　7、恢复数据：

　　innobackupex--apply-log--redo-only/backup/full

　　innobackupex--apply-log--redo-only--incremental-dir=/backup/inc1/backup/full

　　innobackupex--apply-log--incremental-dir=/backup/inc2/backup/full

　　innobackupex--apply-log/backup/full

　　7、xtrabackup生产恢复案例：

　　背景：

　　1、xtrabackup备份策略每周日，full全备

　　2、xtrabackup周一到周六，inc1-inic6

　　3、总数据量200G

　　4、周三上午10点误删除表t1，数据量1G左右

　　5、周二晚上inc2备份完成之后到周三上午10点又做了很多操作

　　如何将数据库恢复到t1表误删除之前状态？

　　思路：

　　 1、停业务，挂维护页

　　 2、找备用库

　　 3、合并full+inc1+inc2

　　 4、截取周二晚上inc2备份后到周三上午10点，t1表删除之前的binlog日志

　　 5、将合并后的full+截取的binlog恢复到备用库

　　 （根据备份日志最后的position，再根据showbinlogeventsin'my-bin.000004';获取最后的position）

　　 6、验证数据可用性和完整性

　　 7、使用备用库替代生产库使用或者将t1表导出并导入回生产库

　　 8、业务恢复

　　-----------------

　　-----------------

　　思考：以上恢复策略是否可以优化？

　　为了恢复1G表，需要将整个全备恢复，有必要吗？有什么好的解决办法？

　　单独恢复一个表：

　　droptablet1;

　　createtablet1(idint,namevarchar(20));

　　altertablet1discardtablespace;

　　cd/application/mysql/data/oldboy

　　cp/backup/full/oldboy/t1.ibd./

　　chown-Rmysql.mysql*

　　altertablet1importtablespace;

　　Syncplicity的文件同步和管理技术使您的所有数据，在一个地方，所以它总是备份，同步，访问，并立即共享。，因为它是设计和建造从地上爬起来，为企业和企业，它给你的控制水平，安全和功能，你需要。