mysql主从架构及分库分表

day4125
645 阅读8分钟

微信入群一块学习技术：Day9884125

主从架构

为什么要主从架构

1、如果主服务器出现问题，额可以快速切换到从服务器提供得服务
2、可以在从服务器上执行查询操作，降低主服务器得访问压力
3、可以在从服务器上执行备份，以避免备份期间影响主服务器得服务

主从方案

1、一主一从
2、一主多从
3、多主一从：集团汇总子公司的财务数据。
4、一主多级从
5、双主：相互读写复制
6、环形主从（复制）
7、多主多从

实现一主一从

其中mysql的版本是5.7,服务器是centos7
配置主节点

配置主服务器

查看mysql的启动配置文件里面

cat /etc/my.cnf

数据同步支持两种方式，一种是基于binlog，一种是基于事务的（GITD,底层也是基于bin-log）。

# 开启binlog，binlog生成的目录，每重启一次mysql都会生成一个binlog文件
log-bin=/usr/local/mysql/data/binlog/mysql -bin
#服务器id，默认是1，服务器id必须唯一
server.id=1
#每次执行写入就与硬盘同步，可能会影响性能，这就需要性能和安全性的权衡
sync-binlog=1
#需要同步的二进制数据库名
binlog-do-db=shop_ds_master
binlog-do-db=shop_ds_master1
#只保留七天的二进制日志，以防磁盘被日志占满
expire-logs-days = 7
#不备份的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=information_schema2
binlog-ignore-db=sys

#登录mysql
mysql -uroot -proot
#看看主服务器的节点
show master status;

查看主服务器的mysql节点
show master status; image.png

然后创建复制账户

   在Master的数据库中建立一个备份账户(user=duay,pwd=root)，每个slave使用标准的mysql用户名和密码链接master，进行复制操作的用户授予replication slave权限。

#创建用户
create user 'duay'@'192.168.%.%' IDENTIFIED BY 'root';
#授权
grant replication slave,replication client on "." to duay@'192.168.%.%' identified by 'root';
#刷新权限
flush privileges;

配置从服务器

  对slave进行配置，打开中继日志，指定唯一的servr ID，设置只读权限，在配置文件加入如下值。

server -id=2
#开启从服务器二进制日志 从服务器上没有子节点时，binlog可以关闭
log-bin=/usr/local/mysql/data/binlog/mysql -bin
#打开mysql日志，日志格式为二进制
relay_log=/usr/local/mysql/data/binlog/mysql-relay-bin
#如果master库名与salve库名不同，使用以下配置【需要做映射】
replicate-rewrite-db=shop_ds_master -> shop_ds_slave
replicate-rewrite-db=shop_ds_master1 -> shop_ds_slave1
#设置制度权限
read_only = 1
#使得更新的数据写进二进制日志中
log_slave_updates = 1

数据同步方式

主从复制：

延时问题，有哪些主从复制方式。
1、同步复制
   主服务器写一条数据完了以后，写入主服务器binlog。然后把这条数据写入到从服务器的中继日志，中继日志整个执行完毕以后，然后给主服务器一个通知，主服务器进行事务提交(commit)。
  这样没有延时，可以保证高同步，但是性能不高
2、异步复制：mysql默认使用异步复制
   主服务器写一条数据完了以后，写入主服务器binlog，然后主服务器进行事务提交。主服务器写入binlog之后，dump将数据写入从服务器的中继日志，而dump有没有写入中继日志，主服务器不管。
   异步复制性能比较高，但是会丢数据，数据会有延迟。
3、半同步复制
   假如一主多从，确保有一个做同步就可以了。半同步有两种，一种时丢数据的，一种是不丢数据的。
   当主数据库写入从数据库的中继日志后，会给主服务器发一个ACK，然后主服务器进行事务提交。当由于网络等其他原因，没有收到ACK，服务器会认为网络比较差，将其降级为异步复制，当后边10秒内就可以同步好之后，就又可以切回半同步。

主从复制原理

   当主服务器数据写入binlog里面时，会有一个线程dump，让它把更新的binlog写到中继服务器里面。中继服务器里面有一个io线程，专门读取dump推过来的binlog，然后再落到从服务器库里面去。 image.png

高可用架构方案总结

MMM方案会出现脑裂，故现在大多使用MHA

MHA方案

  MHA服务，有两种角色，MHA Manager（管理节点）和MHA Node（数据节点），在MySQL故障切换过程中，MHA能做到在0-30秒之内自动完成数据库的故障切换操作，目前MHA主要支持一主多从的架构，需要搭建MHA要求一个复制集群中必须最少有三台数据库服务器。他也可能出现脑裂，但是可以规避。为了防止脑裂，在网络抖动时候，vip在迁移那一刻会关掉原来老的vip主节点。

分库分表

什么是分库分表

  将一个表拆分多张表（库内分表与分库分表）

为什么需要分库分表

1、微服务：基于业务，需要分拆表
2、读写分离
3、建索引，优化查询
4、换数据库oracle，sybase，db2
5、提升性能，任然没有办法解决
能不分尽量不分，不要过度拆分

有哪些拆分方式

横向拆分

  数据量庞大的表,可以拆分成三张表结构一摸一样的表。
  表可以分区：可以解决数据量大的问题，高并发的场景，磁盘读写能力是有限的，这时候就解决不了。
表主键怎么定义？

表怎么路由

纵向拆分

  拆库：根据业务拆，

拆分后产生的问题

1、扩库join
2、主键会产生重复问题
3、做分页查询
4、关联查询
5、统计、去重、求和

分库分表的组件

shardingsphere（京东数科）推荐使用，apache孵化
Mycat（阿里）

如何保证缓存和db的强一致

  将数据库的binlog同步中间件MQ（binlog是二进制数据，需要转化一下MQ才能使用）持久化，然后MQ再把数据推送给redis缓存。

ShardingSphere

  ShardingSphere定义为关系型数据库中间件。

功能

image.png

核心三套件

image.png

Sharding-JDBC

  客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

  • 适用于任何基于JDBC的ORM框架：JPA、Hbernate、mybatis、springJDBC、Template或直接使用JDBC
  • 支持任何第三方的数据库连接池：
  • 支持任意实现JDBC规范的数据库，支持mysql、oracle、sqlServer、PostgreSQL等遵循sql92标准的数据库

Sharding-Proxy

透明化的数据库代理端，兼容所有mysql/postgreSQL协议的访问客户端

  • 向应用程序完全透明，可直接当作mysql/postgreSQL使用
  • 适用于任何兼容mysql/postgreSQL协议的客户端

ShardingSphere的使用

引入依赖

<!-- shardings-jdbc依赖 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RCZ</version>
</dependency>
<!-- mybatis依赖 -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.0.1</version>
    <exclusions>
        <exclusion>
            <artifactId>spring-boot-starter</artifactId>
            <groupId>org.springframework.boot</groupId>
        </exclusion>
    </exclusions>
</dependency>
<!-- mysql驱动依赖 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.48</version>
</dependency>

sharding-jdbc配置多数据源代码示例

/**
* sharding-jdbc配置多数据进行分库分表代码示例
* 注意：该类要和application启动类在一个包下
*/
public class ShardingsphereSourceDemo {

    /*public static void main(String[] args) throws SQLException {
        // 配置真实数据源  2代表两个数据库的dataSource
        Map<String, DataSource> dataSourceMap = new ConcurrentHashMap<>(2);

        // 配置第一个数据源
        HikariDataSource dataSource0 = new HikariDataSource();
        dataSource0.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource0.setJdbcUrl("jdbc:mysql://192.168.241.198:3306/shop_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8");
        dataSource0.setUsername("root");
        dataSource0.setPassword("root");
        dataSourceMap.put("ds0", dataSource0);


        // 配置第二个数据源
        HikariDataSource dataSource1 = new HikariDataSource();
        dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource1.setJdbcUrl("jdbc:mysql://192.168.241.198:3306/shop_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8");
        dataSource1.setUsername("root");
        dataSource1.setPassword("root");
        dataSourceMap.put("ds1", dataSource1);

        *//****************分片表组装************//*
        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
        shardingRuleConfig.getTableRuleConfigs().add(getOrderItemTableRuleConfiguration());
        //绑定表
        shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item");

        //****************分片具体规则************//
        //datanode为数据分片最小单元
        // 配置分库策略
        //我们采用数据库的分片是用user_id user_id %2 主要定位数据库库
        //shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds$->{user_id % 2}"));
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(
                new HintShardingStrategyConfiguration(new HintShardingAlgorithm<Integer>() {
                    @Override
                    public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<Integer> shardingValue) {
                        List<String> shardingList = new LinkedList<>();
                        for (String each : availableTargetNames){
                            for (Integer val : shardingValue.getValues()){
                                if(each.endsWith(val + "")){
                                    shardingList.add(each);
                                }
                                log.info("availableTargetNames:"+each+":shardingValue:"+val);
                            }
                        }
                        return shardingList;
                    }
                }));

        // 配置分表策略 对表分片是采用我们order_id
        shardingRuleConfig.setDefaultTableShardingStrategyConfig(
                new StandardShardingStrategyConfiguration("order_id",
                        new PreciseShardingAlgorithm<Long>() {
            @Override
            public String doSharding(Collection<String> collection, final PreciseShardingValue<Long> preciseShardingValue) {
                for (String each : collection) {
                    if (each.endsWith(preciseShardingValue.getValue() % 2 + "")) {//这句话会产生什么？只会产生偶数的订单
                        return each;
                    }
                }
                throw new UnsupportedOperationException();
            }
        }));


        // 获取数据源对象
        Properties properties = new Properties();
        properties.setProperty("sql.show", "true");
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, properties);

        //-------------测试部分-----------------//
        ShardingsphereSourceDemo test = new ShardingsphereSourceDemo();
        //test.drop(dataSource);
        //test.create(dataSource);
        //插入数据
        //test.insertData(dataSource);
        //test.selectRange(dataSource);
        test.selectPageList(dataSource);
    }

    *//**
     * t_order分表规则配置,主键采用雪花算法
     *//*
    private static TableRuleConfiguration getOrderTableRuleConfiguration() {
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order",
                "ds${0..1}.t_order_${[0, 1]}");
        orderTableRuleConfig.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE"
                , "order_id",getProps()));
        return orderTableRuleConfig;
    }

    *//**
     * t_order_item分表规则配置,主键采用雪花算法
     *//*
    private static TableRuleConfiguration getOrderItemTableRuleConfiguration() {
        TableRuleConfiguration orderItemTableRuleConfig = new TableRuleConfiguration("t_order_item",
                "ds${0..1}.t_order_item_${[0, 1]}");
        orderItemTableRuleConfig.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE"
                , "order_id",getProps()));
        //uuid,snowflake,
        return orderItemTableRuleConfig;
    }

    //雪花算法，需要有机器序号，手动配置序号
    private static Properties getProps(){
        Properties props = new Properties();
        props.setProperty("worker.id", "123");
        return props;
    }

    *//***
     * 用户为中心10 和11 偶数和奇数
     * @param dataSource
     * @throws SQLException
     *//*
    public void insertData(DataSource dataSource) throws SQLException {
        for (int i = 1; i < 10; i++) {
            long orderId = executeAndGetGeneratedKey(dataSource, "INSERT INTO t_order (user_id, address_id, status) VALUES (10,10, 'INIT')");
            execute(dataSource, String.format("INSERT INTO t_order_item (order_id, user_id, status) VALUES (%d, 10, 'INIT')", orderId));
            orderId = executeAndGetGeneratedKey(dataSource, "INSERT INTO t_order (user_id, address_id, status) VALUES (11,11, 'INIT')");
            execute(dataSource, String.format("INSERT INTO t_order_item (order_id, user_id, status) VALUES (%d, 11, 'INIT')", orderId));
        }
    }

    public void selectRange(DataSource dataSource){
        try {
            //强制路由
            HintManager hintManager = HintManager.getInstance();
            Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement();
            hintManager.setDatabaseShardingValue(0);
            ResultSet result = statement.executeQuery(String.format("SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (%d, %d)",405466009580318721l,405466010490482688l));
            while(result.next()){
                System.out.print("order_item_id:" + result.getLong(1) + ", ");

                System.out.print("order_id:" + result.getLong(2) + ", ");

                System.out.print("user_id:" + result.getInt(3) + ", ");

                System.out.print("status:" + result.getString(4));

                System.out.println("");
            }
            hintManager.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void selectPageList(DataSource dataSource){
        try {
            Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement();
            ResultSet result = statement.executeQuery(String.format("SELECT * FROM t_order order by order_id limit 0,10"));
            while(result.next()){
                System.out.print("order_id:" + result.getLong(1) + ", ");
                System.out.print("user_id:" + result.getLong(2) + ", ");
                System.out.print("address_id:" + result.getInt(3) + ", ");
                System.out.print("status:" + result.getString(4));
                System.out.println("");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void execute(final DataSource dataSource, final String sql) throws SQLException {
        try (
                Connection conn = dataSource.getConnection();
                Statement statement = conn.createStatement()) {
            statement.execute(sql);
        }
    }

    private long executeAndGetGeneratedKey(final DataSource dataSource, final String sql) throws SQLException {
        long result = -1;
        try (
                Connection conn = dataSource.getConnection();
                Statement statement = conn.createStatement()) {
            statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            ResultSet resultSet = statement.getGeneratedKeys();
            if (resultSet.next()) {
                result = resultSet.getLong(1);
            }
        }
        return result;
    }



    *//**-----------------------------表初始化--------------------------------*//*
    public void drop(DataSource dataSource) throws SQLException {
        execute(dataSource, "DROP TABLE IF EXISTS t_order");
        execute(dataSource, "DROP TABLE IF EXISTS t_order_item;");
    }

    public void create(DataSource dataSource) throws SQLException {
        execute(dataSource, "CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id ))");
        execute(dataSource, " CREATE TABLE IF NOT EXISTS t_order_item (order_item_id BIGINT AUTO_INCREMENT, order_id BIGINT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));");
    }*/

}

ShardingSphere实战场景与Atlas和Mycat对比

   目前sharding-proxy不支持高可用，挂就挂掉了。    sharding-proxy从库挂掉了不支持自动切换的。就会造成当某一次访问的时候，会访问不到数据，刷新一次再请求，就会又可以请求到数据，没有请求到数据的时候，就是那次访问到了挂掉的从库。目前团队还在完善。它最厉害的还是sharding-jdbc。而mycat是可以实现的。Atlas是360开发的，已经很多年不维护了。

分库分表成本

   sharding + proxy本质上只解决了一个问题，那就是单机数据容量问题，

应用限制

   1、sharding后对应用和sql的侵入都很大，需要sql足够简单，这种简单的应用导致DB弱化为存储。
   2、sql不能跨维度join、聚合、子查询等。
   3、每个分片只能实现Local index，不能实现诸如唯一键，外键等全局约束。

sharding业务维度选择

1、有些业务没有天然的业务维度，本身选择一个维度就是个问题。
2、大部分业务需要多维度的支持，多维度的情况下
  - 其他业务维度产生了数据冗余，如果没有全局事务的话，很难保证一致性，全局事务本身实现很难，并且响应时间大幅度下降，业务互相依赖存在重大隐患，于是经常发送"风控把支付给阻塞了"的问题。
  - 多维度实现方式，数据库同步还是异步，同步依赖应用端实现双写，异步存在时效性问题，对业务有限制，会发生先让订单飞一会的问题。
  - 多维度数据关系表维护。

sharding key选择（非业务维度选择）

   1、非业务维度选择，会存在"我要的数据到底在哪个数据库上"的问题
   2、业务维度列如何选择sharding key
   3、热点如何均衡，数据分布可能有长尾效应

sharding算法选择

   1、Hash算法可以比较好的分散热点数据，但对范围查询需要访问多个分片，反之range算法又存在热点问题。所以要求在设计之初就要清楚自己的业务常用读写类型
   转换算法成本很高

数据一致性

   1、mysql双一方案（redis、binlog提交持久化）严重影响了写入性能
   2、即使双一方案，主库硬盘挂了，由于异步复制，数据还会丢失
   3、强一致场景需求，比如金融行业，mysql目前只能做到双一+半同步复制，既然半同步，随时可能延迟为异步复制，还是会丢数据。

avatar
文章
阅读
粉丝