MySQL mgr部署文档

云掣YunChe3周前技术文章60


一、环境说明

1.1服务器信息

截图_20250122172137.png1.2目录规划


1.2目录规划

截图_20250122172420.png

二、环境配置


2.1 关闭防火墙和selinux

service iptabls stop
/etc/selinux/config
SELINUX=disabled


2.2 配置主机名

hostname

2.3 配置ip域名映射

vi /etc/hosts
172.16.104.8  172-16-104-8
172.16.104.9  172-16-104-9
172.16,104.10 172-16-104-10

2.4 操作系统调优

cat>>/etc/sysctl.conf <<EOF
vm.swappiness = 0
fs.aio-max-nr = 1048576
fs.file-max = 681574400
kernel.shmmax = 137438953472
kernel.shmmni = 4096
kernel.sem = 250 32000 100 200
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
EOF
 
cat>>/etc/security/limits.conf <<EOF
mysql soft nproc 65536
mysql hard nproc 65536
mysql soft nofile 65536
mysql hard nofile 65536
EOF
 
cat>>/etc/profile<<EOF
if [ $USER = "mysql" ]; then
    ulimit -u 16384 -n 65536
fi
EOF

2.5 关闭numa

cat>>/etc/sysctl.conf <<EOF
vm.swappiness = 0
fs.aio-max-nr = 1048576
fs.file-max = 681574400
kernel.shmmax = 137438953472
kernel.shmmni = 4096
kernel.sem = 250 32000 100 200
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
EOF
 
cat>>/etc/security/limits.conf <<EOF
mysql soft nproc 65536
mysql hard nproc 65536
mysql soft nofile 65536
mysql hard nofile 65536
EOF
 
cat>>/etc/profile<<EOF
if [ $USER = "mysql" ]; then
    ulimit -u 16384 -n 65536
fi
EOF

三、部署mysql


3.1 创建用户

groupadd mysql
useradd -g mysql mysql -s /sbin/nologin
id mysql

3.2 配置目录

mkdir -pv /data/mysql/{data,logs,binlog,tmp,redo}
chown -R mysql:mysql /data/mysql/

3.3 解压安装包

mkdir -pv /data/mysql/{data,logs,binlog,tmp,redo}
chown -R mysql:mysql /data/mysql/

3.4 配置环境变量

mkdir -pv /data/mysql/{data,logs,binlog,tmp,redo}
chown -R mysql:mysql /data/mysql/

3.5 修改配置文件

[client]
port    = 3306
socket  = /data/mysql/data/mysql.sock
 
[mysqld]
user=mysql
port=3306
basedir=/usr/local/mysql
datadir=/data/mysql/data
tmpdir =/data/mysql/tmp
socket=/data/mysql/data/mysql.sock
log-error = /data/mysql/logs/error.log
symbolic-links=0
 
server-id=30061   //各节点server-id不要一致
port=3306
gtid_mode=on
enforce_gtid_consistency=on
log-bin = /data/mysql/binlog/binlog
binlog_format=row
binlog_checksum=none
log_slave_updates=on
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log = /data/mysql/binlog/relaylog
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="02eb0070-4419-463f-ac3aece40c0f6ac0"
loose-group_replication_start_on_boot=off
loose-group_replication_member_weigth = 40
loose-group_replication_local_address="172.16.104.8:30061"  //各节点服务器ip:server-id
loose-group_replication_group_seeds="172.16.104.8:30061,172.16.104.9:30062,172.16.104.10:30063"

3.6 初始化

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure &
//初始化加上--initialize-insecure 选项会设置密码为空


3.7 启动mysql

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &


四、配置MGR

4.1启动mgr单主模式

#以下步骤所有节点均执行
mysql  #登陆数据库
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #安装插件
mysql> SET SQL_LOG_BIN=0; #关闭binlog
mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl'; #创建复制账号
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; #授权
mysql> FLUSH PRIVILEGES; #刷新权限
mysql> SET SQL_LOG_BIN=1; #开启binlog
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery'; #创建复制
 
#启动MGR,在172.16.104.8上执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
 
#将其他节点加入MGR,在从库172.16.104.9、172.16.104.10上执行
mysql> START GROUP_REPLICATION;
查看MGR组信息
root@mysql 02:30:  [(none)]>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 19b47031-c397-11ea-a83b-000c29652de7 | node2       |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
| group_replication_applier | 627eae86-c38a-11ea-9dcf-000c2989a02a | node1       |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 652b5dc9-c397-11ea-a416-000c2936f782 | node3       |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
#如果在启动过程中报错,可以参考
performance_schema.replication_connection_status或者日志输出解决问题。

4.2切换为MGR多主模式

#以下步骤所有节点均执行
mysql> stop group_replication;
#是否启动单主模式,如果启动,则本实例是主库,提供读写,其他实例仅提供读,如果为off就是多主模式了
mysql> set global group_replication_single_primary_mode=OFF;
#多主模式下,强制检查每一个实例是否允许该操作,如果不是多主,可以关闭 mysql> set global
group_replication_enforce_update_everywhere_checks=ON;
 
#只在一个节点上执行,任意节点都可以
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
#在其余节点执行
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 | 172.16.104.8 | 3306 | ONLINE | PRIMARY | 8.0.17 | |
group_replication_applier | 9bff969a-c743-11e9-90c0-000c29a2154f | 172.16.104.9 | 3306 | ONLINE | PRIMARY | 8.0.17 | |
group_replication_applier | c8dca41c-c744-11e9-8443-000c2943d916 | 172.16.104.10|3306| ONLINE | PRIMARY | 8.0.17 | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.03 sec)

4.3切换为MGR单主模式

#以下步骤所有节点均执行
mysql> stop group_replication;
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
mysql> set global group_replication_single_primary_mode=ON;
#主节点执行,在192.168.0.118上执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
#在其余节点执行
START GROUP_REPLICATION;
查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 | 192.168.0.118 |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
| group_replication_applier | 9bff969a-c743-11e9-90c0-000c29a2154f | 192.168.0.119 |        3306 | ONLINE       | SECONDARY   | 8.0.17         |
| group_replication_applier | c8dca41c-c744-11e9-8443-000c2943d916 | 192.168.0.120 |        3306 | ONLINE       | SECONDARY   | 8.0.17         |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

4.4单主模式下的failover

#mgr查找主节点:
SHOW STATUS LIKE 'group_replication_primary_member';
SELECT * FROM performance_schema.replication_group_members;
 
#模拟主节点宕机:
[root@172-16-104-8 data]# mysqladmin -uroot -p  shutdown
Enter password:
2020-10-28T11:54:54.927610Z mysqld_safe mysqld from pid file /data/mysql/data/172-16-104-8.pid ended
[1]+  Done                    /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf  (wd: /data)
(wd now: /data/mysql/data)
 
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | 0f2c4453-18e6-11eb-b5c0-faebd3a13700 | 172-16-104-10 |        3306 | ONLINE       |
| group_replication_applier | 79f6ff2c-18e4-11eb-964a-fa566a1b9b00 | 172-16-104-9  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
2 rows in set (0.00 sec)
#主节点切换到了节点172.16.104.10
mysql> SHOW STATUS LIKE 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 0f2c4453-18e6-11eb-b5c0-faebd3a13700 |
+----------------------------------+--------------------------------------+
1 row in set (0.02 sec)
 
mysql> show variables like '%group_replication_member_weight%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| group_replication_member_weight | 50    |
+---------------------------------+-------+
1 row in set (0.01 sec)
 
 
将主节点重启后,重新加入
mysql> select * from performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+-----------+-------------+-------------+--------------+
| group_replication_applier |           |             |        NULL | OFFLINE      |
+---------------------------+-----------+-------------+-------------+--------------+
1 row in set (0.00 sec)
 
mysql> set global group_replication_single_primary_mode=on;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
 
mysql> start group_replication;
Query OK, 0 rows affected (3.01 sec)
 
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | 0f2c4453-18e6-11eb-b5c0-faebd3a13700 | 172-16-104-10 |        3306 | ONLINE       |
| group_replication_applier | 10517159-18cd-11eb-bd67-faa7dd2bc000 | 172-16-104-8  |        3306 | ONLINE       |
| group_replication_applier | 79f6ff2c-18e4-11eb-964a-fa566a1b9b00 | 172-16-104-9  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
3 rows in set (0.00 sec)


相关文章

hive元数据操作

1.查看hive从超过5000分区的表select dbs.name, tbls.TBL_NAME, count(1) as part_count from dbs, tbls, partitions...

迁移Cloudera Manager节点

迁移Cloudera Manager节点

1.概述1.CDH环境已搭建并正常运行2.旧Cloudera Manager节点包含Cloudera Manager Server(即cloudera-scm-server)服务和Cloudera M...

Storage Classes之nfs provisioner

Storage Classes之nfs provisioner

每个 StorageClass 都有一个制备器(Provisioner),用来决定使用哪个卷插件制备 PV。 该字段必须指定。这里我们使用nfs作为StorageClass的制备器,官方并未对nfs进...

kaniko构建镜像的方式

docker构建镜像用docker来构建容器镜像也是常用的方法,在具备构建容器镜像所需的两个要素(Dockerfile & 上下文)的前提下,用下述命令就能构建一个容器镜像出来```Plain...

gitlab的部署

gitlab的部署

一、GitLab简介GitLab 是一个用于仓库管理系统的开源项目。使用Git作为代码管理工具,并在此基础上搭建起来的web服务。可通过Web界面进行访问公开的或者私人项目。它拥有与Github类似的...

rabbitmq-监控告警

rabbitmq-监控告警

插件安装rabbitmq_prometheus这个插件包含在RabbitMQ3.9.x版本中。与所有的插件一样,必须启用它才能使用;在node1,node2,node3 三台机器上执行如下命令:rab...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。