8.集群

予早 2025-08-31 14:59:19
Categories： Tags：

数据库集群

主从复制（主从同步）

概念

主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，在从库根据日志重做从而使得从库和主库数据保持同步。要点有三：

类型

一主一从、一主两从、双主双从

一主一从

image-20230614232630254

一主两从

双主双从

主节点m1用于处理所有写请求，从节点s1和s2处理所有读请求，当m1宕机时，m2负责写请求，m1和m2互为备机

image-20230614232236911

原理

image-20230613222557890

步骤：

  1. 主库事务提交时，将数据变更记录在binlog
  2. 从库读取主库binlog写入到从库的中继日志relay log
  3. 从库重做relay log从而改变自身数据与主库保持一致

分库分表

分库分表的思想是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库性能问题，从而达到提升数据库性能的目的。

垂直拆分的核心时根据数据特点拆分，依据不同业务数据、冷热数据

综上所述，对于MySQL而言，一般是分库分表综合使用，垂直分库和垂直分表是自然而然发生的，垂直分库需要对外屏蔽分库细节，垂直分表无需额外技术支持，当数据复杂，就会将一个库中的多个表放在两个库中，将一个表解耦为多个表，水平分库暗含则在水平分表之中，故最主要的是对表的分片规则，水平分表需要实现分片规则且要对外屏蔽查询细节。

MongoDB实现了水平分表，垂直分库、垂直分表也需要手动操作。

MySQL需要额外的程序代码封装或者中间件封装才能实现分库分表功能。

垂直拆分

image-20220918155555887

水平拆分

image-20220918155632003

分片规则

范围分片

根据指定字段及其配置的范围与数据节点的映射决定分片位置

取模分片

根据指定的字段值与节点数量的取模运算结果决定分片位置

哈希分片

根据某个字段部分值或全部值进行哈希运算的结果决定分片位置，数字哈希、字符串哈希

枚举分片

根据配置的枚举值指定分片位置，适用于省份、性别、状态等

时间分片

对数据按照时间进行分片，按自然日、自然月、自然年

辅助工具，中间件

用于实现读写分离、分库分表

Cobar

阿里，停止维护

MyCat

http://www.mycat.org.cn/

Java语言开发，基于Cobar

DBLE

爱可生开源社区，基于MyCat

Mysql Router

MySQL官方中间件

ShardingSphere

https://shardingsphere.apache.org/

vites

Google

other

美团Atlas、360 Atlas

分库分表引入的复杂性

（1）透明路由
虽然数据分片解决了性能、可用性以及单点备份恢复等问题，但分布式的架构在获得收益的同时，也引入了新的问题。面对如此散乱的分片之后的数据，应用开发工程师和数据库管理员对数据库的操作变得异常繁重就是其中的重要挑战之一。 

    理想情况是用户只要按需求定义好分片规则，而在访问数据时不需要知道从哪个具体的物理库表中获取。这个数据路由的过程应该对用户尽量透明。

（2）跨实例关联查询
在单库未拆分之前，我们可以很方便地使用join操作关联多张表查询数据，但是经过分库分表后，关联表可能不在一个数据库实例中，如何使用join呢？通常有以下几种解决方案。

数据复制：将需要关联的表通过数据库提供的复制机制，整合到同一个实例中。
字段冗余：把需要关联的字段放入主表中，避免join操作。
数据抽象：通过ETL工具将数据汇总聚合，生成新表。
全局表：把一些基础表在每个数据库中都放一份。
应用层组装：将基础数据查出来（即所谓两次查询），通过应用程序计算组装。
（3）分布式事务
单数据库可以用本地事务，使用多数据库就只能通过分布式事务解决了。常用解决方案有两阶段提交（2PC）和柔性事务（BASE）等。

（4）排序、分页、函数计算问题
在使用SQL时order by、limit等关键字和聚合函数需要特殊处理。一般来说采用分片的思路，先在每个分片上执行相应的排序和函数，然后将各个分片的结果集进行汇总并再次计算，得到最终结果。

（5）分布式ID
数据库被切分后，不能再依赖数据库自身的自增主键生成机制，因为多实例之间不感知彼此的ID，会出现ID重复。常用的分布式ID解决方案有：UUID、基于数据库自增单独维护一张全局ID表、互斥号段模式、Redis单线程自增、雪花算法（Snowflake）等。

（6）多数据源
分库分表之后面临同时从多个数据库实例的库表中获取数据，一般的解决思路有客户端适配或代理层（中间件）适配。

一个良好的分库分表方案，它的数据应该是比较均匀地分散在各个库表中。如果设计不当，很容易遇到以下类似问题：

某个数据库实例中，部分表的数据很多，而其他表中的数据却寥寥无几，业务上的表现经常是延迟忽高忽低，飘忽不定。
数据库集群中，部分集群的磁盘使用增长特别块，而部分集群的磁盘增长却很缓慢，每个库的增长幅度相差很大。这种情况会给后续的扩容带来步调不一致，无法统一操作的问题。
可以定义分库分表最大数据偏斜率为：（数据量最大样本 - 数据量最小样本）/ 数据量最小样本。一般来说，最大数据偏斜率在5%以内是可以接受的，如下图所示。

————————————————
版权声明：本文为CSDN博主「wzy0623」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/wzy0623/article/details/124948877

MyCat

http://dl.mycat.org.cn/1.6.7.6/

http://dl.mycat.org.cn/

https://github.com/MyCATApache/

安装

MyCat区分大小写

MyCat中都是逻辑表，没有对应物理表是查询不到的

目录结构

目录 描述
bin 可执行文件，管理mycat
conf 配置文件
lib 依赖JAR
catlet
logs 日志文件
version.txt 版本记录

原理

image-20230613233944008

配置

server.xml

配置MyCat服务端参数

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
    - you may not use this file except in compliance with the License. - You 
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
    - - Unless required by applicable law or agreed to in writing, software - 
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
    License for the specific language governing permissions and - limitations 
    under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
    <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
    <property name="ignoreUnknownCommand">0</property><!-- 0遇上没有实现的报文(Unknown command:),就会报错、1为忽略该报文，返回ok报文。
    在某些mysql客户端存在客户端已经登录的时候还会继续发送登录报文,mycat会报错,该设置可以绕过这个错误-->
    <property name="useHandshakeV10">1</property>
    <property name="removeGraveAccent">1</property>
    <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
    <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
        <property name="sqlExecuteTimeout">300</property>  <!-- SQL 执行超时 单位:秒-->
        <property name="sequnceHandlerType">1</property>
        <!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
        INSERT INTO `travelrecord` (`id`,user_id) VALUES ('next value for MYCATSEQ_GLOBAL',"xxx");
        -->
        <!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况-->
        <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
    <property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
    <property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
    <!-- <property name="processorBufferChunk">40960</property> -->
    <!-- 
    <property name="processors">1</property> 
    <property name="processorExecutor">32</property> 
     -->
        <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
        <property name="processorBufferPoolType">0</property>
        <!--默认是65535 64K 用于sql解析时最大文本长度 -->
        <!--<property name="maxStringLiteralLength">65535</property>-->
        <!--<property name="sequnceHandlerType">0</property>-->
        <!--<property name="backSocketNoDelay">1</property>-->
        <!--<property name="frontSocketNoDelay">1</property>-->
        <!--<property name="processorExecutor">16</property>-->
        <!--
            <property name="serverPort">8066</property> <property name="managerPort">9066</property> 
            <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
            <property name="dataNodeIdleCheckPeriod">300000</property> 5 * 60 * 1000L; //连接空闲检查
            <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
        <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
        <property name="handleDistributedTransactions">0</property>
        
            <!--
            off heap for merge/order/group/limit      1开启   0关闭
        -->
        <property name="useOffHeapForMerge">0</property>

        <!--
            单位为m
        -->
        <property name="memoryPageSize">64k</property>

        <!--
            单位为k
        -->
        <property name="spillsFileBufferSize">1k</property>

        <property name="useStreamOutput">0</property>

        <!--
            单位为m
        -->
        <property name="systemReserveMemorySize">384m</property>


        <!--是否采用zookeeper协调切换  -->
        <property name="useZKSwitch">false</property>

        <!-- XA Recovery Log日志路径 -->
        <!--<property name="XARecoveryLogBaseDir">./</property>-->

        <!-- XA Recovery Log日志名称 -->
        <!--<property name="XARecoveryLogBaseName">tmlog</property>-->
        <!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
        <property name="strictTxIsolation">false</property>
        
        <property name="useZKSwitch">true</property>
        <!--如果为0的话,涉及多个DataNode的catlet任务不会跨线程执行-->
        <property name="parallExecute">0</property>
    </system>
    
    <!-- 全局SQL防火墙设置 -->
    <!--白名单可以使用通配符%或着*-->
    <!--例如<host host="127.0.0.*" user="root"/>-->
    <!--例如<host host="127.0.*" user="root"/>-->
    <!--例如<host host="127.*" user="root"/>-->
    <!--例如<host host="1*7.*" user="root"/>-->
    <!--这些配置情况下对于127.0.0.1都能以root账户登录-->
    <!--
    <firewall>
       <whitehost>
          <host host="1*7.0.0.*" user="root"/>
       </whitehost>
       <blacklist check="false">
       </blacklist>
    </firewall>
    -->

    <user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">TESTDB</property>
        <property name="defaultSchema">TESTDB</property>
        <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->
        
        <!-- 表级 DML 权限设置 -->
        <!-- 		
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>		
         -->
        <!-- 1111分别对应增改查删权限 -->
    </user>

    <user name="user">
        <property name="password">user</property>
        <property name="schemas">TESTDB</property>
        <property name="readOnly">true</property>
        <property name="defaultSchema">TESTDB</property>
    </user>

</mycat:server>

system中定义系统参数

user定义mycat用户及其权限

schema.xml

配置逻辑库、逻辑表、分片规则、分片节点、数据源，主要包含schema、datanode、datahost标签

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
        <!-- auto sharding by id (long) -->
        <!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
        <table name="travelrecord,address" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" splitTableNames ="true"/>
        <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
            /> -->
    </schema>
    <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
        /> -->
    <dataNode name="dn1" dataHost="localhost1" database="db1" />
    <dataNode name="dn2" dataHost="localhost1" database="db2" />
    <dataNode name="dn3" dataHost="localhost1" database="db3" />
    <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
     <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
    <dataNode	name="jdbc_dn2" dataHost="jdbchost" database="db2" />
    <dataNode name="jdbc_dn3" 	dataHost="jdbchost" database="db3" /> -->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="localhost:3306" user="root"
                   password="123456">
        </writeHost>
        <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
    </dataHost>
    <!--
        <dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
        <heartbeat> 		</heartbeat>
         <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng" 	password="jifeng"></writeHost>
         </dataHost>

      <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0" 	dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>
        <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
        <writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base" 	password="123456" > </writeHost> </dataHost>

        <dataHost name="jdbchost" maxCon="1000" 	minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">
        <heartbeat>select 	user()</heartbeat>
        <writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>

        <dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">
        <heartbeat> </heartbeat>
         <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng" 	password="jifeng"></writeHost> </dataHost> -->

    <!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"
        dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"
        url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>
        </dataHost> -->
</mycat:schema>

image-20230614213745092

schema
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
    <table name="travelrecord,address" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" splitTableNames ="true"/>
</schema>

schema用于定义逻辑库，name，逻辑库库名，checkSQLschema，sql语句操作时指定逻辑库，执行时是否去除，true去除，false不去除，sqlMaxLimit，若未指定limit，则默认最多数据条数取该值

table
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
    <table name="travelrecord,address" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" splitTableNames ="true"/>
</schema>

table用于定义逻辑表，name，逻辑表名，dataNode，逻辑表所属dataNode，多个dataNode使用半角逗号分隔，rule，分片规则，细节定义在rule.xml，primaryKey，逻辑表主键，type，逻辑表类型，分为普通表和全局表（冗余表），默认为普通表，global为全局表

dataNode
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />

dataNode用于定义逻辑数据节点，一个dataNode就是一个逻辑数据分片，name，数据节点名称，dataHost，数据库实例主机名称，database，分片所属数据库

dataHost
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="localhost:3306" user="root" password="123456"/>
    <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/>
</dataHost>

dataHost定义数据库实例，每一个dataHost映射到若干物理节点，name，逻辑数据节点名称，maxCon/minCon，最大连接数/最小连接数，balance，负载均衡策略。0,1,2,3，writeType，写操作类型，0，写操作转发到第一个writeHost，第一个宕机转发到第二个，1，随机转发写操作，dbDriver，数据库驱动，支持native和JDBC

rule.xml

tableRule定义表分片规则，function指定实现函数

image-20230614215523860

范围分片

根据指定字段及其配置的范围与数据节点的对应情况决定数据分片位置

image-20230614220830307

image-20230614220845937

取模分片

根据指定字段值与节点数量求模运算决定数据分片位置

image-20230614220936402

image-20230614220959802

枚举分片

根据配置的枚举值指定数据分片位置，适用于按照省份、性别、状态等有限枚举拆分数据

image-20230614221133775

image-20230614221142544

哈希分片

字符串哈希分片，根据截取的指定位置子字符串进行hash运算后的结果决定数据分片位置

image-20230614221626806

image-20230614221726533

日期分片

自然日分片

image-20230614221821222

自然月分片

image-20230614221913351

读写分离

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="localhost:3306" user="root" password="123456">
        <readHost host="hostS1" url="localhost:3307" user="root" password="123456"/>
    </writeHost>
    <writeHost host="hostM2" url="localhost:3316" user="root" password="123456">
        <readHost host="hostS2" url="localhost:3317" user="root" password="123456"/>
    </writeHost>
</dataHost>

由dataHost中的blance参数配置

wirteType

switchType

垂直分库

对商城业务进行垂直分库

image-20230615075617437

将原有的表垂直拆分到三个库中。每个数据库实例均有shopping库

image-20230615075720517

server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server
    xmlns:mycat="http://io.mycat/">
    <system>
        <property name="nonePasswordLogin">0</property>
        <property name="ignoreUnknownCommand">0</property>
        <property name="useHandshakeV10">1</property>
        <property name="removeGraveAccent">1</property>
        <property name="useSqlStat">0</property>
        <property name="useGlobleTableCheck">0</property>
        <property name="sqlExecuteTimeout">300</property>
        <property name="sequenceHandlerType">1</property>
        <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
        <property name="subqueryRelationshipCheck">false</property>
        <property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
        <property name="processorBufferPoolType">0</property>
        <property name="handleDistributedTransactions">0</property>
        <property name="useOffHeapForMerge">0</property>
        <property name="memoryPageSize">64k</property>
        <property name="spillsFileBufferSize">1k</property>
        <property name="useStreamOutput">0</property>
        <property name="systemReserveMemorySize">384m</property>
        <property name="useZKSwitch">false</property>
        <property name="strictTxIsolation">true</property>
        <property name="parallExecute">0</property>
        <property name="serverBacklog">2048</property>
    </system>
    <user name="root" defaultAccount="true">
        <property name="password">mycaT123456_</property>
        <property name="schemas">shopping</property>
    </user>
    <user name="user">
        <property name="password">mycaT123456_</property>
        <property name="schemas">SHOPPING</property>
        <property name="readOnly">true</property>
    </user>
</mycat:server>

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="shopping" checkSQLschema="true" sqlMaxLimit="100">
        <table name="tb_goods_base" dataNode="dn1" primaryKey="id" />
        <table name="tb_goods_brand" dataNode="dn1" primaryKey="id" />
        <table name="tb_goods_cat" dataNode="dn1" primaryKey="id" />
        <table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id" />
        <table name="tb_goods_item" dataNode="dn1" primaryKey="id" />
        <table name="tb_order_item" dataNode="dn2" primaryKey="id" />
        <table name="tb_order_master" dataNode="dn2" primaryKey="order_id" />
        <table name="tb_order_pay_log" dataNode="dn2" primaryKey="out_trade_no" />
        <table name="tb_user" dataNode="dn3" primaryKey="id" />
        <table name="tb_user_address" dataNode="dn3" primaryKey="id" />
        <table name="tb_areas_provinces" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
        <table name="tb_areas_city" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
        <table name="tb_areas_region" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
        <table name="tb_log" dataNode="dn1,dn2,dn3" primaryKey="id" rule="mod-long" />
    </schema>
    <dataNode name="dn1" dataHost="dhost1" database="shopping" />
    <dataNode name="dn2" dataHost="dhost2" database="shopping" />
    <dataNode name="dn3" dataHost="dhost3" database="shopping" />
    <!-- 一个dataNode对应一个database，d-->
    <dataHost name="dhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://192.168.0.4:3307?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;allowPublicKeyRetrieval=true" user="root" password="mysqL123456_" />
    </dataHost>
    <dataHost name="dhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://192.168.0.4:3308?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;allowPublicKeyRetrieval=true" user="root" password="mysqL123456_" />
    </dataHost>
    <dataHost name="dhost3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://192.168.0.4:3309?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;allowPublicKeyRetrieval=true" user="root" password="mysqL123456_" />
    </dataHost>
</mycat:schema>

管理

管理命令

mycat默认开通数据访问端口和数据库管理端口，可在server.xml配置

mysql -h 192.168.1.10 -p 9066 -uroot -p123456
show @@help			查看帮助文档
show @@version		查看mycat版本
show @@database		查看数据源
show @@datanode		查看datanode
show @@threadpool	查看mycat线程池信息
show @@sql			查看执行的sql
show @@sql.sum		查看执行的sql统计
reload @@config		重载配置文件

MyCat-eye

用于监控mycat或者mycat所在服务器的CPU、内存、网络、磁盘等的web服务，仅限于Linux系统

Apache ShardingSphere

image-20230615224154510

image-20230615224411170

image-20230615224546763

image-20230615224838143

image-20230615225014362

image-20230615225403977

image-20230616080056623

事务和关联查询要在一台服务器，就是要求都有

image-20230616190507851

image-20230616191242004

shardingsphere-proxy

image-20230616201741682

image-20230616203716445

image-20230619222309504

安装

mkdir /opt/shardingsphere-proxy

wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.11/mysql-connector-java-8.0.11.jar

主从集群

二进制安装集群，一主两从或双主双从等

节点1

节点目录

mkdir -p ./mysql-cluster/master1

配置文件my.cnf

[mysqld]
port=3307
basedir=/usr/local/mysql
datadir=/usr/local/mysql/mysql-cluster/master1/data

socket=/usr/local/mysql/mysql-cluster/master1/mysqld.sock
pid-file=/usr/local/mysql/mysql-cluster/master1/mysqld.pid

log_error=/usr/local/mysql/mysql-cluster/master1/log/error.log
log_bin=/usr/local/mysql/mysql-cluster/master1/log/bin_log

lower_case_table_names = 1

初始化

/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/mysql-cluster/master1/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/usr/local/mysql/mysql-cluster/master1/data --user=root &

启动

/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/mysql-cluster/master1/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/mysql-cluster/master1/data --user=root &

注：上述defaults-file一定要放在前面，否则会使用默认配置

登陆并修改密码

/usr/local/mysql/bin/mysql -uroot -P 3307 -S /usr/local/mysql/mysql-cluster/master1/mysqld.sock -p

ALTER USER USER() IDENTIFIED BY 'mysqL123456_';

关闭

/usr/local/mysql/bin/mysqladmin -h127.0.0.1 -uroot -P3307 -p shutdown

节点2

节点目录

mkdir -p ./mysql-cluster/slave1
mkdir -p ./mysql-cluster/slave1/data
mkdir -p ./mysql-cluster/slave1/log

配置文件my.cnf

vi ./mysql-cluster/slave1/my.cnf

[mysqld]
port=3308
basedir=/usr/local/mysql
datadir=/usr/local/mysql/mysql-cluster/slave1/data

socket=/usr/local/mysql/mysql-cluster/slave1/mysqld.sock
pid-file=/usr/local/mysql/mysql-cluster/slave1/mysqld.pid

log_error=/usr/local/mysql/mysql-cluster/slave1/log/error.log
log_bin=/usr/local/mysql/mysql-cluster/slave1/log/bin_log

lower_case_table_names = 1

初始化

/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/mysql-cluster/slave1/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/usr/local/mysql/mysql-cluster/slave1/data --user=root 

cat /usr/local/mysql/mysql-cluster/slave1/log/error.log
z14cBoxntZ.%

启动

/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/mysql-cluster/slave1/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/mysql-cluster/slave1/data --user=root &

注：上述defaults-file一定要放在前面，否则会使用默认配置

登陆并修改密码

/usr/local/mysql/bin/mysql -uroot -P 3308 -S /usr/local/mysql/mysql-cluster/slave1/mysqld.sock -p

ALTER USER USER() IDENTIFIED BY 'mysqL123456_';

关闭

/usr/local/mysql/bin/mysqladmin -h127.0.0.1 -uroot -P3308 -p shutdown

节点3

节点目录

mkdir -p ./mysql-cluster/slave2/data
mkdir -p ./mysql-cluster/slave2/log

配置文件my.cnf

vi ./mysql-cluster/slave2/my.cnf

[mysqld]
port=3309
basedir=/usr/local/mysql
datadir=/usr/local/mysql/mysql-cluster/slave2/data

socket=/usr/local/mysql/mysql-cluster/slave2/mysqld.sock
pid-file=/usr/local/mysql/mysql-cluster/slave2/mysqld.pid

log_error=/usr/local/mysql/mysql-cluster/slave2/log/error.log
log_bin=/usr/local/mysql/mysql-cluster/slave2/log/bin_log

lower_case_table_names = 1

初始化

/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/mysql-cluster/slave2/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/usr/local/mysql/mysql-cluster/slave2/data --user=root

cat /usr/local/mysql/mysql-cluster/slave2/log/error.log
2:VrlZAk8zjs

启动

/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/mysql-cluster/slave2/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/mysql-cluster/slave2/data --user=root & echo 'Done'

注：上述defaults-file一定要放在前面，否则会使用默认配置

# 重启
/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/mysql-cluster/slave2/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/mysql-cluster/slave2/data restart

登陆并修改密码

/usr/local/mysql/bin/mysql -uroot -P 3309 -S /usr/local/mysql/mysql-cluster/slave2/mysqld.sock -p

ALTER USER USER() IDENTIFIED BY 'mysqL123456_';

关闭

/usr/local/mysql/bin/mysqladmin -h127.0.0.1 -uroot -P3309 -p shutdown

删库

rm -rf /usr/local/mysql/mysql-cluster/master1/data/*
rm -rf /usr/local/mysql/mysql-cluster/master1/log/*
rm -rf /usr/local/mysql/mysql-cluster/slave1/data/*
rm -rf /usr/local/mysql/mysql-cluster/slave1/log/*
rm -rf /usr/local/mysql/mysql-cluster/slave2/data/*
rm -rf /usr/local/mysql/mysql-cluster/slave2/log/*

主从复制配置

主从复制配置过程：
主节点：

启用二进制日志。
为当前节点设置一个全局唯一的server_id。
创建有复制权限的用户账号 REPLIACTION SLAVE ,REPLIATION CLIENT。

从节点：

启动中继日志。
为当前节点设置一个全局唯一的server_id。
使用有复制权限的用户账号连接至主节点，并启动复制线程。

[mysqld]
server-id=1
port=3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/singleton/data

socket=/usr/local/mysql/singleton/mysqld.sock
pid-file=/usr/local/mysql/singleton/mysqld.pid

log_error=/usr/local/mysql/singleton/log/error.log
log_bin=/usr/local/mysql/singleton/log/bin_log

lower_case_table_names = 1

[mysqld]
server-id=2
port=3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/singleton/data

socket=/usr/local/mysql/singleton/mysqld.sock
pid-file=/usr/local/mysql/singleton/mysqld.pid

log_error=/usr/local/mysql/singleton/log/error.log
log_bin=/usr/local/mysql/singleton/log/bin_log
relay-log=/usr/local/mysql/singleton/log/relay-log
relay-log-index=/usr/local/mysql/singleton/log/relay-log.index

lower_case_table_names = 1
log-slave-updates

以双主双从为例配置主从复制

image-20230615211342153

m1配置

# /etc/my.cnf

# 服务id，集群环境唯一，取值范围：1~2^32-1，默认为1
server-id=1

# 指定同步的数据库
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03

# 作为从库时有写操作也要更新二进制日志
log-slave-updates

双主配置

# 创建用户replication
create user 'replicat_user'@'%' identified with mysql_native_password by 'mYsql123456_';

# 为用户分配主从复制权限
grant replication slave on *.* to 'replicat_user'@'%';

use mysql;
select * from user where user = 'replicat_user';

双从配置

# 配置关联主库，s1对应m1，s2对应m2
change master to MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='xxx', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS=xxx;

change master to MASTER_HOST='192.168.1.105', MASTER_USER='replicat_user', MASTER_PASSWORD='mYsql123456_', MASTER_LOG_FILE='bin_log.000004', MASTER_LOG_POS=670;

show binary logs;
# 注意：主节点的二进制文件一定要是二进制列表中的最后一个二进制文件。

查看主库和从库状态

start slave;
stop slave;
start master???
show master status\G;
show slave status\G;
flush privileges;

show global variables like '%log%';
show global variables like '%server%';

读写分离配置

需要借助MyCat等中间件

分片集群

三分片，水平拆分、垂直拆分

主从分片集群

三分片，每个分片是一个一主一从

create database if not exists shopping charset utf8mb4 collate utf8mb4_general_ci;

source shopping-table.sql;
source shopping-insert.sql;

/usr/local/mysql/bin/mysql -h 127.0.0.1 -uroot -P8066 -p -A

use mysql;
update user set host='%' where user ='root';
flush privileges;
# 是因为上述mycat配置中使用了私有ip访问，不允许使用私有ip网卡来的连接f

Public Key Retrieval is not allowed

https://blog.csdn.net/ly2708169628/article/details/117791091

java.sql.SQLException: null, message from server: “Host ‘xxx’ is not allowed to connect

https://blog.csdn.net/m0_46267375/article/details/116330364

MySQL库名表名大小写问题

https://cloud.tencent.com/developer/article/1842127

my.cnf添加配置lower_case_table_names = 1

某个版本之前可以初始化后更改该设置，某个版本之后就只能初始化时设置

image-20220918215457521

20.插件
6.SQL语句