MySQL5.7.24开启Gtid+MGR组复制

MySQL5.7.24 Gtid+MGR

让客户满意是我们工作的目标,不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户,将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴,公司提供的服务项目有:域名注册雅安服务器托管、营销软件、网站建设、蓝田网站维护、网站推广。

一.服务器环境说明:

由于服务器的资源有限,本次测试采用的是1台机器测试:
一台硬件物理机器:16G内存,一块120G的ssd盘 系统是CentOS6.9 x86_64最小化安装
系统内核:

[root@localhost ~]# uname -a
Linux localhost.localdomain 2.6.32-696.el6.x86_64 #1 SMP Tue Mar 21 19:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

关闭selinux,关闭iptables

服务部署规划说明:
物理机器上以不同的mysql配置文件my.cnf来初始化mysql ,并且以三个不同的端口和添加MGR的配置参数来开启3个MySQL实例
mysql的版本为mysql5.7.24 二进制安装

二.配置步骤如下:

第一,物理机器上开启三个mysql5.7.24 实例:

初始化mysql

下载软件:
wget  https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
tar xf  mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz -C /usr/local
mv /usr/local/mysql-5.7.24-linux-glibc2.12   /usr/local/mysql
useradd mysql -s /sbin/nologin -M
chown -R mysql.mysql   /usr/local/mysql 
yum install numactl  -y

创建MySQL初始化数据存放路径:

mkdir -p /home/mysql3306/{data,binlog,logs}
mkdir -p /home/mysql3307/{data,binlog,logs}
mkdir -p /home/mysql3308/{data,binlog,logs}
chown -R mysql.mysql  /home/{mysql3306,mysql3307,mysql3308}

准备初始化mysql的my.cnf的配置文件:

[root@localhost ~]# ll /opt/my330*
-rw-r--r-- 1 root root 9227 12月 26 18:11 /opt/my3306.cnf
-rw-r--r-- 1 root root 9294 12月 27 18:09 /opt/my3307.cnf
-rw-r--r-- 1 root root 9294 12月 27 18:10 /opt/my3308.cnf
[root@localhost ~]# 

mysql5.7.17开始引入mysql的MGR特性简单介绍MySQL5.7开启Gtid+MGR特性的参数说明:

此次演示环境以mysql3306实例作为第一个MGR的启动实例,
MySQL5.7开启Gtid+MGR特性my3306cnf配置文件必须开启参数如下:

server_id                         =1
socket                              =/tmp/mysql3306.sock 
##:for binlog
binlog_format                  =row  
log_bin                             =/home/mysql3306/binlog/mysql-bin 
binlog_checksum=NONE
log_slave_updates=ON

##:for gtid    
#gtid_executed_compression_period   =1000    
gtid_mode                           =on
enforce_gtid_consistency      =on

####for group_replication
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="3db33b36-0e51-409f-a61d-c99756e90155" ##格式要和server-uuid一致,但是不能和机器上mysql实例的uuid重复
loose-group_replication_start_on_boot=off  ##禁止在重启mysql服务时开启组复制
loose-group_replication_local_address= "192.168.1.233:33061"  #####端口随便指定一个,但是不要和机上其他服务的端口冲突就行,告诉插件本机使用网络地址192.168.1.233和端口33061与组中的其他成员进行内部通信。
loose-group_replication_group_seeds= "192.168.1.233:33061,192.168.1.233:33071,192.168.1.233:33081"
loose-group_replication_ip_whitelist="192.168.1.233/24"  ##此处必须设置ip白名单,否则在开启MGR时,会报错。
loose-group_replication_bootstrap_group= off  ##
##loose-group_replication_single_primary_mode=off  ##在配置MGR的mutl-master模式时开启
##loose-group_replication_enforce_update_everywhere_checks=on ##在配置MGR的mutl-master模式时开启 

my3307.cnf配置文件和my3306.cnf中不同的是把loose-group_replication_local_address修改为 "192.168.1.233:33071"
my3308.cnf配置文件和my3306.cnf中不同的是把loose-group_replication_local_address修改为 "192.168.1.233:33081"

启动mysql实例:

/usr/local/mysql/bin/mysqld --defaults-file=/opt/my3306.cnf --initialize
/usr/local/mysql/bin/mysqld --defaults-file=/opt/my3307.cnf --initialize
/usr/local/mysql/bin/mysqld --defaults-file=/opt/my3308.cnf  --initialize

为了在配置MGR的过程中防止出错,在启动mysql之前把auto.cnf 修改为3个不相同的uuid

[root@localhost ~]# cat /home/mysql3306/data/auto.cnf 
[auto]
server-uuid=1ec3ac79-08ed-11e9-8da8-bcaec502b368
[root@localhost ~]# cat /home/mysql3307/data/auto.cnf 
[auto]
server-uuid=288e7bbe-08f3-11e9-a605-bcaec502b311
[root@localhost ~]# cat /home/mysql3308/data/auto.cnf 
[auto]
server-uuid=883c9421-08f5-11e9-8d47-bcaec502b333

启动mysql服务:

/usr/local/mysql/bin/mysqld --defaults-file=/opt/my3306.cnf  &
/usr/local/mysql/bin/mysqld --defaults-file=/opt/my3307.cnf  &
/usr/local/mysql/bin/mysqld --defaults-file=/opt/my3308.cnf  &

第二.开启MGR配置过程(默认是single-master模式):

mysql3306作为第一个节点开启MGR:

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'Zykjwujianwei';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Zykjwujianwei' FOR CHANNEL 'group_replication_recovery';
install PLUGIN group_replication SONAME 'group_replication.so';
set global group_replication_bootstrap_group=ON;
start group_replication;
select * from performance_schema.replication_group_members;

注意:只有在第一个开启MGR的mysql3306节点上才执行这个参数set global group_replication_bootstrap_group=ON;

root@localhost [(none)]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST           | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 1ec3ac79-08ed-11e9-8da8-bcaec502b368 | localhost.localdomain |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
3 rows in set (0.00 sec)
root@localhost [(none)]>

查看当前的leader:

select *from performance_schema.replication_group_members where member_id =(select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member');
select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member';

节点mysql3307操作:

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'Zykjwujianwei';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Zykjwujianwei' FOR CHANNEL 'group_replication_recovery';
install PLUGIN group_replication SONAME 'group_replication.so';
start group_replication;
select * from performance_schema.replication_group_members;
root@localhost [(none)]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST           | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 1ec3ac79-08ed-11e9-8da8-bcaec502b368 | localhost.localdomain |        3306 | ONLINE       |
| group_replication_applier | 288e7bbe-08f3-11e9-a605-bcaec502b311 | localhost.localdomain |        3307 | ONLINE       |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+

节点mysql3308操作:

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'Zykjwujianwei';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Zykjwujianwei' FOR CHANNEL 'group_replication_recovery';
install PLUGIN group_replication SONAME 'group_replication.so';
start group_replication;
select * from performance_schema.replication_group_members;
root@localhost [(none)]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST           | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 1ec3ac79-08ed-11e9-8da8-bcaec502b368 | localhost.localdomain |        3306 | ONLINE       |
| group_replication_applier | 288e7bbe-08f3-11e9-a605-bcaec502b311 | localhost.localdomain |        3307 | ONLINE       |
| group_replication_applier | 883c9421-08f5-11e9-8d47-bcaec502b333 | localhost.localdomain |        3308 | ONLINE       |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
3 rows in set (0.00 sec)

第三.测试效果:


root@localhost [(none)]>select @@port;create database test01;show databases;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test01             |
+--------------------+
5 rows in set (0.00 sec)

root@localhost [(none)]>select @@port;create database test02;show databases;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.00 sec)

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test01             |
+--------------------+
5 rows in set (0.00 sec)

root@localhost [(none)]>

root@localhost [(none)]>select @@port;create database test02;show databases;
+--------+
| @@port |
+--------+
|   3308 |
+--------+
1 row in set (0.00 sec)

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test01             |
+--------------------+
5 rows in set (0.00 sec)

root@localhost [(none)]>

测试说明:

发现mysql3306上创建的test01同步到了其他的2个mysql。然而在MGR组中mysql3307 和mysql3308是只读,不能写入的。
关掉mysql3306服务会发生什么?? 直接会从mysql3307和mysql3308实例中选择一个作为mysql主库,另外一个作为新主库的slave库
关掉mysql3306服务一段时间后,有重新开启mysql3306,并且start group_replication;重新加入到MGR组会发生什么??
此时mysql3306不会切换为master库,而是成为了宕机后新主库的slave库。

mysql的mult-master多主写入的模式:

MGR默认的模式也就是咱们演示的single-master模式(单主写入模式)。那如何开启mysql的mult-master多主写入的模式呢??下面简单介绍下:

首先要先关闭只读的从库mysql3307和mysql3308实例最后关闭mysql3306

其次在各自的my.cnf配置文件中开启如下参数:

loose-group_replication_single_primary_mode=off  ##在配置MGR的mutl-master模式时开启
loose-group_replication_enforce_update_everywhere_checks=on ##在配置MGR的mutl-master模式时开启 

最后启动mysql实例,按照配置MGR-single-master的顺序步骤来进行配置MGR的multi-master模式

特别提示:
multi-master模式下,3个节点mysql实例都是可读写的。但是在第一个启动的mysql实例上需要执行set global group_replication_bootstrap_group=ON;(注意:后面的节点不需要执行这个sql)
mysql5.7 开启MGR multi-master模式后,查看实例的状态发现不存在所谓的leader

(root@localhost:mysql.sock)[(none)]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST           | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 862addac-10c5-11e9-9af5-bcaec502b317 | localhost.localdomain |        3307 | ONLINE       |
| group_replication_applier | a36b8f24-10c3-11e9-bf76-bcaec502b318 | localhost.localdomain |        3308 | ONLINE       |
| group_replication_applier | da3809c4-10bc-11e9-bb9e-bcaec502b368 | localhost.localdomain |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
3 rows in set (0.00 sec)
(root@localhost:mysql.sock)[(none)]>select *from performance_schema.replication_group_members where member_id =(select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member');
Empty set (0.00 sec)

(root@localhost:mysql.sock)[(none)]>select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member';
+----------------+
| variable_value |
+----------------+
|                |
+----------------+
1 row in set (0.00 sec)

my.cnf配置文件演示:

mysql3307完整的配置文件如下:

[root@localhost ~]# cat /opt/my3307.cnf 
[client]
port            = 3307

[mysql]
auto-rehash
prompt="\u@\h [\d]>"
#pager="less -i -n -S"
#tee=/opt/mysql/query.log

[mysqld]
####: for global
user                                =mysql                         
basedir                             =/usr/local/mysql/             
datadir                             =/home/mysql3307/data    
server_id                           =2                       
port                                =3307                          
character_set_server                =utf8                          
explicit_defaults_for_timestamp     =off                           
log_timestamps                      =system                        
socket                              =/tmp/mysql3307.sock               
read_only                           =0                             
skip_name_resolve                   =1                             
auto_increment_increment            =1                             
auto_increment_offset               =1                             
lower_case_table_names              =1                             
secure_file_priv                    =                              
open_files_limit                    =65536                         
max_connections                     =1000                          
thread_cache_size                   =64                            
table_open_cache                    =81920                         
table_definition_cache              =4096                          
table_open_cache_instances          =64                            
max_prepared_stmt_count             =1048576                       

####: for binlog
binlog_format                       =row                           
log_bin                             =/home/mysql3307/binlog/mysql-bin                     
binlog_rows_query_log_events        =on                            
#log_slave_updates                   =on                            
expire_logs_days                    =7                             
binlog_cache_size                   =65536                         
#binlog_checksum                    =none                         
sync_binlog                         =1                             
slave-preserve-commit-order         =ON                            

####: for error-log
log_error                           =/home/mysql3307/logs/error.log                      

general_log                         =off                            
general_log_file                    =/home/mysql3307/logs/general.log                    

####: for slow query log
slow_query_log                      =on                             
slow_query_log_file                 =/home/mysql3307/logs/slow.log                       
#log_queries_not_using_indexes      =on                            
long_query_time                     =1.000000                       

####: for gtid
#gtid_executed_compression_period   =1000                          
gtid_mode                           =on                             
enforce_gtid_consistency            =on                             

####: for replication
skip_slave_start                     =1                             
#master_info_repository              =table                         
#relay_log_info_repository           =table                         
slave_parallel_type                  =logical_clock                 
slave_parallel_workers               =4                             
#rpl_semi_sync_master_enabled        =1                             
#rpl_semi_sync_slave_enabled         =1                             
#rpl_semi_sync_master_timeout        =1000                          
#plugin_load_add                     =semisync_master.so            
#plugin_load_add                     =semisync_slave.so             
binlog_group_commit_sync_delay       =100                           
binlog_group_commit_sync_no_delay_count = 10                        

####for group_replication
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="3db33b36-0e51-409f-a61d-c99756e90155"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.1.233:33071"
loose-group_replication_group_seeds= "192.168.1.233:33061,192.168.1.233:33071,192.168.1.233:33081"
loose-group_replication_ip_whitelist="192.168.1.233/24"
##loose-group_replication_single_primary_mode=off  ##在配置MGR的mutl-master模式时开启
##loose-group_replication_enforce_update_everywhere_checks=on ##在配置MGR的mutl-master模式时开启 

####: for innodb
default_storage_engine                          =innodb                    
default_tmp_storage_engine                      =innodb                    
innodb_data_file_path                           =ibdata1:1024M:autoextend  
innodb_temp_data_file_path                      =ibtmp1:12M:autoextend     
innodb_buffer_pool_filename                     =ib_buffer_pool            
innodb_log_group_home_dir                       =/home/mysql3307/data                        
innodb_log_files_in_group                       =3                         
innodb_log_file_size                            =1024M                     
innodb_file_per_table                           =on                        
innodb_online_alter_log_max_size                =128M                      
innodb_open_files                               =65535                     
innodb_page_size                                =16k                       
innodb_thread_concurrency                       =0                         
innodb_read_io_threads                          =4                         
innodb_write_io_threads                         =4                         
innodb_purge_threads                            =4                         
innodb_page_cleaners                            =4         
                 #   4(刷新lru脏页)
innodb_print_all_deadlocks                      =on                        
innodb_deadlock_detect                          =on                        
innodb_lock_wait_timeout                        =20                        
innodb_spin_wait_delay                          =128                       
innodb_autoinc_lock_mode                        =2                         
innodb_io_capacity                              =200                       
innodb_io_capacity_max                          =2000                      
#--------Persistent Optimizer Statistics
innodb_stats_auto_recalc                        =on                        
innodb_stats_persistent                         =on                        
innodb_stats_persistent_sample_pages            =20                        

innodb_adaptive_hash_index                      =on                        
innodb_change_buffering                         =all                       
innodb_change_buffer_max_size                   =25                        
innodb_flush_neighbors                          =1                         
#innodb_flush_method                             =                         
innodb_doublewrite                              =on                        
innodb_log_buffer_size                          =128M                      
innodb_flush_log_at_timeout                     =1                         
innodb_flush_log_at_trx_commit                  =1                         
innodb_buffer_pool_size                         =4096M                      
innodb_buffer_pool_instances                    =4
autocommit                                      =1                         
#--------innodb scan resistant
innodb_old_blocks_pct                           =37                        
innodb_old_blocks_time                          =1000                      
#--------innodb read ahead
innodb_read_ahead_threshold                     =56                        
innodb_random_read_ahead                        =OFF                       
#--------innodb buffer pool state
innodb_buffer_pool_dump_pct                     =25                        
innodb_buffer_pool_dump_at_shutdown             =ON                        
innodb_buffer_pool_load_at_startup              =ON                        

到此处mysql5.7.24的Grid+MGR模式演示完毕


文章题目:MySQL5.7.24开启Gtid+MGR组复制
转载来源:http://scyanting.com/article/giidej.html