Centos之Mariadb_Galera集群

1. 环境准备

主机名	IP
mysql1	10.0.0.10
mysql2	10.0.0.11
mysql3	10.0.0.12
mysql4	10.0.0.13

1.1 关闭防火墙与selinux

• 所有节点

sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config; systemctl stop firewalld; systemctl disable firewalld

1.2 更改主机名

• mysql1

hostnamectl set-hostname mysql1

• mysql2

hostnamectl set-hostname mysql2

• mysql3

hostnamectl set-hostname mysql3

• mysql4

hostnamectl set-hostname mysql4

1.3 配置hosts解析

• 所有节点

cat >> /etc/hosts << EOF
10.0.0.10 mysql1
10.0.0.11 mysql2
10.0.0.12 mysql3
10.0.0.13 mysql4
EOF

1.4 配置yum源

• 所有节点
  • 可以根据baseurl选择版本

cat >> /etc/yum.repos.d/MariaDB.repo << EOF
[mariadb]
name=mariadb
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.7/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF


# 加载
yum makecache

2. 安装软件包

• 所有节点

yum -y install MariaDB-server galera-4 MariaDB-client MariaDB-shared MariaDB-backup MariaDB-common  MariaDB-server-debuginfo MariaDB-client-debuginfo MariaDB-shared-debuginfo MariaDB-backup-debuginfo MariaDB-common-debuginfo

3. 编辑配置文件

• mysql1节点

cat > /etc/my.cnf.d/server.cnf << EOF
[client]
socket=/var/lib/mysql/mysql.sock
 
[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
binlog_expire_logs_seconds=604800
skip-external-locking
skip-name-resolve
max_connections=5000
connect_timeout=5
wait_timeout=600
max_allowed_packet=16M
thread_cache_size=128
sort_buffer_size=4M
bulk_insert_buffer_size=16M
tmp_table_size=32M
max_heap_table_size=32M
 
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
 
wsrep_causal_reads=ON
wsrep_provider_options="gcache.size=128M"
wsrep_certify_nonPK=ON
log-bin=/data/mariadb/data/mysql-bin
log_slave_updates=1
query_cache_size=0
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_name=MGC-Cluster
wsrep_cluster_address=gcomm://10.0.0.10,10.0.0.11,10.0.0.12,10.0.0.13
wsrep_node_name=mysql1
wsrep_node_address=10.0.0.10
wsrep_sst_method=xtrabackup-v2
wsrep_sst_method=rsync
binlog_format=row  
default_storage_engine=InnoDB  
innodb_autoinc_lock_mode=2
wsrep_slave_threads=8
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=500M
EOF

• mariadb集群文件准备（mysql1节点）

mkdir -pv /var/run/mysqld/

mkdir -pv /data/mariadb/data/mysql-bin

chown -R mysql.mysql /var/run/mysqld/

chown -R mysql.mysql /data/

4. 引导 GRC 集群

• mysql1节点

galera_new_cluster

• 验证（mysql1）

mysql

show status like 'wsrep%';

5. 加入集群

• mysql2节点

5.1 拷贝集群文件

scp 10.0.0.10:/etc/my.cnf.d/server.cnf /etc/my.cnf.d/

5.2 准备集群文件

mkdir -pv /var/run/mysqld/

mkdir -pv /data/mariadb/data/mysql-bin

chown -R mysql.mysql /var/run/mysqld/

chown -R mysql.mysql /data/

5.3 编辑配置文件

sed -i "s/mysql1/mysql2/g" /etc/my.cnf.d/server.cnf

sed -i "s/wsrep_node_address=10.0.0.10/wsrep_node_address=10.0.0.11/g" /etc/my.cnf.d/server.cnf

5.4 启动数据库并验证

• 启动数据库

systemctl start mariadb

• 验证

mysql

show status like 'wsrep%';

5.5 拷贝集群文件

scp 10.0.0.10:/etc/my.cnf.d/server.cnf /etc/my.cnf.d/

5.6 准备集群文件

mkdir -pv /var/run/mysqld/

mkdir -pv /data/mariadb/data/mysql-bin

chown -R mysql.mysql /var/run/mysqld/

chown -R mysql.mysql /data/

5.7 编辑配置文件

sed -i "s/mysql1/mysql3/g" /etc/my.cnf.d/server.cnf

sed -i "s/wsrep_node_address=10.0.0.10/wsrep_node_address=10.0.0.12/g" /etc/my.cnf.d/server.cnf

5.8 启动数据库并验证

• 启动数据库

systemctl start mariadb

• 验证

mysql

show status like 'wsrep%';

6. 验证集群同步性

• mysql1

create database t1;

• mysql2

create database t2;

• mysql3

create database t3;

• 所有节点验证
  • 如果都有t1、t2、t3库，那么成功

show databases;

7. 加入新节点

7.1 重新定义集群文件

• 编辑文件（mysql1）

cat > /etc/my.cnf.d/server.cnf << EOF
[client]
socket=/var/lib/mysql/mysql.sock
 
[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
binlog_expire_logs_seconds=604800
skip-external-locking
skip-name-resolve
max_connections=5000
connect_timeout=5
wait_timeout=600
max_allowed_packet=16M
thread_cache_size=128
sort_buffer_size=4M
bulk_insert_buffer_size=16M
tmp_table_size=32M
max_heap_table_size=32M
 
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
 
wsrep_causal_reads=ON
wsrep_provider_options="gcache.size=128M"
wsrep_certify_nonPK=ON
log-bin=/data/mariadb/data/mysql-bin
log_slave_updates=1
query_cache_size=0
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_name=MGC-Cluster
wsrep_cluster_address=gcomm://10.0.0.10,10.0.0.11,10.0.0.12,10.0.0.13,10.0.0.14
wsrep_node_name=mysql1
wsrep_node_address=10.0.0.10
wsrep_sst_method=xtrabackup-v2
wsrep_sst_method=rsync
binlog_format=row  
default_storage_engine=InnoDB  
innodb_autoinc_lock_mode=2
wsrep_slave_threads=8
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=500M
EOF

# 重启数据库
systemctl restart mariadb

• 同步文件（mysql1）

for i in {2..4};do scp /etc/my.cnf.d/server.cnf root@mysql$i:/etc/my.cnf.d/;done

• mysql2编辑配置文件

sed -i "s/mysql1/mysql2/g" /etc/my.cnf.d/server.cnf

sed -i "s/wsrep_node_address=10.0.0.10/wsrep_node_address=10.0.0.11/g" /etc/my.cnf.d/server.cnf

systemctl restart mariadb

• mysql3编辑配置文件

sed -i "s/mysql1/mysql3/g" /etc/my.cnf.d/server.cnf

sed -i "s/wsrep_node_address=10.0.0.10/wsrep_node_address=10.0.0.12/g" /etc/my.cnf.d/server.cnf

systemctl restart mariadb

• mysql4编辑配置文件

sed -i "s/mysql1/mysql4/g" /etc/my.cnf.d/server.cnf

sed -i "s/wsrep_node_address=10.0.0.10/wsrep_node_address=10.0.0.13/g" /etc/my.cnf.d/server.cnf

• mysql4

7.2 准备集群文件

mkdir -pv /var/run/mysqld/

mkdir -pv /data/mariadb/data/mysql-bin

chown -R mysql.mysql /var/run/mysqld/

chown -R mysql.mysql /data/

7.3 启动数据库并验证

• 启动数据库

systemctl start mariadb

• 验证
  • 如果有t1、t2、t3库那么表示成功

mysql

show status like 'wsrep%';