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

下文給大家帶來關于Consul, ProxySQL and MySQL MHA架構高可用方案，感興趣的話就一起來看看這篇文章吧，相信看完Consul, ProxySQL and MySQL MHA架構高可用方案對大家多少有點幫助吧。

架構特性：
1，高可用
2，讀寫分離，查詢路由
3，sql過濾等功能

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架構高可用方案分享詳細內容，是否有所收獲。如果想要了解更多相關，可以繼續關注我們的行業資訊板塊。