MySQL 优化实施方案_mysql的实施方案-CSDN博客

本文链接：https://blog.csdn.net/DK_Allen/article/details/121861080

1. 备份的原因

备份是数据安全的最后一道防线，对于任何数据丢失的场景，备份虽然不一定能恢复百分之百的数据(取决于备份周期)，但至少能将损失降到最低。衡量备份恢复有两个重要的指标：恢复点目标(RPO)和恢复时间目标(RTO)，前者重点关注能恢复到什么程度，而后者则重点关注恢复需要多长时间。

1.1 备份的目录

做灾难恢复：对损坏的数据进行恢复和还原

需求改变：因需求改变而需要把数据还原到改变以前

测试：测试新功能是否可用

1.2 备份中需要考虑的问题

可以容忍丢失多长时间的数据；

恢复数据要在多长时间内完；

恢复的时候是否需要持续提供服务；

恢复的对象，是整个库，多个表，还是单个库，单个表。

1.3 备份的类型

热备份：

这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。

冷备份：

这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。

温备份：

这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。

2. 备份的方式

2.1 冷备份

最简单的备份方式就是，关闭MySQL服务器，然后将data目录下面的所有文件进行拷贝保存，需要恢复时，则将目录拷贝到需要恢复的机器即可。这种方式确实方便，但是在生产环境中基本没什么作用。因为所有的机器都是要提供服务的，即使是Slave有时候也需要提供只读服务，所以关闭MySQL停服备份是不现实的。与冷备份相对应的一个概念是热备份，所谓热备份是在不影响MySQL对外服务的情况下，进行备份。

冷备份及停止业务进行备份。

2.2 快照备份

首先要介绍的热备份是快照备份，快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的原理是将所有的数据库文件放在同一分区中，然后对该分区执行快照工作，对于Linux而言，需要通过LVM(Logical Volumn Manager)来实现。LVM使用写时复制(copy-on-write)技术来创建快照，例如，对整个卷的某个瞬间的逻辑副本，类似于数据库中的innodb存储引擎的MVCC，只不过LVM的快照在文件系统层面，而MVCC在数据库层面，而且仅支持innodb存储引擎。

LVM有一个快照预留区域，如果原始卷数据有变化时，LVM保证在任何变更写入之前，会复制受影响块到快照预留区域。简单来说，快照区域内保留了快照点开始时的一致的所有old数据。对于更新很少的数据库，快照也会非常小。

对于MySQL而言，为了使用快照备份，需要将数据文件，日志文件都放在一个逻辑卷中，然后对该卷快照备份即可。由于快照备份，只能本地，因此，如果本地的磁盘损坏，则快照也就损坏了。快照备份更偏向于对误操作防范，可以将数据库迅速恢复到快照产生的时间点，然后结合二进制日志可以恢复到指定的时间点。基本原理如下图：

2.3 逻辑备份（文本表示：SQL 语句）

冷备份和快照备份由于其弊端在生产环境中很少使用，使用更多是MySQL自带的逻辑备份和物理备份工具，这节主要讲逻辑备份，MySQL官方提供了Mysqldump逻辑备份工具，虽然已经足够好，但存在单线程备份慢的问题。在社区提供了更优秀的逻辑备份工具mydumper，它的优势主要体现在多线程备份，备份速度更快。

2.4 其他常用的备份方式

物理备份（数据文件的二进制副本）

全量备份概念

全量数据就是数据库中所有的数据（或某一个库的全部数据）；

全量备份就是把数据库中所有的数据进行备份。

mysqldump会取得一个时刻的一致性数据.

增量备份（刷新二进制日志）

增量数据就是指上一次全量备份数据之后到下一次全备之前数据库所更新的数据

对于mysqldump,binlog就是增量数据.

2.5 备份工具的介绍

1、mysqldump: mysql原生自带很好用的逻辑备份工具

2、mysqlbinlog: 实现binlog备份的原生态命令

3、xtrabackup: precona公司开发的性能很高的物理备份工具

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锁释放，避免对一张表锁更长的时间。

3.1 mysqldump备份流程

3.2 常用的备份参数

3.3 -A 参数

备份全库，备份语句

mysqldump -uroot -p123 -A > /backup/full.sql

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

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

3.6 --single-transaction 参数

对innodb引擎进行热备

只支持innodb引擎

使用该参数会单独开启一个事务进行备份，利用事务的快照技术实现。

基于事务引擎:不用锁表就可以获得一致性的备份.

体现了ACID四大特性中的隔离性，生产中99% 使用innodb事务引擎.

虽然支持热备，并不意味着你可以再任意时间点进行备份，特别是业务繁忙期，不要做备份策略，一般夜里进行备份。

innodb引擎的备份命令如下：

3.7 --flush-logs参数/-F

刷新binlog日志

每天晚上0点备份数据库

mysqldump -A -B -F >/opt/$(date +%F).sql

[root@db02 ~]# ll /application/mysql/logs/ -rw-rw -rw-rw -rw-rw

提示:每个库都会刷新一次.

3.8 压缩备份

压缩备份命令:

解压:

zcat t.sql.gz >t1.sql gzip -d t.sql.gz #删压缩包 gunzip alL_2017-12-22.sql.gz

一个完整的备份语句

innodb引擎的备份命令如下：

适合多引擎混合（例如：myisam与innodb混合）的备份命令如下：

1.3.9 使用Mysqldump备份进行恢复实践

备份innodb引擎数据库clsn并压缩：

人为删除clsn数据库：

[root@db02 opt]# mysql -e “drop database clsn;” [root@db02 opt]# mysql -e “show databases;”

恢复数据库：

使用gzip解压 gzip -d xxx.gz shell> mysql </opt/all_2017-1222.sql 或 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;”

4.【模拟】增量恢复企业案例

4.1 前提条件:

1.具备全量备份（mysqldump）。

2.除全量备份以外，还有全量备份之后产生的的所有binlog增量日志。

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"

全备份:

模拟增量:

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)模拟误删数据：

出现问题10分钟后,发现问题,删除了数据库了.

4.3 恢复数据准备

(1)采用iptables防火墙屏蔽所有应用程序的写入。

或采用mysql 配置参数，但是需要重启数据库

复制二进制日志文件

截取日志

需要恢复的日志:

4.4 进行数据恢复

恢复数据

查看数据库

恢复增量数据：

恢复完毕。

调整iptables允许用户访问.

4.5 多个binlog问题

5.1 发生背景

1、mysql服务器会在每天夜里0点全量备份

2、某个开发人员某个阳光明媚的上午，喝着茶，优雅的误删除了clsn_oss（核心）数据库。

3、导致公司业务异常停止，无法正常提供服务。

5.2 怎么解决的

1、当前系统进行评估。

什么损坏了，有没有备份，

恢复数据时间（误操作的数据有关，备份、恢复策略），

恢复业务时间

2、恢复方案

（1）恢复0点的全备，到测试库

（2）恢复0点开始到故障时间点的binlog，到测试库

（3）将误操作的数据导出，恢复到生产库。

（4）检验数据是不是完整的（开发测试环境测试恢复成功数据库）

（5）检验完成之后，重新开启生产业务

5.3 项目总结

1、经过我的恢复处理，30分钟整体业务重新提供服务（速度慢。。。）

2、在以后的工作中制定严格的开发规范，开发，开发。

3、将来制定更好的架构方案。

6. 备份工具的选择

数据量范围：30G --> TB级别

6.1 数据量大，变换量小

（1）全备分花费的成本较高，mysqldump+binlog实现全备 + 增量备份，缺点是恢复成本比备份时间成本还高

（2）xtrabackup：可以较长时间做一次全备，其余时间都是增量，全量备份空间成本很高如果数据量在30G-->TB级别的话，更推荐使用xtrabackup工具。

6.2 数据量小，变化量大

只需要考虑时间成本。

只用全备备份即可，两种工具选择都可以。恢复成本上xtrabackup小一些

6.3 数据量、变化量都大

时间成本和空间成本都要考虑了。

数据量达到PB或更高时（facebook），mysqldump可能成为首选，占用空间小，但技术成本高。需要对mysqldump进行二次开发（大数据量公司首选）。

7. xtrabackup备份软件

percona公司官网 Percona – The Database Performance Experts

7.1 Xtrabackup介绍

Xtrabackup是由percona开源的免费数据库热备份软件，它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）；mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合。

Xtrabackup安装完成后有4个可执行文件，其中2个比较重要的备份工具是innobackupex、xtrabackup

7.1 Xtrabackup优点

1）备份速度快，物理备份可靠

2）备份过程不会打断正在执行的事务（无需锁表）

3）能够基于压缩等功能节约磁盘空间和流量

4）自动备份校验

5）还原速度快

6）可以流传将备份传输到另外一台机器上

7）在不增加服务器负载的情况备份数据

8）物理备份工具，在同级数据量基础上，都要比逻辑备份性能要好的多。几十G到不超过TB级别的条件下。但在同数据量级别，物理备份恢复数据上有一定优势。

7.2 备份原理

拷贝数据文件、拷贝数据页

对于innodb表可以实现热备。

对于myisam表实现自动锁表拷贝文件。

备份开始时首先会开启一个后台检测进程，实时检测mysql redo的变化，一旦发现有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中，之后复制innodb的数据文件一系统表空间文件ibdatax，复制结束后，将执行flush tables with readlock,然后复制.frm MYI MYD等文件，最后执行unlock tables,最终停止xtrabackup_log

7.3 xtrabackup的安装

安装依赖关系

下载软件包，并安装软件

8. xtrabackup实践操作

8.1 全量备份与恢复

这一阶段会启动xtrabackup内嵌的innodb实例，回放xtrabackup日志xtrabackup_log，将提交的事务信息变更应用到innodb数据/表空间，同时回滚未提交的事务(这一过程类似innodb的实例恢复）。恢复过程如下图：

备份

创建备份目录

进行第一次全量备份

恢复前准备

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

查看合并后的 checkpoints 其中的类型变为 full-prepared 即为可恢复。

破坏数据库数据文件

恢复方法

方法一:直接将备份文件复制回来

方法二:使用innobackupex命令进行恢复(推荐)

说明：无论使用那种恢复方法都要恢复后需改属组属主，保持与程序一致。

启动是数据库

8.2 增量备份与恢复

innobackupex增量备份过程中的"增量"处理，其实主要是相对innodb而言，对myisam和其他存储引擎而言，它仍然是全拷贝(全备份)

"增量"备份的过程主要是通过拷贝innodb中有变更的"页"（这些变更的数据页指的是"页"的LSN大于xtrabackup_checkpoints中给定的LSN）。增量备份是基于全备的，第一次增备的数据必须要基于上一次的全备，之后的每次增备都是基于上一次的增备，最终达到一致性的增备。增量备份的过程如下，和全备的过程很类似，区别仅在第2步。