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配置过程:

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)]>

节点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模式

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模式演示完毕