MySQL

最新推荐文章于 2022-06-17 17:42:30 发布

Wielun

最新推荐文章于 2022-06-17 17:42:30 发布

阅读量386

点赞数

分类专栏：运维文章标签： MySQL主从复制 GTID MySQL半同步复制 MySQL读写分离 MySQL组复制

本文链接：https://blog.csdn.net/Dream_ya/article/details/81710522

版权

运维专栏收录该内容

80 篇文章 3 订阅

订阅专栏

一、安装MySQL

selinux iptables off
操作系统：redhat6.5

1、下载MySQL

官网下载地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads
百度网盘地址： https://pan.baidu.com/s/1NJiPYioi_843WfqMAI4PsA 密码: wdf8
编译安装MySQL博客链接：https://blog.csdn.net/Dream_ya/article/details/81585830

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.23-1.el6.x86_64.rpm-bundle.tar
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.23-1.el6.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.23-1.el6.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.23-1.el6.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-compat-5.7.23-1.el6.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.23-1.el6.x86_64.rpm

2、解压安装

[root@server1 ~]# tar xf mysql-5.7.23-1.el6.x86_64.rpm-bundle.tar
[root@server1 ~]# ls
mysql-5.7.23-1.el6.x86_64.rpm-bundle.tar          mysql-community-embedded-devel-5.7.23-1.el6.x86_64.rpm
mysql-community-client-5.7.23-1.el6.x86_64.rpm    mysql-community-libs-5.7.23-1.el6.x86_64.rpm
mysql-community-common-5.7.23-1.el6.x86_64.rpm    mysql-community-libs-compat-5.7.23-1.el6.x86_64.rpm
mysql-community-devel-5.7.23-1.el6.x86_64.rpm     mysql-community-server-5.7.23-1.el6.x86_64.rpm
mysql-community-embedded-5.7.23-1.el6.x86_64.rpm  mysql-community-test-5.7.23-1.el6.x86_64.rpm

[root@server1 ~]# yum install -y mysql-community-client-5.7.23-1.el6.x86_64.rpm  mysql-community-common-5.7.23-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.23-1.el6.x86_64.rpm mysql-community-libs-5.7.23-1.el6.x86_64.rpm mysql-community-server-5.7.23-1.el6.x86_64.rpm

3、进行初始化设置

[root@server1 ~]# /etc/init.d/mysqld restart
[root@server1 ~]# grep "temporary password" /var/log/mysqld.log           ###查看临时密码：ttjj&Eym0uRW
2018-08-15T13:40:18.809809Z 1 [Note] A temporary password is generated for root@localhost: ttjj&Eym0uRW

[root@server1 ~]# mysql_secure_installation

这里写图片描述

后面的选项一直输入y即可！！！

4、登陆测试

[root@server1 ~]# mysql -u root -pQ1w2e3r4t5y6.

这里写图片描述

5、发送到server2

[root@server1 ~]# scp mysql-community-client-5.7.23-1.el6.x86_64.rpm  mysql-community-common-5.7.23-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.23-1.el6.x86_64.rpm mysql-community-libs-5.7.23-1.el6.x86_64.rpm mysql-community-server-5.7.23-1.el6.x86_64.rpm  root@10.10.10.2:

6、进行安装

[root@server2 ~]# ls
mysql-community-client-5.7.23-1.el6.x86_64.rpm
mysql-community-common-5.7.23-1.el6.x86_64.rpm
mysql-community-libs-5.7.23-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.23-1.el6.x86_64.rpm
mysql-community-server-5.7.23-1.el6.x86_64.rpm

[root@server2 ~]# yum install -y *

然后按照上面把密码修改了以及进行初始化！！！

二、MySQL主从复制

1、配置my.cnf

[root@server1 ~]# vim /etc/my.cnf
server-id=1                      ###2的32次方减1，唯一标识符，不能一样
log-bin=mysql-bin                ###启动二进制日志

[root@server2 ~]# vim /etc/my.cnf
server-id=2

2、重启MySQL

[root@server1 ~]# /etc/init.d/mysqld restart
[root@server2 ~]# /etc/init.d/mysqld restart

3、配置相应权限

（1）MySQL中（server1）

[root@server1 ~]# mysql -uroot -p
mysql> show master status;
mysql> grant REPLICATION SLAVE on *.* to repl@'10.10.10.%' identified by 'Q1w2e3r4t5y6.';               ###root密码
mysql> show master status;

这里写图片描述

（2）MySQL中（server2）

[root@server2 ~]# mysql -uroot -p
###master_host：主IP master_log_pos：为master的position
mysql> change master to master_host='10.10.10.1',master_user='repl',master_password='Q1w2e3r4t5y6.',master_log_file='mysql-bin.000001',master_log_pos=446;        
mysql> start slave;                ###启动复制功能
mysql> show slave status\G;

这里写图片描述

4、测试功能

（1）在server1中建立库和表

mysql> show databases;
mysql> create database dream;
mysql> show databases;
mysql> use dream;
mysql> create table user (username varchar(20) not null,password varchar(15) not null);
mysql> insert into user value ('user1','123');
mysql> insert into user value ('user2','456');

这里写图片描述

（2）在server2中查看

mysql> show databases;
mysql> use dream;
mysql> show tables;
mysql> select * from user;

这里写图片描述

（3）在server1中删除

mysql> delete from user where username='user1';
Query OK, 1 row affected (0.06 sec)
mysql> select * from user;
+----------+----------+
| username | password |
+----------+----------+
| user2    | 456      |
+----------+----------+
1 row in set (0.00 sec)

（4）查看是否同步（server2）

mysql> select * from user;
+----------+----------+
| username | password |
+----------+----------+
| user2    | 456      |
+----------+----------+
1 row in set (0.00 sec)

5、通过GTID实现主从复制

1、配置GTID（2台）

[root@server1 ~]# vim /etc/my.cnf
gtid_mode=ON
enforce-gtid-consistency=true

[root@server1 ~]# /etc/init.d/mysqld restart

2、配置GTID的主从复制（server2）

（1）关闭之前的主从复制在配置GTID主从复制

mysql> stop slave;
Query OK, 0 rows affected (0.05 sec)
mysql> change master to master_host='10.10.10.1',master_user='repl',master_password='Q1w2e3r4t5y6.',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.59 sec)
mysql> start slave;
Query OK, 0 rows affected (0.08 sec)

（2）查看状态

mysql> show slave status\G;

这里写图片描述

（3）查看UUID和日志

[root@server1 ~]# cd /var/lib/mysql
[root@server1 mysql]# ls
[root@server1 mysql]# cat auto.cnf

这里写图片描述

[root@server1 mysql]# mysqlbinlog mysql-bin.000001

这里写图片描述

3、测试

我们可以发现能够自动把数据同步过去！！！

（1）主server1

mysql> use dream;
Database changed
mysql> insert into user value('dream','dreamya');
Query OK, 1 row affected (0.08 sec)
mysql> select * from user;
+----------+----------+
| username | password |
+----------+----------+
| user2    | 456      |
| dream    | dreamya  |
+----------+----------+
2 rows in set (0.00 sec)

（2）从server2

mysql> use dream;
Database changed
mysql> select * from user;
+----------+----------+
| username | password |
+----------+----------+
| user2    | 456      |
| dream    | dreamya  |
+----------+----------+
2 rows in set (0.00 sec)

4、查看二进制日志

通过查看主（server1）的日志通过看从（server2）的ID是否相同，相同则表示同步完成！！！

（1）server1中查看

[root@server1 ~]# cd /var/lib/mysql
[root@server1 mysql]# ls
[root@server1 mysql]# mysqlbinlog mysql-bin.000002|grep GTID

这里写图片描述

（2）sever2中查看

mysql> use mysql;
mysql> select * from gtid_executed;

这里写图片描述

三、启动多线程

1、配置my.cnf（server2）

[root@server2 ~]# vim /etc/my.cnf
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16                  ###打开16个线程，原先的进程变成调度器
###下面是优化参数
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

[root@server2 ~]# /etc/init.d/mysqld restart

2、查看（server2）

（1）未修改配置

mysql> show processlist;

这里写图片描述

（2）修改配置重启后

<1> 查看增加的线程

mysql> show processlist;

这里写图片描述

<2> 查看MySQL表中增加的GTID表（同步日志）

mysql> use mysql;
mysql> show tables;
mysql> select * from slave_relay_log_info\G;

这里写图片描述

四、半同步复制

1、几种不同复制的概念

异步复制：其为Mysql的默认模式，Master在执行完Client所提交的事务会将结果立即返回给Client，但是并不关心Slave是否接收并处理，如果此时Master宕机，主上已经提交的事务可能并没有传到Slave上，Slave提升为Master，数据也会可能不完整。
全同步复制：当Master接受到Client所提交的事务，会等Slave执行了该事务在把结果返回给Client，因此必定有大量的性能损失。
半同步复制（版本5.7）：介于异步复制和全同步复制之间，Master在执行了Client所提交的事务后不会立即返回给Client，而是等待至少有一个Salve接收到relay log才返回给Client。减少了TCP/IP往返的时间，大大提高了的数据的安全性。并且如果timeout，将自动转为异步复制模式。

2、二种配置方法

我们可以通过在SQL中配置，也可以通过配置my.cnf文件实现永久生效！！！
#####（1）通过SQL配置（临时生效）
<1> 加载Master插件（server1）

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

<2> 加载Slave插件（server2）

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

<3> 查看插件是否加载成功

mysql> show plugins;
rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so   | GPL

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+

<4> 开启Master半同步复制（server1）

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

<5> 开启Slave半同步复制（server2）

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

2、通过配置文件my.cnf（永久生效）

（1）Master（server1）

[root@server1 ~]# vim /etc/my.cnf
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1

（2）Slave（server2）

[root@server2 ~]# vim /etc/my.cnf
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

3、重启slave（server2）上的IO线程

重启后，slave才会在master上注册为半同步复制的slave角色！！！

mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;

4、查看半同步是否运行

（1）Master（server1）

可以发现Master（server1）已经发现了Slave（server2）！！！

mysql>  show status like "%semi%";

这里写图片描述

（2）Slave（server2）

mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

（3）超时时间查询（Master）

mysql> show variables  like 'rpl_semi_sync_master_timeout';    ###默认超时时间是10s
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 10000 |
+------------------------------+-------+
1 row in set (0.00 sec)

（4）修改延时方法（Master）

mysql> CHANGE MASTER TO MASTER_DELAY=2147483647;

5、测试

我们不难可以发现当Slave中down了之后，第一次由于是半同步复制的状态，默认等待时间是10s，因此等待10s后进入异步复制模式，第二次就不会等待，并且当Slave恢复时，会恢复到半同步复制模式，数据也会进行同步！！！

（1）模拟Salve宕机

mysql> stop slave;

（2）Master中建立表

mysql> create database test;                 ###第一次建立表
Query OK, 1 row affected (10.04 sec)

mysql> create database test1;
Query OK, 1 row affected (0.29 sec)

（3）Slave中查询效果

mysql> show databases;
mysql> start slave;
mysql> show databases;

这里写图片描述

五、读写分离

1、开启远程连接权限（server1）

因为有主从复制，所以在server1中设置好，server2中也会进行同步，所以只需要在server1上设置即可！！！

mysql> grant all on *.* to root@'%' identified by 'Q1w2e3r4t5y6.';

2、解压tar包

[root@server3 ~]# tar xf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 
[root@server3 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

3、配置mysql-proxy.conf

[root@server3 ~]# mkdir -p /usr/local/mysql-proxy/{conf,logs}
[root@server3 ~]# vim /usr/local/mysql-proxy/conf/mysql-proxy.conf
[mysql-proxy]
user=root
proxy-address=10.10.10.3:3306
proxy-backend-addresses=10.10.10.1:3306
proxy-read-only-backend-addresses=10.10.10.2:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
plugins=proxy
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
daemon=true
keepalive=true
log-level=debug

4、修改rw-splitting.lua

此处修改最小和最大的连接数是为了方便做演示！！！

[root@server3 ~]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

这里写图片描述

5、启动mysql-proxy

（1）报错

[root@server3 ~]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf 
2018-08-18 20:37:47: (critical) mysql-proxy-cli.c:326: loading config from '/usr/local/mysql-proxy/conf/mysql-proxy.conf' failed: permissions of /usr/local/mysql-proxy/conf/mysql-proxy.conf aren't secure (0660 or stricter required)
2018-08-18 20:37:47: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2018-08-18 20:37:47: (message) shutting down normally, exit code is: 1

（2）解决方案

[root@server3 ~]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

6、查看是否启动成功

[root@server3 ~]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf 
[root@server3 ~]# netstat -lntup|grep 3306
tcp        0      0 10.10.10.3:3306             0.0.0.0:*                   LISTEN      1175/mysql-proxy

7、测试

（1）第一次连接

server4新的一台虚拟机这里用来作连接测试！！！

[root@server4 ~]# yum install -y mysql                 ###下载一个连接的命令
[root@server4 ~]# mysql -h 10.10.10.3 -uroot -pQ1w2e3r4t5y6.

这里写图片描述

（2）查看结果

[root@server3 ~]# yum install -y lsof
[root@server3 ~]# lsof -i :3306

这里写图片描述

（3）第二次连接

[root@server4 ~]# mysql -h 10.10.10.3 -uroot -pQ1w2e3r4t5y6.

（4）查看结果

[root@server3 ~]# lsof -i :3306

这里写图片描述

六、MySQL组复制

官网介绍链接：https://dev.mysql.com/doc/refman/5.7/en/group-replication.html
官网安装链接：https://dev.mysql.com/doc/refman/5.7/en/group-replication-configuring-instances.html

这里写图片描述

1、停掉mysql-proxy（server3）

注意：同时删除server1和server2中的数据，/var/lib/mysql下！！！

[root@server3 ~]# ps -ef|grep mysql
[root@server3 ~]# kill -9 pid

2、安装MySQL（server3）

[root@server3 ~]# ls
mysql-community-client-5.7.23-1.el6.x86_64.rpm
mysql-community-common-5.7.23-1.el6.x86_64.rpm
mysql-community-libs-5.7.23-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.23-1.el6.x86_64.rpm
mysql-community-server-5.7.23-1.el6.x86_64.rpm
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

[root@server3 ~]# yum install -y *

3、启动并进行初始化

[root@server3 ~]# /etc/init.d/mysqld restart
[root@server3 ~]# grep "temporary password" /var/log/mysqld.log
[root@server3 ~]# mysql_secure_installation

4、配置my.cnf

（1）server1

[root@server1 ~]# vim /etc/my.cnf
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "10.10.10.1:24901"
loose-group_replication_group_seeds= "10.10.10.1:24901,10.10.10.2:24901,10.10.10.3:24901"
loose-group_replication_bootstrap_group=off

loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks=TRUE
loose-group_replication_ip_whitelist='10.10.10.0/24'

（2）server2

[root@server2 ~]# vim /etc/my.cnf

这里写图片描述

（3）server3

[root@server3 ~]# vim /etc/my.cnf

这里写图片描述

5、重启MySQL（3台）

[root@server3 ~]# /etc/init.d/mysqld restart

6、MySQL中配置（server1）

（1）组配置

[root@server1 ~]# mysql -uroot -pQ1w2e3r4t5y6.
mysql> SET SQL_LOG_BIN=0;             ###关闭日志
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' identified by 'Q1w2e3r4t5y6.';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Q1w2e3r4t5y6.' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';          ###安装

mysql> SHOW PLUGINS;                 ###查看
+----------------------------+----------+--------------------+----------------------+-------------+
| Name                       | Status   | Type               | Library              | License     |
+----------------------------+----------+--------------------+----------------------+-------------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | PROPRIETARY |

(...)

| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | PROPRIETARY |
+----------------------------+----------+--------------------+----------------------+-------------+

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+-------------+
| Name                       | Status   | Type               | Library              | License     |
+----------------------------+----------+--------------------+----------------------+-------------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | PROPRIETARY |

(...)

| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | PROPRIETARY |
+----------------------------+----------+--------------------+----------------------+-------------+

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

（2）查看是否启动成功

状态显示ONLINE即启动成功！！！

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | f83b4f2e-a30e-11e8-8481-5254007b6d74 | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

7、配置server2

[root@server2 ~]# mysql -uroot -pQ1w2e3r4t5y6.
mysql> SET SQL_LOG_BIN=0;
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' identified by 'Q1w2e3r4t5y6.';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Q1w2e3r4t5y6.' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 154e07a4-a30f-11e8-86b1-52540028bda2 | server2     |        3306 | ONLINE       |
| group_replication_applier | f83b4f2e-a30e-11e8-8481-5254007b6d74 | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

8、配置server3

[root@server2 ~]# mysql -uroot -pQ1w2e3r4t5y6.
mysql> SET SQL_LOG_BIN=0;
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' identified by 'Q1w2e3r4t5y6.';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Q1w2e3r4t5y6.' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 154e07a4-a30f-11e8-86b1-52540028bda2 | server2     |        3306 | ONLINE       |
| group_replication_applier | 5d954780-a30f-11e8-894b-525400a33abd | server3     |        3306 | ONLINE       |
| group_replication_applier | f83b4f2e-a30e-11e8-8481-5254007b6d74 | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

9、设置白名单

如果在my.cnf配置中没有加入白名单的配置，启动组复制将会失败，我们可以通过下面的命令来解决！！！
日志会报错：Connection attempt from IP address 10.10.10.1 refused. Address is not in the IP whitelist.

mysql> STOP GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_ip_whitelist='10.10.10.0/24'
mysql> START GROUP_REPLICATION;

10、测试效果

组复制的模式没有Master，所有的都是备机！！！

（1）在3台中随便一个

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.18 sec)

mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.83 sec)

mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.32 sec)

mysql> SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+

（2）在其他2台中能够查看到

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

mysql> USE test;
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 t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)

Wielun

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL

安装MySQL1、下载MySQL官网下载地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloadswget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.23-1.el6.x86_64.rpm-bundle.tarwget https://dev.my...
复制链接

扫一扫