Virtualización JDM: Instalación y Configuración de MariaDB con Galera-Cluster y HAproxy

Creando Cluster con Mariadb Galera Cluster 5.5 - 2 nodos

MariaDB galera cluster 5.5 en Centos 7 64bts

Vamos a instalar en 2 servidores
nodo1 = 100.4.6.18
nodo2 = 100.4.6.19

Primero instalamos el repositorio en /etc/yum.repos.d/mariadb.repo (Esto se debe realizar el cada nodo)
nano /etc/yum.repos.d/mariadb.repo
y pegamos el siguiente código:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5-galera/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Deshabilitar Selinux (Esto se debe realizar el cada nodo)

sed -i 's/enforcing/disabled/g' /etc/selinux/config /etc/selinux/config

Deshabilitar firewall (firewalld) (Esto se debe realizar el cada nodo)

systemctl disable firewalld

Comenzaremos por instalar Epel-release package y actualizando el sistema

rpm --import /etc/pki/rpm-gpg/RPM-GPG*

yum install -y epel-release

yum update –y

yum remove mariadb-libs -y

Instalamos MariaDB galera server, el cliente de mariaDB y galera, si teníamos una instalación de mariadb o mysql tenemos que remover la antes de instalar.

yum install MariaDB-Galera-server MariaDB-client galera

Iniciamos el servicio de MariaDB

service mysql start
Starting MySQL.... SUCCESS!

Se realiza cambio de contraseña y actualización de MariaDB

mysql_secure_installation

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Detenemos mysql y modificamos el archivo que se encuentra en /etc/my.cnf.d/server.cnf

nano /etc/my.cnf.d/server.cnf

Agregamos lo siguiente (Esto lo aplicaremos en el nodo1 - 100.4.6.18)

[mariadb]
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://100.4.6.19
wsrep_cluster_name='cluster1'
wsrep_node_address='100.4.6.18'
wsrep_node_name='nodo1'
wsrep_sst_method=rsync
wsrep_sst_auth=root:Password

Guardamos y repetimos los pasos para nodo 2 - 100.4.6.19

La configuración del nodo2 debería quedar así:

[mariadb]
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://100.4.6.18
wsrep_cluster_name='cluster1'
wsrep_node_address='100.4.6.19'
wsrep_node_name='nodo2'
wsrep_sst_method=rsync
wsrep_sst_auth=root:Password

Una vez instalado y configurado tenemos que bajar los servicios de mysql en los 2 nodos

service mysql stop

Ahora tenemos que iniciar los servicios mysql

Iniciamos el nodo1 con

service mysql bootstrap - Esto indica que es el nodo principal

El resto de los nodos los iniciamos con:

service mysql start

Luego, si cae un nodo cualquiera se inicia con:

service mysql start.

En caso de que se caigan o apaguen todos los nodos tendríamos que iniciar primero el último nodo que se apagó de la siguiente manera

nano /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid: 26d64af8-0115-11e8-b5a3-6b80cc16ab22

seqno: -1

safe_to_bootstrap: 1

service mysql bootstrap

El resto con

service mysql start

El password del usuario root en todos los nodos tiene que ser el mismo

Ahora para probar en alguno de los nodos conectamos a la consola de mysql

mysql -u root -p

Ingresamos el password del root y nos muestra:

Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.49-MariaDB-wsrep MariaDB Server, wsrep_25.14.r9949137

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

si escribimos la orden SHOW STATUS LIKE 'wsrep_%';

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_%';

Así debe Mostrarse la conexión

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

| Variable_name | Value |

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

| wsrep_local_state_uuid | 26d64af8-0115-11e8-b5a3-6b80cc16ab22 |

| wsrep_protocol_version | 7 |

| wsrep_last_committed | 359 |

| wsrep_replicated | 0 |

| wsrep_replicated_bytes | 0 |

| wsrep_repl_keys | 0 |

| wsrep_repl_keys_bytes | 0 |

| wsrep_repl_data_bytes | 0 |

| wsrep_repl_other_bytes | 0 |

| wsrep_received | 3 |

| wsrep_received_bytes | 343 |

| wsrep_local_commits | 0 |

| wsrep_local_cert_failures | 0 |

| wsrep_local_replays | 0 |

| wsrep_local_send_queue | 0 |

| wsrep_local_send_queue_max | 1 |

| wsrep_local_send_queue_min | 0 |

| wsrep_local_send_queue_avg | 0.000000 |

| wsrep_local_recv_queue | 0 |

| wsrep_local_recv_queue_max | 2 |

| wsrep_local_recv_queue_min | 0 |

| wsrep_local_recv_queue_avg | 0.333333 |

| wsrep_local_cached_downto | 18446744073709551615 |

| wsrep_flow_control_paused_ns | 0 |

| wsrep_flow_control_paused | 0.000000 |

| wsrep_flow_control_sent | 0 |

| wsrep_flow_control_recv | 0 |

| wsrep_cert_deps_distance | 0.000000 |

| wsrep_apply_oooe | 0.000000 |

| wsrep_apply_oool | 0.000000 |

| wsrep_apply_window | 0.000000 |

| wsrep_commit_oooe | 0.000000 |

| wsrep_commit_oool | 0.000000 |

| wsrep_commit_window | 0.000000 |

| wsrep_local_state | 4 |

| wsrep_local_state_comment | Synced |

| wsrep_cert_index_size | 0 |

| wsrep_causal_reads | 0 |

| wsrep_cert_interval | 0.000000 |

| wsrep_incoming_addresses | 100.4.6.18:3306,100.4.6.19:3306 |

| wsrep_desync_count | 0 |

| wsrep_evs_delayed | |

| wsrep_evs_evict_list | |

| wsrep_evs_repl_latency | 0.000179983/0.000280134/0.000420615/0.000100374 /4 |

| wsrep_evs_state | OPERATIONAL |

| wsrep_gcomm_uuid | d395ad92-0108-11e8-902c-dba0c0bb7787 |

| wsrep_cluster_conf_id | 2 |

| wsrep_cluster_size | 2 |

| wsrep_cluster_state_uuid | 26d64af8-0115-11e8-b5a3-6b80cc16ab22 |

| wsrep_cluster_status | Primary |

| wsrep_connected | ON |

| wsrep_local_bf_aborts | 0 |

| wsrep_local_index | 0 |

| wsrep_provider_name | Galera |

| wsrep_provider_vendor | Codership Oy <info@codership.com> |

| wsrep_provider_version | 25.3.22(r3764) |

| wsrep_ready | ON |

| wsrep_thread_count | 2 |

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

58 rows in set (0.00 sec)

wsrep_incoming_addresses : 100.4.6.18:3306,100.4.6.19:3306

wsrep_cluster_size: 2

Como se puede ver en las dos variables indican las ip conectadas y en cluster size la cantidad de nodos

------------------------------------------------------------------------------------------------------------------------

Balanceador – HAproxy 100.4.6.17

Primero instalamos el repositorio en /etc/yum.repos.d/mariadb.repo (Esto se debe realizar para la instalación del cliente de MariaDB)
nano /etc/yum.repos.d/mariadb.repo
y pegamos el siguiente código:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5-galera/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Deshabilitar Selinux (Esto se debe realizar el cada nodo)

sed -i 's/enforcing/disabled/g' /etc/selinux/config /etc/selinux/config

Deshabilitar firewall (firewalld) (Esto se debe realizar el cada nodo)

systemctl disable firewalld

init 6

Ejecutar

rpm --import /etc/pki/rpm-gpg/RPM-GPG*

yum update –y

yum install haproxy

Ingresar en el Nodo1 de MariaDB 100.4.6.18

Ingresar a la ruta

cd /usr/bin

nano clustercheck

Debes pegar el siguiente script

#!/bin/bash
	#
	# Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly
	#
	# Author: Olaf van Zandwijk <olaf.vanzandwijk@nedap.com>
	# Author: Raghavendra Prabhu <raghavendra.prabhu@percona.com>
	#
	# Documentation and download: https://github.com/olafz/percona-clustercheck
	#
	# Based on the original script from Unai Rodriguez
	#

	if [[ $1 == '-h' \|\| $1 == '--help' ]];then
	echo "Usage: $0 <user> <pass> <available_when_donor=0\|1> <log_file> <available_when_readonly=0\|1> <defaults_extra_file>"
	exit
	fi

	# if the disabled file is present, return 503. This allows
	# admins to manually remove a node from a cluster easily.
	if [ -e "/var/tmp/clustercheck.disabled" ]; then
	# Shell return-code is 1
	echo -en "HTTP/1.1 503 Service Unavailable\r\n"
	echo -en "Content-Type: text/plain\r\n"
	echo -en "Connection: close\r\n"
	echo -en "Content-Length: 51\r\n"
	echo -en "\r\n"
	echo -en "Percona XtraDB Cluster Node is manually disabled.\r\n"
	sleep 0.1
	exit 1
	fi

	MYSQL_USERNAME="${1-clustercheckuser}"
	MYSQL_PASSWORD="${2-clustercheckpassword!}"
	AVAILABLE_WHEN_DONOR=${3:-0}
	ERR_FILE="${4:-/dev/null}"
	AVAILABLE_WHEN_READONLY=${5:-1}
	DEFAULTS_EXTRA_FILE=${6:-/etc/my.cnf}

	#Timeout exists for instances where mysqld may be hung
	TIMEOUT=10

	EXTRA_ARGS=""
	if [[ -n "$MYSQL_USERNAME" ]]; then
	EXTRA_ARGS="$EXTRA_ARGS --user=${MYSQL_USERNAME}"
	fi
	if [[ -n "$MYSQL_PASSWORD" ]]; then
	EXTRA_ARGS="$EXTRA_ARGS --password=${MYSQL_PASSWORD}"
	fi
	if [[ -r $DEFAULTS_EXTRA_FILE ]];then
	MYSQL_CMDLINE="mysql --defaults-extra-file=$DEFAULTS_EXTRA_FILE -nNE --connect-timeout=$TIMEOUT \
	${EXTRA_ARGS}"
	else
	MYSQL_CMDLINE="mysql -nNE --connect-timeout=$TIMEOUT ${EXTRA_ARGS}"
	fi
	#
	# Perform the query to check the wsrep_local_state
	#
	WSREP_STATUS=$($MYSQL_CMDLINE -e "SHOW STATUS LIKE 'wsrep_local_state';" \
	2>${ERR_FILE} \| tail -1 2>>${ERR_FILE})

	if [[ "${WSREP_STATUS}" == "4" ]] \|\| [[ "${WSREP_STATUS}" == "2" && ${AVAILABLE_WHEN_DONOR} == 1 ]]
	then
	# Check only when set to 0 to avoid latency in response.
	if [[ $AVAILABLE_WHEN_READONLY -eq 0 ]];then
	READ_ONLY=$($MYSQL_CMDLINE -e "SHOW GLOBAL VARIABLES LIKE 'read_only';" \
	2>${ERR_FILE} \| tail -1 2>>${ERR_FILE})

	if [[ "${READ_ONLY}" == "ON" ]];then
	# Percona XtraDB Cluster node local state is 'Synced', but it is in
	# read-only mode. The variable AVAILABLE_WHEN_READONLY is set to 0.
	# => return HTTP 503
	# Shell return-code is 1
	echo -en "HTTP/1.1 503 Service Unavailable\r\n"
	echo -en "Content-Type: text/plain\r\n"
	echo -en "Connection: close\r\n"
	echo -en "Content-Length: 43\r\n"
	echo -en "\r\n"
	echo -en "Percona XtraDB Cluster Node is read-only.\r\n"
	sleep 0.1
	exit 1
	fi
	fi
	# Percona XtraDB Cluster node local state is 'Synced' => return HTTP 200
	# Shell return-code is 0
	echo -en "HTTP/1.1 200 OK\r\n"
	echo -en "Content-Type: text/plain\r\n"
	echo -en "Connection: close\r\n"
	echo -en "Content-Length: 40\r\n"
	echo -en "\r\n"
	echo -en "Percona XtraDB Cluster Node is synced.\r\n"
	sleep 0.1
	exit 0
	else
	# Percona XtraDB Cluster node local state is not 'Synced' => return HTTP 503
	# Shell return-code is 1
	echo -en "HTTP/1.1 503 Service Unavailable\r\n"
	echo -en "Content-Type: text/plain\r\n"
	echo -en "Connection: close\r\n"
	echo -en "Content-Length: 44\r\n"
	echo -en "\r\n"
	echo -en "Percona XtraDB Cluster Node is not synced.\r\n"
	sleep 0.1
	exit 1
	fi

Guarda el archivo

chmod 755 clustercheck

Ingresar en el Nodo2 de MariaDB 100.4.6.19

Ingresar a la ruta

cd /usr/bin

nano clustercheck

Debes pegar el siguiente script

#!/bin/bash
	#
	# Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly
	#
	# Author: Olaf van Zandwijk <olaf.vanzandwijk@nedap.com>
	# Author: Raghavendra Prabhu <raghavendra.prabhu@percona.com>
	#
	# Documentation and download: https://github.com/olafz/percona-clustercheck
	#
	# Based on the original script from Unai Rodriguez
	#

	if [[ $1 == '-h' \|\| $1 == '--help' ]];then
	echo "Usage: $0 <user> <pass> <available_when_donor=0\|1> <log_file> <available_when_readonly=0\|1> <defaults_extra_file>"
	exit
	fi

	# if the disabled file is present, return 503. This allows
	# admins to manually remove a node from a cluster easily.
	if [ -e "/var/tmp/clustercheck.disabled" ]; then
	# Shell return-code is 1
	echo -en "HTTP/1.1 503 Service Unavailable\r\n"
	echo -en "Content-Type: text/plain\r\n"
	echo -en "Connection: close\r\n"
	echo -en "Content-Length: 51\r\n"
	echo -en "\r\n"
	echo -en "Percona XtraDB Cluster Node is manually disabled.\r\n"
	sleep 0.1
	exit 1
	fi

	MYSQL_USERNAME="${1-clustercheckuser}"
	MYSQL_PASSWORD="${2-clustercheckpassword!}"
	AVAILABLE_WHEN_DONOR=${3:-0}
	ERR_FILE="${4:-/dev/null}"
	AVAILABLE_WHEN_READONLY=${5:-1}
	DEFAULTS_EXTRA_FILE=${6:-/etc/my.cnf}

	#Timeout exists for instances where mysqld may be hung
	TIMEOUT=10

	EXTRA_ARGS=""
	if [[ -n "$MYSQL_USERNAME" ]]; then
	EXTRA_ARGS="$EXTRA_ARGS --user=${MYSQL_USERNAME}"
	fi
	if [[ -n "$MYSQL_PASSWORD" ]]; then
	EXTRA_ARGS="$EXTRA_ARGS --password=${MYSQL_PASSWORD}"
	fi
	if [[ -r $DEFAULTS_EXTRA_FILE ]];then
	MYSQL_CMDLINE="mysql --defaults-extra-file=$DEFAULTS_EXTRA_FILE -nNE --connect-timeout=$TIMEOUT \
	${EXTRA_ARGS}"
	else
	MYSQL_CMDLINE="mysql -nNE --connect-timeout=$TIMEOUT ${EXTRA_ARGS}"
	fi
	#
	# Perform the query to check the wsrep_local_state
	#
	WSREP_STATUS=$($MYSQL_CMDLINE -e "SHOW STATUS LIKE 'wsrep_local_state';" \
	2>${ERR_FILE} \| tail -1 2>>${ERR_FILE})

	if [[ "${WSREP_STATUS}" == "4" ]] \|\| [[ "${WSREP_STATUS}" == "2" && ${AVAILABLE_WHEN_DONOR} == 1 ]]
	then
	# Check only when set to 0 to avoid latency in response.
	if [[ $AVAILABLE_WHEN_READONLY -eq 0 ]];then
	READ_ONLY=$($MYSQL_CMDLINE -e "SHOW GLOBAL VARIABLES LIKE 'read_only';" \
	2>${ERR_FILE} \| tail -1 2>>${ERR_FILE})

	if [[ "${READ_ONLY}" == "ON" ]];then
	# Percona XtraDB Cluster node local state is 'Synced', but it is in
	# read-only mode. The variable AVAILABLE_WHEN_READONLY is set to 0.
	# => return HTTP 503
	# Shell return-code is 1
	echo -en "HTTP/1.1 503 Service Unavailable\r\n"
	echo -en "Content-Type: text/plain\r\n"
	echo -en "Connection: close\r\n"
	echo -en "Content-Length: 43\r\n"
	echo -en "\r\n"
	echo -en "Percona XtraDB Cluster Node is read-only.\r\n"
	sleep 0.1
	exit 1
	fi
	fi
	# Percona XtraDB Cluster node local state is 'Synced' => return HTTP 200
	# Shell return-code is 0
	echo -en "HTTP/1.1 200 OK\r\n"
	echo -en "Content-Type: text/plain\r\n"
	echo -en "Connection: close\r\n"
	echo -en "Content-Length: 40\r\n"
	echo -en "\r\n"
	echo -en "Percona XtraDB Cluster Node is synced.\r\n"
	sleep 0.1
	exit 0
	else
	# Percona XtraDB Cluster node local state is not 'Synced' => return HTTP 503
	# Shell return-code is 1
	echo -en "HTTP/1.1 503 Service Unavailable\r\n"
	echo -en "Content-Type: text/plain\r\n"
	echo -en "Connection: close\r\n"
	echo -en "Content-Length: 44\r\n"
	echo -en "\r\n"
	echo -en "Percona XtraDB Cluster Node is not synced.\r\n"
	sleep 0.1
	exit 1
	fi

Guarda el archivo

chmod 755 clustercheck

Ingresar en el Nodo1 de MariaDB 100.4.6.18

mysql -u root -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 9

Server version: 5.5.36-MariaDB-wsrep MariaDB Server, wsrep_25.9.r3961

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user, host from mysql.user where user = 'clustercheckuser';

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

| user | host |

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

| clustercheckuser | localhost |

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

1 row in set (0.00 sec)

Ingresar en el Nodo2 de MariaDB 100.4.6.19

mysql -u root -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 9

Server version: 5.5.36-MariaDB-wsrep MariaDB Server, wsrep_25.9.r3961

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user, host from mysql.user where user = 'clustercheckuser';

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

| user | host |

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

| clustercheckuser | localhost |

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

1 row in set (0.00 sec)

Ingresar en el Nodo1 de MariaDB 100.4.6.18

yum install xinetd

nano /etc/xinetd.d/mysqlchk

Debes pegar esto:

# default: on

# description: mysqlchk

service mysqlchk

{

disable = no

flags = REUSE

socket_type = stream

port = 9200

wait = no

user = nobody

server = /usr/bin/clustercheck

log_on_failure += USERID

only_from = 0.0.0.0/0

per_source = UNLIMITED

}

Guardar el Archivo

nano /etc/services

Comentar

# wap-wsp 9200/tcp # WAP connectionless session service

# wap-wsp 9200/udp # WAP connectionless session service

Y agregar lo siguiente al archivo

mysqlchk 9200/tcp # Galera Clustercheck

Guardar el Archivo

Iniciar servicio xinetd

service xinetd start

Test de Prueba

/usr/bin/clustercheck

HTTP/1.1 200 OK

Content-Type: text/plain

Connection: close

Content-Length: 40

Percona XtraDB Cluster Node is synced.

Ingresar en el Nodo2 de MariaDB 100.4.6.19

yum install xinetd

nano /etc/xinetd.d/mysqlchk

Debes pegar esto:

# default: on

# description: mysqlchk

service mysqlchk

{

disable = no

flags = REUSE

socket_type = stream

port = 9200

wait = no

user = nobody

server = /usr/bin/clustercheck

log_on_failure += USERID

only_from = 0.0.0.0/0

per_source = UNLIMITED

}

Guardar el Archivo

nano /etc/services

Comentar

# wap-wsp 9200/tcp # WAP connectionless session service

# wap-wsp 9200/udp # WAP connectionless session service

Y agregar lo siguiente al archivo

mysqlchk 9200/tcp # Galera Clustercheck

Guardar el Archivo

Iniciar servicio xinetd

service xinetd start

Test de Prueba

/usr/bin/clustercheck

HTTP/1.1 200 OK

Content-Type: text/plain

Connection: close

Content-Length: 40

Percona XtraDB Cluster Node is synced.

Ingresar al servidor HAproxy 100.4.6.17

cp /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg_old

nano /etc/haproxy/haproxy.cfg

Borrar todo lo que este dentro del archivo

Pegar lo siguiente:

global

log 127.0.0.1 local0

log 127.0.0.1 local1 notice

maxconn 1024

user haproxy

group haproxy

daemon

defaults

log global

mode http

option tcplog

option dontlognull

retries 3

option redispatch

maxconn 1024

timeout connect 5000ms

timeout client 50000ms

timeout server 50000ms

listen mariadb_cluster_writes 0.0.0.0:13304

## A failover pool for writes to ensure writes only hit one node at a time.

mode tcp

option httpchk

server MariaDB01 100.4.6.18:3306 check port 9200

server MariaDB02 100.4.6.19:3306 check port 9200 backup

listen mariadb_cluster_reads 0.0.0.0:13305

## A load-balanced pool for reads to utilize all nodes for reads.

mode tcp

balance leastconn

option httpchk

server MariaDB01 100.4.6.18:3306 check port 9200

server MariaDB02 100.4.6.19:3306 check port 9200

listen stats 0.0.0.0:9000

## HAProxy stats web gui.

mode http

stats enable

stats uri /haproxy_stats

stats realm HAProxy\ Statistics

stats auth haproxy:haproxy

stats admin if TRUE

Guardar archivo

service haproxy start

Ingesar a la siguiente URL: http://100.4.6.17:9000/haproxy_stats

Inicie sesión con el usuario haproxy y la contraseña haproxy (configurable en haproxy.cfg). Deberías ver tres cuadros; mariadb_cluster_writes, mariadb_cluster_reads y stats. Aviso en el grupo de escritura, solo un nodo está activo (verde). Los otros son azules y figuran como copias de seguridad. En el grupo de lectura, los tres nodos están activos.

El tráfico MariaDB / MySQL ahora se puede enrutar a través del equilibrador de carga HAProxy.

Las conexiones enviadas al puerto 13305 se conectarán al nodo con la menor cantidad de conexiones para equilibrar la carga de los SELECT en todos los nodos disponibles.

mysql -u root -p -h 100.4.6.17 -P 13305 -e "SELECT @@hostname"

Enter password:

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

| @@hostname |

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

| MariaDB01 |

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

mysql -u root -p -h 100.4.6.17 -P 13305 -e "SELECT @@hostname"

Enter password:

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

| @@hostname |

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

| MariaDB02 |

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

Las conexiones enviadas al puerto 13304 solo se conectarán a un nodo a la vez, asegurando que las escrituras solo sucedan en un nodo para evitar interbloqueos y otros posibles problemas. En esta configuración, las escrituras siempre se enviarán al nodo 1 si está activo.

mysql -u root -p -h 100.4.6.17 -P 13304 -e "SELECT @@hostname"

Enter password:

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

| @@hostname |

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

| MariaDB01 |

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

mysql -u root -p -h 100.4.6.17 -P 13304 -e "SELECT @@hostname"

Enter password:

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

| @@hostname |

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

| MariaDB01 |

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

FIN - Jose Montilva

Virtualización JDM

jueves, 6 de diciembre de 2018

Instalación y Configuración de MariaDB con Galera-Cluster y HAproxy

No hay comentarios:

Publicar un comentario

Datos personales