MySQL 8.0.30 MGR手工安装及新增节点

原创柚子身上依云和恩墨交付战队 2022-09-07

1576

实施步骤

1.实施规划

首先由192.168.11.11~13进行搭建MGR 1主2从环境，然后新增192.168.11.14从节点节点。

IP	hostname	MySQL PORT	Mgr PORT
192.168.11.11	mysqlmgr1	3306	33061
192.168.11.12	mysqlmgr2	3306	33061
192.168.11.13	mysqlmgr3	3306	33061
192.168.11.14	mysqlmgr4	3306	33061

2.MySQL环境安装

每个节点执行

2.1.配置/etc/hosts

# vi /etc/hosts
192.168.11.11  mysqlmgr1
192.168.11.12  mysqlmgr2
192.168.11.13  mysqlmgr3
192.168.11.13  mysqlmgr4

2.2.安装mysql软件

–下载路径: https://dev.mysql.com/downloads/mysql/

# tar -xf mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz -C /usr/local
# cd /usr/local
# mv mysql-8.0.30-linux-glibc2.12-x86_64 mysql

2.3.配置环境变量

# cat .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

export PATH

# . ~/.bash_profile

2.4.配置my.cnf文件

--192.168.11.11配置文件
# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
log_timestamps=SYSTEM
user = mysql
log_error_verbosity = 3

log-bin=binlog
binlog-format=row
log_slave_updates=ON
binlog_checksum=CRC32

master-info-repository=TABLE
relay-log-info-repository=TABLE
gtid-mode=on
enforce-gtid-consistency=true
binlog_transaction_dependency_tracking=writeset
transaction_write_set_extraction=XXHASH64
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers=128 #Can be set to 2-4 times the number of logical CPUs
sql_require_primary_key=1
slave_preserve_commit_order=1
slave_checkpoint_period=2

#mgr
loose-plugin_load_add='mysql_clone.so'
loose-plugin_load_add='group_replication.so'

#The group_replication_group_name value of all nodes must be the same
#This is a standard UUID format, which can be specified manually or a randomly generated UUID
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"

#Specify the IP+port of each node in the MGR cluster, this port is dedicated to MGR, not the usual mysqld instance port
#If you are deploying an MGR cluster on multiple nodes, pay attention to whether this port will be blocked by the firewall
loose-group_replication_group_seeds= "192.168.11.11:33061,192.168.11.12:33061,192.168.11.13:33061"

#It is not recommended to start the MGR service at the same time as mysqld
loose-group_replication_start_on_boot=off

#By default, do not use as the MGR cluster boot node, manually execute it when necessary and immediately change it back to OFF state
loose-group_replication_bootstrap_group=off

#When exiting MGR, set the instance to read_only to avoid misoperation to write data
loose-group_replication_exit_state_action=READ_ONLY

#Generally there is no need to open the flow control mechanism
loose-group_replication_flow_control_mode = "DISABLED"

#【Strongly】Single-master mode is recommended, if it is for experimental purposes, you can try to play in multi-master mode
loose-group_replication_single_primary_mode=ON


datadir=/data/mysql/mgr/
socket=/data/mysql/mgr/mysql.sock
port=3306
server_id=11
log-error=/data/mysql/mgr/error.log
#Specify the IP+port of this node
loose-group_replication_local_address= "192.168.11.11:33061"

其他的节点需要修改参数文件中下面几个参数值:

--192.168.11.12
port=3306
server_id=12
loose-group_replication_local_address= "192.168.11.12:33061"
--192.168.11.13
port=3306
server_id=13
loose-group_replication_local_address= "192.168.11.13:33061"

2.5.初始化MySQL实例

# useradd mysql
# mkdir -p /data/mysql/mgr
# chown mysql.mysql -R /data
# mysqld --no-defaults --datadir=/data/mysql/mgr --initialize --user=mysql

2.6.修改用户密码

mysql> alter user root@'localhost' identified by 'root';

3.MGR配置

3.1.检查group_replication和clone插件

--配置文件已经配置下面参数,正常启动已经加载完毕:
loose-plugin_load_add='mysql_clone.so'
loose-plugin_load_add='group_replication.so'

mysql> select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,LOAD_OPTION from information_schema.plugins where PLUGIN_NAME in('clone','group_replication');
+-------------------+----------------+---------------+-------------+
| PLUGIN_NAME       | PLUGIN_VERSION | PLUGIN_STATUS | LOAD_OPTION |
+-------------------+----------------+---------------+-------------+
| clone             | 1.0            | ACTIVE        | ON          |
| group_replication | 1.1            | ACTIVE        | ON          |
+-------------------+----------------+---------------+-------------+

--如果加载有问题,需要排查日志进行解决,或者手工加载,手工加载方式如下:
mysql> INSTALL PLUGIN group_replication SONAME'group_replication.so';
mysql> INSTALL PLUGIN clone SONAME'mysql_clone.so';

3.2.创建MGR用户

mysql> CREATE USER repl@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY'repl';
mysql> GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO `repl`@`%`;

3.3.重置MySQL环境及创建复制通道

mysql> RESET MASTER; RESET SLAVE ALL;
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='repl', SOURCE_PASSWORD='repl' FOR CHANNEL'group_replication_recovery';

Step 3.1-3.4 需要在每个节点执行!!!

3.4.初始化主节点

--192.168.11.11上执行
mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication USER='repl', PASSWORD='repl';
mysql> set global group_replication_bootstrap_group=OFF;
mysql> SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 923cdab2-2351-11ed-934f-0050568a658a | mysqlmgr1    |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.07 sec)
mysql> SELECT * FROM performance_schema.replication_group_member_stats\G
*************************** 1. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 16613909800305512:1
                                 MEMBER_ID: 923cdab2-2351-11ed-934f-0050568a658a
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 0
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-26
            LAST_CONFLICT_FREE_TRANSACTION: 
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 1
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
1 row in set (0.02 sec)

3.5.备节点启动

–192.168.11.12、13上执行

mysql> start group_replication USER='repl', PASSWORD='repl';

3.6.检查member成员状态

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 5efcf3ca-2352-11ed-9da5-0050568a0095 | mysqlmgr2   |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 923cdab2-2351-11ed-934f-0050568a658a | mysqlmgr1   |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
| group_replication_applier | fd7ab357-2352-11ed-9d3d-0050568a3c82 | mysqlmgr3   |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

3.7.创建测试数据验证同步

--主库
mysql> create database enmo;
mysql> use enmo
mysql> insert into t values(1),(2),(3);
--备库
mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

4.新增节点

4.1.创建新节点mysql实例

参考2.1~2.6步骤

4.2.使用clone方式创建

登录新增的节点执行

mysql> set global clone_valid_donor_list='192.168.11.13:3306';
mysql> clone instance from repl@192.168.11.13:3306 identified by'repl';
mysql> start group_replication USER='repl', PASSWORD='repl';

4.3.检查数据

mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

4.4.检查member及连接状态

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 5efcf3ca-2352-11ed-9da5-0050568a0095 | mysqlmgr2   |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 923cdab2-2351-11ed-934f-0050568a658a | mysqlmgr1   |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
| group_replication_applier | cbc85a78-237b-11ed-a670-0050568a712b | mysqlmgr4   |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | fd7ab357-2352-11ed-9d3d-0050568a3c82 | mysqlmgr3   |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
4 rows in set (0.01 sec)

mysql> select * from performance_schema.replication_connection_status\G
*************************** 1. row ***************************
                                      CHANNEL_NAME: group_replication_applier
                                        GROUP_NAME: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1
                                       SOURCE_UUID: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1
                                         THREAD_ID: NULL
                                     SERVICE_STATE: ON
                         COUNT_RECEIVED_HEARTBEATS: 0
                          LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000
                          RECEIVED_TRANSACTION_SET: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-32
                                 LAST_ERROR_NUMBER: 0
                                LAST_ERROR_MESSAGE: 
                              LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_QUEUED_TRANSACTION: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:32
 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2022-08-25 10:37:47.942144
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2022-08-25 10:37:47.942144
     LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2022-08-25 10:37:47.942462
       LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2022-08-25 10:37:47.942535
                              QUEUEING_TRANSACTION: 
    QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
   QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 2. row ***************************
                                      CHANNEL_NAME: group_replication_recovery
                                        GROUP_NAME: 
                                       SOURCE_UUID: 
                                         THREAD_ID: NULL
                                     SERVICE_STATE: OFF
                         COUNT_RECEIVED_HEARTBEATS: 0
                          LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000
                          RECEIVED_TRANSACTION_SET: 
                                 LAST_ERROR_NUMBER: 0
                                LAST_ERROR_MESSAGE: 
                              LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_QUEUED_TRANSACTION: 
 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
     LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
       LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
                              QUEUEING_TRANSACTION: 
    QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
   QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000

最后修改时间：2022-12-19 21:51:15

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者