Centos6.8 下 部署Mysql组复制（MySQL Group Replication）之多主模式（5.7新特性）
1.关于MGR介绍
1.1提供的特性:
多写，写冲突检测
良好的扩展能力，可动态增删节点，组成员自动管理
组内高可用
确保组内数据最终一致性【重要】(通过分布式协议和分布式recovery机制保证)
- 1
- 2
- 3
- 4
- 5
1.2组复制的两种模式
在单主模式下，组复制具有自动选主功能，每次只有一个 server成员接受更新。
在多主模式下,所有的 server 成员都可以同时接受更新.
- 1
- 2
- 3
1.3组复制的限制
仅支持InnoDB表，并且每张表一定要有一个主键，用于做write set的冲突检测；
必须打开GTID特性，二进制日志格式必须设置为ROW，用于选主与write set
COMMIT可能会导致失败，类似于快照事务隔离级别的失败场景
目前一个MGR集群最多支持9个节点
不支持外键于save point特性，无法做全局间的约束检测与部分部分回滚
二进制日志不支持binlog event checksum
- 1
- 2
- 3
- 4
- 5
- 6
- 7
1.4MySQL组复制协议
上图描述了MySQL组复制协议。
2.安装部署
2.1 环境介绍
三台服务器（centos6.8）安装mysql5.7.19版本，关于mysql安装参考我的另一个文章。
服务器分别为：
192.168.1.208
192.168.1.211
192.168.1.212
| 服务器 | 端口号 | 数据及日志目录 | Group_replication 通讯端口 |
|---|---|---|---|
| 192.168.1.208 | 3306 | /usr/local/mysql/mysql/{data,logs,tmp} | 33061 |
| 192.168.1.211 | 3306 | /usr/local/mysql/mysql/{data,logs,tmp} | 33061 |
| 192.168.1.212 | 3306 | /usr/local/mysql/mysql/{data,logs,tmp} | 33061 |
①.分别在三台服务器添加host
vi /etc/hosts
192.168.1.208 mgr208
192.168.1.212 mgr212
192.168.1.211 mgr211
2.2更改mysql配置文件my.cnf
[client]
port = 3306
socket = /usr/local/mysql/tmp/mysql.sock
[mysqld]
port = 3306
socket = /usr/local/mysql/tmp/mysql.sock
back_log = 80
basedir = /usr/local/mysql
tmpdir = /tmp
datadir = /usr/local/mysql/data
pid-file=/usr/local/mysql/mysqld.pid
#-------------------gobal variables------------#
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 = /usr/local/mysql/log/mysql-bin
transaction_write_set_extraction = XXHASH64 #以便在server收集写集合的同时将其记录到二进制日志。写集合基于每行的主键，并且是行更改后的唯一标识此标识将用于检测冲突。
loose-group_replication_group_name = 'de9be252-2b91-11e6-b8f4-00212889f856' #组的名字可以随便起,但不能用主机的GTID
loose-group_replication_start_on_boot = off #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。
loose-group_replication_bootstrap_group = off
loose-group_replication_local_address = '192.168.1.212:33061' #sql服务器IP
loose-group_replication_group_seeds ='192.168.1.212:33061,192.168.1.208:33061,192.168.1.211:33061'
loose-group_replication_single_primary_mode = off #关闭单主模式的参数（本例测试时多主模式，所以关闭该项）
loose-group_replication_enforce_update_everywhere_checks = on #开启多主模式的参数
max_connect_errors = 20000
max_connections = 2000
wait_timeout = 3600
interactive_timeout = 3600
net_read_timeout = 3600
net_write_timeout = 3600
table_open_cache = 1024
table_definition_cache = 1024
thread_cache_size = 512
open_files_limit = 10000
character-set-server = utf8
collation-server = utf8_bin
skip_external_locking
performance_schema = 1
user = mysql
myisam_recover_options = DEFAULT
skip-name-resolve
local_infile = 0
lower_case_table_names = 0
#--------------------innoDB------------#
innodb_buffer_pool_size = 2000M
innodb_data_file_path = ibdata1:200M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 600
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 8M
innodb_log_file_size = 200M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 85
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_support_xa = 1
innodb_thread_concurrency = 32
innodb_file_per_table
innodb_rollback_on_timeout
#------------session variables-------#
join_buffer_size = 8M
key_buffer_size = 256M
bulk_insert_buffer_size = 8M
max_heap_table_size = 96M
tmp_table_size = 96M
read_buffer_size = 8M
sort_buffer_size = 2M
max_allowed_packet = 64M
read_rnd_buffer_size = 32M
#------------MySQL Log----------------#
log-bin = my3306-bin
binlog_format = row #组复制依赖基于行的复制格式
sync_binlog = 1
expire_logs_days = 15
max_binlog_cache_size = 128M
max_binlog_size = 500M
binlog_cache_size = 64k
slow_query_log
log-slow-admin-statements
log_warnings = 1
long_query_time = 0.25
#---------------replicate--------------#
relay-log-index = relay3306.index
relay-log = relay3306
server-id = 91 #每个db的id唯一
init_slave = 'set sql_mode=STRICT_ALL_TABLES'
log-slave-updates
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
#skip-grant-tables
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
log-error = /usr/local/mysql/log/mysqld_error.log- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
2.3注意事项
启动mysql,如果启动失败，查看日志可能是/usr/local/mysql权限问题，也可以其他问题。
mysql的basedir 必须是/usr/local/mysql 这个mysql官方说basedir如果不在这个目录的话，会有问题。
④.安装组复制插件
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';- 1
2.4启动Group_replication第一个节点(192.168.1.208)
mysql > SET GLOBAL group_replication_bootstrap_group=ON; //只在第一个节点使用
mysql > GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%' IDENTIFIED BY 'Goodluck123456@';
mysql > CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='Goodluck123456@' FOR CHANNEL 'group_replication_recovery';
mysql> SET GLOBAL group_replication_ip_whitelist="127.0.0.1/32,192.168.0.0/16,192.168.1.0/24";
mysql > START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
2.5配置 group_replication_recovery 通道的恢复凭据(192.168.1.211,192.168.1.212)
mysql > SET GLOBAL group_replication_allow_local_disjoint_gtids_join=ON;
mysql > CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='Goodluck123456@' FOR CHANNEL 'group_replication_recovery';
mysql > SET GLOBAL group_replication_ip_whitelist="127.0.0.1/32,192.168.0.0/16,192.168.1.0/24";
mysql > START GROUP_REPLICATION;
mysql > SELECT * FROM performance_schema.replication_group_members;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
2.6 数据同步测试
在192.168.1.212上执行sql
mysql> CREATE TABLE `test1` (`id` int(11) NOT NULL AUTO_INCREMENT ,`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.13 sec)- 1
- 2
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| test1 |
+—————–+
1 row in set (0.00 sec)
在其他两台服务器上查询
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| test1 |
+—————–+
1 row in set (0.00 sec)
浙公网安备 33010602011771号