MariaDB10 Galera Cluster

MariaDB10 Galera Cluster

参考文档:

https://mariadb.com/kb/en/mariadb/getting-started-with-mariadb-galera-cluster/

https://mariadb.com/kb/en/mariadb/galera-load-balancer/

http://galeracluster.com/documentation-webpages/?id=mysql_galera_configuration

http://galeracluster.com/documentation-webpages/configuration.html

http://galeracluster.com/documentation-webpages/galeraparameters.html

http://docs.pivotal.io/p-mysql/bootstrapping.html

How to Setup MariaDB Galera Cluster 10.0 on CentOS/RedHat & Fedora

主要功能:

同步复制
真正的multi-master，即所有节点可以同时读写数据库
自动的节点成员控制，失效节点自动被清除
新节点加入数据自动复制
真正的并行复制，行级
用户可以直接连接集群，使用感受上与MySQL完全一致

优势:
因为是多主，所以不存在Slavelag(延迟)
不存在丢失交易的情况
同时具有读和写的扩展能力
更小的客户端延迟
节点间数据是同步的,而Master/Slave模式是异步的,不同slave上的binlog可能是不同的

环境:

CentOS 7.1 x64

mariadb-galera-10.0.24

galera-25.3.14

说明:MariaDB Galera Cluster目前仅支持linux平台，mariadb-10.1.13虽然直接将wsrep API库内嵌到了mariadb，但实际测试，集群无法配置成功，官方说明也模棱两可，看之后再进一步测试

防止脑裂，Galera Cluster最少需要三节点

db00: 192.168.8.100

db01: 192.168.8.101

db02: 192.168.8.102

http://galeracluster.com/documentation-webpages/installmariadbsrc.html

一.安装

请参看

Mariadb-Galera10二进制包安装

二.配置集群

1.创建集群用户(所有节点)

systemctl start mysql

mysql_secure_installation

GRANT ALL ON *.* TO 'galera'@'192.168.8.%' IDENTIFIED BY 'mypassword';

FLUSH PRIVILEGES;

说明：在选用的初始化节点上创建该用户即可，其它节点加入集群后会自动同步

https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_on

2.定义集群通用配置(所有节点)

cat >/etc/my.cnf.d/galera_common.cnf <<HERE

[mysqld]

wsrep-cluster-name = "test_cluster"

wsrep-provider = /usr/lib64/galera/libgalera_smm.so

wsrep-provider-options = "gcache.size=256M;gcache.page_size=128M"

wsrep-sst-auth = "galera:mypassword"

binlog-format = row

default-storage-engine = InnoDB

innodb-doublewrite = 1

innodb-autoinc-lock-mode = 2

innodb-flush-log-at-trx-commit = 2

innodb-locks-unsafe-for-binlog = 1

HERE

sed -i '1i !includedir /etc/my.cnf.d/' /etc/my.cnf

提示:配置集群的最简配置可以参看/opt/mariadb/support-files/wsrep.cnf，且有相关解释，非常人性化

# Options that need to be customized:

# - wsrep_provider

# - wsrep_cluster_address

# - wsrep_sst_auth

# The rest of defaults should work out of the box.

3.初始化集群(db00)

db00: 192.168.8.100

cat >/etc/my.cnf.d/galera_db00.cnf <<HERE

[mysqld]

wsrep-node-name = "db00"

wsrep-sst-receive-address = 192.168.8.100

wsrep-node-incoming-address = 192.168.8.100

bind-address = 192.168.8.100

wsrep_sst_method = rsync

wsrep-cluster-address = gcomm://192.168.8.101,192.168.8.102

HERE

yum -y install lsof rsync

/etc/init.d/mysql stop

/etc/init.d/mysql start --wsrep-new-cluster

SHOW STATUS LIKE 'wsrep%';

[root@db00 ~]# /etc/init.d/mysql stop

Shutting down MySQL.. SUCCESS!

[root@db00 ~]# /etc/init.d/mysql start --wsrep-new-cluster

Starting MySQL.. SUCCESS!

说明:用哪个节点来初始化无所谓，早期的版本mariadb-galera-10.0.x初始化,是用空值gcomm://来表示新的集群，mariadb-10.1.x版本已经废止了，由wsrep-new-cluster参数取代

`wsrep_sst_method`

Description: Method used for taking the state snapshot transfer (sst). The command wsrep_sst_will then be called, so a custom script can be written for this purpose. See Codership Scriptable State Snapshop page for details. Existing valid values include rsync (the default), mysqldump, xtrabackup and xtrabackup-v2, which use those respective methods to perform the state snapshop transfer, or skip. skip skips the transfer and should only be used temporarily, for example when starting the cluster and manually restoring data. See also the mysqldump and Xtrabackup articles. Note that the rsync and mysqldump SST methods support GTID. However, the xtrabackup-v2and xtrabackup SST methods currently do not support GTID. The xtrabackup-v2 sst method was introduced in MariaDB Galera 5.5.37 and 10.0.10.
Scope: Global
Dynamic: Yes
Default Value: rsync
Valid Values: rsync, mysqldump, xtrabackup, xtrabackup-v2

提示:rsync(需要安装rsync,lsof), rsync,mysqldump都会有READ LOCK的情况，xtrabackup-v2没有，但也有若干限制，请根据实际业务来选定或调整同步方式

xtrabackup安装请参看MariaDB备份还原修复

实测，开始是以rsync方式同步的集群，后改为xtrabackup-v2，非常顺畅，可以无缝切换同步方式

4.配置并启动其余集群节点(db01,db02)

db01: 192.168.8.101

cat >/etc/my.cnf.d/galera_db01.cnf <<HERE

[mysqld]

wsrep-node-name = "db01"

wsrep-sst-receive-address = 192.168.8.101

wsrep-node-incoming-address = 192.168.8.101

bind-address = 192.168.8.101

wsrep-sst-method = rsync

wsrep-cluster-address = gcomm://192.168.8.100,192.168.8.102

HERE

yum -y install lsof rsync

/etc/init.d/mysql restart

[root@db01 ~]# /etc/init.d/mysql restart

Shutting down MySQL... SUCCESS!

Starting MySQL..SST in progress, setting sleep higher. SUCCESS!

db02: 192.168.8.102

cat >/etc/my.cnf.d/galera_db02.cnf <<HERE

[mysqld]

wsrep-node-name = "db02"

wsrep-sst-receive-address = 192.168.8.102

wsrep-node-incoming-address = 192.168.8.102

bind-address = 192.168.8.102

wsrep-sst-method = rsync

wsrep-cluster-address = gcomm://192.168.8.100,192.168.8.101

HERE

yum -y install lsof rsync

/etc/init.d/mysql restart

[root@db02 ~]# /etc/init.d/mysql restart

Shutting down MySQL... SUCCESS!

Starting MySQL..SST in progress, setting sleep higher. SUCCESS!

5.查看集群状态

[root@db00 ~]# netstat -tunlp|grep mysqld

tcp 0 0 192.168.8.100:3306 0.0.0.0:* LISTEN 15429/mysqld

tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 15429/mysqld

3306负载数据存储，4567负载集群维护

SHOW STATUS LIKE 'wsrep%';

[root@db00 ~]# mysql -uroot -p

Enter password:

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

Your MariaDB connection id is 7

Server version: 10.0.24-MariaDB-wsrep MariaDB Server, wsrep_25.13.raf7f02e

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

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

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

| Variable_name | Value |

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

| wsrep_local_state_uuid | 5cbe919b-025c-11e6-b660-c77a70955301 |

| wsrep_protocol_version | 7 |

| wsrep_last_committed | 0 |

| 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 | 14 |

| wsrep_received_bytes | 1552 |

| 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.071429 |

| 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 | 192.168.8.102:3306,192.168.8.100:3306,192.168.8.101:3306 |

| wsrep_evs_delayed | |

| wsrep_evs_evict_list | |

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

| wsrep_evs_state | OPERATIONAL |

| wsrep_gcomm_uuid | 5cbdfa05-025c-11e6-9c91-7700903d3b22 |

| wsrep_cluster_conf_id | 7 |

| wsrep_cluster_size | 3 |

| wsrep_cluster_state_uuid | 5cbe919b-025c-11e6-b660-c77a70955301 |

| 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 |

| wsrep_provider_version | 25.3.14(r3560) |

| wsrep_ready | ON |

| wsrep_thread_count | 2 |

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

57 rows in set (0.00 sec)

wsrep_ready为ON,则说明MariaDB Galera 集群已经正确运行了。

wsrep_cluster_size 标明了组成集群的节点个数

如上，3个集群节点都己成功正常运行。

6.统一修改集群地址并重启

wsrep-cluster-address = gcomm://192.168.8.100,192.168.8.101,192.168.8.102

MariaDB [(none)]> SHOW VARIABLES LIKE 'wsrep%'G

*************************** 1. row ***************************

Variable_name: wsrep_auto_increment_control

Value: ON

*************************** 2. row ***************************

Variable_name: wsrep_causal_reads

Value: OFF

*************************** 3. row ***************************

Variable_name: wsrep_certify_nonpk

Value: ON

*************************** 4. row ***************************

Variable_name: wsrep_cluster_address

Value: gcomm://192.168.8.100,192.168.8.101,192.168.8.102

*************************** 5. row ***************************

Variable_name: wsrep_cluster_name

Value: test_cluster

*************************** 6. row ***************************

Variable_name: wsrep_convert_lock_to_trx

Value: OFF

*************************** 7. row ***************************

Variable_name: wsrep_data_home_dir

Value: /opt/mariadb/data/

*************************** 8. row ***************************

Variable_name: wsrep_dbug_option

Value:

*************************** 9. row ***************************

Variable_name: wsrep_debug

Value: OFF

*************************** 10. row ***************************

Variable_name: wsrep_desync

Value: OFF

*************************** 11. row ***************************

Variable_name: wsrep_dirty_reads

Value: OFF

*************************** 12. row ***************************

Variable_name: wsrep_drupal_282555_workaround

Value: OFF

*************************** 13. row ***************************

Variable_name: wsrep_forced_binlog_format

Value: NONE

*************************** 14. row ***************************

Variable_name: wsrep_load_data_splitting

Value: ON

*************************** 15. row ***************************

Variable_name: wsrep_log_conflicts

Value: OFF

*************************** 16. row ***************************

Variable_name: wsrep_max_ws_rows

Value: 131072

*************************** 17. row ***************************

Variable_name: wsrep_max_ws_size

Value: 1073741824

*************************** 18. row ***************************

Variable_name: wsrep_mysql_replication_bundle

Value: 0

*************************** 19. row ***************************

Variable_name: wsrep_node_address

Value:

*************************** 20. row ***************************

Variable_name: wsrep_node_incoming_address

Value: 192.168.8.100

*************************** 21. row ***************************

Variable_name: wsrep_node_name

Value: db00

*************************** 22. row ***************************

Variable_name: wsrep_notify_cmd

Value:

*************************** 23. row ***************************

Variable_name: wsrep_on

Value: ON

*************************** 24. row ***************************

Variable_name: wsrep_osu_method

Value: TOI

*************************** 25. row ***************************

Variable_name: wsrep_provider

Value: /usr/lib64/galera/libgalera_smm.so

*************************** 26. row ***************************

Variable_name: wsrep_provider_options

Value: base_dir = /opt/mariadb/data/; base_host = 192.168.8.100; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 2; evs.version = 0; evs.view_forget_timeout = P1D; gcache.dir = /opt/mariadb/data/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /opt/mariadb/data//galera.cache; gcache.page_size = 128M; gcache.size = 256M; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.listen_addr = tcp://0.0.0.0:4567; gmcast.mcast_addr = ; gmcast.mcast_ttl = 1; gmcast.peer_timeout = PT3S; gmcast.segment = 0; gmcast.time_wait = PT5S; gmcast.version = 0; ist.recv_addr = 192.168.8.100; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.linger = PT20S; pc.npvo = false; pc.recovery = true; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = P30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 7; socket.checksum = 2;

*************************** 27. row ***************************

Variable_name: wsrep_recover

Value: OFF

*************************** 28. row ***************************

Variable_name: wsrep_replicate_myisam

Value: OFF

*************************** 29. row ***************************

Variable_name: wsrep_restart_slave

Value: OFF

*************************** 30. row ***************************

Variable_name: wsrep_retry_autocommit

Value: 1

*************************** 31. row ***************************

Variable_name: wsrep_slave_fk_checks

Value: ON

*************************** 32. row ***************************

Variable_name: wsrep_slave_threads

Value: 1

*************************** 33. row ***************************

Variable_name: wsrep_slave_uk_checks

Value: OFF

*************************** 34. row ***************************

Variable_name: wsrep_sst_auth

Value: ********

*************************** 35. row ***************************

Variable_name: wsrep_sst_donor

Value:

*************************** 36. row ***************************

Variable_name: wsrep_sst_donor_rejects_queries

Value: OFF

*************************** 37. row ***************************

Variable_name: wsrep_sst_method

Value: rsync

*************************** 38. row ***************************

Variable_name: wsrep_sst_receive_address

Value: 192.168.8.100

*************************** 39. row ***************************

Variable_name: wsrep_start_position

Value: 5cbe919b-025c-11e6-b660-c77a70955301:0

*************************** 40. row ***************************

Variable_name: wsrep_sync_wait

Value: 0

40 rows in set (0.00 sec)

三.测试

A.任意节点读写

1.在节点db02上导入了一个zabbix.sql的库

[root@db02 ~]# mysql -uroot -p -e "CREATE DATABASE IF NOT EXISTS zabbix CHARACTER SET utf8"

Enter password:

[root@db02 ~]# mysql -uroot -p zabbix

Enter password:

2.在db01上查看

[root@db01 ~]# mysql -uroot -p

Enter password:

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

Your MariaDB connection id is 10

Server version: 10.0.24-MariaDB-wsrep MariaDB Server, wsrep_25.13.raf7f02e

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

MariaDB [(none)]> SHOW DATABASES;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| zabbix |

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

4 rows in set (0.01 sec)

MariaDB [(none)]> USE zabbix;

Database changed

MariaDB [zabbix]> SELECT alias,name,surname FROM users;

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

| alias | name | surname |

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

| Admin | Zabbix | Administrator |

| guest | | |

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

2 rows in set (0.00 sec)

B.节点宕机

[root@db02 ~]# mysql -uroot -p -e "SHOW STATUS LIKE 'wsrep_local_state_comment'"

Enter password:

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

| Variable_name | Value |

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

| wsrep_local_state_comment | Synced |

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

[root@db02 ~]# service mysql stop

Shutting down MySQL..... SUCCESS!

[root@db00 ~]# mysql -uroot -p -e "SHOW STATUS LIKE 'wsrep_local_state_comment'"

Enter password:

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

| Variable_name | Value |

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

| wsrep_local_state_comment | Synced |

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

当节点恢复后，会自动加入到集群，非常方便

四.关闭/启动整个集群

A.关闭

在所有集群节点上执行service mysql stop即可

注意:最后关闭的节点非常重要，因为它维护着集群最新的状态，所以在启动的时候必须最先启动，否则，在启动其它节点的会因找不到初始节点而报连接超时的错误

160415 1:20:26 [Warning] WSREP: (2dbcde95, 'tcp://0.0.0.0:4567') address 'tcp://192.168.8.102:4567' points to own listening address, blacklisting

160415 1:20:29 [Warning] WSREP: no nodes coming from prim view, prim not possible

160415 1:20:29 [Note] WSREP: view(view_id(NON_PRIM,2dbcde95,1) memb {

2dbcde95,0

} joined {

} left {

} partitioned {

})

160415 1:20:29 [Warning] WSREP: last inactive check more than PT1.5S ago (PT3.50635S), skipping check

160415 1:20:59 [Note] WSREP: view((empty))

160415 1:20:59 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)

at gcomm/src/pc.cpp:connect():162

160415 1:20:59 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out)

160415 1:20:59 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1379: Failed to open channel 'test_cluster' at 'gcomm://192.168.8.100,192.168.8.101,192.168.8.102': -110 (Connection timed out)

160415 1:20:59 [ERROR] WSREP: gcs connect failed: Connection timed out

160415 1:20:59 [ERROR] WSREP: wsrep::connect(gcomm://192.168.8.100,192.168.8.101,192.168.8.102) failed: 7

160415 1:20:59 [ERROR] Aborting

160415 1:20:59 [Note] WSREP: Service disconnected.

160415 1:21:00 [Note] WSREP: Some threads may fail to exit.

160415 1:21:00 [Note] /opt/mariadb/bin/mysqld: Shutdown complete

160415 01:21:00 mysqld_safe mysqld from pid file /opt/mariadb/mysqld.pid ended

B.启动

最先启动最后关闭的mysql节点，实测，仍然无法启动集群

如果忘记了哪个最后关闭的，可以通过集群初始化的节点重启初始化下，如：

[root@db00 ~]# /etc/init.d/mysql start --wsrep-new-cluster

Starting MySQL.. SUCCESS!

[root@db00 ~]# mysql -uroot -p -e "SHOW STATUS LIKE 'wsrep_local_state_comment'"

Enter password:

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

| Variable_name | Value |

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

| wsrep_local_state_comment | Synced |

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

[root@db01 ~]# service mysql start

Starting MySQL... SUCCESS!

[root@db02 ~]# service mysql start

Starting MySQL... SUCCESS!

五.Galera监控

示例脚本/opt/mariadb/support-files/wsrep_notify

查看MySQL版本: SHOW GLOBAL VARIABLES LIKE 'version';
查看wsrep版本: SHOW GLOBAL STATUS LIKE 'wsrep_provider_version';
查看wsrep有关的所有变量: SHOW VARIABLES LIKE 'wsrep%' G
查看Galera集群状态: SHOW STATUS LIKE 'wsrep%';

监控状态参数说明

集群完整性检查:
wsrep_cluster_state_uuid:在集群所有节点的值应该是相同的,有不同值的节点,说明其没有连接入集群
wsrep_cluster_conf_id:正常情况下所有节点上该值是一样的。如果值不同,说明该节点被临时"分区"了。当节点之间网络连接恢复的时候应该会恢复一样的值
wsrep_cluster_size:如果这个值跟预期的节点数一致,则所有的集群节点已经连接。
wsrep_cluster_status:集群组成的状态。如果不为"Primary",说明出现"分区"或是"split-brain"状况

节点状态检查:
wsrep_ready:该值为ON,则说明可以接受SQL负载。如果为Off,则需要检查wsrep_connected
wsrep_connected: 如果该值为Off,且wsrep_ready的值也为Off,则说明该节点没有连接到集
群。(可能是wsrep_cluster_address或wsrep_cluster_name等配置错造成的。具体错误需要
查看错误日志)
wsrep_local_state_comment:如果wsrep_connected为On,但wsrep_ready为OFF,则可以从该项查看原因

复制健康检查:
wsrep_flow_control_paused:表示复制停止了多长时间。即表明集群因为Slave延迟而慢的程度。值为0~1,越靠近0越好,值为1表示复制完全停止。可优化wsrep_slave_threads的值来改善
wsrep_cert_deps_distance:有多少事务可以并行应用处理。wsrep_slave_threads设置的值不应高出该值太多。
wsrep_flow_control_sent:表示该节点已经停止复制了多少次
wsrep_local_recv_queue_avg:表示slave事务队列的平均长度。slave瓶颈的预兆。最慢的节点的
wsrep_flow_control_sent和wsrep_local_recv_queue_avg这两个值最高。这两个值较低的话,相对更好

检测慢网络问题:
wsrep_local_send_queue_avg:网络瓶颈的预兆。如果这个值比较高的话,可能存在网络瓶冲突或死锁的数目
wsrep_last_committed:最后提交的事务数目
wsrep_local_cert_failures和wsrep_local_bf_aborts:回滚,检测到的冲突数目