欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-24 11:49:55
MGR组复制环境部署 (多写模式)一、环境准备192.168.145.134 MGR-node1 (master1)192.168.145.135 MGR-node2 (master2)192.168.145.136 MGR-node3 (master3)192.168.145.137 ProxySQL-node查看系统版本信息[root@MGR-node1 ~]# cat /etc/redhat-releaseCentOS Linux release 7.6.1810 (Core)为了方...

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

一、环境准备

192.168.145.134 MGR-node1 (master1)
192.168.145.135 MGR-node2 (master2)
192.168.145.136 MGR-node3 (master3)
192.168.145.137 ProxySQL-node
查看系统版本信息
[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
[root@MGR-node1 ~]# scp /etc/hosts 192.168.145.135:/etc/
[root@MGR-node1 ~]# scp /etc/hosts 192.168.145.136:/etc/

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

安装mysql

[root@MGR-node1 ~]# yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
[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 ~]# cat /var/log/mysqld.log|grep 'A temporary password'
2020-07-18T09:02:57.600363Z 1 [Note]A temporary password is generated for  root@localhost: eabae!YIr5Gg                    
登录mysql并设置密码安全策略              
[root@MGR-node1 ~]# mysql -peabae!YIr5Gg
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("123.com");
Query OK, 0 rows affected, 1 warning (0.00 sec)
    
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查看mysql版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.31    |
+-----------+
1 row in set (0.00 sec)
查看uuid
mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 9b85c047-c8d9-11ea-8107-000c2958bafd |
+--------------------------------------+
1 row in set (0.00 sec)

备份数据库配置文件三台同文件格式,server_id和IP得更改

[root@MGR-node1 ~]# cp /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  #指示Server必须为每个事务收集写集合,并使用XXHASH64哈希算法将其编码为散列
loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820" #表示将加入或者创建的复制组命名为5db40c3c-180c-11e9-afbf-005056ac6820,可以自己指定
loose-group_replication_start_on_boot=off  #设置为Server启动时不自动启动组复制
loose-group_replication_local_address= "192.168.145.134:24901" #绑定本地的192.168.145.134以及24901端口接受其他组成员的连接,IP地址必须为其他组成员可正常访问
loose-group_replication_group_seeds= "192.168.145.134:24901,192.168.145.135:24901,192.168.145.136:24901"   #本行为告诉服务器当服务器加入组时,应当连接到这些192.168.145.134:24901,192.168.145.135:24901,192.168.145.136:24901种子服务器进行配置。本设置可以不是全部的组成员服务地址
loose-group_replication_bootstrap_group=off   #配置是否自动引导组
loose-group_replication_single_primary_mode=off #设置组自动选择一个 server 来处理读/写工作。 这个 server 是主(PRIMARY),所有其他的都是从
loose-group_replication_enforce_update_everywhere_checks=on  #多主模式下为多主更新启用或禁用严格一致性检查。
loose-group_replication_ip_whitelist="192.168.145.0/24,127.0.0.1/8" 

数据库内部操作修改密码策略(生产不建议这么简单)配置组发现和授权(三天同配)


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> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@'%' IDENTIFIED BY '123.com'
    -> ;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.00 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='123.com' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.05 sec)

mysql> show plugins;

| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
| 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 (3.18 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 773a7f5c-c8d5-11ea-8c0c-000c29c0e6e0 | MGR-node2   |        3306 | ONLINE       |
| group_replication_applier | 773c9bf1-c8d5-11ea-9186-000c2958bafd | MGR-node1   |        3306 | ONLINE       |
| group_replication_applier | 77411665-c8d5-11ea-aa07-000c293a4b1b | MGR-node3   |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from test.haha;
+----+----------+
| id | name     |
+----+----------+
|  1 | wangwu   |
|  2 | maliu    |
|  3 | zhangsan |
|  4 | lisi     |
+----+----------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 773a7f5c-c8d5-11ea-8c0c-000c29c0e6e0 | MGR-node2   |        3306 | ONLINE       |
| group_replication_applier | 773c9bf1-c8d5-11ea-9186-000c2958bafd | MGR-node1   |        3306 | ONLINE       |
| group_replication_applier | 77411665-c8d5-11ea-aa07-000c293a4b1b | MGR-node3   |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.01 sec)

测试:

node2上更新数据
mysql> insert into test.haha 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 test.haha;
+----+----------+
| id | name     |
+----+----------+
|  1 | wangwu   |
|  2 | maliu    |
| 11 | beijing  |
| 12 | shanghai |
| 13 | anhui    |
+----+----------+
5 rows in set (0.00 sec)

接下来的是ProxySQL读写分离和主节点故障无感知切换,请稍等》》》》》》

本文地址:https://blog.csdn.net/alanpo_/article/details/107457544

相关标签: 运维 mysql