Mariadb Galera集群部署

Mariadb Galera集群部署

Galera Cluster 本质上是用来在多个数据库服务器之间形成一个集群。它广泛与 MySQL、MariaDB 和 XtraDB 数据库软件系统结合使用。Galera Cluster 是这些数据库系统的组成部分。因此，它可以与其中之一一起安装。
本测试实例为三台mariadb组Galera Cluster。
记录操作步骤，备忘。

测试节点参考：
node1 192.168.10.70
node2 192.168.10.71
node3 192.168.10.72

安装 mariadb galera

1、配置mariadb软件源

官方的源配置参考以下内容：

1
2
3
4
5
6
7
# MariaDB.repo

[mariadb]
name = MariaDB
baseurl = https://yum.mariadb.org/version/package
gpgkey = https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck = 1

具体源配置可以通过[配置链接][1]来查看。也可以配置为国内的阿里源或清华源。国内源配置参考如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
[mariadb]
# 阿里源
name = MariaDB
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.9/rhel/7/x86_64/
pkpkey = https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1

[mariadb]
# 清华源
name = MariaDB
baseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.9/rhel/7/x86_64/
pkpkey = https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1

2、更新配置的源

1
2
3
4
# 导入gpg key
rpm --import https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
# 更新配置的源
yum makecache

3、安装mariadb galera

1
2
# galera 作为依赖会自动安装
yum install mariadb-server mariadb-common galera rsync

4、galera配置
mariadb的配置文件在/etc/my.cnf.d/目录下，可以先备份默认的配置文件，再按实际需求配置。例如node70的配置见下引用内容：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
cd /etc/my.cnf.d
cp server.cnf server.cnf.$(date "+%Y-%m-%d")

vim server.cnf

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address=0.0.0.0
user=mysql

innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=128M

binlog_format=ROW
# this is only for the mysqld standalone daemon
[mysqld]

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so     
wsrep_node_name='node70'                    
wsrep_node_address='192.168.10.70'
wsrep_cluster_name='galera-cluster'
wsrep_cluster_address="gcomm://192.168.10.70,192.168.10.71,192.168.10.72"

binlog_format=row

default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.7 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.7]

node71 的galera配置见下引用：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address=0.0.0.0
user=mysql

innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=128M

binlog_format=ROW
# this is only for the mysqld standalone daemon
[mysqld]

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_node_name='node71'
wsrep_node_address='192.168.10.71'
wsrep_cluster_name='galera-cluster'
wsrep_cluster_address="gcomm://192.168.10.70,192.168.10.71,192.168.10.72"

binlog_format=row

default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.7 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.7]

node72上的配置如上所示，更改wsrep node name, wsrep node address为第三个节点的主机信息即可。

5、Galera Cluster 防火墙配置

配置Galera Cluster需要在防火墙上放通以下端口，操作下示例：

1
2
3
4
5
6
7
firewall-cmd --permanent --add-service=mysql
firewall-cmd --permanent --add-port=4444/tcp
firewall-cmd --permanent --add-port=4567/tcp
firewall-cmd --permanent --add-port=4567/udp
firewall-cmd --permanent --add-port=4568/tcp
firewall-cmd --permanent --add-port=9200/tcp
firewall-cmd --complete-reload

三台集群服务器上的防火墙配置保持一致。

6、启动Galera Cluster集群

在任意节点上执行以下命令：

1
2
galera_new_cluster
systemctl enable mariadb

依次在另两个节点上执行以下命令：

1
2
3
4
systemctl start mariadb
systemctl enable mariadb
# 或
systemctl enable --now mariadb

如无报错，则数据库集群启动完毕。

7、验证数据库集群

任意一台节点登录mysql。查看wsrep相关的配置状态。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
MariaDB [(none)]> show status like "wsrep_cluster_size";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]> show status like "wsrep_ready";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_ready   | ON    |
+---------------+-------+
1 row in set (0.001 sec)

# wsrep相关的配置状态

MariaDB [(none)]> show status like "wsrep%";
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name                 | Value                                                                                                                                          |
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| wsrep_local_state_uuid        | 7e5591e4-1ec4-11ed-a606-d7bc4e9aadb5                                                                                                           |
| wsrep_protocol_version        | 10                                                                                                                                             |
| wsrep_last_committed          | 12                                                                                                                                             |
| 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          | 256                                                                                                                                            |
| 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                                                                                                                                              |
| wsrep_local_recv_queue        | 0                                                                                                                                              |
| wsrep_local_recv_queue_max    | 1                                                                                                                                              |
| wsrep_local_recv_queue_min    | 0                                                                                                                                              |
| wsrep_local_recv_queue_avg    | 0                                                                                                                                              |
| wsrep_local_cached_downto     | 12                                                                                                                                             |
| wsrep_flow_control_paused_ns  | 0                                                                                                                                              |
| wsrep_flow_control_paused     | 0                                                                                                                                              |
| wsrep_flow_control_sent       | 0                                                                                                                                              |
| wsrep_flow_control_recv       | 0                                                                                                                                              |
| wsrep_flow_control_active     | false                                                                                                                                          |
| wsrep_flow_control_requested  | false                                                                                                                                          |
| wsrep_cert_deps_distance      | 0                                                                                                                                              |
| wsrep_apply_oooe              | 0                                                                                                                                              |
| wsrep_apply_oool              | 0                                                                                                                                              |
| wsrep_apply_window            | 0                                                                                                                                              |
| wsrep_apply_waits             | 0                                                                                                                                              |
| wsrep_commit_oooe             | 0                                                                                                                                              |
| wsrep_commit_oool             | 0                                                                                                                                              |
| wsrep_commit_window           | 0                                                                                                                                              |
| wsrep_local_state             | 4                                                                                                                                              |
| wsrep_local_state_comment     | Synced                                                                                                                                         |
| wsrep_cert_index_size         | 0                                                                                                                                              |
| wsrep_causal_reads            | 0                                                                                                                                              |
| wsrep_cert_interval           | 0                                                                                                                                              |
| wsrep_open_transactions       | 0                                                                                                                                              |
| wsrep_open_connections        | 0                                                                                                                                              |
| wsrep_incoming_addresses      | AUTO,AUTO,AUTO                                                                                                                                 |
| wsrep_cluster_weight          | 3                                                                                                                                              |
| wsrep_desync_count            | 0                                                                                                                                              |
| wsrep_evs_delayed             |                                                                                                                                                |
| wsrep_evs_evict_list          |                                                                                                                                                |
| wsrep_evs_repl_latency        | 0/0/0/0/0                                                                                                                                      |
| wsrep_evs_state               | OPERATIONAL                                                                                                                                    |
| wsrep_gcomm_uuid              | 60614047-1ed6-11ed-9a12-cffefacd6a64                                                                                                           |
| wsrep_gmcast_segment          | 0                                                                                                                                              |
| wsrep_applier_thread_count    | 1                                                                                                                                              |
| wsrep_cluster_capabilities    |                                                                                                                                                |
| wsrep_cluster_conf_id         | 11                                                                                                                                             |
| wsrep_cluster_size            | 3                                                                                                                                              |
| wsrep_cluster_state_uuid      | 7e5591e4-1ec4-11ed-a606-d7bc4e9aadb5                                                                                                           |
| wsrep_cluster_status          | Primary                                                                                                                                        |
| wsrep_connected               | ON                                                                                                                                             |
| wsrep_local_bf_aborts         | 0                                                                                                                                              |
| wsrep_local_index             | 2                                                                                                                                              |
| wsrep_provider_capabilities   | :MULTI_MASTER:CERTIFICATION:PARALLEL_APPLYING:TRX_REPLAY:ISOLATION:PAUSE:CAUSAL_READS:INCREMENTAL_WRITESET:UNORDERED:PREORDERED:STREAMING:NBO: |
| wsrep_provider_name           | Galera                                                                                                                                         |
| wsrep_provider_vendor         | Codership Oy <info@codership.com>                                                                                                              |
| wsrep_provider_version        | 26.4.11(r67341d0)                                                                                                                              |
| wsrep_ready                   | ON                                                                                                                                             |
| wsrep_rollbacker_thread_count | 1                                                                                                                                              |
| wsrep_thread_count            | 2                                                                                                                                              |
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
69 rows in set (0.001 sec)

7、验证Galera Cluster

node71上创建新的测试数据库，node72上查看同步状态。后在node72上删除已创建的测试数据库，在node70上查看是否同步。

```bash
[root@node71 ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.9.1-MariaDB MariaDB Server

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

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

MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.011 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.001 sec)

MariaDB [(none)]> 

# 查看node72上的数据库同步状态

[root@node72 ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.9.1-MariaDB MariaDB Server

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

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |              # 已经看到node71上创建的test数据库
+--------------------+
5 rows in set (0.001 sec)

MariaDB [(none)]> 

# 在node72上删除创建的数据库

MariaDB [(none)]> drop database test;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> 

# 在node70上查看数据库同步状态

[root@node70 my.cnf.d]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.9.1-MariaDB MariaDB Server

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

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.003 sec)

MariaDB [(none)]> 

# 可以看到test数据库删除操作已经同步到node70节点。

MariaDB Galera Cluster集群搭建完毕。

8、集群自启动

如上所示，Galera集群启动时必须按照一个特定的规则启动，研究了下，发现规则如下：

  • 如果集群从来没有启动过（3个节点上都没有/var/lib/mysql/grastate.dat文件），则必要由其中一个节点以–wsrep-new-cluster参数启动，另外两个节点正常启动即可

  • 如果集群以前启动过，则参考/var/lib/mysql/grastate.dat，找到safe_to_bootstrap为1的节点，在该节点上以–wsrep-new-cluster参数启动，另外两个节点正常启动即可

  • 如果集群以前启动过，但参考/var/lib/mysql/grastate.dat，找不到safe_to_bootstrap为1的节点（一般是因为mariadb服务非正常停止造成），则在3个节点中随便找1个节点，将/var/lib/mysql/grastate.dat中的safe_to_bootstrap修改为1，再在该节点上以–wsrep-new-cluster参数启动，另外两个节点正常启动即可

从以上3种场景可知，正常情况下很难保证mariadb galera cluster可以无人值守地完成开机自启动。

补充一个检测脚本，未验证，见下代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
cat /usr/local/bin/mariadb_cluster_helper.sh

#!/bin/bash
GRASTATE_FILE=/var/lib/mysql/grastate.dat
WSREP_NEW_CLUSTER_LOG_FILE=/tmp/wsrep_new_cluster.log
# 如果启动mariadb超过10秒还没返回0，则认为失败了
START_MARIADB_TIMEOUT=10
# 以--wsrep-new-cluster参数启动，超过5次检查，发现仍没有其它节点加入集群，则认为此路不通
SPECIAL_START_WAIT_MAX_COUNT=5
# 得到本机IP
MY_IP=$(grep 'wsrep_node_address' /etc/my.cnf.d/server.cnf | awk -F '=' '{print $2}')
# 杀掉mysqld进程
function kill_mysqld_process() {
    (ps -ef|grep mysqld|grep -v grep|awk '{print $2}'|xargs kill -9) &>/dev/null
}
# 正常启动mariadb
function start_mariadb_normal(){
    # 首先确保safe_to_bootstrap标记为0
    sed -i 's/^safe_to_bootstrap.*$/safe_to_bootstrap: 0/' $GRASTATE_FILE
    timeout $START_MARIADB_TIMEOUT systemctl start mariadb &> /dev/null
    return $?
}
# 以--wsrep-new-cluster参数启动mariadb
function start_mariadb_special(){
    # 首先确保safe_to_bootstrap标记为1
    sed -i 's/^safe_to_bootstrap.*$/safe_to_bootstrap: 1/' $GRASTATE_FILE
    # 以--wsrep-new-cluster参数启动mariadb
    /usr/sbin/mysqld --user=mysql --wsrep-new-cluster &> $WSREP_NEW_CLUSTER_LOG_FILE &
    disown $!
    try_count=0
    # 循环检查
    while [ 1 ]; do
        # 如果超过SPECIAL_START_WAIT_MAX_COUNT次检查，仍没有其它节点加入集群，则认为此路不通，尝试正常启动，跳出循环
        if [ $try_count -gt $SPECIAL_START_WAIT_MAX_COUNT ] ; then
            kill_mysqld_process
            start_mariadb_normal
            return $?
        fi
        new_joined_count=$(grep 'synced with group' /tmp/wsrep_new_cluster.log | grep -v $MY_IP|wc -l)
        exception_count=$(grep 'exception from gcomm, backend must be restarted' $WSREP_NEW_CLUSTER_LOG_FILE | wc -l)
        # 如果新加入的节点数大于0，则认为集群就绪了，可正常启动了，跳出循环
        # 如果运行日志中发现了异常(两个节点都以--wsrep-new-cluster参数启动，其中一个会报错)，则认为此路不通，尝试正常启动，跳出循环
        if [ $new_joined_count -gt 0 ] || [ $exception_count -gt 0 ] ; then
            kill_mysqld_process
            start_mariadb_normal
            return $?
        else
            try_count=$(( $try_count + 1 ))
        fi
        sleep 5
    done
}
# 首先杀掉mysqld进程
kill_mysqld_process
ret=-1
# 如果safe_to_bootstrap标记为1，则立即以--wsrep-new-cluster参数启动
if [ -f $GRASTATE_FILE ]; then
    safe_bootstrap_flag=$(grep 'safe_to_bootstrap' $GRASTATE_FILE | awk -F ': ' '{print $2}')
    if [ $safe_bootstrap_flag -eq 1 ] ; then
        start_mariadb_special
        ret=$?
    else
        start_mariadb_normal
        ret=$?
    fi
else
    start_mariadb_normal
    ret=$?
fi
# 随机地按某种方式启动，直到以某种方式正常启动以止；否则杀掉mysqld进程，随机休息一会儿，重试
while [ $ret -ne 0 ]; do
    kill_mysqld_process
    sleep_time=$(( $RANDOM % 10 ))
    sleep $sleep_time
    choice=$(( $RANDOM % 2 ))
    ret=-1
    if [ $choice -eq 0 ] ; then
        start_mariadb_special
        ret=$?
    else
        start_mariadb_normal
        ret=$?
    fi
done

# 使上述脚本开机自启动
chmod +x /usr/local/bin/mariadb_cluster_helper.sh
chmod +x /etc/rc.d/rc.local
echo '
/usr/local/bin/mariadb_cluster_helper.sh &> /var/log/mariadb_cluster_helper.log &' >> /etc/rc.d/rc.local
database
#security #mariadb #mysql #galera
Mariadb Galera集群部署
https://ywmy.xyz/2022/08/18/Mariadb-Galera集群部署/
作者
ian
发布于
2022年8月18日
许可协议