5大主流方案对比：MySQL千亿级数据线上平滑扩容实战下-阿里云开发者社区

2.1.4 搭建应用服务工程

ShardingJDBC的介绍
是ShardingSphere 下的一个产品
定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。

支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP 等。
支持任意实现 JDBC 规范的数据库，目前支持 MySQL，Oracle，SQLServer，PostgreSQL 以及任何遵循 SQL92 标准的数据库

ShardingJDBC初始化流程
1）配置ShardingRuleConfiguration对象
2）配置表分片规则TableRuleConfiguration对象，设置分库、分表策略
3）通过Factory对象将Rule对象与DataSource对象装配
4）ShardingJDBC使用DataSource对象进行分库

ShardingJDBC集成配置
1）maven依赖
2）规则配置application.yml
3）创建DataSource
验证应用服务动态扩容

// 动态数据源配置实现扩容
Properties properties = loadProperties("datasource1.properties");
try {
    log.info("load datasource config url: " + properties.get("url"));
    DruidDataSource druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
    druidDataSource.setRemoveAbandoned(true);
    druidDataSource.setRemoveAbandonedTimeout(600);
    druidDataSource.setLogAbandoned(true);
    // 设置数据源错误重连时间
    druidDataSource.setTimeBetweenConnectErrorMillis(60000);
    druidDataSource.init();
    OrchestrationShardingDataSource dataSource = SpringContextUtil.getBean("tradeSystemDataSource", OrchestrationShardingDataSource.class);
    Map<String, DataSource> dataSourceMap = dataSource.getDataSource().getDataSourceMap();
    dataSourceMap.put(DatasourceEnum.DATASOURCE_2.getValue(), druidDataSource);
    Map<String, DataSourceConfiguration> dataSourceConfigMap = new HashMap<String, DataSourceConfiguration>();
    for(String key : dataSourceMap.keySet()) {
        dataSourceConfigMap.put(key, DataSourceConfiguration.getDataSourceConfiguration(dataSourceMap.get(key)));
    }
    String result = SHARDING_RULE_TABLE_ORDER.replace(SHARDING_RULE_DATASOURCE, newRule);
    replaceActualDataNodes(result);
    SHARDING_RULE_DATASOURCE = newRule;
    dataSource.renew(new DataSourceChangedEvent(
        "/" + DruidSystemDataSourceConfiguration.DYNAMIC_SHARDING + "/config/schema/logic_db/datasource",
        dataSourceConfigMap));
    return;
} catch (Exception e) {
    log.error(e.getMessage(), e);
}

配置两个数据源，分别指向Server1和Server2

分片只配置一个数据源
动态增加另一个数据源
注意事项
Sharding JDBC, Mycat, Drds 等产品都是分布式数据库中间件, 相比直接的数据源操作, 会存在一些限制, Sharding JDBC在使用时, 要注意以下问题:

有限支持子查询

不支持HAVING
不支持OR，UNION 和 UNION ALL
不支持特殊INSERT
每条INSERT语句只能插入一条数据，不支持VALUES后有多行数据的语句
不支持DISTINCT聚合
不支持dual虚拟表查询
不支持SELECT LAST_INSERT_ID(), 不支持自增序列
不支持CASE WHEN

2.2 实现数据库的秒级平滑2N扩容

扩容部署架构：

2.2.1 新增数据库VIP

在Server2节点，增加VIP
修改/etc/keepalived/keepalived.conf

global_defs {
   router_id vip2
}
vrrp_instance VI_1 {            #vrrp实例定义
    state BACKUP               #lvs的状态模式，MASTER代表主， BACKUP代表备份节点
    interface ens33               #绑定对外访问的网卡
    virtual_router_id 112        #虚拟路由标示，同一个vrrp实例采用唯一标示
    priority 100               #优先级，100代表最大优先级， 数字越大优先级越高
    advert_int 1              #master与backup节点同步检查的时间间隔，单位是秒
    authentication {           #设置验证信息
        auth_type PASS         #有PASS和AH两种
        auth_pass 6666         #验证密码，BACKUP密码须相同
    }
    virtual_ipaddress {         #KeepAlived虚拟的IP地址
        192.168.116.151
    }
}
virtual_server 192.168.116.151 3306 {       #配置虚拟服务器IP与访问端口
    delay_loop 6                 #健康检查时间
    persistence_timeout 0        #会话保持时间，这里要做测试， 所以设为0， 实际可根据session有效时间配置
    protocol TCP               #转发协议类型，支持TCP和UDP
    real_server 192.168.116.141 3306{    #配置服务器节点VIP1
    notify_down /usr/local/shell/mariadb.sh
    weight 1               #设置权重，越大权重越高
    TCP_CHECK {              #r状态监测设置
       connect_timeout 10       #超时配置， 单位秒
       retry 3             #重试次数
       delay_before_retry 3        #重试间隔
       connect_port 3306         #连接端口， 和上面保持一致
       }
    }
}

注意配置项：

virtual_router_id 112        #虚拟路由标示，同一个vrrp实例采用唯一标示
priority 100               #优先级，100代表最大优先级， 数字越大优先级越高

2.2.2 应用服务增加动态数据源

修改应用服务配置，增加新的数据源，指向新设置的VIP： 192.168.116.151
通过应用服务接口，动态扩容调整

2.2.3 解除原双主同步

mysql -uroot -p654321

进入Server1:

MariaDB [(none)]> stop slave;

进入Server2:

MariaDB [(none)]> stop slave;

通过应用服务接口验证数据是否解除同步

2.2.4 安装MariaDB扩容服务器

新建两台虚拟机，分别为Server3和Server4。
在Server3和Server4两台节点上安装MariaDB服务
参考2.1.1 MariaDB服务安装
配置Server3与Server1，实现新的双主同步

[mysqld]
server-id = 3
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
log-slave-updates=on
slave-skip-errors=all
auto-increment-offset=2
auto-increment-increment=2
binlog_format=mixed
expire_logs_days=10

service mariadb restart

MariaDB [(none)]> grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica';
mysql> flush privileges;

mysqldump -uroot -p654321 --routines --single_transaction --master-data=2 --databases smooth > server1.sql

...
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_='mysql-bin.000002', MASTER_LOG_POS=17748;
...

scp server1.sql root@192.168.116.142:/usr/local/

mysql -uroot -p654321 < /usr/local/server1.sql

根据上面的master status信息，在Server3中执行：

MariaDB [(none)]> change master to master_host='192.168.116.140',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000016', master_log_pos=1754, master_connect_retry=30;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

如果出现问题，复原主从同步信息：

MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status \G
**** **** **** **** **** **** *** 1. row ** **** **** **** **** **** **** *
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.20.125
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_: mysql-bin.000004
          Read_Master_Log_Pos: 11174
               Relay_Log_: mysql-relay-bin.000002
                Relay_Log_Pos: 1746
        Relay_Master_Log_: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

查看Server3的日志信息：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
|              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     4781 |              |                  |
+------------------+----------+--------------+------------------+

在Server1节点，配置同步信息：

MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> change master to master_host='192.168.116.142',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000005', master_log_pos=6931, master_connect_retry=30;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

配置Server1与Server3节点的同步

检查同步状态信息：
开启主从同步：
配置主从同步信息
将数据还原至Server3节点：
将备份的server1.sql通过scp命令拷贝至Server3节点。
查看并记录master status信息：
在Server1节点，进行数据全量备份：
创建replica用于主从同步的用户：
重启Server3数据库
Server3节点，修改/etc/my.cnf:

配置Server4与Server2的双主同步

[mysqld]
server-id = 4
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
log-slave-updates=on
slave-skip-errors=all
auto-increment-offset=2
auto-increment-increment=2
binlog_format=mixed
expire_logs_days=10

service mariadb restart

MariaDB [(none)]> grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica';
mysql> flush privileges;

mysqldump -uroot -p654321 --routines --single_transaction --master-data=2 --databases smooth > server2.sql

...
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_='mysql-bin.000003', MASTER_LOG_POS=4208;
...

scp server2.sql root@192.168.116.143:/usr/local/

mysql -uroot -p654321 < /usr/local/server2.sql

根据上面的master status信息，在Server4中执行：

MariaDB [(none)]> change master to master_host='192.168.116.141',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000007', master_log_pos=3006, master_connect_retry=30;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

注意，如果出现问题，复原主从同步信息：

MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status \G
**** **** **** **** **** **** *** 1. row ** **** **** **** **** **** **** *
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.20.125
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_: mysql-bin.000004
          Read_Master_Log_Pos: 11174
               Relay_Log_: mysql-relay-bin.000002
                Relay_Log_Pos: 1746
        Relay_Master_Log_: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

查看Server4的日志信息：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
|              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     3696 |              |                  |
+------------------+----------+--------------+------------------+

在Server2节点，配置同步信息：

MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> change master to master_host='192.168.116.143',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000005', master_log_pos=5787, master_connect_retry=30;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

配置Server2与Server4节点的同步

检查同步状态信息：
开启主从同步：
配置主从同步信息
将数据还原至Server4节点：
将备份的server2.sql通过scp命令拷贝至Server4节点。
查看并记录master status信息：
在Server2节点，进行数据全量备份：
创建replica用于主从同步的用户：
重启Server4数据库
Server4节点，修改/etc/my.cnf:

2.2.5 增加KeepAlived服务实现高可用

确保新增的Server3和Server4节点安装Keepalived服务。
修改Server3节点配置

global_defs {
   router_id vip3          # 机器标识，一般设为hostname，故障发生时，邮件通知会使用到。
}
vrrp_instance VI_1 {            #vrrp实例定义
    state BACKUP               #lvs的状态模式，MASTER代表主， BACKUP代表备份节点
    interface ens33               #绑定对外访问的网卡
    virtual_router_id 111        #虚拟路由标示，同一个vrrp实例采用唯一标示
    priority 98               #优先级，100代表最大优先级， 数字越大优先级越高
    advert_int 1              #master与backup节点同步检查的时间间隔，单位是秒
    authentication {           #设置验证信息
        auth_type PASS         #有PASS和AH两种
        auth_pass 6666         #验证密码，BACKUP密码须相同
    }
    virtual_ipaddress {         #KeepAlived虚拟的IP地址
        192.168.116.150
    }
}
virtual_server 192.168.116.150 3306 {       #配置虚拟服务器IP与访问端口
    delay_loop 6                 #健康检查时间
    persistence_timeout 0        #会话保持时间，这里要做测试， 所以设为0， 实际可根据session有效时间配置
    protocol TCP               #转发协议类型，支持TCP和UDP
    real_server 192.168.116.142 3306{    #配置服务器节点VIP3
    notify_down /usr/local/shell/mariadb.sh
    weight 1               #设置权重，越大权重越高
    TCP_CHECK {              #r状态监测设置
       connect_timeout 10       #超时配置， 单位秒
       retry 3             #重试次数
       delay_before_retry 3        #重试间隔
       connect_port 3306         #连接端口， 和上面保持一致
       }
    }
}

注意里面IP配置正确，修改完成后重启服务。
创建关闭脚本mariadb.sh
/usr/local/shell/mariadb.sh：

pkill keepalived

加入执行权限：

chmod a+x mariadb.sh

修改Server4节点配置

global_defs {
   router_id vip4          # 机器标识，一般设为hostname，故障发生时，邮件通知会使用到。
}
vrrp_instance VI_1 {            #vrrp实例定义
    state BACKUP               #lvs的状态模式，MASTER代表主， BACKUP代表备份节点
    interface ens33               #绑定对外访问的网卡
    virtual_router_id 112        #虚拟路由标示，同一个vrrp实例采用唯一标示
    priority 98               #优先级，100代表最大优先级， 数字越大优先级越高
    advert_int 1              #master与backup节点同步检查的时间间隔，单位是秒
    authentication {           #设置验证信息
        auth_type PASS         #有PASS和AH两种
        auth_pass 6666         #验证密码，BACKUP密码须相同
    }
    virtual_ipaddress {         #KeepAlived虚拟的IP地址
        192.168.116.151
    }
}
virtual_server 192.168.116.151 3306 {       #配置虚拟服务器IP与访问端口
    delay_loop 6                 #健康检查时间
    persistence_timeout 0        #会话保持时间，这里要做测试， 所以设为0， 实际可根据session有效时间配置
    protocol TCP               #转发协议类型，支持TCP和UDP
    real_server 192.168.116.143 3306{    #配置服务器节点VIP4
    notify_down /usr/local/shell/mariadb.sh
    weight 1               #设置权重，越大权重越高
    TCP_CHECK {              #r状态监测设置
       connect_timeout 10       #超时配置， 单位秒
       retry 3             #重试次数
       delay_before_retry 3        #重试间隔
       connect_port 3306         #连接端口， 和上面保持一致
       }
    }
}

创建关闭脚本mariadb.sh
/usr/local/shell/mariadb.sh：

pkill keepalived

给所有的用户组加入执行权限：

chmod a+x mariadb.sh

修改完后重启Keepalived服务。

2.2.6 清理数据并验证

通过应用服务动态扩容接口做调整和验证
在Server1节点清理数据
根据取模规则，保留accountNo为偶数的数据

delete from t_trade_order where accountNo % 2 != 0

在Server2节点清理数据
根据取模规则，保留accountNo为奇数的数据

delete from t_trade_order where accountNo % 2 != 1

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 实现的后台管理系统 + 用户小程序，支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

项目地址：https://github.com/YunaiV/yudao-cloud

视频教程：https://doc.iocoder.cn/video/

3.keepalived高可用配置大全

在Server1（192.168.116.140）中执行：

MariaDB [(none)]> change master to master_host='192.168.116.141',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000005', master_log_pos=3207, master_connect_retry=30;

在Server2（192.168.116.141）中执行：

MariaDB [(none)]> change master to master_host='192.168.116.140',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000012', master_log_pos=1951, master_connect_retry=30;

在Server3（192.168.116.142）中执行：

MariaDB [(none)]> change master to master_host='192.168.116.140',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000013', master_log_pos=2781, master_connect_retry=30;
Query OK, 0 rows affected (0.01 sec)

在Server4（192.168.116.143）中执行：

MariaDB [(none)]> change master to master_host='192.168.116.141',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000005', master_log_pos=7358, master_connect_retry=30;
Query OK, 0 rows affected (0.01 sec)

Server1和Server2双主关系

Server1: keepalived.conf

vi /etc/keepalived/keepalived.conf

global_defs {
   router_id vip1
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 111
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 6666
    }
    virtual_ipaddress {
        192.168.116.150
    }
}
virtual_server 192.168.116.150 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR // NAT|DR|TUN
    persistence_timeout 0
    protocol TCP
    real_server 192.168.116.140 3306 {
    notify_down /usr/local/shell/mariadb.sh
    weight 1
    TCP_CHECK {
       connect_timeout 10
       retry 3
       delay_before_retry 3
       connect_port 3306
       }
    }
}

Server2:keepalived.conf

vi /etc/keepalived/keepalived.conf

global_defs {
   router_id vip2
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 111
    priority 98
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 6666
    }
    virtual_ipaddress {
        192.168.116.150
    }
}
virtual_server 192.168.116.150 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 0
    protocol TCP
    real_server 192.168.116.141 3306{
    notify_down /usr/local/shell/mariadb.sh
    weight 1
    TCP_CHECK {
       connect_timeout 10
       retry 3
       delay_before_retry 3
       connect_port 3306
       }
    }
}

新增数据库VIP

Server2:keepalived.conf

vi /etc/keepalived/keepalived.conf

global_defs {
   router_id vip2
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 112
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 6666
    }
    virtual_ipaddress {
        192.168.116.151
    }
}
virtual_server 192.168.116.151 3306 {
    delay_loop 6
    persistence_timeout 0
    protocol TCP
    real_server 192.168.116.141 3306{
    notify_down /usr/local/shell/mariadb.sh
    weight 1
    TCP_CHECK {
       connect_timeout 10
       retry 3
       delay_before_retry 3
       connect_port 3306
       }
    }
}

Server1和Server3双主关系

Server3: keepalived.conf

vi /etc/keepalived/keepalived.conf

global_defs {
   router_id vip3
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 111
    priority 98
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 6666
    }
    virtual_ipaddress {
        192.168.116.150
    }
}
virtual_server 192.168.116.150 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 0
    protocol TCP
    real_server 192.168.116.142 3306 {
    notify_down /usr/local/shell/mariadb.sh
    weight 1
    TCP_CHECK {
       connect_timeout 10
       retry 3
       delay_before_retry 3
       connect_port 3306
       }
    }
}

Server2和Server4双主关系

Server4: keepalived.conf

vi /etc/keepalived/keepalived.conf

global_defs {
   router_id vip4
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 112
    priority 98
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 6666
    }
    virtual_ipaddress {
        192.168.116.151
    }
}
virtual_server 192.168.116.151 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 0
    protocol TCP
    real_server 192.168.116.143 3306{
    notify_down /usr/local/shell/mariadb.sh
    weight 1
    TCP_CHECK {
       connect_timeout 10
       retry 3
       delay_before_retry 3
       connect_port 3306
       }
    }
}

5大主流方案对比：MySQL千亿级数据线上平滑扩容实战下

2.1.4 搭建应用服务工程

2.2 实现数据库的秒级平滑2N扩容

2.2.1 新增数据库VIP

2.2.2 应用服务增加动态数据源

2.2.3 解除原双主同步

2.2.4 安装MariaDB扩容服务器

2.2.5 增加KeepAlived服务实现高可用

2.2.6 清理数据并验证

3.keepalived高可用配置大全

Server1和Server2双主关系

Server1: keepalived.conf

Server2:keepalived.conf

新增数据库VIP

Server2:keepalived.conf

Server1和Server3双主关系

Server3: keepalived.conf

Server2和Server4双主关系

Server4: keepalived.conf

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

5大主流方案对比：MySQL千亿级数据线上平滑扩容实战 下

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

5大主流方案对比：MySQL千亿级数据线上平滑扩容实战下