mysql 几种集群部署模式_mysql集群搭建几种方式

1.mysql 常见的部署模式: 一主一从  主主复制  一主多从  多主一从 外加haproxy代理

2.主从部署

 2.1单机安装mysql

 链接: 有道云笔记

2.2修改配置文件

master

[mysqld]
basedir=/usr/local/mysql5.7/
datadir=/usr/local/mysql5.7/data
user=mysql
max_connections=20000
character-set-server=utf8
default-storage-engine=INNODB
max_allowed_packet=16M
log-bin=mysql-bin
server-id=140
 
[client]
port=3306
 
 
[mysqld_safe]
log-error=/usr/local/mysql5.7/log/mysqld.log

slave01

[mysqld]
basedir=/usr/local/mysql5.7/
datadir=/usr/local/mysql5.7/data
user=mysql
max_connections=20000
character-set-server=utf8
default-storage-engine=INNODB
max_allowed_packet=16M
log-bin=mysql-bin
server-id=141
[client]
port=3306
[mysqld_safe]
log-error=/usr/local/mysql5.7/log/mysqld.log

slave02

[mysqld]
basedir=/usr/local/mysql5.7/
datadir=/usr/local/mysql5.7/data
user=mysql
max_connections=20000
character-set-server=utf8
default-storage-engine=INNODB
max_allowed_packet=16M
log-bin=mysql-bin
server-id=142
[client]
port=3306
[mysqld_safe]
log-error=/usr/local/mysql5.7/log/mysqld.log

在 140 主节点 master 查看状态

• SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1960 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------

在slave节点 执行以下指令链接到master

change master to master_host='192.168.174.204',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=0;

在slave

start slave
 
show slave status\G

接下自行测试了 

3.主主复制

选择两台服务器部署

master01(192.168.174.204)

[mysqld]
basedir=/usr/local/mysql5.7/
datadir=/usr/local/mysql5.7/data
user=mysql
max_connections=20000
character-set-server=utf8
default-storage-engine=INNODB
max_allowed_packet=16M
log-bin=mysql-bin
server-id=140
binlog_format=row  ## cdc
binlog-do-db=* 
relay-log = /usr/local/mysql5.7/data/relay-bin
relay-log-info-file = /usr/local/mysql5.7/data/relay-log.info
log-slave-updates
 
binlog-ignore-db=mysql
auto_increment_increment = 2
auto_increment_offset = 1
[client]
port=3306
[mysqld_safe]
log-error=/usr/local/mysql5.7/log/mysqld.log

master02(192.168.174.206)

[mysqld]
basedir=/usr/local/mysql5.7/
datadir=/usr/local/mysql5.7/data
user=mysql
max_connections=20000
character-set-server=utf8
default-storage-engine=INNODB
max_allowed_packet=16M
 
binlog_format=row  ## cdc
binlog-do-db=*
 
log-bin=mysql-bin
server-id=142
 
 
relay-log = /usr/local/mysql5.7/data/relay-bin
relay-log-info-file = /usr/local/mysql5.7/data/relay-log.info
log-bin = mysql-bin
log-slave-updates
auto_increment_increment = 2
auto_increment_offset = 2
binlog-ignore-db=mysql
 
 
[client]
port=3306
[mysqld_safe]
log-error=/usr/local/mysql5.7/log/mysqld.log

两台服务器重启

service mysqld restart

两台服务器对彼此赋权 可以连接

grant replication slave,replication client,select on *.* to slave@'192.168.174.%' identified by '123456';

步骤:

flush privileges;
 
##锁住库 两台
 flush tables with read lock; 
 
 show master status;(记住 position file)
 
 unlock tables;
 
 stop slave;
 
 
##master01
change master to master_host='192.168.174.206',master_user='root',master_password='123456',master_log_file='mysql-bin.000010',master_log_pos=640;
 
master02
change master to master_host='192.168.174.204',master_user='root',master_password='123456',master_log_file='mysql-bin.000008',master_log_pos=640;
 
 
 
start slave;
 
show slave status\G;
 
            ##Slave_IO_Running: Yes   这两个都是ON 就可以了
            ##Slave_SQL_Running: Yes 

接下来测试:略

4.MGR 模式

master

[mysqld]
basedir=/usr/local/mysql5.7/
datadir=/usr/local/mysql5.7/data
 
user=mysql
max_connections=20000
character-set-server=utf8
default-storage-engine=INNODB
max_allowed_packet=16M
 
log-bin=mysql-bin
server-id=140
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
 
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
 
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.174.204:24901"
loose-group_replication_group_seeds="192.168.174.204:24901,192.168.174.205:24901,192.168.174.206:24901"
loose-group_replication_bootstrap_group=off
report_host=192.168.174.204
report_port=3306
 
[client]
port=3306
 
[mysqld_safe]
log-error=/usr/local/mysql5.7/log/mysqld.log
pid-file=/usr/local/mysql5.7/data/localhost.localdomain.pid

slave01

[mysqld]
basedir=/usr/local/mysql5.7/
datadir=/usr/local/mysql5.7/data
 
user=mysql
max_connections=20000
character-set-server=utf8
default-storage-engine=INNODB
max_allowed_packet=16M
 
 
log-bin=mysql-bin
server-id=141
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
 
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
 
 
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.174.205:24901"
loose-group_replication_group_seeds="192.168.174.204:24901,192.168.174.205:24901,192.168.174.206:24901"
loose-group_replication_bootstrap_group=off
report_host=192.168.174.205
report_port=3306
 
 
[client]
port=3306
 
 
[mysqld_safe]
log-error=/usr/local/mysql5.7/log/mysqld.log
pid-file=/usr/local/mysql5.7/data/node02.pid

slave02

[mysqld]
basedir=/usr/local/mysql5.7/
datadir=/usr/local/mysql5.7/data
 
user=mysql
max_connections=20000
character-set-server=utf8
default-storage-engine=INNODB
max_allowed_packet=16M
 
 
log-bin=mysql-bin
server-id=142
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
 
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
 
 
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.174.206:24901"
loose-group_replication_group_seeds="192.168.174.204:24901,192.168.174.205:24901,192.168.174.206:24901"
loose-group_replication_bootstrap_group=off
report_host=192.168.174.206
report_port=3306
 
 
[client]
port=3306
 
 
[mysqld_safe]
log-error=/usr/local/mysql5.7/log/mysqld.log
pid-file=/usr/local/mysql5.7/data/node03.pid
 

接下来重启mysql服务 三台服务器 执行接下来的步骤

service mysqld restart

 INSTALL PLUGIN group_replication SONAME 'group_replication.so';
 
SET SQL_LOG_BIN=0;
 
CREATE USER kuber@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO kuber@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='kuber', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

单主模式

在主节点上部署

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
SELECT * FROM performance_schema.replication_group_members;
 
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST     | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
| group_replication_applier | 90b7daa9-405b-11ec-b230-000c294807c0 | 192.168.174.204 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+

在其他的slave节点上部署

START GROUP_REPLICATION;
 
##出现报错
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
##解决方案
 
set global group_replication_allow_local_disjoint_gtids_join=ON;
 
##接着继续操作
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;
 
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST     | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
| group_replication_applier | 0094f1a7-04a0-11ec-9e4e-000c29fb0b92 | 192.168.174.205 |        3306 | ONLINE       |
| group_replication_applier | 747ae5c5-4064-11ec-99d6-000c29b619af | 192.168.174.206 |        3306 | ONLINE       |
| group_replication_applier | 90b7daa9-405b-11ec-b230-000c294807c0 | 192.168.174.204 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
##注意 MEMBER_PORT 必须是  ONLINE 模式

当出现节点是 RECOVERING状态

#在主节点操作
show global variables like 'gtid_purged';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| gtid_purged   | 8769f936-3e51-11e9-acaa-005056ac6820:1-2 |
+---------------+------------------------------------------+
##如果没有值 那就三台执行以下步奏  如果有,那就其他两台执行以下的步奏
 
 STOP GROUP_REPLICATION;
 reset master;
 set global gtid_purged = '8769f936-3e51-11e9-acaa-005056ac6820:1-2';
 START GROUP_REPLICATION;
 
 ##查看节点状态
 SELECT * FROM performance_schema.replication_group_members;

接下测试:略

总结: 这种模式 只能在主节点上进行增删查改等操作,其他的slave 没有办法进行操作的,如果需要其他的slave的节点能进行增删查改的操作,那就需要接下的多主模式

输入接下的指令进行切换到多主模式

报错

ERROR 3094 (HY000): The START GROUP_REPLICATION command failed as the applier module failed to start.
##在my.ini 中添加
relay-log-recovery=1
 
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
 
## 解决方案
set global group_replication_bootstrap_group = ON; 
start group_replication;

查看角色(参考仅可)

SELECT 
    MEMBER_ID,
    MEMBER_HOST,
    MEMBER_PORT,
    MEMBER_STATE,
    IF(global_status.VARIABLE_NAME IS NOT NULL,
        'PRIMARY',
        'SECONDARY') AS MEMBER_ROLE
FROM
    performance_schema.replication_group_members
        LEFT JOIN
    performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member'
        AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID;

使用 show global variables like "%read_only%"; 查看 全部为OFF 即可

切换多主模式

##停止组复制 所有的节点都停止
stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;
 
 
##随便选择某个MGR
 SET GLOBAL group_replication_bootstrap_group=ON;
 START GROUP_REPLICATION;
 SET GLOBAL group_replication_bootstrap_group=OFF;
 
 ##在其他的两个节点操作
START GROUP_REPLICATION;
 
SELECT * FROM performance_schema.replication_group_members;
 
##所有的MGR节点都是ONLINE 即可

测试验证:略

切换到单主模式

 stop group_replication;
 set global group_replication_enforce_update_everywhere_checks=OFF;
 set global group_replication_single_primary_mode=ON;
 
 
 ## 选择主节点服务器
 SET GLOBAL group_replication_bootstrap_group=ON;
 START GROUP_REPLICATION;
 SET GLOBAL group_replication_bootstrap_group=OFF; 
 ## 在其他的节点操作
  START GROUP_REPLICATION;
 
  SELECT * FROM performance_schema.replication_group_members;
  
  ##所有的MGR节点都是ONLINE 即可

5. mysql haproxy 主主复制

5.1.首先关闭

firewall-cmd --state  
 
systemctl stop firewalld.service
 
systemctl disable firewalld.service 
 
## 修改该文件
vi /etc/selinux/config
 
SELINUX=disabled 
 
reboot

2.配置数据库双主复制

参考上面的 主主复制

3.配置haproxy

#添加用户组
groupadd haproxy
useradd -g haproxy haproxy
 
#install haproxy 
yum install haproxy
 
#加入 centos7 服务
chmod +x /etc/init.d/haproxy
chkconfig haproxy on
 
#开启日志服务
touch /var/log/haproxy.log
chown haproxy:haproxy /var/log/haproxy.log
chmod a+x /var/log/haproxy.log

开启rsyslog记录haproxy日志功能

vi /etc/rsyslog.conf 
(打开如下配置项)
# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514
# Provides TCP syslog reception
$ModLoad imtcp
$InputTCPServerRun 514
 
(在RULES块中添加)
#### RULES ####
# save haproxy.log
local0.* /var/log/haproxy.log

修改 /etc/sysconfig/rsyslog

vi /etc/sysconfig/rsyslog
 
# Options for rsyslogd
# Syslogd options are deprecated since rsyslog v3.
# If you want to use them, switch to compatibility mode 2 by "-c 2"
# See rsyslogd(8) for more details
SYSLOGD_OPTIONS="-r -m 0 -c 2"

编辑haproxy.cfg

global
    # to have these messages end up in /var/log/haproxy.log you will
    # need to:
    #
    # 1) configure syslog to accept network log events.  This is done
    #    by adding the '-r' option to the SYSLOGD_OPTIONS in
    #    /etc/sysconfig/syslog
    #
    # 2) configure local2 events to go to the /var/log/haproxy.log
    #   file. A line like the following can be added to
    #   /etc/sysconfig/syslog
    #
    #    local2.*                       /var/log/haproxy.log
    #
    log         127.0.0.1 local0
 
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
 
    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats
 
#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode                     tcp
    log                     global
    option                  httplog
    option                  dontlognull
    option                  http-server-close
    option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000
 
#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------
frontend  main *:5000   
#  acl url_static       path_beg       -i /static /images /javascript /stylesheets
# acl url_static       path_end       -i .jpg .gif .png .css .js
 
#  use_backend static          if url_static
    default_backend              mysql_backend
 
#---------------------------------------------------------------------
# static backend for serving up images, stylesheets and such
#---------------------------------------------------------------------
#backend static
#   balance     roundrobin
#   server      static 192.168.174.205:3306 check
 
#---------------------------------------------------------------------
# round robin balancing between the various backends
#--------------------------------------------------------------------
 backend              mysql_backend
    balance           leastconn
    server  mysql1 192.168.174.205:3306 check port 3306 maxconn 300
    server  mysql2 192.168.174.206:3306 check port 3306 maxconn 300
    #server  app3 127.0.0.1:5003 check
   # server  app4 127.0.0.1:5004 check
 
listen admin_stats
	mode    http
	stats   enable
	bind    *:7777
	stats   refresh 30s
	stats   uri /admin
	stats   realm haproxy
	stats   auth root:root
	stats   hide-version

重启rsyslog与Haproxy服务

service rsyslog restart
service haproxy restart

网页访问 192.168.174.204:7777 可以是mysql客户端 访问代理服务器

192.168.174.204:5000

kuberss

123456