Mysql组复制-简单集群搭建

你的名字 2023-02-27 15:52 24阅读 0赞

**主机分配：**

## 一 环境准备 ##

编辑三台主机的hosts文件：**重点：一定要注意设置好主机名以及hosts文件要不然mysql会找不到对应的机器**

192.168.182.100 MGR-node1
    192.168.182.101 MGR-node2
    192.168.182.102 MGR-node3

系统版本查看

[root@MGR-node1 ~]# cat /etc/redhat-release
    CentOS Linux release 7.6.1810 (Core)

为了方便实验，关闭所有节点的防火墙

[root@MGR-node1 ~]# systemctl stop firewalld
    [root@MGR-node1 ~]# firewall-cmd --state
    not running

[root@MGR-node1 ~]# cat /etc/sysconfig/selinux |grep "SELINUX=disabled"
    SELINUX=disabled
    [root@MGR-node1 ~]# setenforce 0 
    setenforce: SELinux is disabled
    [root@MGR-node1 ~]# getenforce 
    Disabled

## **二 在三个节点上安装Mysql5.7** ##

下载mysql官方源

[root@MGR-node1 ~]# yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

安装MySQL 5.7

[root@MGR-node1 ~]# yum install -y mysql-community-server

启动MySQL服务器和MySQL的自动启动

[root@MGR-node1 ~]# systemctl start mysqld.service
    [root@MGR-node1 ~]# systemctl enable mysqld.service

核对三台主机的mysql版本

[root@MGR-node1 ~]# mysql -V
    mysql  Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using  EditLine wrapper

查看初始密码并登录

[root@MGR-node1 ~]# cat /var/log/mysqld.log|grep 'A temporary password'
    2020-07-18T06:54:38.562194Z 1 [Note] A temporary password is generated for root@localhost: <Ezzv8w+pX7,

使用上面查看的密码<Ezzv8w+pX7, 登录mysql，并重置密码为123456

[root@MGR-node1 ~]# mysql -p'<Ezzv8w+pX7,'

进入数据库设置简单密码策略

mysql> set global validate_password_policy=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set global validate_password_length=1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set password=password("123456");
    Query OK, 0 rows affected, 1 warning (0.00 sec)
        
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

## 三、MGR组复制环境部署 (多写模式) ##

这里有几个命令大家可能会用到，在部署环境命令错误时，需要初始化mysql。

# systemctl stop mysqld
    # rm -rf /var/lib/mysql
    # systemctl start mysqld

执行完，需要重新查看初始密码登入并修改密码

**注意：3台节点的操作略有不同，不建议克隆操作**

## 1) MGR-node1 配置 ##

[root@MGR-node1 ~]# cp /etc/my.cnf /etc/my.cnf.bak
    [root@MGR-node1 ~]# >/etc/my.cnf
    [root@MGR-node1 ~]# vim /etc/my.cnf

[mysqld]
    datadir = /var/lib/mysql
    socket = /var/lib/mysql/mysql.sock
    
    symbolic-links = 0
    
    log-error = /var/log/mysqld.log
    pid-file = /var/run/mysqld/mysqld.pid
    
    #GTID:
    server_id = 1
    gtid_mode = on
    enforce_gtid_consistency = on
    
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    binlog_checksum=NONE
    
    #binlog
    log_bin = mysql-bin
    log-slave-updates = 1
    binlog_format = row
    sync-master-info = 1
    sync_binlog = 1
    
    #relay log
    skip_slave_start = 1
    
    transaction_write_set_extraction=XXHASH64
    loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820"
    loose-group_replication_start_on_boot=off
    loose-group_replication_local_address= "192.168.182.100:24901"
    loose-group_replication_group_seeds= "192.168.182.100:24901,192.168.182.101:24901,192.168.182.102:24901"
    loose-group_replication_bootstrap_group=off
    loose-group_replication_single_primary_mode=off
    loose-group_replication_enforce_update_everywhere_checks=on
    loose-group_replication_ip_whitelist="192.168.182.0/24,127.0.0.1/8"

注意下图的几点：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h0OTk5OWk_size_16_color_FFFFFF_t_70]  
修改完成后保存  
重启mysql服务

[root@MGR-node1 ~]# systemctl restart mysqld

登录mysql进行相关设置操作

[root@MGR-node1 ~]# mysql -p123456

mysql> SET SQL_LOG_BIN=0; 
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@'%' IDENTIFIED BY 'slave@123';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
     
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.01 sec)
     
    mysql> reset master;
    Query OK, 0 rows affected (0.19 sec)
     
    mysql> SET SQL_LOG_BIN=1;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='slave@123' FOR CHANNEL 'group_replication_recovery';
    Query OK, 0 rows affected, 2 warnings (0.33 sec)
     
    mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> SHOW PLUGINS;
    +----------------------------+----------+--------------------+----------------------+---------+
    | Name                       | Status   | Type               | Library              | License |
    +----------------------------+----------+--------------------+----------------------+---------+
    ...............
    ...............
    | group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
    +----------------------------+----------+--------------------+----------------------+---------+
    46 rows in set (0.00 sec)
     
    mysql> SET GLOBAL group_replication_bootstrap_group=ON;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> START GROUP_REPLICATION;
    Query OK, 0 rows affected (2.34 sec)
     
    mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> SELECT * FROM performance_schema.replication_group_members;
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    | group_replication_applier | 42ca8591-34bb-11e9-8296-005056ac6820 | MGR-node1   |        3306 | ONLINE       |
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    1 row in set (0.00 sec)

最后一栏保证是ONLINE 才是正常的！

创建一个测试库

mysql> CREATE DATABASE xingming CHARACTER SET utf8 COLLATE utf8_general_ci;     
    Query OK, 1 row affected (0.03 sec)
      
    mysql> use xingming;
    Database changed
    mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
    Query OK, 0 rows affected (0.24 sec)
    
    mysql> insert into xingming.ziliao values(1,"maliu"),(2,"tangwu"),(3,"lisi"),(4,"zhangsan");
    Query OK, 4 rows affected (0.01 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    
    
    mysql> select * from xingming.ziliao;
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | maliu    |
    |  2 | tangwu   |
    |  3 | lisi     |
    |  4 | zhangsan |
    +----+----------+
    4 rows in set (0.00 sec)

--------------------

## 2) MGR-node2 配置 ##

[root@MGR-node2 ~]# cp /etc/my.cnf /etc/my.cnf.bak
    [root@MGR-node2 ~]# >/etc/my.cnf
    [root@MGR-node2 ~]# vim /etc/my.cnf

[mysqld]
    datadir = /var/lib/mysql
    socket = /var/lib/mysql/mysql.sock
    
    symbolic-links = 0
    
    log-error = /var/log/mysqld.log
    pid-file = /var/run/mysqld/mysqld.pid
    
    #GTID:
    server_id = 2
    gtid_mode = on
    enforce_gtid_consistency = on
    
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    binlog_checksum=NONE
    
    #binlog
    log_bin = mysql-bin
    log-slave-updates = 1
    binlog_format = row
    sync-master-info = 1
    sync_binlog = 1
    
    #relay log
    skip_slave_start = 1
    
    transaction_write_set_extraction=XXHASH64
    loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820"
    loose-group_replication_start_on_boot=off
    loose-group_replication_local_address= "192.168.182.101:24901"
    loose-group_replication_group_seeds= "192.168.182.100:24901,192.168.182.101:24901,192.168.182.102:24901"
    loose-group_replication_bootstrap_group=off
    loose-group_replication_single_primary_mode=off
    loose-group_replication_enforce_update_everywhere_checks=on
    loose-group_replication_ip_whitelist="192.168.182.0/24,127.0.0.1/8"

重启mysql服务

[root@MGR-node2 ~]# systemctl restart mysqld

登录mysql进行相关设置操作

[root@MGR-node2 ~]# mysql -p123456
    .........
    mysql> SET SQL_LOG_BIN=0;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@'%' IDENTIFIED BY 'slave@123';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
     
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> reset master;
    Query OK, 0 rows affected (0.17 sec)
     
    mysql> SET SQL_LOG_BIN=1;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='slave@123' FOR CHANNEL 'group_replication_recovery';
    Query OK, 0 rows affected, 2 warnings (0.21 sec)
     
    mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
    Query OK, 0 rows affected (0.20 sec)
     
    mysql> SHOW PLUGINS;
    +----------------------------+----------+--------------------+----------------------+---------+
    | Name                       | Status   | Type               | Library              | License |
    +----------------------------+----------+--------------------+----------------------+---------+
    .............
    .............
    | group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
    +----------------------------+----------+--------------------+----------------------+---------+
    46 rows in set (0.00 sec)
     
    mysql> START GROUP_REPLICATION;
    Query OK, 0 rows affected (6.25 sec)
    
    mysql> SELECT * FROM performance_schema.replication_group_members;
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    | group_replication_applier | 8af18ff4-c8c3-11ea-bb9d-000c29e59105 | mgr-node1   |        3306 | ONLINE       |
    | group_replication_applier | b9bfc6e9-c8c7-11ea-b043-000c29c3f2a0 | mgr-node2   |        3306 | ONLINE       |
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    2 rows in set (0.00 sec)

查看下，发现已经将MGR-node1节点添加的数据同步过来了

mysql> use xingming
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from ziliao;
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | maliu    |
    |  2 | tangwu   |
    |  3 | lisi     |
    |  4 | zhangsan |
    +----+----------+
    4 rows in set (0.01 sec)

--------------------

## 3) MGR-node3 配置 ##

[root@MGR-node3 ~]# cp /etc/my.cnf /etc/my.cnf.bak
    [root@MGR-node3 ~]# >/etc/my.cnf
    [root@MGR-node3 ~]# vim /etc/my.cnf

[mysqld]
    datadir = /var/lib/mysql
    socket = /var/lib/mysql/mysql.sock
    
    symbolic-links = 0
    
    log-error = /var/log/mysqld.log
    pid-file = /var/run/mysqld/mysqld.pid
    
    #GTID:
    server_id = 3
    gtid_mode = on
    enforce_gtid_consistency = on
    
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    binlog_checksum=NONE
    
    #binlog
    log_bin = mysql-bin
    log-slave-updates = 1
    binlog_format = row
    sync-master-info = 1
    sync_binlog = 1
    
    #relay log
    skip_slave_start = 1
    
    transaction_write_set_extraction=XXHASH64
    loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820"
    loose-group_replication_start_on_boot=off
    loose-group_replication_local_address= "192.168.182.102:24901"
    loose-group_replication_group_seeds= "192.168.182.100:24901,192.168.182.101:24901,192.168.182.102:24901"
    loose-group_replication_bootstrap_group=off
    loose-group_replication_single_primary_mode=off
    loose-group_replication_enforce_update_everywhere_checks=on
    loose-group_replication_ip_whitelist="192.168.182.0/24,127.0.0.1/8"

重启mysql服务

[root@MGR-node3 ~]# systemctl restart mysqld

登录mysql进行相关设置操作

[root@MGR-node3 ~]# mysql -p123456
    ..........
    mysql> SET SQL_LOG_BIN=0;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@'%' IDENTIFIED BY 'slave@123';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
     
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.01 sec)
     
    mysql> reset master;
    Query OK, 0 rows affected (0.10 sec)
     
    mysql> SET SQL_LOG_BIN=1;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='slave@123' FOR CHANNEL 'group_replication_recovery';
    Query OK, 0 rows affected, 2 warnings (0.27 sec)
     
    mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
    Query OK, 0 rows affected (0.04 sec)
     
    mysql> SHOW PLUGINS;
    +----------------------------+----------+--------------------+----------------------+---------+
    | Name                       | Status   | Type               | Library              | License |
    +----------------------------+----------+--------------------+----------------------+---------+
    .............
    | group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
    +----------------------------+----------+--------------------+----------------------+---------+
    46 rows in set (0.00 sec)
     
    mysql> START GROUP_REPLICATION;
    Query OK, 0 rows affected (4.54 sec)
     
    mysql> SELECT * FROM performance_schema.replication_group_members;
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    | group_replication_applier | 8af18ff4-c8c3-11ea-bb9d-000c29e59105 | mgr-node1   |        3306 | ONLINE       |
    | group_replication_applier | 8af6f48d-c8c3-11ea-8105-000c2908dcc4 | mgr-node3   |        3306 | ONLINE       |
    | group_replication_applier | b9bfc6e9-c8c7-11ea-b043-000c29c3f2a0 | mgr-node2   |        3306 | ONLINE       |
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    3 rows in set (0.00 sec)

验证一下是否同步数据

只要组状态栏里都是online就说明是正常的  
大功告成，可以测试一下是否一个节点写入，同时更新

**node2节点操作**

mysql> select * from ziliao;
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | maliu    |
    |  2 | tangwu   |
    |  3 | lisi     |
    |  4 | zhangsan |
    +----+----------+
    4 rows in set (0.00 sec)
    
    mysql> insert into xingming.ziliao values(11,"beijing"),(12,"shanghai"),(13,"anhui");
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0

node1查看

mysql> select * from xingming.ziliao;
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | maliu    |
    |  2 | tangwu   |
    |  3 | lisi     |
    |  4 | zhangsan |
    | 11 | beijing  |
    | 12 | shanghai |
    | 13 | anhui    |
    +----+----------+
    7 rows in set (0.00 sec)
    
    mysql> update xingming.ziliao set id=100 where name="anhui"; ##更新一个数据
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

在node3上验证

mysql> select * from ziliao;
    +-----+----------+
    | id  | name     |
    +-----+----------+
    |   1 | maliu    |
    |   2 | tangwu   |
    |   3 | lisi     |
    |   4 | zhangsan |
    |  11 | beijing  |
    |  12 | shanghai |
    | 100 | anhui    |
    +-----+----------+
    7 rows in set (0.00 sec)

验证成功，说明mysql的gtid组复制多点写入搭建就成功了，  
下面分享一些常见报错和解决方法

**原因：如果my.cnf没问题的话，那就是hosts文件和主机名了。**

当配置了错误的组信息，也无法插入数据时，  
可以使用如下命令重新配置组信息

mysql> stop group_replication;  ##先停止组复制集群
    mysql> reset master;            ##初始化master
    mysql> start group_replication; ##开始组复制集群
    mysql> SELECT * FROM performance_schema.replication_group_members; ##查看组复制状态

--------------------

***-------------------------当你发现自己突然嫉妒起其他人时，就请安静下来学习吧！------------------------***

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h0OTk5OWk_size_16_color_FFFFFF_t_70]: /images/20230209/6524ba4dc0d84bca9b738a6fb92818c5.png