使用HAProxy搭建MySQL集群

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. 架构图

    image-20201210093548972

安装MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[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

配置无需密码登录

略…

启动MySQL

1
2
3
service mysqld start

/usr/local/mysql/bin/mysql -uroot -p
1
2
3
4
5
6
7
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

1
2
3
4
5
6
7
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映射

1
2
3
4
5
6
7
8

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. 主
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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;

  1. 备

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    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

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

  2. 安装

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20

    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. 配置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    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. 启动

    1
    haproxy -f /etc/haproxy/haproxy.conf

串一串浪人

断舍离