文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

Consul, ProxySQL and MySQL MHA架构高可用方案分享

2024-04-02 19:55

关注

下文给大家带来关于Consul, ProxySQL and MySQL MHA架构高可用方案,感兴趣的话就一起来看看这篇文章吧,相信看完Consul, ProxySQL and MySQL MHA架构高可用方案对大家多少有点帮助吧。

架构特性:
1,高可用
2,读写分离,查询路由
3,sql过滤等功能

Consul, ProxySQL and MySQL MHA架构高可用方案分享

Installation of Consul:
Firstly, we’ll need to install the required packages, download the Consul archive and perform the initial configuration. We’ll need to perform the same installation on each of the nodes (i.e., appserver, mysql1 and mysql2).
Install pre-requisite packages:
sudo yum -y install wget unzip bind-utils dnsmasq
Install Consul:
sudo useradd consul
sudo mkdir -p /opt/consul /etc/consul.d
sudo touch /var/log/consul.log /etc/consul.d/proxysql.json
cd /opt/consul
sudo wget https://releases.hashicorp.com/consul/0.6.4/consul_0.6.4_linux_amd64.zip
sudo unzip consul_0.6.4_linux_amd64.zip
sudo ln -s /opt/consul/consul /usr/bin/consul
sudo chown consul:consul -R /etc/consul /opt/consul /var/log/consul.log

Configuration of Consul on Application Server (used as ‘bootstrap’ node):
Now, that we’re done with the installation on each of the hosts, let’s continue with the configuration. In this example we’ll bootstrap the Consul cluster using “appserver”:

Edit configuration files
$ sudo vi /etc/consul.conf
{
"datacenter": "dc1",
"data_dir": "/opt/consul/",
"log_level": "INFO",
"node_name": "agent1",
"server": true,
"ui": true,
"bootstrap": true,
"client_addr": "0.0.0.0",
"advertise_addr": "192.168.1.119"  ## Add server IP here
}
######
$ sudo vi /etc/consul.d/proxysql.json
{"services": [
{
"id": "proxy1",
"name": "proxysql",
"address": "192.168.1.120",
"tags": ["mysql"],
"port": 6033,
"check": {
"script": "mysqladmin ping --host=192.168.1.120 --port=6033 --user=root --password=123",
"interval": "3s"}
},
{
"id": "proxy2",
"name": "proxysql",
"address": "192.168.1.121",
"tags": ["mysql"],
"port": 6033,
"check": {
"script": "mysqladmin ping --host=192.168.1.121 --port=6033 --user=root --password=123",
"interval": "3s"}
}
]
}

Start Consul agent
$ sudo su - consul -c 'consul agent -config-file=/etc/consul.conf -config-dir=/etc/consul.d > /var/log/consul.log &'

Setup DNSMASQ (as root)
vim /etc/dnsmasq.conf
resolv-file=/etc/resolv.conf
server=/consul/127.0.0.1#8600
service dnsmasq restart

Remember to add the localhost as a DNS server (this step can vary
depending on how your DNS servers are managed... here I'm just
adding the following line to resolve.conf:
sudo vi /etc/resolve.conf
#... snippet ...#
nameserver 127.0.0.1
#... snippet ...#
Restart dnsmasq
sudo service dnsmasq restart

====================================================================

Configuration of Consul on Proxy Servers:
The next item is to configure each of the proxy Consul agents. Note that the “agent name” and the “IP address” need to be updated for each host (values for both must be unique):

Edit configuration files
$ sudo vi /etc/consul.conf
{
"datacenter": "dc1",
"data_dir": "/opt/consul/",
"log_level": "INFO",
"node_name": "agent2",  ### Agent node name must be unique
"server": true,
"ui": true,
"bootstrap": false,   ### Disable bootstrap on joiner nodes
"client_addr": "0.0.0.0",
"advertise_addr": "192.168.1.xxx",  ### Set to local instance IP
"dns_config": {
"only_passing": true
}
}
######
$ sudo vi /etc/consul.d/proxysql.json
{"services": [
{
"id": "proxy1",
"name": "proxysql",
"address": "192.168.1.120",
"tags": ["mysql"],
"port": 6033,
"check": {
"script": "sh /etc/consul.d/test.sh",
"interval": "3s"}
},
{
"id": "proxy2",
"name": "proxysql",
"address": "192.168.1.121",
"tags": ["mysql"],
"port": 6033,
"check": {
"script": "sh /etc/consul.d/test.sh",
"interval": "3s"}
}
]
}

=============================================================

[root@localhost consul.d]# more test.sh
mysql -u haproxy -h 127.0.0.1 -P 3306 -e "select 1" >/dev/null 2>&1

if [ "$?" -ne 0 ]
then echo "not available"
exit 8
fi

==============================================================

Start Consul agent:
$ sudo su - consul -c 'consul agent -config-file=/etc/consul.conf -config-dir=/etc/consul.d > /var/log/consul.log &'
Join Consul cluster specifying 1st node IP e.g.
$ consul join 192.168.1.119
Verify logs and look out for the following messages:
$ cat /var/log/consul.log
==> Starting Consul agent...
==> Starting Consul agent RPC...
==> Consul agent running!
Node name: 'agent2'
Datacenter: 'dc1'
Server: true (bootstrap: false)
Client Addr: 0.0.0.0 (HTTP: 8500, HTTPS: -1, DNS: 8600, RPC: 8400)
Cluster Addr: 192.168.1.120 (LAN: 8301, WAN: 8302)
Gossip encrypt: false, RPC-TLS: false, TLS-Incoming: false
Atlas:
==> Log data will now stream in as it occurs:
... snippet ...
2016/09/05 19:48:04 [INFO] agent: Synced service 'consul'
2016/09/05 19:48:04 [INFO] agent: Synced check 'service:proxysql1'
2016/09/05 19:48:04 [INFO] agent: Synced check 'service:proxysql2'
... snippet ...

=============================================================================

Install ProxySQL packages and initialise ProxySQL DB
sudo yum -y install https://github.com/sysown/proxysql/releases/proxysql-1.4.3-1-centos67.x86_64.rpm
sudo service proxysql initial
sudo service proxysql stop
Edit the ProxySQL configuration file to update username / password
vi /etc/proxysql.cnf

admin_variables=
{
admin_credentials="admin:admin"
mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
}

Start ProxySQL
sudo service proxysql start
Connect to ProxySQL and configure
mysql -P6032 -h227.0.0.1 -uadmin -padmin
First we create a replication hostgroup:
mysql> INSERT INTO mysql_replication_hostgroups VALUES (10,11,'Standard Replication Groups');
Add both nodes to the hostgroup 11 (ProxySQL will automatically put the writer node in hostgroup 10)
mysql> INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.120',10,3306,1000);
mysql> INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.121',11,3306,1000);
Save server configuration
mysql> LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
Add query rules for RW split
mysql> INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, cache_ttl, apply) VALUES (1, '^SELECT . FOR UPDATE', 10, NULL, 1);
mysql> INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, cache_ttl, apply) VALUES (1, '^SELECT .', 11, NULL, 1);
mysql> LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;
Finally configure ProxySQL user and save configuration
mysql> INSERT INTO mysql_users (username,password,active,default_hostgroup,default_schema) VALUES ('root','123',1,10,'test');
mysql> LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
mysql> EXIT;

总结,坑点:
1,用户访问的数据库,主从,读写都是通过hostgroup 要定义和标识,所以这个参数一定对应

2,consul 健康检查
如果健康script 脚本返回error code 为1 ,code 1 只是warning ,还是会被解析,所以修改检查脚本内容,如果不匹配,手动返回error code 2或者其他值,之后这个异常服务才不解析。

看了以上关于Consul, ProxySQL and MySQL MHA架构高可用方案分享详细内容,是否有所收获。如果想要了解更多相关,可以继续关注我们的行业资讯板块。

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯