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
上一篇: Lua教程(十一):模块与包详解
下一篇: Linux 中常用的sed命令