Shulou(Shulou.com)06/01 Report--

The following brings you about Consul, ProxySQL and MySQL MHA architecture high availability solutions, if you are interested, let's take a look at this article, I believe that after reading Consul, ProxySQL and MySQL MHA architecture high availability solutions will be of some help to you.

Architectural features:

1, High availability

2. Read-write separation, query routing

3Perfect SQL filtering and other functions

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

19:48:04 on 2016-09-05 [INFO] agent: Synced service 'consul'

19:48:04 on 2016-09-05 [INFO] agent: Synced check 'service:proxysql1'

19:48:04 on 2016-09-05 [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.1lug 6032Tracer TMP hand proxysqlaccounadmin.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, 10, 10, 11, 10, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11, 10, 11,

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)

Mysql > INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES (192.168.1.121)

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

Summary, pit point:

1. The database accessed by the user, master-slave, read and write are all defined and identified through hostgroup, so this parameter must correspond

2the health check-up of dint consul

If the healthy script script returns an error code of 1 and code 1 is just warning, it will still be parsed, so modify and check the content of the script and manually return error code 2 or other values if it does not match, then the exception service will not be parsed.

Read the above about Consul, ProxySQL and MySQL MHA architecture highly available solutions to share details, whether there is anything to gain. If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.