MariaDB Galera Cluster with HA proxies and keepalived on Cents 6

Haproxy to achieve database load balancing, health monitoring. Weight distribution.

IP planning:

192.168.0.221 OS---221 db192.168.0.222 os---222 db192.168.0.223 OS---223 db192.168.0.224 OS---224 haproxy1 92.168.0.225 OS---225 haproxyvip:192.168.0.226

Get ready:

Before installing three hosts, SELinux, hostname, and iptables must be set up first. As follows:

(1) Prohibit selinuxselinux=disabled (2) Configure Firewall service iptables stop chkconfig iptables offiptables-f (3) Edit Hosts file Vi/etc/hosts1 92.168.0.221 OS---221192.168.0.222 OS---222192.168.0.223 OS---223

(4) Reboot

1. Add Mariadb.repo,yum Source

[Mariadb]name = Mariadbbaseurl = http://yum.mariadb.org/10.0/centos6-amd64gpgkey=https://yum.mariadb.org/ Rpm-gpg-key-mariadbgpgcheck=1

Yum Makecache

2. Installation

Yum Install Mariadb-galera-server mariadb-client galera

3. Initialize MARIADB on three hosts and create a cluster account

All nodes:

Initialize as follows: Service MySQL startmysql_secure_installation #提示创建密码, root is telnet, delete test database.  The authorized account is as follows: Mysql-pgrant all privileges on * * to ' CP ' @ '% ' identified by ' 123 ' with GRANT Option;flush privileges;quit then stop all Nodes data Service MySQL stop

4. Build cluster: Setup Cluster Configuration on Client221

Vi/etc/my.cnf.d/server.conf

[Mariadb]query_cache_size=0binlog_format=rowdefault_storage_engine=innodbinnodb_autoinc_lock_mode=2wsrep_ Provider=/usr/lib64/galera/libgalera_smm.sowsrep_cluster_address=gcomm://192.168.0.223,192.168.0.222wsrep_ Cluster_name= ' cp ' wsrep_node_address= ' 192.168.0.221 ' wsrep_node_name= ' os6---221 ' wsrep_sst_method=rsyncwsrep_sst_ Auth=cp:123

After saving exits, execute

/etc/init.d/mysql Bootstrap

5. Build cluster: Setup Cluster Configuration on Client222, only three different from above

[Mariadb]query_cache_size=0binlog_format=rowdefault_storage_engine=innodbinnodb_autoinc_lock_mode=2wsrep_ Provider=/usr/lib64/galera/libgalera_smm.sowsrep_cluster_address=gcomm://192.168.0.221,192.168.0.223wsrep_ Cluster_name= ' cp ' wsrep_node_address= ' 192.168.0.222 ' wsrep_node_name= ' os6---222 ' wsrep_sst_method=rsyncwsrep_sst_ Auth=cp:123

At last

/etc/init.d/mysql start

6. Build cluster: Setup Cluster Configuration on Client223, same as only three and above

[Mariadb]query_cache_size=0binlog_format=rowdefault_storage_engine=innodbinnodb_autoinc_lock_mode=2wsrep_ Provider=/usr/lib64/galera/libgalera_smm.sowsrep_cluster_address=gcomm://192.168.0.221,192.168.0.222wsrep_ Cluster_name= ' cp ' wsrep_node_address= ' 192.168.0.223 ' wsrep_node_name= ' os6---223 ' wsrep_sst_method=rsyncwsrep_sst_ Auth=cp:123

At last

/etc/init.d/mysql start

7. Test the cluster

Omitted

8. Install the Haproxy, respectively, in 192.168.0.224 and 192.168.0.225

Yum Install Haproxy-y

9. Configure Haproxy, edit/usr/local/haproxy/etc/haproxy.cfg, respectively in 192.168.0.224 and 192.168.0.225

Mv/usr/local/haproxy/etc/haproxy.cfg/usr/local/haproxy/etc/haproxy.cfg.bakvi/usr/local/haproxy/etc/haproxy.cfg

Globallog 127.0.0.1 local0 noticeuser haproxygroup haproxy defaultslog globalretries 2timeout connect 1000timeout Server 5 000timeout Client Listen mariadb-clusterbind 0.0.0.0:3306mode tcpoption mysql-check user haproxybalance Roundrobinserver DB221 192.168.0.221:3306 checkserver DB222 192.168.0.222:3306 checkserver DB223 192.168.0.223:3306 Check Listen webinterfacebind 0.0.0.0:8080mode httpstats enablestats uri/haproxy/statsstats Realm strictly\ Privatestat S Auth haproxy:haproxy

10. Establish a user on the database for haproxy detection of DB

Idea: Execute on a second database. It is not necessary to perform all of the 3 db.

CREATE USER ' haproxy ' @ ' 192.168.0.224 '; CREATE USER ' haproxy ' @ ' 192.168.0.225 '; FLUSH privileges;

11. Start Haproxy

Service Haproxy Start

Boot up

echo "/etc/init.d/haproxy start" >>/etc/rc.local

Visit Haproxy's test page:

224:http://192.1680.224/haproxy/stats User:haproxy Pass:haproxy225:http://192.168.0.225/haproxy/stats User:haproxy Pass:haproxy

Attempting to log in to MySQL:

Mysql-h 192.168.0.224-p 3306-pmysql-h 192.168.0.225-p 3306-p

Test:

Arbitrarily close one of the databases/two databases, test connectivity

12. Installing keepalived

Script Installation: Omit

13. Configuring the Keepalived configuration file for primary haproxy (192.168.0.224)

Vi/etc/keepalived/keepalived.conf

global_defs {   notification_email {     [email  protected]   }   notification_email_from [email protected]    smtp_server smtp.163.com   smtp_connect_timeout 30   router_id  LVS_DEVEL}vrrp_script chk_haproxy {script  "Killall -0 haproxy" interval  1weight 2}vrrp_instance vi_1 {    state master     interface eth0    virtual_router_id 8    priority 100     advert_int 1    authentication {         auth_type pass        auth_pass 1111     }    virtual_ipaddress {         192.168.0.226    }    track_script {          chk_haproxy}}

14. Haproxy configuration file for configuration 192.168.0.225 (keepalived)

global_defs {   notification_email {     [email  protected]   }   notification_email_from [email protected]    smtp_server smtp.163.com   smtp_connect_timeout 30   router_id  LVS_DEVEL}vrrp_script chk_haproxy {script  "Killall -0 haproxy" interval  1weight 2}vrrp_instance vi_1 {    state slave     interface eth0    virtual_router_id 8    priority 99     advert_int 1    authentication {         auth_type pass        auth_pass 1111     }    virtual_ipaddress {         192.168.0.226    }    track_script {          chk_haproxy}}

15. Start Haproxy and keepalived to test the high availability of the Haproxy

Service Haproxy start Service keepalived start

The test procedure is omitted.

16. The root user of the licensed MySQL can log in at any IP address

Mysql-p >grant All privileges on * * to [email protected] '% ' identified by ' 123 ' with Grant Option;>flush privileges;

17. Test log in to see if every time you reconnect to MySQL instance, load balancing effect is achieved.

[[email protected] xinetd.d]# mysql -h  192.168.0.226  -penter password:mariadb [(None)]> show  variables   like   ' wsrep_node_name '; +-----------------+-----------+| variable_name   |  value     |+-----------------+-----------+| wsrep_node_name |  OS6---222 |+-----------------+-----------+1 row in set  (0.00 sec) mariadb [( None)]> exitbye 

[Email protected] xinetd.d]# mysql-h 192.168.0.226-p
Enter Password:
MariaDB [(None)]> show variables like ' wsrep_node_name ';
+-----------------+-----------+
| variable_name | Value |
+-----------------+-----------+
| Wsrep_node_name | OS6---223 |
+-----------------+-----------+
1 row in Set (0.00 sec)
MariaDB [(None)]> exit
Bye

[[email protected] xinetd.d]# mysql-h 192.168.0.226-penter password:mariadb [(none)]> show variables like ' Wsrep_ Node_name '; +-----------------+-----------+| variable_name | Value |+-----------------+-----------+| Wsrep_node_name | OS6---221 |+-----------------+-----------+1 row in Set (0.01 sec)

This article from the "Do not ask for the best, only better" blog, please be sure to keep this source http://yujianglei.blog.51cto.com/7215578/1726752

MariaDB Galera Cluster with HA proxies and keepalived on Cents 6