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
Copyright (c) 2000, 2014, Oracle, Monty
Program Ab and others.
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
Copyright (c) 2000, 2014, Oracle, Monty
Program Ab and others.
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
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
