MySQL5.7.24 开启Gtid+MGR组复制

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-f来初始化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-f的配置文件：

[root@localhost ~]# ll /opt/my330* -rw-r--r-- 1 root root 9227 12月 26 18:11 /opt/my3306-f -rw-r--r-- 1 root root 9294 12月 27 18:09 /opt/my3307-f -rw-r--r-- 1 root root 9294 12月 27 18:10 /opt/my3308-f [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-f配置文件和my3306-f中不同的是把loose-group_replication_local_address修改为 "192.168.1.233:33071" my3308-f配置文件和my3306-f中不同的是把loose-group_replication_local_address修改为 "192.168.1.233:33081"

启动mysql实例：

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

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

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

启动mysql服务：

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

第二.开启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-f配置文件中开启如下参数：

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-f配置文件演示：

mysql3307完整的配置文件如下：

[root@localhost ~]# cat /opt/my3307-f [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模式演示完毕

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。