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

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

常用架构：

        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配置稍复杂。

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

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 

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...才会生效，可以使用冷备或者热备或者清空数据库

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;

	
	
		
		
	

	
	
	
		
		select user()
		
		
		
		
	

    
		
			order_id
			mod-long
		
	
    
		3
        autopartition-long.txt //这个适用于配置属性多，自定义配置文件的情况
	

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

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

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