一、面试中常问：mysql数据库做哪些优化也提高mysql性能
1.查询时，能不用* 就不用，尽量写全字段名。
2.索引不是越多越好，每个表控制在6个索引以内。范围where条件的情况下，索引不起作用，比如where value<100
3.大部分情况连接效率远大于子查询，但是有例外。当你对连接查询的效率都感到不能接受的时候可以试试用子查询，虽然大部分情况下你会更失望，但总有碰到惊喜的时候不是么...
4.多用explain 和 profile分析查询语句
5.有时候可以1条大的sql可以分成几个小sql顺序执行，分了吧，速度会快很多。
6.每隔一段时间用alter table table_name engine=innodb;优化表
7.连接时注意:小表 jion 大表的原则
8.学会用explain 和 profile判断是什么原因使你的sql慢。
9.查看慢查询日志，找出执行时间长的sql试着优化去吧~~
二、基于Xtrabackup8的Mysql定时全量,增量备份及恢复实战演练
所有mysql实例皆为mysql8版本,使用的xtrabackup备份组件为xtrabackup8.
生产mysql使用基于percona的分支,相对于原版mysql多了一些性能调教和监控视图,版本为:percona-server-server-8.0.22,备份相关工具对mysql8的官方版本也是完全兼容的.percona的分支相关信息: https://www.percona.com/software/mysql-database/percona-server
生产环境mysql一共3个实例,使用mgr组成集群以实现灵活的高可用与读写分离.并由前置的proxysql进行数据的路由与转发.
模拟故障为:在生产环境mysql8 的mgr集群完全不可用,有前一天的xtrabackup全量备份和增量备份,需要从之前的全量和增量备份完整恢复到故障最近的时间点.本次故障恢复的要求是读取前一天的所有增量和全量数据并恢复.快速重组生产mgr集群.
在这里会用ansible脚本快速搭建3个mysql实例,以模拟生产环境(略过).
backup_func.sh :
策略是每天的0:30做一次全量备份,之后每两个小时的半点会做一次增量备份.
注: 本次恢复属于完整的生产环境集群恢复,下面的 获取备份文件 , 准备备份 , 开始恢复 相关流程,需要在每一台服务器上执行,实际操作中,如果只需要恢复并查看数据则只做一个点就可以了.
这里取的是前一天的打过包的备份文件,根据备份脚本的规则,每天凌晨的全量备份之前,会自动将前一天的全量备份和增量备份目录全部打包并以前一天的日期命名:
20210609.tar.gz
将其scp到待恢复的服务器上并解压:
解压后的目录结构:
确保需要恢复的服务器有mysql实例,xtrabackup8工具已安装,由于备份是经过压缩的,确保qpress也已安装.
由于备份脚本在备份时使用了 --compress 指令,在恢复备份前,需要先解压缩备份,
这里将所有增量和全量备份路径均执行一下解压缩指令:
在同时存在全量和增量备份需要合并的情况下,准备备份时需要带上 --apply-log-only 参数,但是要注意在准备最后一个增量备份的时候,不需要加该参数.
以上操作会将所有的增量备份合并到全量备份中.
根据各自安装时指定的相关路径去删除数据.(data,binglog,logs,undolog),一般在my.cnf中有指定
如果my.cnf不是在默认路径(/etc/my.cnf),需要指定一下mysql配置文件的路径: --defaults-file=${db_conf}
至此,备份数据在单节点上的恢复已经完成了.
查看下最后一份增量备份里才会有的一些数据
先确保同样的mysql恢复操作分别在三台服务器上执行完成.(如果在新建服务器上恢复mgr集群,一定要检查my.cnf中mgr集群相关配置,比如 loose-group_replication_local_address , loose-group_replication_group_seeds , loose-group_replication_start_on_boot )
master节点启动:
mgr的三台节点的权重实际上是一样的,选择其中的一台做master即可.
mster节点启动完成后,再分别在两台slave节点启动mgr:
由于3台mysql实例的数据是一样的,节点间状态同步迅速就ok了.
至此,3台mysql的mgr状态均为 online ,整个集群启动完成,全演练流程结束.
三、Mysql中如何实现某字段数据自动加1
随着 mysql 8.0.16 的发布，我们为 mgr 添加了一些功能，以增强其高可用性。其中一个功能是能够在某些情况下启用已离开组的成员自动重新加入，而无需用户干预。
为了理解这个功能的好处以及如何使用它，我们将快速查看它背后的概念以及它首先存在的动机。
介绍
mgr 允许 mysql 用户轻松管理高可用组，并完成保证系统高可用所需的所有特征，例如容错或故障检测。
mgr 中提供的基本保证之一是该组呈现给用户的是一个不可分割的整体，这意味着一旦成员加入或离开该组，该更改将立即被其他成员得知。默认情况下，组内的数据本身最终是一致的，尽管可以被修改。为了实现这种保证，mgr 使用组成员服务，以及通过一致性算法检测有冲突的事务并中止它们。mgr 的这一方面超出了本文的范围，与成员自动重新加入功能并不完全相关，本文不作赘述。
组内新成员必须符合一些条件。其中新成员需要在事务方面赶上组进度（是通过选择组内一个成员来将已处理的事务流式传输给他，在 mgr 中称为“捐赠”）。最后，只要在此“分布式恢复”过程中没有遇到任何错误，组内新成员将被声明为 online 状态。
mgr 依靠组通信层 (gcs) 来管理组。该层实现了用于解决冲突事务的一致性算法，并强制执行一些通信特性。对于实现前面提到的组的不可分割视图，这些特性至关重要，如消息的总顺序、安全传递或视图同步等。
gcs 需要能够检测组中哪些成员失效或看起来失效。一旦这些成员被检测为失效，就将其从该组中移除，以便保持该组正常使用。为此 gcs 在每个成员中引入了一个故障检测器，用于分析组内交换的消息。如果它在一段时间内没有收到来自指定成员的消息，则故障检测器将对该成员产生“怀疑”，并认为该成员可能已经失效。成员从“怀疑”到真正失效的等待时间是可以配置的。
重新加入成员存在的问题
我们已经了解 mgr 必须为了高可用提供的策略，以及它如何实现，接下来请看示例：
一个小组由三个成员组成，其中一个成员偶尔会遇到丢失数据包、断连或者其它导致无法解决的错误情况的影响组内通信。还要考虑这些错误持续时间超过group_replication_member_expel_timeout的值。
其中一个组员发生故障，小组的其他成员将决定踢出该成员。问题是，一旦该成员重新入组，他将被组驱逐加入失败，需要通过手动干预。
如果该成员的驱逐超时属性设置不为 0，则它将在被驱逐前等待满足该时间量（将超时设置为 0 意味着他将永远等待）。超时后成员将被驱逐并重新建立连接，并且无法重新加入旧组，需要再次手动干预。
于此，当存在网络故障时，显然需要手动干预。
在 mysql 8.0.16 中，我们引入了自动重新加入组的功能，一旦成员被驱逐出组，它就会自动尝试重新加入该组，直到达到预设的次数为止。有时每次重试之间至少等待5分钟。
如何启动自动重新加入？
可以通过将group_replication_autorejoin_tries设置为所需的重试次数来开启并使用自动重新加入功能。
set global group_replication_autorejoin_tries = 3
默认值为 0，表示服务器禁用自动重新加入。
如何验证自动重新加入？
与 mysql 中的许多功能一样，自动重新加入过程是可以监测的。自动重新加入的可检测性依赖于性能模式基础架构，阶段式收集有关数据。
他们获取以下信息：
事件发生的线程id（thread_id）
活动名称（event_name）
起止时间戳以及事件的总持续时间（timer_start，timer_end 和 timer_wait）
在事件停止之前完成的工作单位和预估工作单位（work_completed，work_estimated）
因此，当自动重新加入过程开始时，它将在performance schema中注册一个名为“stage / grouprpl / undergoing auto-rejoinprocedure”的事件。使用表performance_schema.events_stage_current，performance_schema.events_stages_summary_global_by_event_name和performance_schema.events_stages_history_long我们可以观察到以下内容：
是否正在进行自动重新加入程序
到目前为止，已经减少重试的次数
直到下一次重试的估计剩余时间
自动重新加入过程状态
可以通过过滤包含“auto-rejoin”字符串的活动事件来查找自动重新加入过程状态（即，是否正在进行）：
select count(*) from performance_schema.events_stages_current
where event_name like '%auto-rejoin%';
count(*)
1
查询结果存在，证明服务器上运行了自动重新加入过程。
到目前为止的重试次数
如果正在进行自动重新加入程序，我们可以通过选择阶段事件上的工作单元数来检查到目前为止尝试的重试次数：
select work_completed from performance_schema.events_stages_current where
event_name like '%auto-rejoin%';
work_completed
1
在这个例子中，到目前为止只有一次尝试。
预计到下次重试的剩余时间
在每次重新加入尝试之间，服务器将处于 5 分钟的可中断睡眠中。 重新加入尝试直到成功或失败之间的时间是无法估计的。 因此，为了粗略估计剩余时间，我们可以将到目前为止尝试的重试次数乘以 5 分钟，并减去到目前为止的阶段事件所花费的时间，以估计我们还需要多长时间：
select (300.0 - ((timer_wait*10e-12) - 300.0 * num_retries)) as time_remaining from
(select count(*) - 1 as num_retries from
performance_schema.events_stages_current where event_name like '%auto-rejoin%') as t,
performance_schema.events_stages_current where event_name like '%auto-rejoin%';
time_remaining
30.0
所以在这个例子中，在下一次重新加入之前还有 30 秒。注意性能模式表中的所有时间记帐都以微秒精度保持，因此我们将 timer_wait 缩放为秒。
使用自动重新加入与驱逐超时的权衡
到目前为止，在这篇文章中我们只关注自动重新加入。实际上，有两种不同的方法可以实现离开组的成员的重新加入：
设置自动重新加入尝试次数来实现自动重新加入
设置该成员的驱逐超时时间然后配合手动干预
能有延缓删除组内可疑成员，并且如果配置为足够长的驱逐超时时间，则增加了重新建立连接的机会，再次与组进行交互。
虽然这两个功能实现了相同的目标，但它们的工作方式是不同的，并且需要权衡。通过使用驱逐超时，您可以维护组中可疑的成员，其缺点是您无法添加或删除成员或选择新的主机。如果通过使用自动重新加入，该成员将不再是该组的正常组员，将保持在 superreadonly 模式，直到重新加入该组。但在此期间，重新加入成员的同步旧数据的可能性将增加。自动重新加入过程可监控，而驱逐超时不是真正可监控的。
所以，总结一下：
驱逐超时的优点
- 该成员一直在该组内
- 可能更适合足够小的网络故障
驱逐超时的缺点
- 在怀疑某个成员时，无法在该组上添加/删除成员
- 在怀疑某个成员时，无法选择新的主机
- 您无法监控此过程
自动重新加入的优点
- 该组将在没有重新加入成员的情况下运行，您可以添加/删除成员并选择新的主机
- 您可以监控该过程
自动重新加入的缺点
- 您增加了重新加入成员上过时读取的可能性
- 可能不适合足够小的网络故障
总而言之，我从启用自动重新加入中获得了什么？
通过启用自动重新加入，您可以减少对mysql实例的手动干预的需要。您的系统
更加适应瞬间网络故障，同时满足对容错性和高可用的保证。
摘要
我们引入了一个名为group_replication_autorejoin_tries的新系统变量，允许用户设置 mgr 成员在被驱逐或与组的大多数人失去联系后尝试重新加入组的次数。
默认情况下，此自动重新加入过程处于关闭状态。它能帮助用户在面对瞬间网络故障时避免对 mgr 成员进行手动干预。
