MySQL高可用架构之MySQL5.7组复制MGR
###########################################################################################
规划MGR:
host1.us.oracle.com 10.10.20.95
host2.us.oracle.com 10.10.20.97
host3.us.oracle.com 10.10.20.132
MySQL版本：5.7.21
MySQL实例是33061端口

server-id=330611 330612 330613

###########################################################################################
host1：

MySQL实例是33061端口
mysql> select @@port;
+--------+
| @@port |
+--------+
| 33061 |
+--------+
1 row in set (0.01 sec)
[root@host1 ]#cat /etc/hosts
#FOR TEST MGR
10.10.20.132 host3.us.oracle.com
10.10.20.95 host1.us.oracle.com
10.10.20.97 host2.us.oracle.com

vi /data/mysql_33061/my.cnf

 [client]

 port = 33061

 socket = /data/mysql_33061/run/mysql.sock

 [mysqld]

 port = 33061

 socket = /data/mysql_33061/run/mysql.sock

 pid_file = /data/mysql_33061/run/mysql.pid

 basedir = /usr/local/mysql

 datadir = /data/mysql_33061/data

 default_storage_engine = InnoDB

 max_allowed_packet = 128M

 max_connections = 1024

 open_files_limit = 65535

 server-id=330611

 skip-name-resolve

 lower_case_table_names=1

 character-set-server = utf8mb4

 collation-server = utf8mb4_unicode_ci

 init_connect='SET NAMES utf8mb4'

 innodb_buffer_pool_size = 4096M

 innodb_buffer_pool_instances = 8

 innodb_buffer_pool_load_at_startup = 1

 innodb_buffer_pool_dump_at_shutdown = 1

 innodb_file_per_table = 1

 innodb_flush_log_at_trx_commit = 2

 innodb_data_home_dir=/data/mysql_33061/data/

 innodb_log_group_home_dir=/data/mysql_33061/logs/

 innodb_log_files_in_group=3

 innodb_log_file_size=512M

 innodb_data_file_path=ibdata1:512M:autoextend

 log_slave_updates=1

 binlog_format=ROW

 key_buffer_size = 16M

 innodb_log_buffer_size = 32M

 innodb_io_capacity = 4000

 innodb_io_capacity_max = 8000

 innodb_flush_method = O_DIRECT

 innodb_print_all_deadlocks = 1

 log-error = /data/mysql_33061/logs/mysql_error.log

 log-bin = /data/mysql_33061/logs/mysql_bin

 slow_query_log = 1

 slow_query_log_file = /data/mysql_33061/logs/mysql_slow_query.log

 long_query_time = 2

 sync_binlog = 600

 relay_log_recovery = 1

 tmp_table_size = 64M

 max_heap_table_size = 64M

 #FOR MGR

 transaction_write_set_extraction = XXHASH64

 loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212889f856' #不能用主机的GTID

 loose-group_replication_start_on_boot = off

 loose-group_replication_bootstrap_group = off

 loose-group_replication_ip_whitelist = '10.10.20.95,10.10.20.132,10.10.20.97'

 loose-group_replication_local_address = '10.10.20.95:33066' #写自己主机所在IP，端口自选

 loose-group_replication_group_seeds ='10.10.20.95:33066,10.10.20.97:33066,10.10.20.132:33066'#写MGR组内主机所在IP，端口自选

 loose-group_replication_single_primary_mode = off #关闭单主模式的参数

 loose-group_replication_enforce_update_everywhere_checks = off #开启多主模式的参数

 init_slave = 'set sql_mode=STRICT_ALL_TABLES'

 #自动启动组复制

 #loose-group_replication_start_on_boot = on

 #group_replication_allow_local_disjoint_gtids_join = on

 #FOR GTID

 gtid_mode=on

 enforce_gtid_consistency=on

 binlog_gtid_simple_recovery=1

 log_slave_updates=1

 binlog_checksum=NONE

 master_info_repository=TABLE

 relay_log_info_repository=TABLE

my.cnf

mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
......
| BLACKHOLE         | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED         | DISABLED | STORAGE ENGINE | NULL | GPL |
| ngram             | ACTIVE | FTPARSER | NULL | GPL |
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.00 sec)

mysql>GRANT REPLICATION SLAVE ON *.* TO 'dbsync'@'10.10.%.%' IDENTIFIED BY 'xxxxxxx';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='dbsync',MASTER_PASSWORD='xxxxxxx' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.27 sec)
mysql> set global group_replication_ip_whitelist="10.10.20.95,10.10.20.132,10.10.20.97";
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> set auto_increment_offset=5555;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.04 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 57bc1e44-a447-11e8-8e78-5254000f067d | host1.us.oracle.com | 33061 | ONLINE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
1 row in set (0.00 sec)
###########################################################################################

host2:

[root@host2 ]#cat /etc/hosts
#FOR TEST MGR
10.10.20.132 host3.us.oracle.com
10.10.20.95 host1.us.oracle.com
10.10.20.97 host2.us.oracle.com

vi /data/mysql_33061/my.cnf

 [client]

 port = 33061

 socket = /data/mysql_33061/run/mysql.sock

 [mysqld]

 port = 33061

 socket = /data/mysql_33061/run/mysql.sock

 pid_file = /data/mysql_33061/run/mysql.pid

 basedir = /usr/local/mysql

 datadir = /data/mysql_33061/data

 default_storage_engine = InnoDB

 max_allowed_packet = 128M

 max_connections = 1024

 open_files_limit = 65535

 skip-name-resolve

 lower_case_table_names=1

 character-set-server = utf8mb4

 collation-server = utf8mb4_unicode_ci

 init_connect='SET NAMES utf8mb4'

 innodb_buffer_pool_size = 4096M

 innodb_buffer_pool_instances = 8

 innodb_buffer_pool_load_at_startup = 1

 innodb_buffer_pool_dump_at_shutdown = 1

 innodb_file_per_table = 1

 innodb_flush_log_at_trx_commit = 2

 innodb_data_home_dir=/data/mysql_33061/data/

 innodb_log_group_home_dir=/data/mysql_33061/logs/

 innodb_log_files_in_group=3

 innodb_log_file_size=512M

 innodb_data_file_path=ibdata1:512M:autoextend

 log_slave_updates=1

 binlog_format=ROW

 key_buffer_size = 16M

 innodb_log_buffer_size = 32M

 innodb_io_capacity = 4000

 innodb_io_capacity_max = 8000

 innodb_flush_method = O_DIRECT

 innodb_print_all_deadlocks = 1

 log-error = /data/mysql_33061/logs/mysql_error.log

 log-bin = /data/mysql_33061/logs/mysql_bin.log

 slow_query_log = 1

 slow_query_log_file = /data/mysql_33061/logs/mysql_slow_query.log

 long_query_time = 2

 sync_binlog = 600

 relay_log_recovery = 1

 tmp_table_size = 64M

 max_heap_table_size = 64M

 server-id=330612

 #FOR MGR

 transaction_write_set_extraction = XXHASH64

 loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212889f856' #不能用主机的GTID

 loose-group_replication_start_on_boot = off

 loose-group_replication_bootstrap_group = off

 loose-group_replication_ip_whitelist = '10.10.20.95,10.10.20.132,10.10.20.97'

 loose-group_replication_local_address = '10.10.20.97:33066' #写自己主机所在IP，端口自选

 loose-group_replication_group_seeds ='10.10.20.95:33066,10.10.20.97:33066,10.10.20.132:33066'#写MGR组内主机所在IP，端口自选

 loose-group_replication_single_primary_mode = off #关闭单主模式的参数

 loose-group_replication_enforce_update_everywhere_checks = off #开启多主模式的参数

 init_slave = 'set sql_mode=STRICT_ALL_TABLES'

 #FOR GTID

 gtid_mode=on

 enforce_gtid_consistency=on

 binlog_gtid_simple_recovery=1

 log_slave_updates=1

 binlog_checksum=NONE

 master_info_repository=TABLE

 relay_log_info_repository=TABLE

my.cnf

mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

mysql> CHANGE MASTER TO MASTER_USER='dbsync',MASTER_PASSWORD='xxxxxxx' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.56 sec)
mysql> set global group_replication_ip_whitelist="10.10.20.95,10.10.20.132,10.10.20.97";
Query OK, 0 rows affected (0.00 sec)

mysql> set auto_increment_offset=5555;
Query OK, 0 rows affected (0.00 sec)

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (6.88 sec)

###########################################################################################
host3:
[root@host3 ]#cat /etc/hosts
#FOR TEST MGR
10.10.20.132 host3.us.oracle.com
10.10.20.95 host1.us.oracle.com
10.10.20.97 host2.us.oracle.com

vi /data/mysql_33061/my.cnf

 [client]

 port = 33061

 socket = /data/mysql_33061/run/mysql.sock

 [mysqld]

 port = 33061

 socket = /data/mysql_33061/run/mysql.sock

 pid_file = /data/mysql_33061/run/mysql.pid

 basedir = /usr/local/mysql

 datadir = /data/mysql_33061/data

 default_storage_engine = InnoDB

 max_allowed_packet = 128M

 max_connections = 1024

 open_files_limit = 65535

 skip-name-resolve

 lower_case_table_names=1

 character-set-server = utf8mb4

 collation-server = utf8mb4_unicode_ci

 init_connect='SET NAMES utf8mb4'

 innodb_buffer_pool_size = 4096M

 innodb_buffer_pool_instances = 8

 innodb_buffer_pool_load_at_startup = 1

 innodb_buffer_pool_dump_at_shutdown = 1

 innodb_file_per_table = 1

 innodb_flush_log_at_trx_commit = 2

 innodb_data_home_dir=/data/mysql_33061/data/

 innodb_log_group_home_dir=/data/mysql_33061/logs/

 innodb_log_files_in_group=3

 innodb_log_file_size=512M

 innodb_data_file_path=ibdata1:512M:autoextend

 log_slave_updates=1

 binlog_format=ROW

 key_buffer_size = 16M

 innodb_log_buffer_size = 32M

 innodb_io_capacity = 4000

 innodb_io_capacity_max = 8000

 innodb_flush_method = O_DIRECT

 innodb_print_all_deadlocks = 1

 log-error = /data/mysql_33061/logs/mysql_error.log

 log-bin = /data/mysql_33061/logs/mysql_bin.log

 slow_query_log = 1

 slow_query_log_file = /data/mysql_33061/logs/mysql_slow_query.log

 long_query_time = 2

 sync_binlog = 600

 relay_log_recovery = 1

 tmp_table_size = 64M

 max_heap_table_size = 64M

 server-id=330613

 #FOR MGR

 transaction_write_set_extraction = XXHASH64

 loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212889f856' #不能用主机的GTID

 loose-group_replication_start_on_boot = off

 loose-group_replication_bootstrap_group = off

 loose-group_replication_ip_whitelist = '10.10.20.95,10.10.20.132,10.10.20.97'

 loose-group_replication_local_address = '10.10.20.132:33066' #写自己主机所在IP，端口自选

 loose-group_replication_group_seeds ='10.10.20.95:33066,10.10.20.97:33066,10.10.20.132:33066'#写MGR组内主机所在IP，端口自选

 loose-group_replication_single_primary_mode = off #关闭单主模式的参数

 loose-group_replication_enforce_update_everywhere_checks = off #开启多主模式的参数

 init_slave = 'set sql_mode=STRICT_ALL_TABLES'

 #FOR GTID

 gtid_mode=on

 enforce_gtid_consistency=on

 binlog_gtid_simple_recovery=1

 log_slave_updates=1

 binlog_checksum=NONE

 master_info_repository=TABLE

 relay_log_info_repository=TABLE

my.cnf

mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

mysql> CHANGE MASTER TO MASTER_USER='dbsync',MASTER_PASSWORD='xxxxxxx' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.56 sec)
mysql> set global group_replication_ip_whitelist="10.10.20.95,10.10.20.132,10.10.20.97";
Query OK, 0 rows affected (0.00 sec)
mysql> set auto_increment_offset=5555;
Query OK, 0 rows affected (0.00 sec)
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (3.27 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 041eac3b-a40a-11e8-b11f-545200297bd8 | host2.us.oracle.com | 33061 | ONLINE |
| group_replication_applier | 57bc1e44-a447-11e8-8e78-5254000f067d | host1.us.oracle.com | 33061 | ONLINE |
| group_replication_applier | e3d6b2db-a44e-11e8-8769-545200494892 | host3.us.oracle.com | 33061 | ONLINE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
3 rows in set (0.00 sec)

host1:
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 041eac3b-a40a-11e8-b11f-545200297bd8 | host2.us.oracle.com | 33061 | ONLINE |
| group_replication_applier | 57bc1e44-a447-11e8-8e78-5254000f067d | host1.us.oracle.com | 33061 | ONLINE |
| group_replication_applier | e3d6b2db-a44e-11e8-8769-545200494892 | host3.us.oracle.com | 33061 | ONLINE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
3 rows in set (0.00 sec)
host2:
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 041eac3b-a40a-11e8-b11f-545200297bd8 | host2.us.oracle.com | 33061 | ONLINE |
| group_replication_applier | 57bc1e44-a447-11e8-8e78-5254000f067d | host1.us.oracle.com | 33061 | ONLINE |
| group_replication_applier | e3d6b2db-a44e-11e8-8769-545200494892 | host3.us.oracle.com | 33061 | ONLINE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
3 rows in set (0.00 sec)

查询主节点：

select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member';

查看当前实例是否有延迟等性能问题（通过队列深度判断）的方法：
select * from performance_schema.replication_group_member_stats\G;
通过查看COUNT_TRANSACTIONS_IN_QUEUE的值，可以判断等待处理的事务数。

参考：

http://www.cnblogs.com/xinysu/p/6674832.html

https://www.cnblogs.com/manger/p/7211932.html

https://www.cnblogs.com/lemon-le/p/9241984.html

最新文章

  1. FFT时域与频域的关系，以及采样速率与采样点的影响
  2. DELPHI实现关闭指定进程，自身防杀
  3. 不用画线 设置UITableView的全屏分隔线
  4. Android Design Support Library——Floating Action Button
  5. win7家庭版升级旗舰版
  6. 【python学习笔记01】python的数据类型
  7. delphi 修改代码补全的快捷键(由Ctrl+Space 改为 Ctrl + alt + Space)
  8. OpenCV300 CMake生成project在项目过程中的问题
  9. React和动态网站接口的经济学
  10. HibernateSessionFactory类的主要方法
  11. nyoj 1129 Salvation 模拟
  12. Log.isLoggable之一正确的使用姿势
  13. winform devexpress 用法汇总
  14. LCA - Tarjan 算法
  15. Python_day6
  16. eclipse添加插件实现php的增删改查
  17. node.js 基础一 安装
  18. 应用程序发生异常 unknown software exception (0xc00000fd)... - 栈溢出(Stack overflow)
  19. UIView和layer的区别
  20. GRU门控制循环单元【转载】

热门文章

  1. 03.AutoMapper 之反向映射与逆向扁平化(Reverse Mapping and Unflattening)
  2. vue项目，webpack中配置src路径别名及使用
  3. 富文本编辑器Ueditor
  4. MIT-线性代数公开课
  5. Win10 + Ubuntu 安装教程（痛苦踩坑）
  6. PAT Basic 1037 在霍格沃茨找零钱 (20 分)
  7. PAT Advanced 1009 Product of Polynomials (25 分)（vector删除元素用的是erase）
  8. LoadPicture函数用法示例
  9. Nginx配置https兼容http
  10. 【学习】022 ActiveMQ