数据库技术---（一）MySQL

2021/12/1 2:08:15

编程Tag： 服务器 mysql 数据库复制 master Slave ## ---

本文主要是介绍数据库技术---（一）MySQL，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1、Mysql的编译安装

软件下载地址：MySQL :: Developer Zonehttps://dev.mysql.com/

安装编译工具：

yum install cmake -y

yum install -y gcc-c++

安装依赖包：

yum install -y boost

yum install -y bison

yum install -y ncurses-devel

yum install -y ncurses

编译安装：

##boost也可以使用编译参数-DDOWNLOAD_BOOST=1/自动下载
参数详解：
-DCMAKE_INSTALL_PREFIX #安装目录
-DSYSCONFDIR #置文件存放（默认可以不安装配置文件）
-DMYSQL_DATADIR #数据目录，错误日志文件也会在这个目录
-DMYSQL_UNIX_ADDR #sock文件位置，用来做网络通信的，客户端连接服务器的时候用
-DDEFAULT_CHARSET #默认字符集；字符集的支持，可以调
-DWITH_EXTRA_CHARSETS=all #扩展的字符集支持所有
-DDEFAULT_COLLATION #支持的排序规则
-DENABLED_LOCAL_INFILE=1 #从本地倒入数据，不是备份和恢复
-DWITH_INNOBASE_STORAGE_ENGINE=1 #默认的存储引擎，支持外键

2、使用mysql的基础配置

##配置系统用户、创建数据目录

##配置mysql相关命令的环境变量

##配置mysqld的配置文件，指定数据目录等文件位置

##初始化数据库

##创建mysql启动脚本并启动mysql

##mysql的安全初始化；此处所用密码是mysql初始化时生成的（见上图）

以上配置完成后，即可成功使用mysql

3、mysql结合php

配置步骤：

访问效果：

但此时还无法登录：

继续以下配置步骤：

##在php配置文件中指定mysql的sock文件

此时访问就可以成功登陆并使用

4、mysql的高级配置

【1】主从复制

主从复制的意义：

～～在业务复杂的系统中，假设有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务；使用主从复制，让主库负责写，从库负责读，这样即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运行

～～做数据的热备，主库宕机后能够及时替换主库，保证业务可用性

～～当I/O访问频率过高，单机无法满足时，可做多库的存储以扩展架构，降低磁盘I/O访问的频率，提高单个机器的I/O性能

主从复制的原理：

##主库db的更新事件(update、insert、delete)被写到binlog

##从库启动并发起连接，连接到主库

##主库创建一个binlog dump thread，把binlog的内容发送到从库

##从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log

##从库启动之后，创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

配置步骤：

##配置server1作为主mysql数据库

##创建mysql用户并授权通过此用户进行主从复制；查看主mysql状态及主mysql的server-id

##配置server2的mysql相关文件

##server2的mysql使用基础配置

##首先同步主从数据库的数据（必须）

##配置server2作为从mysql数据库

配置完成，测试效果：

##在master数据库中添加数据

##slave数据库中也可以访问到

【2】GTID复制

GTID功能：

可以在集群全局范围标识事务，用于取代过去通过binlog文件偏移量定位复制位置的传统方式，借助GTID，在发生主备切换的情况下，MySQL的其它Slave可以自动在新主上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生误操作的风险，另外，基于GTID的复制可以忽略已经执行过的事务,减小了数据发生不一致的风险

配置步骤：

##配置master数据库开启GTID复制模式并重启mysql

##配置slave数据库开启GTID复制模式并重启mysql

##重新配置slave数据库的slave复制模式

配置完成，测试效果：

##在master端插入数据

##slave端成功同步数据（此处多处的user4数据为此实验前手动插入的，不影响实验效果即可）

##在slave端查看GTID的执行

##master端插入数据

##GTID执行记录

【3】多从复制

利用slave端作为下一slave端的master进行线性复制

配置步骤：

##从slave端拷贝文件至下一slave端；配置slave端作为master并开通slave复制功能；授权下一slave端进行复制的mysql用户

##下层slave基础配置

##下层slave首先进行数据同步

##配置slave功能

##重启mysql，查看此下层slave是否配置成功

配置完成，测试效果：

##在主master端插入数据

##新配置的下层slave端也可以成功查看到数据

【4】半同步复制

mysql有三种复制方式：异步复制、同步复制、半同步复制

异步复制：master库将事件写入到binlog日志后，不会等待slave库返回回执就会触发引擎提交，返回结果至客户端；这样就不能保证所有的事件都已经成功复制到slave库，因此如果master和slave之间有网络延迟，就会产生暂时地数据不一致的现象；如果master出故障由slave库担任主库时，而数据还未成功复制至slave库的情况下，就会造成数据丢失，客户端再次访问时之前显示成功提交的数据反而又不存在了，大大降低了用户体验；但此种方式的优点在于效率较其他两种复制方式高，上述主从复制就是一个异步复制过程，也是mysql默认使用的复制方式

同步复制：master库将事件发送给slave库后会触发一个等待，直到所有slave节点返回数据复制成功的回执给master才会触发引擎提交返回客户端；这种复制方式最安全，但是效率也是最差

半同步复制：master库将事件发送给slave库后会触发一个等待，直到其中一个slave节点返回数据复制成功的回执至master库就会触发引擎提交返回结果至客户端；此复制方式增强了数据的一致性，但因为master库的确认开销，会损耗一部分性能；另外，半同步复制除了不需要等待所有的slave库确认事件的接收外，半同步数据复制并不要求那些事件完全地执行；因此仍有可能看到在slave库中数据复制延迟的发生，如果因为网络延迟等原因造成slave迟迟没有返回复制成功的回执并超过了master库设置的超时时长（rpl_semi_sync_master_timeout），半同步复制就降级为异步复制方式继续数据复制，因此为保证数据的一致性，通常将此超时时长设置为无穷大，而后直到至少有一个slave节点从master成功复制数据并返回回执至master库，master库才会切换回半同步复制模式

配置步骤（以下从库的复制数据位置都基于GTID）：

##首先确认是否支持动态加载插件（确保此变量的值为YES）
##master库安装半同步复制插件并启用

##slave库安装半同步复制插件并启用

半同步复制模式必须在主库和从库中同时开启，否则将会默认为异步复制模式

##mysql的插件所在目录

select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';

##查看主/从库插件状态

##查看主/从库半同步复制的变量值

##查看主库半同步复制的参数状态；此时Rpl_semi_sync_master_clients=0

##查看从库的半同步复制的参数状态并通过重启从库的io_thread来开启从库的半同步复制

##此时查看主库的半同步复制的参数状态时显示Rpl_semi_sync_master_clients=1，说明这两台主从库的半同步复制已经配置好了

测试效果：

##在主库插入数据时从库成功复制

##主库半同步复制参数发生变化

##在从库中模拟无法给到主库数据复制成功的回执

##在主库插入数据，此处重新设置了超时时间为5000ms

##此时从库复制数据失败

##主库的相关参数发生变化

##重启从库后数据被复制，不过次条数据是通过降级后的异步复制得来的

##重启从库的半同步复制后主库的参数变化

为了防止这些动态的半同步复制参数在服务器重启后失效，需要写入配置文件/etc/my.conf中，使其永久生效

##主库配置文件

##从库配置文件

【5】并行复制

mysql的并行复制通过在slave端开启多个sql线程加速复制以解决复制延迟问题

配置方式：

在slave端编辑mysql配置文件/etc/my.cnf

slave-parallel-type=LOGICAL_CLOCK

##二进制日志中的last_committed和sequence_number：last_committed表示事务提交的时候，上次事务提交的编号，如果事务具有相同的last_committed，表示这些事务都在一组内，可以进行并行的回放；而sequence_number是顺序增长的，每个事务对应一个序列号；另外，还有一个细节，其实每一个组的last_committed值，都是上一个组中事务的sequence_number最大值，也是本组中事务sequence_number最小值减1；同时这两个值的有效作用域都在文件内，只要换一个文件（flush binary logs），这两个值就都会从0开始计数；上述的last_committed和sequence_number代表的就是所谓的LOGICAL_CLOCK

slave-parallel-workers=16

##开启16个worker，sql线程由单个进化为对应的多个；若将slave_parallel_workers设置为0，则MySQL 5.7退化为原单线程复制，如果将slave_parallel_workers设置为1，则sql线程功能转化为coordinator线程，但是只有1个worker线程进行回放，也是单线程复制；然而，这两种性能却又有一些的区别，因为多了一次coordinator线程的转发，因此slave_parallel_workers=1的性能反而比0还要差

slave_preserve_commit_order=1

##MySQL 5.7后的MTS可以实现更小粒度的并行复制，但需要将slave_parallel_type设置为LOGICAL_CLOCK，但仅仅设置为LOGICAL_CLOCK也会存在问题，因为此时在slave上应用事务的顺序是无序的，和relay log中记录的事务顺序不一样，这样数据一致性是无法保证的；为了保证事务是按照relay log中记录的顺序来回放，就需要开启参数slave_preserve_commit_order；开启该参数后，执行线程将一直等待, 直到提交之前所有的事务；当从线程正在等待其他工作人员提交其事务时，它报告其状态为等待前面的事务提交，所以虽然MySQL 5.7添加MTS后，虽然slave可以并行应用relay log，但commit部分仍然是顺序提交，其中可能会有等待的情况；当开启slave_preserve_commit_order参数后，slave_parallel_type只能是LOGICAL_CLOCK，如果使用级联复制，那LOGICAL_CLOCK可能会使离master越远的slave并行性越差；但是经过测试，这个参数在MySQL 5.7.18中设置之后，也无法保证slave上事务提交的顺序与relay log一致，在MySQL 5.7.19设置后，slave上事务的提交顺序与relay log中一致，所以生产中要想使用MTS特性，版本大于等于MySQL 5.7.19才是安全的

master_info_repository=TABLE

##优化选项，master_info默认以文件存储方式记录master信息；并行复制开启后对于元master.info这个文件的更新将会大幅提升，用表来记录时可以提升50%~80%的性能

relay_log_info_repository=TABLE

##优化选项，上同，为提升性能

relay_log_recovery=ON

##激活recovery，读取master二进制日志；如果损坏，直接丢弃然后重新读取

重启mysql后查看效果

【6】延迟复制

延迟复制就是将slave节点与master节点保持指定时间的复制间隔；所谓的延迟也只是对SQL_Thread线程的延迟；对于IO_Thread线程，主库发生的任何操作的日志都会同步到slave，也就是说slave节点的IO_Thread线程和主库是没有延迟的，只是SQL_Thread与主库有延迟，即延迟复制只是执行时间的延迟，而不是读取binlog的时间延迟；延迟复制可应用于误操作的数据恢复

配置方式：

##在slave端(单位为秒)

##show slave status\G;

测试效果：

##在主库插入数据

##从库大于30秒后数据才同步

##也可通过查看从库的slave状态来查看效果

【7】慢查询

MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句；具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中

配置步骤：

##查看是否开启慢查询；默认为未启用

##启用慢查询并设定慢查询阀值；此方式设定的阀值在重新登陆数据库时会恢复默认值

配置完成，测试效果：

##重新登陆后重新设定慢查询阀值，并使用以上sql语句进行慢查询测试

##慢查询被记录进日志中

【8】组复制

组复制原理

MySQL Group Replication（MGR）是MySQL 5.7.17版本引入的一个服务器插件，可用于创建高可用、可扩展、容错的复制拓扑结构；组复制可以在单主模式下操作，其中只有一个服务器接受更新，这个单主是系统自动选举出来的，而对于高级用户，也可以部署为多主模式，其中所有服务器都可以接受更新，内置的组成员服务可以在任何给定的时间点保持组的视图一致并可供所有服务器使用；当服务器加入或离开组时，视图也会相应更新，当服务器宕机，故障检测机制会检测到此情况并通知组其视图已更改，这些都是自动进行的

创建容错系统最常见的方法是组件冗余；换句话说，一个组件被移除时，系统应该继续按预期运行，这产生了一系列挑战，将这种系统的复杂性提高到了一个完全不同的水平；数据复制必须维护和管理多个服务器，还必须处理若干其它经典的分布式系统问题，如网络分区或脑裂；对MySQL而言，最终的挑战是将数据复制逻辑与协调多个服务器的逻辑相融合，这可以概括为让每个服务器的状态在数据变化时达成一致，即便它们都作为单个数据库系统运行，但最终都收敛到相同的状态

MGR对属于同一组的服务器自动进行协调；对于要提交的事务，组成员必须就全局事务序列中给定事务的顺序达成一致，提交或回滚事务由每个服务器单独完成，但所有服务器都必须做出相同的决定，如果存在网络分区，导致成员无法达成事先定义的分割策略，则在解决此问题之前系统不会继续进行，这是一种内置的自动裂脑保护机制；MGR由组通信系统（Group Communication System，GCS）协议支持，该系统提供故障检测机制、组成员服务以及安全且有序的消息传递；所有这些属性都是创建系统的关键，可确保在服务器组中一致地复制数据；该技术的核心是Paxos算法的实现，它充当组通信引擎

MGR中的一组服务器构成一个复制组，组名形式为UUID；组是动态的，服务器可以离开（主动或被动）并随时加入组；服务器加入或离开时，组会自行调整；如果服务器加入组，组会通过从现有服务器获取状态自动更新新加入的服务器，状态通过MySQL异步复制进行传输；如果服务器离开该组，其余服务器会知道它已离开并自动重新配置该组

组由多个服务器构成，通过传递消息进行交互，通信层保证原子消息传递；MGR构建于此通信层抽象之上，并实现了多主更新复制协议；组中的每个服务器独立地执行事务，但是所有读写事务只有在得到组的批准后才会提交，只读事务在组内不需要协调，因此立即提交；对于任何读写事务，当事务准备好在始发服务器处提交时，服务器以原子方式广播写入值（更改的行）和对应的写入集（更新的行的唯一标识符），然后将该事务加入全局事务列表，最终所有服务器都以相同的顺序接收并应用相同的事务集，所以它们在组内保持一致

不同服务器上并发执行的事务之间可能存在冲突；MGR在certify过程中检查并发事务的写集来检测这种冲突；如果在不同服务器上执行的两个并发事务更新同一行，则存在冲突；解决方案是先到事务提交，后到事务回滚，即按顺序第一个事务在所有服务器提交，而第二个事务在在原始服务器上回滚并在组中的其它服务器中删除；这实际上体现的是多主分布式事务的首个提交获胜原则

组复制使用场景

组复制可用来创建具有冗余的容错系统，即使某些服务器发生故障，只要它不是全部或大多数，系统仍然可用；根据失败的服务器数量，可能会降低性能或可伸缩性，但它仍然可用；组成员服务跟踪服务器故障，该服务依赖于分布式故障检测器，能够在任何服务器脱离组时发出信号，无论是意外停止还是主动停止；分布式恢复过程确保当服务器加入组时能自动更新，单个服务器发生故障时不会停止服务，也无需服务器故障转移；总之，MGR保证数据库服务持续可用

需要注意，尽管数据库服务可用，但在服务器崩溃的情况下，必须将连接到它的客户端重定向或转移到其它服务器，组复制不解决数据库连接重定向的问题，连接器、负载平衡器、路由器或某种形式的中间件更适合处理此问题，例如MySQL Router；以下是组复制的典型使用场景：

～～弹性复制：服务器的数量能够动态增加或减少，并且尽可能减小副作用，例如云数据库服务
～～高可用分片：分片是实现写扩展的流行方法，使用MGR实现高可用分片，其中每个分片映射到复制组
～～主从复制的替代方案：在某些情况下，使用单个主服务器会使其成为热点，写入整个组会更具可扩展性

组复制相关服务

～～故障检测：组复制包括一种故障检测机制，该机制能够查找并报告哪些服务器已经宕机；当服务器A在给定时间内没有从服务器B接收到消息时，发生超时并引发怀疑，然后如果组同意怀疑是真的，那么该组决定该服务器确实宕机了；这意味着该组中的其余成员将采取协调决策以排除给定成员；如果一个服务器与组的其余部分隔离，它会怀疑所有其它服务器都已失败，但由于无法与该组达成协议（因为无法确保法定票数），其怀疑并没有结果；当服务器以这种方式与组隔离时，它无法执行任何本地事务

～～组成员服务：MGR依赖于组成员服务，该服务内置于插件中；它定义了哪些服务器在线并参与该组，在线服务器列表通常称为视图；因此组中的每个服务器都具有一致的视图，其中是在给定时刻主动参与该组的成员；服务器不仅必须同意事务提交，还要同意当前视图；因此如果服务器同意新服务器成为组的一部分，则组本身将重新配置该服务器集成在其中，从而触发视图更改；相反的情况也会发生，如果服务器离开组，则组会动态更新配置并触发视图更改；组成员离开组分主动与被动：主动离开会启动组的动态重新配置，这会触发所有其它成员必须在没有该服务器的情况下就新视图达成一致；被动离开（如已意外停止或断网）时，故障检测机制会建议重新配置组，这需要组中大多数服务器的同意；如果该组无法达成协议，为阻止脑裂，系统无法动态更改配置，这意味着管理员需要介入解决此问题

～～容错：MGR构建于Paxos分布式算法的实现之上，需要多数服务器处于活动状态才能达到法定票数，从而做出决定，这直接影响系统可以容忍的故障机数量，但不会影响组复制自身及其整体功能；容忍 f 个故障机所需的服务器数量 n 为：n = 2 * f + 1；实践中为了容忍一个故障机，该组必须具有三个服务器，因为此时如果一个服务器发生故障，仍然有两个服务器构成多数，并允许系统继续自动做出决策并继续提供服务，但如果第二个服务器继续失败，那么该组（剩下一个服务器）会阻塞，因为没有多数票可以做出决定

配置多主模式组复制

配置server1、server2、server3这三台主机作为复制组的组成员

因之前相关实验的影响，需要关闭mysqld并删除这三台主机mysql数据目录下的文件，然后重新初始化mysqld

配置此三台主机的mysql配置文件为以下

##除server-id和local_address参数不同，其余参数三台主机保持一致

以上配置完成后，启动mysqld

数据库sql语句下的配置：

##三台主机同样的配置完成后显示复制组中有三台数据库主机；当复制组成员状态为recovering时可能是对应的主机没有地址解析

注意：对于复制组成员启用组复制（start group_replication）时，在启用的第一个节点需要使用引导启动，其余节点则可省略此步骤

配置完成，测试效果：

mysql复制组成员中任意主机都可插入数据，且其他组成员都能同步数据

【9】Mysql路由器（MySQL Router）

Mysql路由器是高可用性(HA)解决方案的构建块；它通过智能地将连接路由到Mysql服务器来简化应用程序开发，以提高性能和可靠性；Mysql Router是 InnoDB Cluster 的一部分，是轻量级中间件，可在应用程序和后端Mysql服务器之间提供透明路由；可用于各种用例，例如通过将数据库流量路由到适当的后端Mysql 服务器来提供高可用性和可扩展性，可插拔架构还使开发人员能够为自定义用例扩展Mysql路由器

Mysql使用 Group Replication 跨多个服务器复制数据库，同时在服务器发生故障时执行自动故障转移；当与 Mysql InnoDB Cluster 一起使用时，Mysql Router 充当代理以隐藏网络上的多个MySQL实例并将数据请求映射到集群实例之一；只要有足够的在线副本并且组件之间的通信完好无损，应用程序就可以联系其中之一；MySQL Router 还通过让应用程序连接到 MySQL Router 而不是直接连接到Mysql来实现这一点

配置Mysql路由器：

首先保证有一个mysql集群以便路由器可以进行请求转发

配置server4作为mysql路由器主机，IP=172.25.100.4

##安装mysql route软件并添加配置至其配置文件

##启动mysql路由服务

配置完成，测试效果：

##在物理机安装mysql客户端并通过mysql路由器的7001端口访问时，请求被转发至后端服务器集群中的server1上

##当server1挂掉后，请求被转发至server2上；此顺序按照上述配置文件中的destinations中指定的顺序进行轮询，前提是轮询策略是round-robin；此策略下，后端mysql服务器都正常运作情况下的请求转发连接轮询也是按照destinations中的指定顺序

##在物理机通过mysql路由器的7002端口访问时，请求被转发至后端服务器集群中的server1上

##当server1挂掉后，请求转发按配置文件中的目的地排序进行轮询；first-available策略的含义是排序第一位的服务器正常运作的情况下都将请求转发给它，但已经建立的链接不会跳转给它

【10】MHA高可用

MHA（MasterHigh Availability）是一款开源的MySQL的高可用程序，它为MySQL主从复制架构提供了automating master failover功能；MHA在监控到master节点故障时，会提升其中拥有最新数据的slave节点成为新的master节点，在此期间，MHA会通过与其它从节点获取额外信息来避免一致性方面的问题；MHA还提供了master节点的在线切换功能，即按需切换master/slave节点；相较于其它HA软件，MHA的目的在于维持MySQL Replication中Master库的高可用性，其最大特点是可以修复多个Slave之间的差异日志，最终使所有Slave保持数据一致，然后从中选择一个充当新的Master，并将其它Slave指向它

MHA服务有两种角色：MHA Manager（管理节点）和MHA Node（数据节点）
MHA Manager：通常单独部署在一台独立的机器上或者直接部署在其中一台slave上（不建议后者），管理多个master/slave集群，每个master/slave集群称作一个application；其作用为

～～master自动切换及故障转移命令运行

～～其他的帮助脚本运行：手动切换master、master/slave状态检测

Manager工具包主要包括以下几个工具：

masterha_check_ssh //检查MHA的SSH配置状况

masterha_check_repl //检查MySQL复制状况

masterha_manger //启动MHA

masterha_check_status //检测当前MHA运行状态

masterha_master_monitor //检测master是否宕机

masterha_master_switch //控制故障转移（自动或者手动）

masterha_conf_host //添加或删除配置的server信息
MHA node：运行在每台MySQL服务器上（master/slave/manager），它通过监控具备解析和清理logs功能的脚本来加快故障转移；其作用为

～～复制主节点的binlog数据

～～对比从节点的中继日志文件

～～无需停止从节点的SQL线程，定时删除中继日志

Node工具包（由MHA Manager的脚本触发，无需人为操作）主要包括以下几个工具：

save_binary_logs //保存和复制master的二进制日志

apply_diff_relay_logs //识别差异的中继日志事件并将其差异的事件应用于其他的slave

filter_mysqlbinlog //去除不必要的ROLLBACK事件（MHA已不再使用这个工具）

purge_relay_logs //清除中继日志（不会阻塞SQL线程）

MHA的配置

在配置MHA之前搭建其配置所需的环境，即至少搭建一个一主两从复制集群；本人设定server1为master，server2、server3为slave端；并设定server4为manager端

##在manager端和node端需要的工具包(此处省略server1和server3的节点工具包安装截图)

##创建manager端配置文件并添加相关配置

2：manager工作目录

3：manager日志文件

4：mysql主服务器的binlog目录

5：failover自动切换脚本

6：手动切换脚本

7+8：mysql主从节点的管理员用户+密码(确保可以从远程登陆)

9：发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行failover

10：远端mysql在发生切换时binlog的保存位置

11+12：主从复制用户+密码

13：manager检测后端mysql集群的热备；一般采用第三方主机（除过master/slave/manager的主机）

14：ssh用户名

23：指定failover时此slave会接管成为master，即使数据不是最新的

24：默认情况下如果一个slave落后master100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master

29：表示此节点始终是slave

##配置基于ssh进行通信的环境；集群中的各节点彼此之间均需要基于ssh互信通信，以实现远程控制及数据管理功能