5大主流方案对比:MySQL千亿级数据线上平滑扩容实战(下篇)
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
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
}
}
}
文章转载自公众号: 码猿技术专栏