内容简介：MySQL5.7 Group Replication (MGR) 集群搭建
1 介绍
MySQL Group Replication（简称MGR）是 MySQL 官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。MySQL组复制提供了高可用、高扩展、高可靠的MySQL集群服务。
-
高一致性，基于原生复制及paxos协议的组复制技术，并以插件的方式提供，提供一致数据安全保证；
-
高容错性，只要不是大多数节点坏掉就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理，并且内置了自动化脑裂防护机制；
-
高扩展性，节点的新增和移除都是自动的，新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致，如果某节点被移除了，其他节点自动更新组信息，自动维护新的组信息；
-
高灵活性，有单主模式和多主模式，单主模式下，会自动选主，所有更新操作都在主上进行；多主模式下，所有server都可以同时处理更新操作。
MGR是MySQL数据库未来发展的一个重要方向。
2 环境准备
2.1 数据库服务器规划
| 序号 | IP地址 | 主机名 | 数据库 | 数据库端口号 | 数据库Server ID | 备注 |
|---|---|---|---|---|---|---|
| 1 | 192.168.56.181 | apollo.mgr.com | mysql-5.7 | 3306 | 181 | 操作系统CentOS7 |
| 2 | 192.168.56.182 | artemis.mgr.com | mysql-5.7 | 3306 | 182 | 操作系统CentOS7 |
| 3 | 192.168.56.183 | uranus.mgr.com | mysql-5.7 | 3306 | 183 | 操作系统CentOS7 |
2.2 安装mysql5.7.17
安装mysql请参照 http://www.linuxidc.com/Linux/2017-06/144554.htm ，在这里就不详细介绍。
MySQL 5.7.17 Group Relication（组复制）搭建手册 http://www.linuxidc.com/Linux/2017-03/142378.htm
2.3 设置hostname和ip映射
在三台数据库服务器上都设置：
[mgradmin@apollo mysql-5.7.17-1.el7.x86_64.rpm-bundle]$ vim /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.56.181 apollo.mgr.com 192.168.56.182 artemis.mgr.com 192.168.56.183 uranus.mgr.com
3. 创建复制环境
3.1 服务器apollo.mgr.com
3.1.1 配置/etc/my.cnf
[mgradmin@apollo mysql-5.7.17-1.el7.x86_64.rpm-bundle]$ sudo vim /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid # Group Replication server_id = 181 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 = 'ce9be252-2b71-11e6-b8f4-00212844f856' loose-group_replication_start_on_boot = off loose-group_replication_local_address = 'apollo.mgr.com:33061' loose-group_replication_group_seeds ='apollo.mgr.com:33061,artemis.mgr.com:33062,uranus.mgr.com:33063' loose-group_replication_bootstrap_group = off
3.1.2 服务器apollo.mgr.com上建立复制账号：
mysql> set SQL_LOG_BIN=0; mysql> create user repl@'%' identified by 'Love88me=-.,'; mysql> grant replication slave on *.* to repl@'%'; mysql> flush privileges; mysql> set SQL_LOG_BIN=1; mysql> change master to master_user='repl',master_password='Love88me=-.,' for channel 'group_replication_recovery'; -- 构建group replication集群
3.1.3 在mysql服务器apollo.mgr.com上安装group replication插件
-- 安装插件 mysql> install PLUGIN group_replication SONAME 'group_replication.so'; -- 查看group replication组件 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 | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | 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 | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | 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 | +----------------------------+----------+--------------------+----------------------+---------+
3.1.4 启动服务器apollo.mgr.com上mysql的group replication
-- 设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准，以后加入的就不需要设置。 mysql> set global group_replication_bootstrap_group=ON; -- 作为首个节点启动mgr集群 mysql> start group_replication; mysql> set global group_replication_bootstrap_group=OFF;
3.1.5 查看mgr的状态
-- 查询表performance_schema.replication_group_members mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+----------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+----------------+-------------+--------------+ | group_replication_applier | ecf48652-1c72-11e7-a7c1-08002785d027 | apollo.mgr.com | 3306 | ONLINE | +---------------------------+--------------------------------------+----------------+-------------+--------------+
3.1.6 测试服务器apollo.mgr.com上的mysql
mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test; Database changed mysql> create table t1(c1 int primary key, c2 text not null); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (1, 'Luis'); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | Luis | +----+------+ 1 row in set (0.00 sec) mysql> show binlog events; +---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | binlog.000001 | 4 | Format_desc | 181 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 | | binlog.000001 | 123 | Previous_gtids | 181 | 150 | | | binlog.000001 | 150 | Gtid | 181 | 211 | SET @@SESSION.GTID_NEXT= 'ce9be252-2b71-11e6-b8f4-00212844f856:1' | | binlog.000001 | 211 | Query | 181 | 270 | BEGIN | | binlog.000001 | 270 | View_change | 181 | 369 | view_id=14916679781649312:1 | | binlog.000001 | 369 | Query | 181 | 434 | COMMIT | | binlog.000001 | 434 | Gtid | 181 | 495 | SET @@SESSION.GTID_NEXT= 'ce9be252-2b71-11e6-b8f4-00212844f856:2' | | binlog.000001 | 495 | Query | 181 | 585 | create database test | | binlog.000001 | 585 | Gtid | 181 | 646 | SET @@SESSION.GTID_NEXT= 'ce9be252-2b71-11e6-b8f4-00212844f856:3' | | binlog.000001 | 646 | Query | 181 | 769 | use `test`; create table t1(c1 int primary key, c2 text not null) | | binlog.000001 | 769 | Gtid | 181 | 830 | SET @@SESSION.GTID_NEXT= 'ce9be252-2b71-11e6-b8f4-00212844f856:4' | | binlog.000001 | 830 | Query | 181 | 898 | BEGIN | | binlog.000001 | 898 | Table_map | 181 | 941 | table_id: 219 (test.t1) | | binlog.000001 | 941 | Write_rows | 181 | 983 | table_id: 219 flags: STMT_END_F | | binlog.000001 | 983 | Xid | 181 | 1010 | COMMIT /* xid=40 */ | +---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 15 rows in set (0.00 sec)
3.2 复制组添加新实例artemis.mgr.com
3.2.1 修改/etc/my.cnf 配置文件，方法和之前相同
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid # Group Replication server_id = 182 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 = 'ce9be252-2b71-11e6-b8f4-00212844f856' loose-group_replication_start_on_boot = off loose-group_replication_local_address = 'artemis.mgr.com:33062' loose-group_replication_group_seeds ='apollo.mgr.com:33061,artemis.mgr.com:33062,uranus.mgr.com:33063' loose-group_replication_bootstrap_group = off
3.2.2 用户凭证
mysql> set SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> create user repl@'%' identified by 'Love88me=-.,'; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave on *.* to repl@'%'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; 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='repl',master_password='Love88me=-.,' for channel 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.02 sec) -- 安装group replication插件 mysql> install PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.00 sec)
3.2.3 把实例添回到之前的复制组
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON; Query OK, 0 rows affected (0.00 sec) mysql> start group_replication; Query OK, 0 rows affected (6.65 sec)
3.2.4 在apollo.mgr.com上查看复制组状态
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | group_replication_applier | ecf48652-1c72-11e7-a7c1-08002785d027 | apollo.mgr.com | 3306 | ONLINE | | group_replication_applier | f0a20978-1c72-11e7-a17b-0800272ce349 | artemis.mgr.com | 3306 | ONLINE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+
3.2.5 在新回的实例上查看数据库发现test库和t1表已经同步
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> 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)
3.3 以同样方法添加uranus.mgr.com
详细步骤请参考3.2, 这里只给出配置文件/etc/my.cnf：
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid # Group Replication server_id = 183 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 = 'ce9be252-2b71-11e6-b8f4-00212844f856' loose-group_replication_start_on_boot = off loose-group_replication_local_address = 'uranus.mgr.com:33063' loose-group_replication_group_seeds ='apollo.mgr.com:33061,artemis.mgr.com:33062,uranus.mgr.com:33063' loose-group_replication_bootstrap_group = off
3.4 查看复制组状态
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | group_replication_applier | ecf48652-1c72-11e7-a7c1-08002785d027 | apollo.mgr.com | 3306 | ONLINE | | group_replication_applier | f0a20978-1c72-11e7-a17b-0800272ce349 | artemis.mgr.com | 3306 | ONLINE | | group_replication_applier | f2ca816c-1c72-11e7-a1aa-080027eaa4e1 | uranus.mgr.com | 3306 | ONLINE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+
本文永久更新链接地址 ： http://www.linuxidc.com/Linux/2017-06/144776.htm
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持 码农网
猜你喜欢:
- Elasticsearch 集群搭建和集群原理
- Zookeeper学习系列【二】Zookeeper 集群章节之集群搭建
- Spark集群环境搭建
- Zookeeper搭建集群
- FastDFS集群搭建
- Zookeeper集群环境搭建
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
