使用HAProxy搭建MySQL集群

引言

我们知道搭建MySQL集群有MHA、MGR、MMM，以及MySQL官方提供的MySQL-Cluster方式，以下主要以MGR方式为主做介绍，稍后再介绍这几种集群方式的差异。

我们先简单的介绍一下MGR架构，MGR的全称是MySQL Group Replication，是官方在5.7.17版本推出的基于paxos协议的集群方案。有人说MGR是基于状态机的集群设计，每一个节点都可以看做是一个状态机，任何一个节点出现

1. 服务器信息

   我准备了四台机器，一台haproxy节点，三台MySQL节点，这三个MySQL节点均为Master，并且互为主备。

   主机IP	OS	软件
   10.154.8.18	centos 7.0	haproxy
   10.154.8.113	centos 7.0	master
   10.154.8.130	centos 7.0	master
   10.154.8.149	centos 7.0	master

2. 架构图

安装MySQL

cd /data1/software/mysql/
tar -zxvf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

mkdir /usr/local/mysql

cp -R /data1/software/mysql/mysql-5.7.31-linux-glibc2.12-x86_64/* /usr/local/mysql/

mkdir /usr/local/mysql/data

cd /usr/local/mysql

groupadd mysql
useradd mysql -g mysql
chown -R mysql .
chgrp -R mysql .

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/ --explicit_defaults_for_timestamp

rm -rf /tmp/mysql.sock

ln -s /usr/local/mysql/mysql.sock /tmp/mysql.sock

# 开机启动设置
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

chkconfig --add mysqld

配置MySQL

vim /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
socket=/usr/local/mysql/mysql.sock
character_set_server=utf8mb4

wait_timeout=360000

log-bin=/usr/local/mysql/data/binlog
log-bin-index=/usr/local/mysql/data/binlog.index

# 每个节点必须不同
server-id = 208

gtid-mode=on
enforce-gtid-consistency=on
master-info-repository=TABLE
relay-log-info-repository=TABLE
binlog-checksum=none
log-slave-updates=on
binlog-format=ROW

transaction-write-set-extraction=XXHASH64
loose-group_replication_group_name='7e6fe64a-3bc0-4117-9cac-2439f9c3f19e'
loose-group_replication_start_on_boot=off
loose-group_replication_local_address='10.154.8.113:33061'
loose-group_replication_group_seeds='10.154.8.113:33061,10.154.8.130:33062,10.154.8.149:33063'
loose-group_replication_bootstrap_group=off

[mysqld_safe]
log-error=/usr/local/mysql/data/error.err
pid-file=/usr/local/mysql/data/mysql.pid

!includedir /etc/my.cnf.d

配置无需密码登录

https://blog.csdn.net/luxiaoruo/article/details/106637222

启动MySQL

service mysqld start

/usr/local/mysql/bin/mysql -uroot -p

alter user 'root'@'localhost' identified by 'ojbKwcrpqiOhsg8/i';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'ojbKwcrpqiOhsg8/i' WITH GRANT OPTION;

grant replication slave,replication client on *.* to 'slave'@'%' identified by 'k3G_w9Z&eeip';

flush privileges;

---

haproxy

GRANT ALL ON *.* TO 'haproxy'@'%' IDENTIFIED BY '123';

GRANT ALL ON *.* TO 'haproxy'@'10.154.8.18' IDENTIFIED BY '123';

update mysql.user set authentication_string=PASSWORD('') where user='haproxy';

flush privileges;

MGR说明

server-id=1　　// Mysql服务ID，集群内必须唯一
gtid-mode=on　　// 全局事务
enforce-gtid-consistency=on　　// 强制GTID的一致性
master-info-repository=TABLE　　// 将master.info元数据保存在系统表中
relay-log-info-repository=TABLE　　// 将relay.info元数据保存在系统表中
binlog-checksum=none　　// 禁用二进制日志事件校验
log-slave-updates=on　　// 级联复制
log-bin=binlog　　// 开启二进制日志记录
binlog-format=ROW　　// 以行的格式记录

transaction-write-set-extraction=XXHASH64　　// 使用哈希算法将其编码为散列
loose-group_replication_group_name=‘7e6fe64a-3bc0-4117-9cac-2439f9c3f19e’　　// 加入的组名，可以修改，只要格式对
loose-group_replication_start_on_boot=off　　// 不自动启用组复制集群
loose-group_replication_local_address=‘node1:33061’　　// 以本机端口33061接受来自组中成员的传入连接
loose-group_replication_group_seeds=‘node1:33061,node2:33062,node3:33063’　　// 组中成员访问表
loose-group_replication_bootstrap_group=off　　// 不启用引导组

设置hostname映射

vim /etc/hosts

# 使用hostname指令查看主机名
10.154.8.18 devtools1e.novalocal node1
10.154.8.113 devtools1b.novalocal node2
10.154.8.130 devtools1c.novalocal node3
10.154.8.149 devtools1d.novalocal node4

开启组复制

1. 主

   change master to master_user='slave',master_password='k3G_w9Z&eeip' for channel 'group_replication_recovery';
   
   install PLUGIN group_replication SONAME 'group_replication.so';
   
   SET GLOBAL group_replication_ip_whitelist="10.154.8.113,10.154.8.130,10.154.8.149";
   
   # 让该节点成为主节点
   set global group_replication_bootstrap_group=on;
   
   start group_replication;
   
   set global group_replication_bootstrap_group=off;
   
   select * from performance_schema.replication_group_members;
   
   

2. 备

   change master to master_user='slave',master_password='k3G_t9Z&eeip' for channel 'group_replication_recovery';
   
   install PLUGIN group_replication SONAME 'group_replication.so';
   
   SET GLOBAL group_replication_ip_whitelist="10.154.8.113,10.154.8.130,10.154.8.149,10.154.8.208";
   
   set global group_replication_allow_local_disjoint_gtids_join=ON;
   
   start group_replication;
   
   select * from performance_schema.replication_group_members;
   
   

搭建haproxy

1. 下载haproxy-1.8.25.tar.gz

   wget -C https://www.haproxy.org/download/1.8/src/haproxy-1.8.25.tar.gz
   

2. 安装

   mkdir /usr/local/haproxy
   
   mkdir /etc/haproxy
   
   touch /etc/haproxy/haproxy.conf
   
   yum install -y gcc
   
   tar -zxvf haproxy-1.8.25.tar.gz
   
   cd haproxy-1.8.25
   
   make TARGET=generic  prefix=/usr/local/haproxy
   
   make install PREFIX=/usr/local/haproxy
   
   ln -s /usr/local/haproxy/sbin/haproxy /usr/local/bin/
   
   ln -s /usr/local/haproxy/sbin/haproxy /usr/local/etc/haproxy
   
   

3. 配置

   cat > /etc/haproxy/haproxy.conf << EOF
   global
       # chroot /usr/local/etc/haproxy
       log 127.0.0.1 local5 info
       daemon
   defaults
       log global
       mode http
       option httplog
       option dontlognull
       timeout connect 5000
       timeout client 50000
       timeout server 50000
   listen admin_stats
       bind 0.0.0.0:8888
       mode http
       stats uri /dbs_monitor
       stats realm Global\ statistics
       stats auth admin:admin
       listen proxy-mysql
       bind 0.0.0.0:3306
       mode tcp
       balance roundrobin
       option tcplog
       option mysql-check user haproxy
       server MySQL_1 10.154.8.113:3306 check weight 1 maxconn 2000
       server MySQL_2 10.154.8.130:3306 check weight 1 maxconn 2000
       server MySQL_3 10.154.8.149:3306 check weight 1 maxconn 2000
       option tcpka
   EOF
   

4. 启动

   haproxy -f /etc/haproxy/haproxy.conf