mysql 5.7 的组复制

最新推荐文章于 2024-06-27 23:48:08 发布
最新推荐文章于 2024-06-27 23:48:08 发布
阅读量387 收藏
点赞数
文章标签： linux mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hh5820/article/details/81741573
版权

组复制

简介

组复制分单主模式和多主模式，mysql 的复制技术仅解决了数据同步的问题，如果 master 宕机，意味着数据库管理员需要介入，应用系统可能需要修改数据库连接地址或者重启才能实现。（这里也可以使用数据库中间件产品来避免应用系统数据库连接的问题，例如 mycat 和 atlas 等产品）。组复制在数据库层面上做到了，只要集群中大多数主机可用，则服务可用，也就是说3台服务器的集群，允许其中1台宕机。组复制的流程图如下
这里写图片描述这里写图片描述这里写图片描述

特点

● 高一致性
基于原生复制及 paxos 协议的组复制技术，并以插件的方式提供，提供一致数据安全保证；
● 高容错性
只要不是大多数节点坏掉就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理，并且内置了自动化脑裂防护机制；
● 高扩展性
节点的新增和移除都是自动的，新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致，如果某节点被移除了，其他节点自动更新组信息，自动维护新的组信息；
● 高灵活性
有单主模式和多主模式，单主模式下，会自动选主，所有更新操作都在主上进行；
多主模式下，所有 server 都可以同时处理更新操作。

实验

实验环境

server1：172.25.54.1
server2：172.25.54.2
server3：172.25.54.3

由于先前有做过其他数据库实验, 所以需要进行清理

  • 配置server1——作为第一个节点
[root@server1 ~]# cd /var/lib/mysql/
[root@server1 mysql]# rm -fr *
[root@server1 mysql]# cd
[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="2cbe319c-9c7c-11e8-92dd-5254009c0397"  
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.54.1:24901"  //本机IP
loose-group_replication_group_seeds= "172.25.54.1:24901,172.25.54.2:24901,172.25.54.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="172.25.54.0/24,127.0.0.1/8"

loose-group_replication_group_name的获取方式如下：
注意：三台机子配置文件中的loose-group_replication_group_name是一样的

[root@server3 ~]# mysql -p5820hhXM\!\@#
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 2cbe319c-9c7c-11e8-92dd-5254009c0397 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> Bye
[root@server1 ~]# > /var/log/mysqld.log
[root@server1 ~]# /etc/init.d/mysqld start
Initializing MySQL database:                               [  OK  ]
Installing validate password plugin:                       [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server1 ~]# grep password /var/log/mysqld.log
2018-08-10T10:26:52.210939Z 1 [Note] A temporary password is generated for root@localhost: (fUBB,jWD8Lp
2018-08-10T10:27:15.966802Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.Rjm21c.sql' started.
2018-08-10T10:27:16.023917Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.Rjm21c.sql' ended.
2018-08-10T10:27:17.741454Z 0 [Note] Shutting down plugin 'sha256_password'
2018-08-10T10:27:17.741460Z 0 [Note] Shutting down plugin 'mysql_native_password'
2018-08-10T10:27:19.818513Z 3 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
[root@server1 ~]# mysql -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@server1 ~]# mysql -p5820hhXM\!\@#
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@server1 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17-log

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER root@localhost identified by '5820hhXM!@#';
Query OK, 0 rows affected (0.11 sec)

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

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY '5820hhXM!@#';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.18 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='5820hhXM!@#' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.35 sec)

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

mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL                 | GPL     |
| 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> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.63 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | aa28042d-9c7d-11e8-9a37-52540040ca21 | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

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

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

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

mysql> SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)

mysql> Bye
[root@server1 ~]#
  • 配置server2——第二节点
[root@server2 ~]# cd /var/lib/mysql
[root@server2 mysql]# rm -fr *
[root@server2 mysql]# cd
[root@server2 ~]# vim /etc/my.cnf
server_id=2
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="2cbe319c-9c7c-11e8-92dd-5254009c0397"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.54.2:24901"
loose-group_replication_group_seeds= "172.25.54.1:24901,172.25.54.2:24901,172.25.54.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="172.25.54.0/24,127.0.0.1/8"
[root@server2 ~]# > /var/log/mysqld.log
[root@server2 ~]# /etc/init.d/mysqld start
Initializing MySQL database:                               [  OK  ]
Installing validate password plugin:                       [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server2 ~]# grep password /var/log/mysqld.log
2018-08-10T09:59:46.029171Z 1 [Note] A temporary password is generated for root@localhost: s6j>A-gnKrry
2018-08-10T10:00:17.118035Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.lgZovY.sql' started.
2018-08-10T10:00:17.300585Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.lgZovY.sql' ended.
2018-08-10T10:00:19.341664Z 0 [Note] Shutting down plugin 'sha256_password'
2018-08-10T10:00:19.341669Z 0 [Note] Shutting down plugin 'mysql_native_password'
2018-08-10T10:00:21.885500Z 3 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
[root@server2 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-log

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER root@localhost identified by '5820hhXM!@#';
Query OK, 0 rows affected (0.13 sec)

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY '5820hhXM!@#';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.24 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='5820hhXM!@#' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.69 sec)

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

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (5.93 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1c479d6f-9c84-11e8-a5a3-5254009c0397 | server2     |        3306 | ONLINE       |
| group_replication_applier | aa28042d-9c7d-11e8-9a37-52540040ca21 | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)
  • 配置server3——第三节点
[root@server3 ~]# cd /var/lib/mysql
[root@server3 mysql]# rm -fr *
[root@server3 mysql]# cd
[root@server3 ~]# vim /etc/my.cnf
server_id=3
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="2cbe319c-9c7c-11e8-92dd-5254009c0397"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.54.3:24901"
loose-group_replication_group_seeds= "172.25.54.1:24901,172.25.54.2:24901,172.25.54.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="172.25.54.0/24,127.0.0.1/8"
[root@server3 ~]# > /var/log/mysqld.log
[root@server3 ~]# /etc/init.d/mysqld start
Initializing MySQL database:                               [  OK  ]
Installing validate password plugin:                       [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server3 ~]# grep password /var/log/mysqld.log
2018-08-10T10:26:52.210939Z 1 [Note] A temporary password is generated for root@localhost: (fUBB,jWD8Lp
2018-08-10T10:27:15.966802Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.Rjm21c.sql' started.
2018-08-10T10:27:16.023917Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.Rjm21c.sql' ended.
2018-08-10T10:27:17.741454Z 0 [Note] Shutting down plugin 'sha256_password'
2018-08-10T10:27:17.741460Z 0 [Note] Shutting down plugin 'mysql_native_password'
2018-08-10T10:27:19.818513Z 3 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
[root@server3 ~]# mysql -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@server3 ~]# mysql -p5820hhXM\!\@#
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@server3 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17-log

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER root@localhost identified by '5820hhXM!@#';
Query OK, 0 rows affected (0.13 sec)

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY '5820hhXM!@#';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.24 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='5820hhXM!@#' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.69 sec)

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

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (5.93 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | e59abed0-9c87-11e8-b17e-52540053a9de | server3     |        3306 | ONLINE       |
| group_replication_applier | 1c479d6f-9c84-11e8-a5a3-5254009c0397 | server2     |        3306 | ONLINE       |
| group_replication_applier | aa28042d-9c7d-11e8-9a37-52540040ca21 | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 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)

测试

  • server3
mysql> INSERT INTO t1 VALUES (2, 'fly');
Query OK, 1 row affected (0.40 sec)

mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | fly  |
+----+------+
2 rows in set (0.00 sec)

mysql>
  • server1
mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | fly  |
+----+------+
2 rows in set (0.00 sec)
  • server2
mysql> mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | fly  |
+----+------+
2 rows in set (0.00 sec)
  • server1
mysql> INSERT INTO t1 VALUES (3, 'tutu');
Query OK, 1 row affected (0.27 sec)
  • server2
mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | fly  |
|  3 | tutu |
+----+------+
3 rows in set (0.00 sec)
  • server3
mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | fly  |
|  3 | tutu |
+----+------+
3 rows in set (0.00 sec)
20butterfly
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql 5.7 基于组复制(MySQL Group Replication) - 精华版
01-17
本篇文章详细介绍了Mysql 5.7 基于组复制(MySQL Group Replication)的配置说明，实为线上操作手册，在此分享，希望能帮助到有用到的朋友～
MySQL5.7主从复制集群配置
12-05
从库可以配置多线程并行复制，MySQL 5.7 中提供了两种类型的并行复制方式：DATABASE 基于库的并行复制和 LOGICAL_CLOCK 基于组提交的并行复制方式。 6. 注意事项 在配置 MySQL 5.7 主从复制集群时需要注意以下几点...
mysql5.7组复制_mysql5.7组复制最佳实践
weixin_36307834的博客
01-19 109
mysql5.7的版本是真正意义上实现了组提交和组复制的，在mysql5.6版本虽然也有并行复制，但其实现的是不同模式下的表才能并行复制，而在5.7的版本，只要是一组没有冲突的事物(没有模式的限制)，就可以并行在从库中进行回放，大大的提高复制效率。在mysql5.7版本在现有的主从复制环境，可以在线去配置并行复制，基本对业务无影响，下面就来看看怎么在线配置并行复制首先在master端配置2个并行复...
MySQL 复制技术详解
最新发布
www_tlj的专栏
06-27 880
主从复制是一种常见的数据复制模式，其中一个数据库服务器作为主服务器（Master），负责处理所有的写操作和部分读操作，而一个或多个从服务器（Slave）负责复制主服务器的数据并处理读请求。这样可以实现读写分离，提高系统的性能和可用性。主主复制是一种高级的复制模式，两个MySQL服务器既是主服务器又是从服务器，能够互相同步数据。每个服务器既可以处理写操作，也可以处理从另一个服务器复制过来的写操作。高可用性：当一个服务器宕机时，另一个服务器可以继续提供服务。负载均衡。
mysql5.7 组复制 多主_MySQL 5.7组复制实践-多主模式
weixin_31935793的博客
02-09 180
这是官方出品类似 pxc 的数据库高可用解决方案，影响深远。从异步复制，到半同步复制，到组复制，mysql高可用技术越来越强大。随着mgr的逐渐成熟，将会越来越多的应用MySQL的多种场景中。MySQL Group Replication is a MySQL Server plugin that provides distributed state machine replication wit...
mysql5.7实现组复制（MGR）
chengrowe的博客
01-08 996
实验主机server1，7，8： 组复制原理： 组复制是一种可用于实现容错系统的技术。 复制组是一个通过消息传递相互交互的 server 集群。通信层提供了原子消息(atomic message)和完全有序信息交互等保障机制  实现了基于复制协议的多主更新  复制组由多个 server成员构成,并且组中的每个 server 成员可以独立地执行事务。但所有读写(RW)事务只有在冲突检测成功后才会提...
mysql 5.7 组复制_MySQL 5.7.23 Group Relication（组复制）搭建
weixin_33785828的博客
01-27 162
本文介绍了Group Replication的两种工作模式的架构介绍。并详细介绍了Single-Master Mode的部署过程，以及如何切换到Multi-Master Mode。当然，文末给出了Group Replication的配置要求和一些限制。〇 结构介绍在2016年12月发布的5.7.17版本的MySQL，甲骨文宣布Group Replication已经GA博文以MySQL5.7.2...
mysql5.7.44安装包
01-17
5. **组复制**（Group Replication）：这是一种高可用性解决方案，允许数据在多个节点间进行多主复制，提高了系统的容错性。 6. **更好的安全特性**：MySQL 5.7 提供了更严格的默认安全设置，如密码哈希策略的更新...
MySQL5.7精简版.zip
06-30
5. **更好的复制功能**：MySQL5.7的复制功能得到了改进，包括GTID（全局事务标识符）的广泛支持，使得复制更加可靠且易于管理。 6. **GROUP BY优化**：MySQL5.7放宽了对GROUP BY语句的限制，允许SELECT列不包含在...
mysql5.7.37版本安装包
02-20
6. **组复制**：MySQL 5.7 引入了组复制功能，这是一种高可用性和灾难恢复解决方案，可以实现多个MySQL实例间的实时数据同步。 7. **半同步复制**：5.7 版本改进了复制机制，提供了一种称为“半同步复制”的选项，...
mysql 5.7 组复制_mysql5.7 mgr组复制
weixin_42467573的博客
01-27 141
1Mgr安装配置安装前环境准备:IP端口路径Server-id组复制端口主机名操作系统192.168.56.131(主)23306/usr/local/13123356mysqlCentos6.5192.168.56.130(主)23306/usr/local/13023356weblogicCentos6.5192.168.56.128(主)23306/usr/local/12823356oe...
Mysql5.7的组复制方法
letter_A的博客
08-11 604
环境：          虚拟机系统版本：linux 6.5 实验主机：          server3 : 172.25.17.3          server4 : 172.25.17.4          server5 : 172.25.17.5 ###建议：在书写sql语句时，如果要使用大写就全部使用大写（大写更规范），如果要使用小写就全部使用小写。否则可能会像我一样，授...
mysql5.7新特性组复制官网简易翻译
敖尔其楞的专栏
05-05 1155
组复制 原以为mysql的开发会按照oracle的方式去走，最终就是小版本的oracle，没想到在mysql57中出了组复制的功能，组复制提供了了容错能力，只要组中的大多数的成员存活，那么系统就是可用的，组复制有2中形式，多master，所有server都能接受更新和单master自动选主，只有master接受更新,有更新冲突的时候，会采用先提交获胜的策略，回滚掉后提交的，组复
mysql5.7组复制多主一从搭建,MySQL 5.7 多主一从的复制结构
weixin_42504327的博客
03-23 215
多源复制的作用MySQL 5.7.6 开始，添加了一个新特性：多源复制Multi-Source Replication可以让你同时从多个master中并行复制，也就是形成了一种新的主从复制结构一从多主图以前都是一主多从的结构，想实现一从多主的复制需要自己想办法，现在好了，直接支持了多源复制的应用场景示例：比如我们分库后有多台数据库服务器，后台统计系统需要对数据进行汇总，就可以使用多源复制实现汇总功...
mysql并行复制_浅析MySQL并行复制
weixin_39669075的博客
01-18 157
01 并行复制的概念在MySQL的主从复制架构中，主库上经常会并发的执行很多SQL，只要这些SQL没有产生锁等待，那么同一时间并发好几个SQL线程是没有问题的。我们知道，MySQL的从库是要通过IO_thread去拉取主库上的binlog的，然后存入本地，落盘成relay-log，通过sql_thread来应用这些relay-log。在MySQL5.6之前的版本中，当主库上有多个线程并发执行SQL...
mysql 5.7 组复制_MySQL 5.7组复制(group replication)的要求和限制
weixin_35823048的博客
01-19 142
限制和要求:1. 所有涉及的数据都必须发生在InnoDB存储引擎的表内。2. 所有的表必须有明确的主键定义。3. 网络地址只支持IPv4。4. 需要低延迟，高带宽的网络。5. 目前集群限制最多允许9个节点。6. 必须启用binlog。7. binlog 格式必须是row格式。8. 必须打开gtid模式。9. 复制相关信息必须使用表存储。10.事务写集合(Transaction write set ...
MySQL 5.7.17 Group Relication（组复制）搭建手册
cq377078944的博客
02-09 284
本博文介绍了Group Replication的两种工作模式的架构。 并详细介绍了Single-Master Mode的部署过程，以及如何切换到Multi-Master Mode。 当然，文末给出了Group Replica...
mysql 5.7实现组复制 （MGR）
xys2333的博客
02-25 339
一.原理赘述   1.组复制插件架构图 MySQL组复制是一个MySQL插件，它基于常规的MySQL复制，利用了基于行格式的二进制日志和GTID等特性。下图是MySQL组复制的整体框架图。   1.从上图的最顶端开始，有一系列的API控制组复制插件如何和MySQL Server进行交互(图中灰色方框)。 2.中间有一些接口可以使信息从MySQL Server流向组复制插件，反...
mysql5.7可以主从多进程复制吗
07-15
例如，可以使用MySQL官方提供的MySQL Group Replication插件，它是基于组复制的高可用解决方案，支持并行复制和自动故障转移。 总结起来，MySQL 5.7版本本身不支持主从多进程复制，但可以通过使用第三方工具或插件...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值