Teaker's Blog

0、首先说明需要奇数个节点，以免脑裂。

1、安装stalling MariaDB
vi /etc/hosts
1.1.1.1 db1.test.com
2.2.2.2 db2.test.com
3.3.3.3 db3.test.com

yum -y install mariadb-server rsync galera

# 互交安装mysql
mysql_secure_installation

2、配置集群文件figuring for Galera
vi /etc/mysql/conf.d/galera.cnf
# common to all
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name=”galera_cluster”
wsrep_cluster_address=”gcomm://1.1.1.1,2.2.2.2,3.3.3.3″
wsrep_sst_method=rsync

# unique to this node
wsrep_node_address=”1.1.1.1″
wsrep_node_name=”db1″

DB2的时候修改这两行
# unique to this node
wsrep_node_address=”2.2.2.2″
wsrep_node_name=”db2″

DB3的时候修改这两行
# unique to this node
wsrep_node_address=”3.3.3.3″
wsrep_node_name=”db3″

3、启动集群服务Starting Up the Galera Cluster
在DB1运行集群命令:
galera_new_cluster

查看MYSQL集群初始化
ps -ef | grep -i mysql

在DB2和DB3运行
systemctl start mariadb.service

查看节点数
# mysql
MariaDB [(none)]> show status like ‘wsrep_cluster_size’;
+——————–+——-+
| Variable_name | Value |
+——————–+——-+
| wsrep_cluster_size | 3 |
+——————–+——-+
1 row in set (0.001 sec)

4、测试Testing Galera
建立DB并填充数据
create database gtest;
use gtest;
create table buffy ( name varchar(30), team_role varchar(100) );
insert into buffy (name, team_role) values ( ‘Buffy’, ‘slayer’);
insert into buffy (name, team_role) values ( ‘Giles’, ‘research’);
insert into buffy (name, team_role) values ( ‘Zander’, ‘comic relief’);
insert into buffy (name, team_role) values ( ‘Cordelia’, ‘eye candy’);

切换到DB3
root@db3:/etc/mysql# mysql gtest
MariaDB [gtest]> select * from buffy;
+———-+————–+
| name | team_role |
+———-+————–+
| Buffy | slayer |
| Giles | research |
| Zander | comic relief |
| Cordelia | eye candy |
+———-+————–+
4 rows in set (0.001 sec)

关闭DB2的DB服务
systemctl stop mariadb
切换去DB1执行
use gtest;
insert into buffy (name, team_role) values ( ‘Faith’, ‘slayer’);
insert into buffy (name, team_role) values ( ‘Wesley’, ‘whiner’);
insert into buffy (name, team_role) values ( ‘Oz’, ‘werewolf’);
启动DB2的DB服务
systemctl start mariadb
tail -f /var/log/mysql/error.log
2020-07-21 21:03:02 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 15)
2020-07-21 21:03:02 0 [Note] WSREP: Member 0.0 (db2) synced with group.
2020-07-21 21:03:02 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 15)
2020-07-21 21:03:02 1 [Note] WSREP: Synchronized with group, ready for connections
在DB2执行语句查询内容
MariaDB [(none)]> show status like ‘wsrep_last_committed’;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| wsrep_last_committed | 15 |
+———————-+——-+
1 row in set (0.001 sec)

切换去DB3，执行语句
use gtest;
insert into buffy (name, team_role) values ( ‘Willow’, ‘research’);
insert into buffy (name, team_role) values ( ‘Anya’, ‘bad news’);

切换去DB1查询
MariaDB [(none)]> use gtest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [gtest]> select * from buffy;
+———-+————–+
| name | team_role |
+———-+————–+
| Buffy | slayer |
| Giles | research |
| Zander | comic relief |
| Cordelia | eye candy |
| Faith | slayer |
| Wesley | whiner |
| Oz | werewolf |
| Willow | research |
| Anya | bad news |
+———-+————–+
9 rows in set (0.000 sec)

PS：https://jeremyxu2010.github.io/2018/02/mariadb-galera-cluster%E9%83%A8%E7%BD%B2%E5%AE%9E%E6%88%98/
1、如果集群从来没有启动过（3个节点上都没有/var/lib/mysql/grastate.dat文件），则必要由其中一个节点以–wsrep-new-cluster参数启动，另外两个节点正常启动即可

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

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

=====自启动脚本
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