Miradb+galera+haproxy

Galera Introduction: Galera provides primary master replication for MSYQL/MARIADB, all MSYQL/MARIADB servers are equivalent, without a master-slave relationship. Build Miradb+galera Below

This environment is 2 virtual machines, the operating system is CENTOS7 which:

node1:192.168.8.59

node2:192.168.8.140

1. Turn off firewall and SELinux

2. Configure the Yum source

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

3. Install the following services on 2 hosts respectively

Yum Install rsync nmap lsof perl-dbi nc mariadb-server mariadb-client mariadb-compat galera socat jemalloc

4, start the database service, and security optimization of the database, "mysql_secure_installation" set the password to ' root '

5, modify the configuration file (two service side to change, note wsrep_node_address as the native IP)

Vim /etc/my.cnf.d/server.cnf

#

[Galera]

Wsrep_on=on

Wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_cluster_address= ' gcomm://192.168.8.59,192.168.8.140 '

Wsrep_cluster_name= ' Galera '

wsrep_node_address= ' 192.168.8.140 '

Wsrep_node_name= ' Node1 '

Wsrep_sst_method=rsync

Binlog_format=row

Default_storage_engine=innodb

innodb_autoinc_lock_mode=2

bind-address=0.0.0.0

Wsrep_sst_auth=root:root

4, start the service, view the port

[Email protected] ~]# lsof-i:4567

COMMAND PID USER FD TYPE DEVICE size/off NODE NAME

Mysqld 22162 MySQL 11u IPv4 44908 0t0 TCP *:tram (LISTEN)

Mysqld 22162 MySQL 13u IPv4 44911 0t0 TCP 192.168.8.140:35922->192.168.8.59:tram (established)

[Email protected] ~]# lsof-i:3306

COMMAND PID USER FD TYPE DEVICE size/off NODE NAME

Mysqld 22162 mysql 32u IPv4 45258 0t0 TCP *:mysql (LISTEN)

You can see more than one 4567 of the ports

5. View status

[Email protected] ~]# mysql-u root-p-e "show status Like ' wsrep% '"

Enter Password:

+------------------------------+--------------------------------------+

| variable_name | Value |

+------------------------------+--------------------------------------+

| Wsrep_apply_oooe | 0.000000 |

| Wsrep_apply_oool | 0.000000 |

| Wsrep_apply_window | 0.000000 |

| Wsrep_causal_reads | 0 |

| Wsrep_cert_deps_distance | 0.000000 |

| Wsrep_cert_index_size | 0 |

| Wsrep_cert_interval | 0.000000 |

| wsrep_cluster_conf_id | 2 |

| Wsrep_cluster_size | 2 |

| Wsrep_cluster_state_uuid | a857719c-2667-11e6-8e3f-aa2deb9d92a9 |

| Wsrep_cluster_status | Primary |

| Wsrep_commit_oooe | 0.000000 |

| Wsrep_commit_oool | 0.000000 |

| Wsrep_commit_window | 0.000000 |

| wsrep_connected | On |

|                                      wsrep_evs_delayed | |

|                                      Wsrep_evs_evict_list | |

| wsrep_evs_repl_latency | 0/0/0/0/0 |

| Wsrep_evs_state | Operational |

| wsrep_flow_control_paused | 0.000000 |

| Wsrep_flow_control_paused_ns | 0 |

| Wsrep_flow_control_recv | 0 |

| wsrep_flow_control_sent | 0 |

| Wsrep_gcomm_uuid | 5c7c83d5-2672-11e6-a40c-4ac0f0858b55 |

| wsrep_incoming_addresses | 192.168.8.59:3306,192.168.8.140:3306 |

| wsrep_last_committed | 11 |

| Wsrep_local_bf_aborts | 0 |

| Wsrep_local_cached_downto | 18446744073709551615 |

| Wsrep_local_cert_failures | 0 |

| Wsrep_local_commits | 0 |

| Wsrep_local_index | 0 |

| Wsrep_local_recv_queue | 0 |

| Wsrep_local_recv_queue_avg | 0.166667 |

| Wsrep_local_recv_queue_max | 2 |

| Wsrep_local_recv_queue_min | 0 |

| Wsrep_local_replays | 0 |

| Wsrep_local_send_queue | 0 |

| Wsrep_local_send_queue_avg | 0.000000 |

| Wsrep_local_send_queue_max | 1 |

| Wsrep_local_send_queue_min | 0 |

| Wsrep_local_state | 4 |

| wsrep_local_state_comment | synced |

| Wsrep_local_state_uuid | a857719c-2667-11e6-8e3f-aa2deb9d92a9 |

| wsrep_protocol_version | 7 |

| Wsrep_provider_name | Galera |

| Wsrep_provider_vendor | Codership Oy <[email protected]> |

| wsrep_provider_version | 25.3.15 (r3578) |

| Wsrep_ready | On |

| wsrep_received | 6 |

| Wsrep_received_bytes | 477 |

| Wsrep_repl_data_bytes | 0 |

| Wsrep_repl_keys | 0 |

| Wsrep_repl_keys_bytes | 0 |

| Wsrep_repl_other_bytes | 0 |

| wsrep_replicated | 0 |

| Wsrep_replicated_bytes | 0 |

| Wsrep_thread_count | 2 |

+------------------------------+--------------------------------------+

Look at the status of the main view:

Wsrep_cluster_size 2 #number of nodeswsrep_connected Onwsrep_ready on #It ' s Running,awesome!wsrep_incoming_addresses | This is the status of the connection

You can see that the status is normal

Test!

Create a database on Node1 Erick

MariaDB [(None)]> CREATE database Erick;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect ...

Connection id:16

Current database: * * * NONE * * *

Query OK, 1 row affected (0.06 sec)

MariaDB [(None)]> show databases;

+--------------------+

| Database |

+--------------------+

| Erick |

| Information_schema |

| MySQL |

| Performance_schema |

+--------------------+

4 rows in Set (0.00 sec)

View on Node2, and delete

MariaDB [(None)]> show databases;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect ...

Connection Id:4

Current database: * * * NONE * * *

+--------------------+

| Database |

+--------------------+

| Erick |

| Information_schema |

| MySQL |

| Performance_schema |

+--------------------+

4 rows in Set (0.00 sec)

MariaDB [(None)]>

MariaDB [(None)]> drop database Erick;

Query OK, 0 rows affected (0.14 sec)

MariaDB [(None)]>

See if the database is deleted on Node1

MariaDB [(None)]> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| MySQL |

| Performance_schema |

+--------------------+

3 Rows in Set (0.00 sec)

MariaDB [(None)]>

The experiment is complete!

Reference Source:

http://tunnelix.com/mariadb-galera-cluster-installation/

https://geekdudes.wordpress.com/2015/07/18/setting-up-failover-cluster-for-mariadb-on-centos-7/

############################### #3

Add a haproxy server here to do load balancing, machine for centos6.5

Hostname IP

Erick1 192.168.8.163

Haproxy installation process slightly. Modifying a configuration file

[Email protected] ~]# cat/etc/haproxy/haproxy.cfg

Global

Log 127.0.0.1 local0 Notice

Maxconn 65536

Nbproc 10

Ulimit-n 231097

Tune.ssl.default-dh-param 1024

Daemon

Defaults

Log Global

Mode http

Option Httplog

Option Dontlognull

Option Forwardfor

Retries 3

Option Redispatch

Maxconn 65535

Timeout Connect 5s

Timeout Client 5m

Timeout Server 5m

timeout check 1s

Timeout Http-request 10s

Timeout Http-keep-alive 10s

Listen Stats *:8080

Mode http

Stats enable

Stats URI/

Stats Refresh 15s

Stats Show-node

Stats Show-legends

Stats Hide-version

Listen Mysqld 192.168.8.163:3306

#cookie ServerID Rewrite

Mode TCP

Maxconn 200

Balance Roundrobin

#option Mysql-check user Erick

Server Node1 192.168.8.140:3306 Check Port 3306

Server Node2 192.168.8.59:3306 Check Port 3306

You can then monitor the status on the Web

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/82/1C/wKioL1dL82jzWhLmAAI5HWkCCnE793.png-wh_500x0-wm_3 -wmp_4-s_3228166952.png "title=" 1.png "alt=" Wkiol1dl82jzwhlmaai5hwkccne793.png-wh_50 "/>

You can see the status of MSYQL, even when disconnecting one MSYQL server does not affect the work.

This article is from "Perfect world!" "Blog, be sure to keep this provenance http://shyln.blog.51cto.com/6890594/1784438

Miradb+galera+haproxy