江晓龙的博客

jiangxl~🔥个人简介🔥CSDN博客专家、51CTO专家博主、阿里云博客专家、华为云享专家、DevOps运维领域优质创作者、2021年度博客之星运维与安全领域TOP1，某厂高级运维工程师擅长Linux系统运维、开源监控软件维护、Kubernetes容器技术、CI/CD持续集成、自动化运维、大规模互联网WEB集群架构、开源软件部署维护等领域。🎉博客领域🎉云原生、云计算、数据库、DevOps运维开发。⭐️获得的奖项⭐️《Kubernetes集群方方面面进阶之路》《阿里云入门到精通实战》...........

如下图所示，设置分表的属性为10天进行一次分表，分表周期是2022-01-01到2022-01-30这个时间范围，每隔10天进行一次分表，那么在这个范围内一共会进行3次分片，注意以时间范围进行分表，分表的次数要与分片的节点数相对应，否则将会分表失败。可以看到1月的数据写入到分片1中，2月的数据写入到了分片2中，3月的数据写入到了分片2中，4月的数据写入到了分片1中。设置了按月分片后，1月的数据写入到分片1，2月的数据写入到分片2，3月的数据写入分片3，4月的数据写入分片1。

如下图所示，设置分表的属性为10天进行一次分表，分表周期是2022-01-01到2022-01-30这个时间范围，每隔10天进行一次分表，那么在这个范围内一共会进行3次分片，注意以时间范围进行分表，分表的次数要与分片的节点数相对应，否则将会分表失败。可以看到1号到15号的数据写入到了分片1中，15-30号的数据写入到了分片2中，31号的数据相当于下一个轮回，写入到分片1。按照日期进行分片，单位为天，根据设置的规则属性，例如10天一分表，每10天就会进行一次分表。

当字段值为word时，首先根据截取的子字符串长度（0:2）截取3位，截取后子字符串为wor，然后通过Hash运算得到一个二进制数，再通过固定Hash，将二进制数与1111111111进行位运算，得到一个十进制数5，拿着这个十进制数5在数组集合中查找，最后查找到5位于0-511之内，0-511属于分片1，此时这条数据就会路由到分片1中存储。在0-511之间数组的结构中，会记录上第一个分片节点的ID0，512-1023之间的数组的结构中，记录第二个分片节点的ID1。

例如当字段值为515，经过固定Hash算法运算，将515转换成二进制数，与1023的二进制数进行位运算，最后的出来位运算的结果是十进制数515，515位于512-1023数组之间，此时就会拿导512-1023数组对应的分片ID号，然后将这条数据写入到对应的分片节点中。我们可以将分片策略设置0-255之间划分到分片1，256-512之间划分到分片2，512-1023划分到分片3，当依据字段被固定Hash转换成2进制数并且位运算完后，得到一个十进制数时，根据十进制数所在的分片，将数据写入到对应的分片节点中。

应用指定Hash算法分片指的是，由应用自主决定路由到哪一个分片节点，根据分片的字段通过Hash算法计算出分片号，最终将数据写入到特定的分片节点中应用指定Hash算法分片的字段必须是数字类型的内容，否则没有分片的条件。字段值的内容例如是01xxxx，我们在配置分片规则时，就可以取前两个数字，然后根据Hash算法写入到对应的分片中。

所谓的一致性哈希，指的是相同的哈希因子计算值总是会被划分到相同的分片节点上，也就是作为分表依据的字段说通过Hash计算的哈希因子计算值，具有相同计算值的数据会被划分到相同的分片节点中，是按照哈希因子的计算值进行水平分表的。如果我们以id列作为一致性哈希分片的依据列，那么就不需要调整分片规则了，只需要调整分片规则函数中的节点数量即可。通过一致性哈希分片不会因为将来分片节点数的增加，而改变数据原来的存放位置，有效解决了分布式数据的问题。分片依旧是2个，还是之前垂直分库分表时使用的两套双主双从集群。

枚举分片使用时，需要先定义一个配置文件，在配置文件中声明枚举字段的值对应的分片节点ID，需要实现规划好枚举字段值的所有可能出现的值，避免有漏掉的字段值，没有在配置文件中定义，导致无法写入数据，当然如果在枚举函数中配置了defaultNode参数，可以避免此问题，当这条数据中的枚举字段值没有在配置文件中指定对应的节点ID，这条数据就被写入到默认的分片节点中。含义：当枚举字段的值为bj时，该数据写入到分片ID为0的的分片节点，当值为sh时，写入到分片ID为1的分片节点。下面我们通过枚举分片来实现这个需求。

根据id列进行取模分片，写入数据的id为15，分片节点数为2个，运算过程：15/2=7余1，余数为1，取模分片此时就会将这个数据写入到分片2中，因为在Mycat分片中，分片的ID都是从0开始的，余数的值就是要写入对应分片节点的ID号，余数为1就对应分片2这个节点。指定取模分片的字段一定要是数字类型的字段，否则是无法进行运算的。在Rule分片规则配置文件中，我们要配置主要是根据表中的那个字段进行范围分片，如果做取模分片的字段也是id字段，就不需要调整这个配置文件，只需要调整取模分片中传入的参数。

如果是生产环境的某张表进行水平分表，比如说表数据400w行都在分片1中读写的，水平分表时，分片1中的数据可以不动，按照范围，400w以后的数据在分表完成后，会自动路由到分片2上，因此只需要在所有的分片上准备好表结构即可，旧数据就在原实例。如果同时由多个表需要范围分片，并且做范围分片的字段都是不同的，我们也可以自己定义一个分片规则，只是给分片规则改个名而已，然后指定做分片的字段，指定调用哪一个函数即可，范围分片的函数是rang-long，在配置文件下面有显示。

我们的商城系统数据库，目前是单点数据库，随着业务量越来越大，每日产生的数据量越来越多，单台数据库的存储能力和计算能力是有限的，为了保证用户的体验度和满意度，在数据库性能到达瓶颈之前，我们先对数据进行性能优化，目前的优化方案是对商城库进行垂直分表，扩展数据库节点，将不同业务的表存储在多个数据库节点中，提高数据库的性能。垂直分库指的是将一个库中的多个表，拆分到多个数据库实例中，也就是拆分到了多台不同的数据库服务器上，缓解了单台数据库所承担的压力。为了保证数据库的高可用性和读写分离，我们在前面准备了2套双主双从的

水平分库是按照一定的策略以字段作为依据，将一个表的数据拆分到多张表中，缓解单表的压力，拆分出来的多张表只是名字不同，表数据都相同，用户的读写操作会被路由到其中一个分表中，拆分出来的所有表的数据量加起来才是完整的数据量。水平分库是按照一定的策略以字段作为依据，将一个库的所有表拆分到多个库中，每个库中的表结构都是相同的，只是数据量不同，用户的读写操作会被路由到某一个分库中，缓解单库的压力，水平分库后，所有库的数据量才是完整的数据量。垂直分库指的是将一个库中的多个表，拆分到不同的库中，这就是垂直分库。

首先添加多个Schema，一个Schema看不出来效果，然后配置Mycat显示那些Schema。1）定义多个Schema

logs目录:wrapper.log #mycat启动日志 mycat.log #mycat详细工作日志 conf目录:schema.xml #主配置文件（读写分离、高可用、分布式策略定制、节点控制） server.xml #mycat软件本身相关的配置 rule.xml #分片规则配置文件,记录分片规则列表、使用方法等定义一个逻辑库，一般都和真实的数据库库名保持一致，在配置文件中定义了几个schema逻辑库，通过mycat连接之后就只能看见定义的这些逻辑库，其他未定义的数据库将看不到。

Mycat是开源的、活跃的、基于JAVA语言编写的MySQL数据库中间件，可以把它看做是一个代理程序，开发人员可以像使用MySQL一样来使用Mycat，连接上Mycat就可以操作管理的数据库，无需知道底层到底有哪些数据库，每一台数据库都做了怎样的配置。Mycat是一个开源的分布式数据库系统，在企业环境中，只要涉及分布式的数据库架构，大多数都会采用Mycat去实现，Mycat的核心功能有读写分离、分库分表等等。

Atlas是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。Atlas是一个位于应用程序与MySQL之间中间件。在后端DB看来，Atlas相当于连接它的客户端，在前端应用看来，Atlas相当于一个DB。Atlas作为服务端与应用程序通讯，它实现了MySQL的客户端和服务端协议，同时作为客户端与MySQL通讯。

在高可用环境中，如果主库故障宕机，从库们无法SSH到主库，如果主从延时较大，可能就会导致数据丢失，因为从库被选举为新的主库后，会获取新主库与故障主库差异的Binlog日志，如果SSH无法连接，此时差异的Binlog就无法获取，就会导致数据丢失。因此我们可以搭建一个Binlog服务器，专门实时同步主库产生的Binlog日志文件，当主库故障宕机后，MHA直接从Binlog服务器中读取主库的Binlog日志。因此下面我们来配置当MHA故障切换完成后，就发送一个邮件告警，通知我们去维护MHA。

当主库发生故障，从库切换成主库后，程序是无感知的，程序并不知道谁成为了主库，程序还是会连接曾经的主库，就会导致平台访问异常，因此我们需要准备一个VIP漂移地址，当主库故障后，VIP就漂移到新的主库上，保证平台的高可用。第一次配置VIP地址，需要现在主库对应的网卡上配置好VIP地址，否则当故障出现时，VIP地址不能漂移到新的主库，并且还会报错，因为脚本要先删掉主库中的VIP，然后在新的主库上添加，如果一开始就不存在，肯定会报错。目前mysql-3成为了主库，VIP也漂移到了mysql-3的服务器中。

我们并没有在MHA中配置强制主库的参数，因此第一个算法不会生效，根据所有从库的信息来看，和主库的数据是一模一样的，不存在数据差异，因此第二个算法也不会生效，而在MHA的配置文件中，是根据主从从节点的顺序来书写的，mysql-1、mysql-2、mysql-3，根据第三个算法，那么当主库故障后，mysql-2这个节点的从库会提升为主库。mysql-2中的从库已经成为新的主库了，当查看slave的状态时，没有任何输出，就表示它是主库。故障的主库已经成功的修复完成，并且已经成为mysql-2新主库的从库。

MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton（现就职于Facebook公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。

半同步复制模式下，当从库的I/O线程接收到主库传来的Binlog日志后，写入到TCP/IP缓存区后，不会立即给主库的Dump线程返回ACK，而是要通过ack_sed插件监控，当传来的Binlog真正写入到Relaylog落盘之后，由ack_sed插件给主库的Dump线程发送一个ACK确认信息，主库的acl_receiver只有收到从库发来的ACK确认信息，才会进行新事物的提交工作。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。

在MySQL主从复制集群中，既可以对全库进行主从复制，也可以对数据库实例中的某个数据库进行主从复制，对单个库进行主从复制的场景我们称为过滤复制，只对指定的库实现主从复制机制。在db_1数据库中创建一个表，写入数据，验证是否会被复制到从库。过滤复制可以在主库层面实现，也可以在从库层面实现。db_1数据库只要有操作就会被复制到从库。再创建一个db_5数据库观察是否会同步。此时只会复制db_1数据库产生的操作。db_5数据库不会被复制过去。在从库的配置文件里操作。只复制db_1数据库。

在还有277秒就要将主库操作同步到从库时，我们发现了主库的误操作行为，为了防止将从库的数据也误删除，我们决定先停止掉从库的SQL线程，然后通过从库延时还未执行的主库传来的Binlog日志，恢复主库误删除的数据。延时从库指的是经过一段时间后，再执行主库传输的Binlog，主库传输的Binlog会写入到Relaylog中，我们要截取主库误删除操作之前的Binlog，通过Binlog去还原主库误删除的数据。在主库中执行下面的SQL，创建一个数据库，创建一个表，写入几条数据，然后删除数据库。

主库重新生成了新的Binlog日志，并且也产生了数据，我们不好确定从库故障时，主库的Binlog位置号，因此我们直接从154号开始同步，一定是最全的数据，重新配置主从同步信息，Binlog指对之后，从库的I/O状态正常。I/O状态目前处于No的状态，我们已经知道原因了，就是从库要请求主库的Binlog日志不对，我们调整一下请求的Binlog日志，然后指定Binlog标识位号为从库I/O线程故障时的标识位号，保证主从的数据同步。二进制日志，没有从库要请求的日志，因此I/O会报错。二进制日志，但是主库只有。

通过配置延时主库，可以降低数据误删除的风险，例如配置的从库延时时间为2小时，那么主库发生了新操作后，2小时之后才会同步到从库，如果2小时内有误删除的操作，可以通过从库还原数据。这个信息时，就表示主库一切正常，主库已经将全部的Binlog日志发送给了从库，等待新的Binlog产生，有几行该信息就表示有几个从库。MySQL主从复制集群的监控，其实主要是针对从库进行监控的，监控来源就是根据从库的状态值进行监控的。关于主库的信息时从master.info文件中读取的。通过以下目录查看从库的状态信息。

主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。主库出现问题，可以快速切换到从库提供服务。实现读写分离，降低主库的访问压力。可以在从库中执行备份，以避免备份期间影响主库服务。

当人员误删除操作时，误删除库的可能性比较小，但是误删除某张表的概率是非常大的，如果误删了某张表，我们也用全备数据去还原，那么是得不偿失的，此时就建议我们从XBK备份中找到某张表，然后只针对这张表进行数据还原。任何运维的工作，都分为技术性和非技术性，做好任何一项技术时，都要考虑一切困难发生的因素，减少失败的可能性，技术对于运维都不是难题，非技术性问题一定要好好琢磨。建议使用mysqldump备份低版本的数据，排除掉所有的系统库，然后将备份在高版本的数据库中还原，最后升级数据字典即可。

XBK增量备份的概念图如下，周日的时候进行全量备份，周一到周六进行增量备份，增量备份时并不是从全库备份处开始进行增量备份的，而是从前一天增量结束位置处，备份一天内的增量数据，相当于是基于上次的备份开始的增量备份。：整理备份的数据，无论是全量备份还是增量备份，都需要先整理备份的数据，将备份过程中产生的“已提交事务的数据”通过redo log写入到备份文件中，将“未提交事务的数据”通过undo log回滚。全量数据已经整理完毕了，下面将增量备份一个个的合并到全量备份中，首先将周一的增量备份合并到全量备份中。

XtraBackup是Percona公司开源的一款MySQL InnoDB（包括XtraDB，MyISAM）数据库备份工具，基于InnoDB的崩溃恢复功能，由于支持不锁表的在线物理热备而被广泛应用于生产环境。

这个参数，此参数会在备份文件中帮我们记录：从备份开始的时间算，Binlog中最近的一个GTID号、当前使用的Binlog日志名称、Binlog中事件的最近一个Position标识位号，有了这三个信息之后，找起点不再是难事，我们可以非常快速的指定我们要截取那些Binlog日志。（本次模拟过程中，没有将数据库所有文件删除，是因为Binlog也在这个路径，如果模拟数据库文件全部损坏，那么修复的时候就需要重新初始化数据库的，Binlog也会被覆盖，因此这里只模拟平台库数据库文件损坏，全部丢失。

首先想到的就是利用前天晚上的全库备份去还原被删除的数据库，对于0点到当前时间的数据时没有备份的，此时就需要Binlog日志中截取了，但是截取Binlog就非常棘手了，不管是基于时间的标识位号截取还是基于GTID号截取，终点都特别好找，但是起点非常的难找，我们做不到哪一天才是0点备份之后第一个产生的数据。最终恢复好的数据可能有缺失的现象。：开启快照备份的功能，当使用此参数时，会开启一个快照备份的功能，此时要备份的表就不会产生锁表的现象，在备份之前，针对要备份的表打一个快照，备份快照中的数据，相当于热备份。

MySQL的备份工具分为两类逻辑备份工具逻辑备份是基于SQL语句进行备份的，包括建表语句、数据的SQL语句等等。常用的MySQL逻辑备份工具就是MySQL自带的mysqldump以及mysqlbinlog两种，大多数生产环境中都是使用mysqldump和mysqlbinlog进行数据备份以及还原。物理备份工具物理备份是基于磁盘数据文件的备份。

通过-t参数分析前10个慢查询SQL，主要分析慢SQL在数据库范围中执行的次数（Count）和执行的时间（Time），通过列出的慢SQL语句经过分析后进行优化。慢查询日志并不是说这个日志有多慢，而是将数据库中所有执行比较慢是SQL语句记录在这个日志中，我们可以根据慢查询日志中记录的SQL，去分析和优化这个慢SQL。开启慢查询日志最好配置在MySQL的配置文件中，当然也可以在交互模式通过变量来开启，但是重启后会失效，建议配置在主配置文件中。慢查询日志默认情况下是没有开启的。

MySQL Binlog日志滚动指的就是产生一个新的Binlog日志，然后进行记录，因为如果都在一个Binlog中记录，查询是非常慢的，检索的效率也很低。通过mysqladmin刷新一个Binlog。手动刷新一个Binlog。

Binlog日志非常重要，但是占用的磁盘空间也很大，我们也需要定期的去清理二进制日志，在MySQL数据库中，提供了自动清理Binlog日志的参数，根据指定的天数，保留n天内的Binlog日志，也可以手动人为删除。

GTID的全称为全局事务标识符（global transaction identifiner），是MySQL5.6版本中引入的新特性，并且在MySQL5.7版本中进行了加强，在MySQL5.7版本中，GTID即使不开启也会有自动生成。GTID可以保证MySQL数据库中的每一个事务都有一个全局唯一的标识号，这个标识号在当前MySQL实例，甚至是MySQL主从复制集群中都会保证是全局唯一的标识号，从而保证数据的一致性。

根据需求我们得知要恢复误删除的这张表，那么在事件信息中找到创建该表的事件，这个事件的开始标识位就作为我们截取Binlog日志的开始标识位号，结束标识位更好确定了，在事件信息中找到删除表的事件，这个事件的开始标识位也就作为我们截取Binlog日志的结束标识位，开始标识位号是上一个事件的结束表示为号。注意：一定不要将删除表事件的结束标识位号作为截取Binlog日志的结束标识位号，如果截取Binlog日志的结束标识位号是删除表的结束标识位号，就会把删除表的操作在还原时又进行了一遍，数据就不能恢复了。

使用标识位Position号来截取Binlog日志中部分的数据内容时，最关键的部分就在于如何确定要截取数据的标识位号范围，也就是确定标识位的起点和终点部分，我们可以查看Binlog的事件信息，找到要恢复的数据属于哪一个事件，然后获取事件的开始标识位和结束标识位，此时也就能确定要恢复的数据在Binlog日志中的位置。根据特定的情况以及需求去恢复Binlog日志中的数据时，需要通过截取的方式，截取特定时间段产生的Binlog日志，然后进行数据恢复，或者截取指定的开始标识位到结束标识位之间的Binlog日志。

MySQL数据库中的Binlog二进制日志中，记录了所有的DDL数据库定义语句和DML数据操纵语句，但是在二进制日志中不包括DQL数据查询语句。Binlog二进制日志会记录数据的所有操作记录，几乎是实时的，当数据库发生故障导致数据丢失，并且也没有备份时，可以从Binlog日志中恢复数据库的数据。灾难时可恢复数据库中的数据。MySQL主从复制通过Binlog来同步数据。Binlog日志默认会保留30年内产生的二进制日志，并且每当重启MySQL后，就会向新的Binlog日志中写入数据。

前面是使用的yum的方式安装的MySQL数据库，在企业生产环境中大多数都用二进制方式安装。本次使用二进制方式搭建MySQL 5.7.36版本。