MySQL Load Balancing with HaProxy

Привет,

В работата ми се наложи да правя схема за MySQL Cluster (master-master-master) репликация с HaProxy отпред (MySQL Load Balancing with HaProxy) и в този пост ще опиша подробно как става това.
Ето и сценария:

Използвах 4 Ubuntu 14.04 сървъра :

MySQL Load Balancing with HaProxy

Инсталацията

MySQL cluster-a ни е съставен по следния начин :


node #1
hostname: mysql1
IP: 192.168.188.160

node #2
hostname: mysql2
IP: 192.168.188.161

node #3
hostname: mysql3
IP: 192.168.188.162

Подробно описание как се инсталира Percona XtraDB-Cluster може да се намери тук. Много е важно да се конфигурира предварително AppArmor за да пуска трафика за портове: 3306, 4444, 4567 и 4568. Аз директно го спрях за по-лесно!

Конфигуриране на нодовете

Node 1

Конфигурационният файл се намира в /etc/mysql/my.cnf и за първия нод изглежда по следния начин:

[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]

socket = /var/run/mysqld/mysqld.sock

nice = 0

[mysqld]

user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

lc-messages-dir = /usr/share/mysql

skip-external-locking

server_id=1

# Path to Galera library

wsrep_provider=/usr/lib/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3

wsrep_cluster_address=gcomm://192.168.188.160,192.168.188.161,192.168.188.162

# In order for Galera to work correctly binlog format should be ROW

binlog_format=ROW

# MyISAM storage engine has only experimental support

default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2

# Node #1 address

wsrep_node_address=192.168.188.160

# SST method

wsrep_sst_method=xtrabackup-v2

# Cluster name

wsrep_cluster_name=middlepoint

# Authentication for SST method

wsrep_sst_auth="sstuser:s3cretPass"

#bind-address = 127.0.0.1

key_buffer = 16M

max_allowed_packet = 16M

thread_stack = 192K

thread_cache_size       = 8

# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched

myisam-recover         = BACKUP

query_cache_limit = 1M

query_cache_size        = 16M

log_error = /var/log/mysql/error.log

expire_logs_days = 10

max_binlog_size         = 100M

innodb_buffer_pool_size = 256M

innodb_log_buffer_size  = 8M

innodb_file_per_table   = 1

innodb_open_files       = 400

innodb_io_capacity      = 400

innodb_flush_method     = O_DIRECT

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

[mysql]

[isamchk]

key_buffer = 16M

!includedir /etc/mysql/conf.d/

След това е необходимо да се стартира mysql-a чрез:

root@mysql1:~# /etc/init.d/mysql bootstrap-pxc

Така се извършва така наречения bootstrap на cluster-a – повече инфо тук.

След като стартираме първия MySQL сървър, можем да проверим и статуса на cluster-a чрез:

mysql> show status like 'wsrep%';

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

| Variable_name                | Value                                                          |

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

| wsrep_local_state_uuid       | cf7112d5-f663-11e5-ab0f-db98aa318fae                           |

******

| wsrep_incoming_addresses     | 192.168.188.161:3306,192.168.188.160:3306,192.168.188.162:3306 |

| wsrep_evs_delayed            |                                                                |

| wsrep_evs_evict_list         |                                                                |

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

| wsrep_evs_state              | OPERATIONAL                                                    |

| wsrep_gcomm_uuid             | 3e25b20a-f675-11e5-8ac2-4fcee9ba268c                           |

| wsrep_cluster_conf_id        | 22                                                             |

| wsrep_cluster_size           | 3                                                              |

| wsrep_cluster_state_uuid     | cf7112d5-f663-11e5-ab0f-db98aa318fae                           |

| wsrep_cluster_status         | Primary                                                        |

| wsrep_connected              | ON                                                             |

| wsrep_local_bf_aborts        | 0                                                              |

| wsrep_local_index            | 1                                                              |

| wsrep_provider_name          | Galera                                                         |

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

| wsrep_provider_version       | 3.14(r53b88eb)                                                 |

| wsrep_ready                  | ON                                                             |

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

58 rows in set (0.00 sec)

mysql>

За да работи коректно cluster-a, е нужно да се създаде MySQL потребител със съответните права :

mysql@mysql1> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass';
mysql@mysql1> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
mysql@mysql1> FLUSH PRIVILEGES;

Node 2

Конфигурационният файл се намира в /etc/mysql/my.cnf и за втория нод изглежда по следния начин:

[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]

socket = /var/run/mysqld/mysqld.sock

nice = 0

[mysqld]

#

# * Basic Settings

#

user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

lc-messages-dir = /usr/share/mysql

skip-external-locking

server_id=2

# Path to Galera library

wsrep_provider=/usr/lib/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3

wsrep_cluster_address=gcomm://192.168.188.160,192.168.188.161,192.168.188.162

# In order for Galera to work correctly binlog format should be ROW

binlog_format=ROW

# MyISAM storage engine has only experimental support

default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2

# Node #1 address

wsrep_node_address=192.168.188.161

# SST method

wsrep_sst_method=xtrabackup-v2

# Cluster name

wsrep_cluster_name=middlepoint

# Authentication for SST method

wsrep_sst_auth="sstuser:s3cretPass"

#bind-address = 127.0.0.1

key_buffer = 16M

max_allowed_packet = 16M

thread_stack = 192K

thread_cache_size       = 8

# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched

myisam-recover         = BACKUP

#max_connections        = 100

#table_cache            = 64

#thread_concurrency     = 10

query_cache_limit = 1M

query_cache_size        = 16M

log_error = /var/log/mysql/error.log

expire_logs_days = 10

max_binlog_size         = 100M

innodb_buffer_pool_size = 256M

innodb_log_buffer_size = 8M

innodb_file_per_table = 1

innodb_open_files = 400

innodb_io_capacity = 400

innodb_flush_method = O_DIRECT

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

[mysql]

#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]

key_buffer = 16M

!includedir /etc/mysql/conf.d/

И стартираме MySQL сървъра чрез:

root@mysql2:~# /etc/init.d/mysql start

Node 3

Конфигурационният файл се намира в /etc/mysql/my.cnf и за третия нод изглежда по следния начин:

[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]

socket = /var/run/mysqld/mysqld.sock

nice = 0

[mysqld]

user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

lc-messages-dir = /usr/share/mysql

skip-external-locking

server_id=3

# Path to Galera library

wsrep_provider=/usr/lib/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3

wsrep_cluster_address=gcomm://192.168.188.160,192.168.188.161,192.168.188.162

# In order for Galera to work correctly binlog format should be ROW

binlog_format=ROW

# MyISAM storage engine has only experimental support

default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2

# Node #1 address

wsrep_node_address=192.168.188.162

# SST method

wsrep_sst_method=xtrabackup-v2

# Cluster name

wsrep_cluster_name=middlepoint

# Authentication for SST method

wsrep_sst_auth="sstuser:s3cretPass"

#bind-address = 127.0.0.1

key_buffer = 16M

max_allowed_packet = 16M

thread_stack = 192K

thread_cache_size       = 8

myisam-recover         = BACKUP

query_cache_limit = 1M

query_cache_size        = 16M

log_error = /var/log/mysql/error.log

expire_logs_days = 10

max_binlog_size         = 100M

innodb_buffer_pool_size = 256M

innodb_log_buffer_size = 8M

innodb_file_per_table = 1

innodb_open_files = 400

innodb_io_capacity = 400

innodb_flush_method = O_DIRECT

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

[mysql]

[isamchk]

key_buffer = 16M

!includedir /etc/mysql/conf.d/

И стартираме MySQL сървъра чрез:

root@mysql3:~# /etc/init.d/mysql start

Тестване на репликацията

Тук подробно е описано как може да се направи тест-а. Накратко идеята е да създадем база в единия нод, да създадем таблица в другия и да вкараме някакви данни в третия. После да видим дали и в трите тази информация фигурира.
Ако всичко в конфигурирането е изпъленено както съм го написал по-горе, значи няма да има проблеми !

Нещо много важно!

Ако и трите сървъра паднат едновременно и след това тръгнат едновременно е нужно да се изпълни bootstrap-а отново. В противен случай MySQL процесите няма да се стартират сами. 

Иначе спирането и пускането на MySQL става по стандартния начин и на 3те нода.

Инсталиране и конфигуриране на HaProxy

Използвал съм последната стабилна версия на HaProxy – 1.6. За ubuntu 14.04 инсталацията му става по следния начин:

apt-get install software-properties-common
add-apt-repository ppa:vbernat/haproxy-1.6
apt-get update
apt-get install haproxy

А конфигурационният файл на haproxy се намира в /etc/haproxy/haproxy.cfg и изглежда по следния начин:

global
log 127.0.0.1 local0 notice
user haproxy
group haproxy

defaults
log global
retries 2
timeout connect 3000
timeout server 5000
timeout client 5000

listen mysql-cluster
bind 0.0.0.0:3306
mode tcp
balance roundrobin
option httpchk

server mysql01 192.168.188.160:3306 check port 9200 inter 12000 rise 3 fall 3
server mysql02 192.168.188.161:3306 check port 9200 inter 12000 rise 3 fall 3
server mysql03 192.168.188.162:3306 check port 9200 inter 12000 rise 3 fall 3

listen haproxy-web
bind 0.0.0.0:8080
mode http
stats enable
stats uri /
stats realm Strictly\ Private
stats auth admin:admin
stats refresh 5s

Следва стартиране на haproxy процеса :

/etc/init.d/haproxy start

Тестване на clustera

За да работи коректно cluster-a е нужно едно последно нещо:

И на трите MySQL сървъра трябва да сложим един демон, които ще казва на haproxy дали MySQL процеса ни работи коректно или не.

За целта трябва да инсталираме xinetd:

apt-get install xinetd

И да създадем /etc/xinetd.d/mysqlchk :

service mysqlchk
{
# this is a config for xinetd, place it in /etc/xinetd.d/
disable = no
flags = REUSE
socket_type = stream
type = UNLISTED
port = 9200
wait = no
user = nobody
server = /usr/local/bin/clustercheck
log_on_failure += USERID
only_from = 0.0.0.0/0
#
# Passing arguments to clustercheck
# <available_when_donor=0|1> <available_when_readonly=0|1> "
# Recommended: server_args = user pass 1 /var/log/log-file 0 /etc/my.cnf.local"
# Compatibility: server_args = user pass 1 /var/log/log-file 1 /etc/my.cnf.local"
# 55-to-56 upgrade: server_args = user pass 1 /var/log/log-file 0 /etc/my.cnf.extra"
#
# recommended to put the IPs that need
# to connect exclusively (security purposes)
per_source = UNLIMITED
}

И най-важното , трябва да създадем /usr/local/bin/clustercheck  и да му сетнем след това chmod +x :

#!/bin/bash
#
# This script checks if a mysql server is healthy running on localhost. It will
# return:
#
# "HTTP/1.x 200 OK\r" (if mysql is running smoothly)
#
# - OR -
#
# "HTTP/1.x 500 Internal Server Error\r" (else)
#
# The purpose of this script is make haproxy capable of monitoring mysql properly
#
# Author: Unai Rodriguez
#
# It is recommended that a low-privileged-mysql user is created to be used by
# this script. Something like this:
#
# mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' \
# -> IDENTIFIED BY '257retfg2uysg218' WITH GRANT OPTION;
# mysql> flush privileges;

MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="mysqlchkusr"
MYSQL_PASSWORD="257retfg2uysg218"

TMP_FILE="/tmp/mysqlchk.out"
ERR_FILE="/tmp/mysqlchk.err"

WSSREP_STATUS=`/usr/bin/mysql --login-path=/root/.mysql -e "show status like 'wsrep_local_state';" | awk '{if (NR!=1){print $2}}' 2>/dev/null`

#
# Check the galera cluster consistent on node, your solution still
#allow connect to node even if cluster is desynced but mysql hear on
# 3306
#
#
if [ "$WSSREP_STATUS" == "4" ]
then
# mysql is fine, return http 200
/bin/echo -e "HTTP/1.1 200 OK\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "MySQL is running.\r\n"
/bin/echo -e "\r\n"
else
# mysql is down, return http 503
/bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "MySQL is *down*.\r\n"
/bin/echo -e "\r\n"
fi

Следват и две много важни стъпки!

  • Създаваме потребител mysqlchkusr
  • Използваме mysql_config_editor за да съхраним паролата за потребителя mysqlchkusr (ново изискване в MySQL 5.6) – Иначе ще имаме следният проблем : Warning: Using a password on the command line interface can be insecure.

Стъпките са следните :

mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost'  IDENTIFIED BY '257retfg2uysg218' WITH GRANT OPTION;

mysql> flush privileges;

root@mysql1:˜# mysql_config_editor set --login-path=/root/.mysql --host=localhost --use=mysqlchkusr --password

Последното трябва да се изпълни и на трите нода!

След това рестартираме xinetd: /etc/init.d/xinetd restart и воала! Ако се логнем на 8080 порт на HaProxy ще видим следното :

haproxy stats

И трите нода работят!

Заключение

Плюсовете на тази схема са:

  • Забравяме за грешките в стандартната репликация, out of sync и всички останали проблеми, които редовно се случват при MySQL репликацията.
  • При отпадана не нод и евентуалното му връщане след определено време, не се занимаваме с dump-ване на база, наливане и синхронизиране с другия сървър – всичко става автоматично!
  • Паралелна репликация

Има и минуси :

  • Поддържа се само InnoBD – В документацията пише че тестове има пусната поддръжка за MyISAM таблици но не се препоръчва използването им!
  • Репликирането на mysql.* таблици също не се случва – тоест трябва да се създават и редактират потребители ръчно и всеки един от нодовете. (Това в моят случай обаче работи – създадох си потребител и той се репликира навсякъде) – Повече инфо тук.

mysql cluster

Общо взето това е! 🙂