Mariadb Galera Cluster

环境准备

修改主机名
配置主机名解析
关闭防火墙 & selinux
配置 yum 源

hostnamectl set-hostname node1

echo "10.35.172.77 node1
10.35.172.78 node2
10.35.172.79 node3" >> /etc/hosts

systemctl stop firewalld && setenforce 0

echo "[mariadb]
name=mariadb
baseurl=ftp://10.35.172.81/gpmall-repo
gpgcheck=0
enabled=1

[centos]
name=centos
baseurl=ftp://10.35.172.81/centos
gpgcheck=0
enabled=1" > /etc/yum.repos.d/ftp.repo

安装Mariadb Galera Cluster

# 安装 mariadb
yum install mariadb-server -y

# 初始化数据库
 systemctl start mariadb
 mysql_secure_installation 
 u
 # 配置数据库文件
 /etc/my.cnf.d/server.cnf

 # 修改数据库密码
 grant all privileges on *.* to root@'%' identified by '123456';

 # 停止数据库
 systemctl stop mariadb

 # 启动数据库集群
 node1
   galera_new_cluster
 node2 & node3
   systemctl start mariadb

# 验证
node1
  MariaDB [(none)]> show status like "wsrep_ready";
  MariaDB [(none)]>  show status like "wsrep_cluster_size";
  MariaDB [(none)]> show status like "wsrep%"; 
  MariaDB [(none)]> create database abc; 
  MariaDB [(none)]> show databases;
node2
  MariaDB [(none)]> show databases;
node3
  MariaDB [(none)]> show databases;

wsrep_on=ON 
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
# 集群的主机IP
wsrep_cluster_address="gcomm://10.35.172.77,10.35.172.78,10.35.172.79"
# 主机名
wsrep_node_name=node1
# 主机IP
wsrep_node_address=10.35.172.77
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=120M
wsrep_sst_method=rsync
wsrep_causal_reads=ON
# 主机IP
bind-address=10.35.172.77

安装 HAProxy

## node1
# 安装 HAProxy
yum install haproxy -y

# HAProxy服务配置
vi /etc/haproxy/haproxy.cfg
global
  log         127.0.0.1 local2
  chroot      /var/lib/haproxy
  maxconn     4000
  user        haproxy
  group       haproxy
  daemon
  stats socket /var/lib/haproxy/stats

defaults
  log  global
  maxconn  4000
  option  redispatch
  retries  3
  timeout  http-request 10s
  timeout  queue 1m
  timeout  connect 10s
  timeout  client 1m
  timeout  server 1m
  timeout  check 10s

listen stats
  bind    10.35.172.77:9000 # （改）
  mode    http
  stats   enable
  stats   uri     /stats
  stats   auth    admin:admin
  stats   admin   if TRUE

listen mariadb
  balance roundrobin
  mode tcp
  option tcplog
  option tcpka
  bind 10.35.172.77:3307 # （改）
  server node1 10.35.172.77:3306 check weight 1
  server node2 10.35.172.78:3306 check weight 1
  server node3 10.35.172.79:3306 check weight 1

# 启动 HAProxy
systemctl start haproxy
netstat -ntpl

# HAProxy服务验证
网页访问 10.35.172.77:9000/stats （admin/admin）

mysql -h 10.35.172.77 -P 3307 -uroot -p000000

# 数据库设置 服务ID
mysql -h 10.35.172.77 -uroot -p123456 -e "SET GLOBAL server_id=77;"
mysql -h 10.35.172.78 -uroot -p000000 -e "SET GLOBAL server_id=78;"
mysql -h 10.35.172.79 -uroot -p000000 -e "SET GLOBAL server_id=79;"

# 检查 HAProxy服务 正常运行
mysql -h 10.35.172.77 -P 3307  -uroot -p000000 -e "show variables like 'server_id'" # 多次执行查看server_id