mariadb galera集群

http://galeracluster.com
文档：
https://galeracluster.com/library/documentation/index.html

部署规划：（至少三个节点）

192.168.47.15   galera-node1
192.168.47.16   galera-node2
192.168.47.17   galera-node3

环境准备：(每个节点都要操作)

#关闭selinux
sed -i 's/^SELINUX=enforcing$/SELINUX=disabled/' /etc/selinux/config && setenforce 0

#开放防火墙端口 
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=4567/tcp --permanent
firewall-cmd --reload

#设置主机名和ip映射关系
hostnamectl set-hostname galera-node1
hostnamectl set-hostname galera-node2
hostnamectl set-hostname galera-node3

cat /etc/hosts
192.168.47.15   galera-node1
192.168.47.16   galera-node2
192.168.47.17   galera-node3

#添加mariadb yum源
cat /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

--可选 更换为中科大的国内源
sed -i 's#yum\.mariadb\.org#mirrors.ustc.edu.cn/mariadb/yum#' /etc/yum.repos.d/mariadb.repo


#更新缓存
yum makecache fast

#安装mariadb
yum install -y MariaDB-server MariaDB-client rsync

#启动服务
systemctl start mariadb && systemctl enable mariadb


#创建用于同步的用户并授权
mysql_secure_installation  #初始化设置密码

mysql -uroot -p

grant reload, lock tables, process, replication client on *.* to 'abc'@'%' identified by 'admin123$';
flush privileges;


节点配置：
#备份现有的配置：
cp /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf_backup 

node1:
cat > /etc/my.cnf.d/server.cnf <<EOF
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address=0.0.0.0
user=mysql

default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=128M

binlog_format=ROW
log-error=/var/log/mysqld.log

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_node_name='galera-node1'
wsrep_node_address="192.168.47.15"
wsrep_cluster_name='galera-cluster'
#wsrep_sst_auth=abc:admin123$  #用于同步的用户和密码
#wsrep_sst_method=xtrabackup-v2   #此方式需要同步的账户
wsrep_cluster_address="gcomm://192.168.47.16,192.168.47.17,192.168.47.15"

wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_slave_threads=1
wsrep_sst_method=rsync  #此种方式不需要同步的账户
EOF

node2：
cat > /etc/my.cnf.d/server.cnf <<EOF
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address=0.0.0.0
user=mysql

default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=128M

binlog_format=ROW
log-error=/var/log/mysqld.log

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_node_name='galera-node2'
wsrep_node_address="192.168.47.16"
wsrep_cluster_name='galera-cluster'
#wsrep_sst_auth=abc:admin123$  #用于同步的用户和密码
wsrep_cluster_address="gcomm://192.168.47.16,192.168.47.17,192.168.47.15"

wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_slave_threads=1
wsrep_sst_method=rsync
EOF

node3:
cat > /etc/my.cnf.d/server.cnf <<EOF
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address=0.0.0.0
user=mysql

default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=128M

binlog_format=ROW
log-error=/var/log/mysqld.log

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_node_name='galera-node3'
wsrep_node_address="192.168.47.17"
wsrep_cluster_name='galera-cluster'
#wsrep_sst_auth=abc:admin123$  #用于同步的用户和密码
wsrep_cluster_address="gcomm://192.168.47.16,192.168.47.17,192.168.47.15"

wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_slave_threads=1
wsrep_sst_method=rsync
EOF

#以上配置仅仅是示例，如需要配置更多参数可以根据实际需求增加

集群启动与验证：

#任意一个节点上执行：
galera_new_cluster

#检查
ps -ef | grep mysql

#启动节点上启动mariadb
systemctl start mariadb && systemctl status mariadb

#验证
 mysql -uroot -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

 value为3则正常

#数据同步验证：
在任意节点创建数据库，然后在其他节点查看是否同步
mysql -e "create database galera_test"

mysql -e "show databases;"

集群的重启：
#MariaDB galera cluster 所有节点服务全部停止后再次启动会报错，需要按照以下方法启动集群
cat /var/lib/mysql/grastate.dat
修改safe_to_bootstrap参数改为1，然后在该节点执行以下命令启动第一个节点，执行以下命令后参数会被重新置为0
galera_new_cluster
然后启动另外的节点
systemctl start mariadb

#集群状态监控
mysql -e "SHOW GLOBAL STATUS LIKE 'wsrep_%'";
#单独查看cluster_status
mysql -e "SHOW STATUS LIKE 'wsrep_cluster_status'";