MySQL学习整理-码农进阶之路(四)

最新推荐文章于 2023-09-12 14:34:32 发布

长弓成zerozero

最新推荐文章于 2023-09-12 14:34:32 发布

阅读量477

点赞数 2

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/changgongcheng_yq/article/details/120865291

版权

数据库专栏收录该内容

22 篇文章 0 订阅

订阅专栏

五：分库分表

MySQL分库分表主要解决的问题是，当数据规模大到一定程度时，查询性能会急剧下降，甚至数据容量突破单机磁盘上限，不得不在多个机器节点上分散存储。然而过多的切分会使系统过于复杂难以维护，因此个人建议是能不切分就不切分，切分也优先按功能领域作垂直切分，仅对某些数据规模过大的表作水平切分。

5.1 基础概念及常见问题

常用架构：

M-S一主多从：主不能停机，从机过多会出现延迟

M-S-S级联复制：使用级联复制减轻主机的IO压力，但从机延迟更大了

M-M-S双主复制：M1负责写 M2负责同步从机，双主还可以搭建高可用

复制流程：[client]commit - [master]binlog- dump thread - [slave] - IO thread - SQL thread

垂直切分：按照功能模块，各个微服务使用自己的数据库。如商品库，订单库...

水平切分：数据库横向扩展，单表数据规模大，垂直切分后仍不能满足高性能存储要求[数据量级突破磁盘上限，比如10亿级查询很慢]，根据id取模、字段枚举、日期、一致性hash等方式，将一张表数据拆分到多张表存储。

分库分表常见问题：跨节点join、分布式事务、跨节点分页及排序

分库分表常见方案：

mysql自身支持了主从同步是读写分离的基础，使用HAProxy和keepalived实现服务的高可用。

mysql cluster：官方mysql集群，好像还不是很成熟，没有经历过大规模生产环境的验证

ShardingSphere：由JDBC Proxy Sidecar组成，加入了apache孵化，理念较新使用yml配置简单，发展较快；但是官方文档过时，网上资料少，入门比较难，需要团队有一定的研发实力[新技术嘛可能有bug，我曾花了一整个周末照着官网文档就为了实现一个简单的springboot主从复制项目，搞来搞去最后项目还是没起得来，官网提供的配置有问题]

mycat：经过生产验证的有丰富功能的产品，使用xml配置稍复杂。

5.2 主从复制

5.2.1 mysql安装

docker run --name mysql3306 -p 3306:3306 --privileged=true -ti 
-e MYSQL_ROOT_PASSWORD=1234 -e MYSQL_DATABASE=enjoy 
-e MYSQL_USER=user -e MYSQL_PASSWORD=1234 
-v /root/docker/mysql/3306/conf:/etc/mysql/conf.d 
-v /root/docker/mysql/3306/data/:/var/lib/mysql 
-v /root/docker/mysql/3306/logs/:/var/log/mysql 
-d mysql:5.7

5.2.2 主从复制配置my.cnf

master配置
    server-id=1013306   //我这里是机器号加端口号，可自行配置保证不重复。
    log-bin=mysql-bin   //开启复制功能
    auto_increment_increment=2 //步长 主主时，保证id不重复。一般为主机个数
    auto_increment_offset=1 //初始偏移量 m1为1 m2为2 
    lower_case_table_names=1 //忽略大小写
slave配置 
    server-id=1023306 
    log-bin=mysql-bin 
    auto-increment-increment=2 
    auto-increment-offset=2 
    lower_case_table_names=1

5.2.3 主从复制权限配置

1.在master mysql添加权限
    GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *.* TO 'user'@'%' IDENTIFIED BY '1234'; 
    FLUSH PRIVILEGES;   //刷新权限
    show master status; //查看master的二进制日志用于从机绑定主机,如果为空说明log-bin没开启，修改好配置文件后重启服务
    show processlist;   //查看进程信息
    show global variables like '%log%'; //查看二进制日志是否开启

2.在slave中设置master的信息，相当于slave绑定master
    change master to master_host='192.168.42.101',master_port=3306,master_user='root',
    master_password='1234',master_log_file='mysql-bin.000001',master_log_pos=154;
    start slave;        //开启slave，启动SQL和IO线程
    show slave status;  //查看slave的状态 slave_io slave_sql

3.查看日志 docker logs -f mysql3306

ps:必须两边数据一致 change...才会生效，可以使用冷备或者热备或者清空数据库

5.2.4 半同步复制

mysql主从复制有半同步复制和异步复制两种模式，当半同步启动时会自动使用半同步模式，半同步超时时会切为异步模式。

1.加载插件lib，所有节点都要配置 mysql窗口安装
    主库：install plugin rpl_semi_sync_master soname 'semisync_master.so';
    从库：install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; 
    show plugins; //查看插件安装情况：
    show global status like "%sync%"; //查询主库状态信息： 
    show global variables like '%log%'; //查询主库参数信息；可以手动改 set global xxx 如下面的配置修改
2.启动半同步：
    主库：
        set global rpl_semi_sync_master_enabled = {0|1};   //1：启用，0：禁止
        set global rpl_semi_sync_master_timeout = 10000;   //单位为ms 默认10s 半同步等待时间，超时自动切换为异步
    从库：set global rpl_semi_sync_slave_enabled = {0|1};  //1：启用，0：禁止
3.重启io_thread
    stop slave io_thread;
    start slave io_thread;

5.3 mycat

5.3.1 schema.xml

<mycat:schema xmlns:mycat="http://io.mycat/">
	<!--逻辑库，跟数据库的database的概念相同-->
	<schema name="enjoyDB" checkSQLschema="true" dataNode="localdn">
		<!--
		name:逻辑表的名称，名称必须唯一
		dataNode:值必须跟dataNode标签中的name对应，如果值过多可以用 dataNode="dn$0-99,cn$100-199"
		rule:分片规则配置，定义在rule.xml中，必须与tableRule中的name对应
		primaryKey：该逻辑表对应真实表的主键
		-->
		<table name="t_order" dataNode="localdn" autoIncrement="true" subTables="t_order$1-3" primaryKey="order_id" rule="mod-long">
		</table>
	</schema>

	<dataNode name="localdn" dataHost="localhost1" database="consult" />
	<!--
	maxCon:指定每个读写实例连接池的最大连接。
	minCon:指定每个读写实例连接池的最小连接，初始化连接池的大小
	balance:
        1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。
        2. balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。
        3. balance="2"，所有读操作都随机的在writeHost、readhost上分发。
        4. balance="3"，所有读请求随机的分发到writeHost对应的readhost执行，writerHost不负担读压力，注意balance=3只在1.4及其以后版本有，1.3没有。
	writeType:
	    负载均衡类型，目前的取值有3种：
        1. writeType="0", 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .
        2. writeType="1"，所有写操作都随机的发送到配置的writeHost。
        3. writeType="2"，没实现
	switchType:
        -1 表示不自动切换
       1 默认值，自动切换
       2 基于MySQL主从同步的状态决定是否切换
      心跳语句为 show slave status
       3 基于MySQL galary cluster的切换机制（适合集群）（1.4.1）
      心跳语句为 show status like ‘wsrep%’
	-->
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<!--heartbeat标签
		MYSQL可以使用select user()
		Oracle可以使用select 1 from dual
		-->
		<heartbeat>select user()</heartbeat>
		<connectionInitSql></connectionInitSql>
		<!--
		如果writeHost指定的后端数据库宕机，那么这个writeHost绑定的所有readHost都将不可用。另一方面，由于这个writeHost宕机系统会自动的检测到，并切换到备用的writeHost上去
		-->
		<writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
				   password="123456">
		</writeHost>
	</dataHost>
</mycat:schema>

5.3.2 rule.xml

    <tableRule name="mod-long">
		<rule>
			<columns>order_id</columns>
			<algorithm>mod-long</algorithm>
		</rule>
	</tableRule>
    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
		<property name="count">3</property>
        <property name="mapFile">autopartition-long.txt</property> //这个适用于配置属性多，自定义配置文件的情况
	</function>

//由自定义的类去实现各种分片规则，如mod、jumpConsistentHash

5.4 sharding-jdbc

application.properties

这个因版本不同配置也不同[spring-boot-starter-jdbc 2.2.2.RELEASE]，需要结合源码查看

//分表
spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://192.168.67.142:3400/consult?characterEncoding=utf-8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.67.143:3400/consult?characterEncoding=utf-8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456


spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=createTime
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=cn.enjoy.algorithm.MonthPreciseShardingAlgorithm
spring.shardingsphere.sharding.tables.t_order.key-generator.column=orderId
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

spring.shardingsphere.props.sql.show=true

//读写分离

spring.shardingsphere.datasource.names=master,slave0

spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://192.168.67.142:3400/consult
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://192.168.67.142:3401/consult
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=123456

spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0

spring.shardingsphere.props.sql.show=true

5.5 小结

就如同springMVC是基于servlet规范一样，mycat sharding-jdbc ORM框架以及transaction其实也是基于JDBC规范，主要就是通过代理获取DataSource的Connection，然后代理prepareStatement，完成属性设置后excute执行代理方法。其实就是不断的在jdbc的基础上套壳，使得sql的执行更智能化

长弓成zerozero

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
MySQL学习整理-码农进阶之路(四)

五：分库分表MySQL分库分表主要解决的问题是，当数据规模大到一定程度时，查询性能会急剧下降，甚至数据容量突破单机磁盘上限，不得不在多个机器节点上分散存储。然而过多的切分会使系统过于复杂难以维护，因此个人建议是能不切分就不切分，切分也优先按功能领域作垂直切分，仅对某些数据规模过大的表作水平切分。5.1 基础概念及常见问题常用架构： M-S一主多从：主不能停机，从机过多会出现延迟 M-S-S级联复制：使用级联复制减轻主机的IO压力，但从机延迟更大了 ...
复制链接

扫一扫

专栏目录