通过proxysql实现mysql读写分离,减少mysql压力,通过keepalived实现proxysql高可用,以下结构也有问题如:资源争用而导致的死锁
以下是整体架构图
首先所有服务器时间点同步,其次是要有主从复制环境,这个在我的上一篇博客提到,先配置通了再配置下面的
以下是keepalived+proxysql的主节点
# keeplived主节点
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc #邮件相关
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id node1 # 主从节点标识
vrrp_mcast_group4 224.9.9.9 # 多播地址用于两个节点之间的沟通
}
vrrp_script chk_proxysql {
script "killall -0 proxysql && exit 0 || exit 1"
interval 1
weight -20
fall 2
rise 1
}
vrrp_script chk_down {
script "killall -0 keepalived && exit 0 || exit 1"
interval 1
weight -20
fall 2
rise 1
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 1
priority 100
advert_int 1
# 两节点之间的验证
authentication {
auth_type PASS
auth_pass 661111
}
virtual_ipaddress {
172.18.7.8/16
}
# proxysql、keepalived检测脚本
track_script {
chk_proxysql
chk_down
}
}
# proxysql主节点
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:admin"
mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:3306;/tmp/mysql.sock" # 监听任意IP的3306端口
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30" # 登录是显示服务器的proxysql版本号
connect_timeout_server=3000
monitor_username="monitor"
monitor_password="monitor"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
mysql_servers =
(
# 主从mysql的IP,端口,所属组,最大连接数
{ address="192.168.6.7" , port=3306 , hostgroup=0, max_connections=200 },
{ address="192.168.6.17" , port=3306 , hostgroup=1 }
)
# 定义通过proxysql登录的mysql用户,先在mysql授权创建
mysql_users:
(
{
username = "proxy" # no default , required
password = "123456" # default: ''
default_hostgroup = 0 # default: 0
active = 1 # default: 1
}
)
#defines MySQL Query Rules
mysql_query_rules:
(
{
rule_id=1
active=1
match_pattern="^SELECT" #匹配以select开头的语句
destination_hostgroup=1 #请求发到第一组,即前面定义的从服务器
apply=1
}
)
# 定义读写组
mysql_replication_hostgroups=
(
{
writer_hostgroup=0 # 把0组定义为写
reader_hostgroup=1
comment="test repl 1"
}
)
以下是keepalived+proxysql的从节点
# keepalived从节点,主要展示和主节点不一样的,把对应项修改即可
router_id node2 # 主从节点标识
state BACKUP
priority 90
proxysql在从节点上的配置文件一样,复制即可,然后启动所有服务
测试是否成功
[root@centos6 ~]# mysql -uproxy -p123456 -h172.18.7.8
停掉一个proxysql服务看是否成功发送请求,来验证是否实现proxysql高可用。