乐趣区

关于java:5大主流方案对比MySQL千亿级数据线上平滑扩容实战

jiezi

大家好，我是不才陈某~

在我的项目初期，咱们部署了三个数据库 A、B、C，此时数据库的规模能够满足咱们的业务需要。为了将数据做到平均分配，咱们在 Service 服务层应用 uid%3 进行取模分片，从而将数据平均分配到三个数据库中。

关注公众号：码猿技术专栏，回复关键词：1111 获取阿里外部 Java 性能优化手册！

如图所示：

前期随着用户量的减少，用户产生的数据信息被源源不断的增加到数据库中，最终达到数据库的最佳存储容量。如果此时持续向数据库中新增数据，会导致数据库的 CRUD 等基本操作变慢，进而影响整个服务的响应速度。

这时，咱们须要减少新的节点，对数据库进行程度扩容，那么退出新的数据库 D 后，数据库的规模由原来的 3 个变为 4 个。

如图所示：

此时因为分片规定产生了变动（uid%3 变为 uid%4），导致大部分的数据，无奈命中原有的数据，须要从新进行调配，要做大量的数据迁徙解决。

比方之前 uid 如果是 uid= 3 取模 3%3=0，是调配在 A 库上，新退出 D 库后，uid= 3 取模 3%4=3，调配在 D 库上；

新增一个节点，大略会有 90% 的数据须要迁徙，这样会面临大量的数据压力，并且对服务造成极大的不稳定性。

1. 五个计划

1.1 停机计划

  1. 发布公告：为了进行数据的从新拆分，在进行服务之前，咱们须要提前告诉用户，比方：咱们的服务会在 yyyy-MM-dd 进行降级，给您带来的不便敬请体谅。
  2. 进行服务：敞开 Service
  3. 离线数据迁徙（拆分，重新分配数据）：将旧库中的数据依照 Service 层的算法，将数据拆分，重新分配数据
  4. 数据校验：开发定制一个程序对旧库和新库中的数据进行校验，比对
  5. 更改配置：批改 Service 层的配置算法，也就是将原来的 uid%3 变为 uid%4
  6. 复原服务：重启 Service 服务
  7. 回滚预案：针对上述的每个步骤都要有数据回滚预案，一旦某个环节（如：数据迁徙，复原服务等）执行失败，立即进行回滚，从新再来

进行服务之后，可能保障迁徙工作的失常进行，然而服务进行，挫伤用户体验，并造成了工夫压力，必须在指定的工夫内实现迁徙。

1.2 停写计划

  1. 反对读写拆散：数据库反对读写拆散，在扩容之前，每个数据库都提供了读写性能，数据重新分配的过程中，将每个数据库设置为只读状态，敞开写的性能
  2. 降级布告：为了进行数据的从新拆分，在停写之前，咱们须要提前告诉用户，比方：咱们的服务会在 yyyy-MM-dd 进行降级，给您带来的不便敬请体谅。
  3. 中断写操作，隔离写数据源（或拦挡返回对立提醒）：在 Service 层对所有的写申请进行拦挡，对立返回提示信息，如：服务正在降级中，只对外提供读服务
  4. 数据同步解决：将旧库中的数据依照 Service 层的算法，将数据重新分配，迁徙（复制数据）
  5. 数据校验：开发定制一个程序对旧库中的数据进行备份，应用备份的数据和重新分配后的数据进行校验，比对
  6. 更改配置：通过配置核心，批改 Service 层的配置算法，也就是将原来的 uid%3 变为 uid%4，这个过程不须要重启服务
  7. 复原写操作：设置数据库复原读写性能，去除 Service 层的拦挡提醒
  8. 数据清理：应用 delete 语句对冗余数据进行删除
  9. 回滚预案：针对上述的每个步骤都要有数据回滚预案，一旦某个环节（如：数据迁徙等）执行失败，立即进行回滚，从新再来

毛病：在数据的复制过程须要耗费大量的工夫，停写工夫太长，数据须要先复制，再清理冗余数据

1.3 日志计划

外围是通过日志进行数据库的同步迁徙，次要操作步骤如下：

1、数据迁徙之前，业务利用拜访旧的数据库节点。

2、日志记录

在降级之前，记录“对旧数据库上的数据批改”的日志（这里批改包含增、删、改），这个日志不须要记录具体的数据信息，次要记录：

（1）批改的库；

（2）批改的表；

（3）批改的惟一主键；

（4）批改操作类型。

日志记录不必关注新增了哪些信息，批改的数据格式，只须要记录以上数据信息，这样日志格局是固定的，这样能保障计划的通用性。

服务降级日志记录性能危险较小：

写和批改接口是多数，改变点少；

降级只是减少了一些日志，采纳异步形式实现，对业务性能没有太多影响。

3、数据迁徙：

研发定制数据迁徙工具，作用是把旧库中的数据迁徙至新库中。

整个过程依然采纳旧库进行对外服务。

数据同步工具实现复杂度不高。

只对旧库进行读取操作，如果同步呈现问题，都能够对新库进行回滚操作。

能够限速或分批迁徙执行，不会有工夫压力。

数据迁徙实现之后，并不能切换至新库提供服务。

因为旧库仍然对线上提供服务，库中的数据随时会发生变化，但这些变动的数据并没有同步到新库中，旧库和新库数据不统一，所以不能间接进行切换，须要将数据同步残缺。

4、日志增量迁徙

研发一个日志迁徙工具，把下面迁徙数据过程中的差别数据追平，解决步骤：

读取 log 日志，获取具体是哪个库、表和主键产生了变动批改；

把旧库中的主键记录读取进去

依据主键 ID，把新库中的记录替换掉

这样能够最大水平的保障数据的一致性。危险剖析：

整个过程，依然是旧库对线上提供服务；

日志迁徙工具实现的复杂度较低；

任何工夫发现问题，能够从新再来，有充沛的容错空间；

能够限速重放解决日志，处理过程不会因为对线上影响造成工夫压力。

然而，日志增量同步实现之后，还不能切换到新的数据库。

因为日志增量同步过程中，旧库中可能有数据发生变化，导致数据不统一，所以须要进一步读取日志，追平数据记录；日志增量同步过程随时可能会产生新的数据，新库与旧库的数据追平也会是一个有限迫近的过程。

5、数据校验

筹备好数据校验工具，将旧库和新库中的数据进行比对，直到数据完全一致。

6、切换新库

数据比对实现之后，将流量转移切换至新库，至此新库提供服务，实现迁徙。

然而在极限状况下，即使通过下面的数据校验解决，也有可能呈现 99.99% 数据统一，不能保障完全一致，这个时候能够在旧库做一个 readonly 只读性能，或者将流量屏蔽降级，期待日志增量同步工具齐全追平后，再进行新库的切换。

至此，实现日志计划的迁徙扩容解决，整个过程可能继续对线上提供服务，只会短暂的影响服务的可用性。

这种计划的弊病，是操作繁琐，须要适配多个同步解决工具，老本较高，须要制订个性化业务的同步解决，不具备普遍性，消耗的工夫周期也较长。

1.4 双写计划（中小型数据）

双写计划可通过 canal 或 mq 做实现。

  1. 减少新库，依照现有节点，减少对应的数量。
  2. 数据迁徙：防止增量影响，先断开主从，再导入（耗时较长），同步实现并做校验
  3. 增量同步：开启 Canal 同步服务，监听从节点数据库，再开启主从同步，从节点收到数据后会通过 Canal 服务，传递至新的 DB 节点。
  4. 切换新库：通过 Nginx，切换拜访流量至新的服务。

  5. 修复切换异样数据：在切换过程中，如果呈现，Canal 未同步，但已切换至新库的申请（比方下单，批改了资金，但还未同步），能够通过定制程序，读取检测异样日志，做主动修复或人工解决。

    针对此种状况，最好是在凌晨用户量小的时候，或专门进行外网拜访，进行切换，缩小异样数据的产生。

  6. 数据校验：为保障数据的完全一致，有必要对数据的数量完整性做校验。

1.5 平滑 2N 计划（大数据量）

线上数据库，为了保障其高可用，个别每台主库会配置一台从库，主库负责读写，从库负责读取。下图所示，A，B 是主库，A0 和 B0 是从库。

1、当须要扩容的时候，咱们把 A0 和 B0 降级为新的主库节点，如此由 2 个分库变为 4 个分库。同时在下层的分片配置，做好映射，规定如下:

把 uid%4= 0 和 uid%4= 2 的数据别离调配到 A 和 A0 主库中

把 uid%4= 1 和 uid%4= 3 的数据调配到 B 和 B0 主库中

2、因为 A 和 A0 库的数据雷同，B 和 B0 数据雷同，此时无需做数据迁徙。只需调整变更一下分片配置即可，通过配置核心更新，不须要重启。

因为之前 uid%2 的数据是调配在 2 个库外面，扩容之后须要散布到 4 个库中，但因为旧数据仍存在（uid%4= 0 的节点, 还有一半 uid%4= 2 的数据），所以须要对冗余数据做一次清理。

这个清理，并不会影响线上数据的一致性，能够随时随地进行。

3、解决实现之后，为保证数据的高可用，以及未来下一步的扩容需要。

能够为现有的主库再次调配一个从库。

2. 平滑 2N 扩容计划实际

2.1 实现应用服务级别的动静扩容

扩容前部署架构：

2.1.1 MariaDB 服务装置

  1. 切换阿里云镜像服务（YUM 装置过慢能够切换）

    yum -y install wget
## 备份 CentOS-Base.repo
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.bak

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

wget -P /etc/yum.repos.d/ http://mirrors.aliyun.com/repo/epel-7.repo

yum clean all

yum makecache

  2. 配置 YUM 源

    vi /etc/yum.repos.d/mariadb-10.2.repo

    减少以下内容：

    [mariadb]
name = MariaDB
baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

  3. 执行装置

    yum -y install mariadb mariadb-server MariaDB-client  MariaDB-common

  4. 如果之前曾经装置，须要先删除（如果之前没有装置，能够疏忽此步骤）

    • 进行 Mariadb 服务

      [root@localhost yum.repos.d]# ps -ef | grep mysql
root       1954      1  0 Oct04 ?        00:05:43 /usr/sbin/mysqld --wsrep-new-cluster --user=root
root      89521  81403  0 07:40 pts/0    00:00:00 grep --color=auto mysql
[root@localhost yum.repos.d]# kill 1954

    • 卸载 Mariadb 服务

      yum -y remove Maria*

    • 删除数据与配置：

      rm -rf /var/lib/mysql/*
rm -rf /etc/my.cnf.d/
rm -rf /etc/my.cnf

  5. 启动 MariaDB 后，执行平安配置向导命令，可依据平安配置向导进步数据库的安全性

    systemctl start mariadb

mysql_secure_installation

  6. 开启用户近程连贯权限

    将连贯用户 root 开启近程连贯权限；

    mysql -uroot -p654321

    进入 MySQL 服务，执行以下操作：

    use mysql;

delete from user;
## 配置 root 用户应用明码 654321 从任何主机都能够连贯到 mysql 服务器
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '654321' WITH GRANT OPTION;

FLUSH PRIVILEGES;

2.1.2 MariaDB 双主同步

  1. 在 Server1 减少配置：

    在 /etc/my.cnf 中增加以下配置：

    [mysqld]
server-id  = 1
log-bin=mysql-bin
relay-log = mysql-relay-bin
## 疏忽 mysql、information_schema 库下对表的操作
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
## 默认的状况下 mysql 是敞开的;
log-slave-updates=on
## 复制过程中，有任何谬误，间接跳过
slave-skip-errors=all
auto-increment-offset=1
auto-increment-increment=2
## binlog 的格局：STATEMENT，ROW，MIXED
binlog_format=mixed
## 主动过期清理 binlog，默认 0 天，即不主动清理
expire_logs_days=10

    留神，Server1 自增为奇数位：

    auto-increment-offset=1 主键自增基数, 从 1 开始。

    auto-increment-increment=2 主键自增偏移量，每次为 2。

  2. 在 Server2 减少配置：

    批改 /etc/my.cnf:

    [mysqld]
server-id = 2
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

    Server2 自增为偶数位：

    auto-increment-offset=2 主键自增基数, 从 2 开始。

    auto-increment-increment=2 主键自增偏移量，每次为 2。

    配置批改实现后，重启数据库。

  3. 同步受权配置

    在 Server1 创立 replica 用于主从同步的用户：

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

    查问日志文件与偏移量，开启同步时需应用：

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

    同样，在 Server2 创立 replica 用于主从同步的用户：

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

    查问日志文件与偏移量：

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

  4. 配置主从同步信息

    在 Server1 中执行：

    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=374, master_connect_retry=30;

    在 Server2 中执行：

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

  5. 开启双主同步

    在 Server1 和 Server2 中别离执行：

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

    在 Server1 查问同步信息：

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

    在 Server2 查问同步信息：

    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.000001
          Read_Master_Log_Pos: 663
               Relay_Log_: mysql-relay-bin.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

    Slave_IO_Running 和 Slave_SQL_Running 都是 Yes，阐明双主同步配置胜利。

2.1.3 KeepAlived 装置与高可用配置

  1. 在 Server1 与 Server2 两台节点装置 keepalived：

    yum -y install keepalived

  2. 敞开防火墙

    systemctl stop firewalld
systemctl disable firewalld

  3. 设置主机名称：

    Server1 节点：

    hostnamectl set-hostname vip1

    Server2 节点：

    hostnamectl set-hostname vip2

  4. Server1 节点配置

    /etc/keepalived/keepalived.conf：

    global_defs {router_id vip1           # 机器标识，和主机名保持一致，运行 keepalived 服务器的一个标识}
vrrp_instance VI_1 {            #vrrp 实例定义
    state BACKUP               #lvs 的状态模式，MASTER 代表主，BACKUP 代表备份节点
    interface ens33               #绑定对外拜访的网卡，vrrp 实例绑定的网卡
    virtual_router_id 111        #虚构路由标示，同一个 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.150
    }
}
virtual_server 192.168.116.150 3306 {       #配置虚构服务器 IP 与拜访端口
    delay_loop 6                 #健康检查工夫
    lb_algo rr                  #负载平衡调度算法，rr 代表轮询
    lb_kind DR                   #负载平衡转发规定 DR/NAT/
    persistence_timeout 0        #会话放弃工夫，这里要做测试，所以设为 0，理论可依据 session 无效工夫配置
    protocol TCP               #转发协定类型，反对 TCP 和 UDP
    real_server 192.168.116.140 3306 {    #配置服务器节点 VIP1    
    notify_down /usr/local/shell/mariadb.sh #当服务挂掉时，会执行此脚本，完结 keepalived 过程
    weight 1               #设置权重，越大权重越高
    TCP_CHECK {              #状态监测设置
       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

  5. Server2 节点配置：

    global_defs {router_id vip2           # 机器标识，和主机名保持一致，运行 keepalived 服务器的一个标识}
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                 #健康检查工夫
    lb_algo rr                  #负载平衡调度算法，rr 代表轮询, 能够敞开
    lb_kind DR                   #负载平衡转发规定, 能够敞开
    persistence_timeout 0        #会话放弃工夫，这里要做测试，所以设为 0，理论可依据 session 无效工夫配置
    protocol TCP               #转发协定类型，反对 TCP 和 UDP
    real_server 192.168.116.141 3306{    #配置服务器节点 VIP2
    notify_down /usr/local/shell/mariadb.sh #当服务挂掉时，会执行此脚本，完结 keepalived 过程
    weight 1               #设置权重，越大权重越高
    TCP_CHECK {              #r 状态监测设置
       connect_timeout 10       #超时配置，单位秒
       retry 3             #重试次数
       delay_before_retry 3        #重试距离
       connect_port 3306         #连贯端口，和下面保持一致
       }
    }

}

    和 Server1 的差别项：

    router_id vip2   # 机器标识，和主机名保持一致
priority 98               #优先级，100 代表最大优先级，数字越大优先级越高
real_server 10.10.20.126 3306  #配置服务器节点 VIP2

    留神，两台节点都设为 BACKUP

    virtual_router_id 111        #同一个 vrrp 实例采纳惟一标示
state BACKUP

    如果不想重启后，抢夺备用节点的 VIP，能够设置此项

    nopreempt #不被动抢占资源

    留神：这个配置只能设置在 backup 主机上，而且这个主机优先级要比另外一台高

  6. 验证高可用

    进行主节点 MariaDB 服务，验证是否主动切换。

2.1.4 搭建应用服务工程

  1. 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 规范的数据库

  2. ShardingJDBC 初始化流程

    1）配置 ShardingRuleConfiguration 对象

    2）配置表分片规定 TableRuleConfiguration 对象，设置分库、分表策略

    3）通过 Factory 对象将 Rule 对象与 DataSource 对象拆卸

    4）ShardingJDBC 应用 DataSource 对象进行分库

  1. ShardingJDBC 集成配置

    1）maven 依赖

    2）规定配置 application.yml

    3）创立 DataSource

  2. 验证应用服务动静扩容

    1. 配置两个数据源，别离指向 Server1 和 Server2
    2. 分片只配置一个数据源
    3. 动静减少另一个数据源
    // 动静数据源配置实现扩容
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);
}

  3. 注意事项

    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

  1. 在 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 应用服务减少动静数据源

  1. 批改应用服务配置，减少新的数据源，指向新设置的 VIP：192.168.116.151
  2. 通过应用服务接口，动静扩容调整

2.2.3 解除原双主同步

mysql -uroot -p654321

  1. 进入 Server1:

    MariaDB [(none)]> stop slave;

  2. 进入 Server2:

    MariaDB [(none)]> stop slave;
  3. 通过应用服务接口验证数据是否解除同步

2.2.4 装置 MariaDB 扩容服务器

  1. 新建两台虚拟机，别离为 Server3 和 Server4。

  2. 在 Server3 和 Server4 两台节点上装置 MariaDB 服务

    参考 2.1.1 MariaDB 服务装置

  3. 配置 Server3 与 Server1，实现新的双主同步

    • Server3 节点，批改 /etc/my.cnf:
    [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
    • 重启 Server3 数据库
    service mariadb restart
    • 创立 replica 用于主从同步的用户：
    MariaDB [(none)]> grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica';
mysql> flush privileges;
    • 在 Server1 节点，进行数据全量备份：
    mysqldump -uroot -p654321 --routines --single_transaction --master-data=2 --databases smooth > server1.sql
    • 查看并记录 master status 信息：
    ...
--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_='mysql-bin.000002', MASTER_LOG_POS=17748;
...
    • 将备份的 server1.sql 通过 scp 命令拷贝至 Server3 节点。
    scp server1.sql root@192.168.116.142:/usr/local/
    • 将数据还原至 Server3 节点：
    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
    • 配置 Server1 与 Server3 节点的同步

    查看 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)

  4. 配置 Server4 与 Server2 的双主同步

    • Server4 节点，批改 /etc/my.cnf:
    [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
    • 重启 Server4 数据库
    service mariadb restart
    • 创立 replica 用于主从同步的用户：
    MariaDB [(none)]> grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica';
mysql> flush privileges;
    • 在 Server2 节点，进行数据全量备份：
    mysqldump -uroot -p654321 --routines --single_transaction --master-data=2 --databases smooth > server2.sql
    • 查看并记录 master status 信息：
    ...
--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_='mysql-bin.000003', MASTER_LOG_POS=4208;

...
    • 将备份的 server2.sql 通过 scp 命令拷贝至 Server4 节点。
    scp server2.sql root@192.168.116.143:/usr/local/
    • 将数据还原至 Server4 节点：
    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
    • 配置 Server2 与 Server4 节点的同步

    查看 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)

2.2.5 减少 KeepAlived 服务实现高可用

  1. 确保新增的 Server3 和 Server4 节点装置 Keepalived 服务。

  2. 批改 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

  3. 批改 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
  4. 批改完后重启 Keepalived 服务。

2.2.6 清理数据并验证

  1. 通过应用服务动静扩容接口做调整和验证

  2. 在 Server1 节点清理数据

    依据取模规定，保留 accountNo 为偶数的数据

    delete from t_trade_order where accountNo % 2 != 0

  3. 在 Server2 节点清理数据

    依据取模规定，保留 accountNo 为奇数的数据

    delete from t_trade_order where accountNo % 2 != 1

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
       }
    }
}

最初说一句（别白嫖，求关注）

陈某每一篇文章都是精心输入，如果这篇文章对你有所帮忙，或者有所启发的话，帮忙 点赞 、 在看 、 转发 、 珍藏，你的反对就是我坚持下去的最大能源！

关注公众号：【码猿技术专栏】，公众号内有超赞的粉丝福利，回复：加群，能够退出技术探讨群，和大家一起探讨技术，吹牛逼！

退出移动版