MySQL5.7.24 开启Gtid+MGR组复制

MySQL5.7.24 Gtid+MGR

一.服务器环境说明：

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

[root@localhost ~]# uname -aLinux 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.gztar xf  mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz -C /usr/localmv /usr/local/mysql-5.7.24-linux-glibc2.12   /usr/local/mysqluseradd mysql -s /sbin/nologin -Mchown -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                         =1socket                              =/tmp/mysql3306.sock ##:for binlogbinlog_format                  =row  log_bin                             =/home/mysql3306/binlog/mysql-bin binlog_checksum=NONElog_slave_updates=ON##:for gtid    #gtid_executed_compression_period   =1000    gtid_mode                           =onenforce_gtid_consistency      =on####for group_replicationmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONtransaction_write_set_extraction=XXHASH64loose-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-rehashprompt="\u@\h [\d]>"#pager="less -i -n -S"#tee=/opt/mysql/query.log[mysqld]####: for globaluser                                =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 binlogbinlog_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-loglog_error                           =/home/mysql3307/logs/error.log                      general_log                         =off                            general_log_file                    =/home/mysql3307/logs/general.log                    ####: for slow query logslow_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 replicationskip_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_replicationmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONtransaction_write_set_extraction=XXHASH64loose-group_replication_group_name="3db33b36-0e51-409f-a61d-c99756e90155"loose-group_replication_start_on_boot=offloose-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 innodbdefault_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 Statisticsinnodb_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                    =4autocommit                                      =1                         #--------innodb scan resistantinnodb_old_blocks_pct                           =37                        innodb_old_blocks_time                          =1000                      #--------innodb read aheadinnodb_read_ahead_threshold                     =56                        innodb_random_read_ahead                        =OFF                       #--------innodb buffer pool stateinnodb_buffer_pool_dump_pct                     =25                        innodb_buffer_pool_dump_at_shutdown             =ON                        innodb_buffer_pool_load_at_startup              =ON                        

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