在雨润2年，在老大临走之际才知道我们的MySQL不是简单的主从结构，而是MGR集群。哈哈，看了老大留下来的文档，于是尝试着在自己的VM上搭建一下，练习练习

环境准备

  • 192.168.2.3 centos01
  • 192.168.2.4 centos02
  • 192.168.2.5 centos03
  1. tar -zxvf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
  3. mv mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql/
  5. mkdir -p /home/mysql/data
  6. mkdir -p /home/mysql/log
  7. chmod -R 777 /home/mysql/data/
  8. chmod -R 777 /home/mysql/log/
  10. cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
  12. ln -s /usr/local/mysql/bin/mysql /usr/bin
  14. /usr/local/mysql/bin/mysqld --initialize --basedir=/usr/local/mysql --datadir=/home/mysql/data
  16. chkconfig --add mysql
  17. chkconfig --list

image.png

image.png
image.png

  1. [mysqld]
  2. character_set_server=utf8mb4
  3. datadir=/home/mysql/data
  4. socket=/tmp/mysql.sock
  5. user=root
  6. # Disabling symbolic-links is recommended to prevent assorted security risks
  7. symbolic-links=0
  8. #优化
  9. max_connections = 3000
  10. max_connect_errors = 10000
  11. max_allowed_packet = 1024M
  12. slow_query_log = 1
  13. long_query_time = 5
  14. slow_query_log_file = /home/mysql/log/mysql-slow.log
  15. innodb_page_cleaners = 4
  16. lower_case_table_names = 1
  17. #Group Replication
  18. server_id = 1
  19. #服务ID
  20. gtid_mode = ON
  21. #全局事物
  22. enforce_gtid_consistency = ON
  23. #强制GTID的⼀致性
  24. binlog_gtid_simple_recovery = 1
  25. master_info_repository = TABLE
  26. #将master.info元数据保存在系统表中
  27. relay_log_info_repository = TABLE
  28. #将relay.info元数据保存在系统表中
  29. relay_log=/home/mysql/log/relay-log
  30. binlog_checksum = NONE
  31. #禁⽤⼆进制⽇志事件校验
  32. log_slave_updates = ON
  33. #级联复制
  34. log_bin = /home/mysql/data/binlog
  35. #开启⼆进制⽇志记录
  36. binlog_format= ROW
  37. #以⾏的格式记录
  38. log-error=/home/mysql/log/error.log
  39. pid-file=/home/mysql/data/mysqld.pid
  40. ##优化
  41. loose-group_replication_compression_threshold = 15000
  42. loose-group_replication_transaction_size_limit = 1073741824
  43. loose-group_replication_unreachable_majority_timeout = 5
  44. transaction_write_set_extraction = XXHASH64
  45. #使⽤哈希算法将其编码为散列
  46. loose-group_replication_group_name = 'ce9be252-2b72-11e6-b8f4-00212844f856' 
  47. #加⼊的组名,随便写
  48. loose-group_replication_start_on_boot = off
  49. #不启⽤⾃动复制集群功能
  50. loose-group_replication_member_weigth = 30
  51. # ⾮必需，mysql 5.7.20才开始⽀持该选项
  52. loose-group_replication_local_address = 'centos01:33061'
  53. #以本机3306端⼝接收来⾃组成员的传⼊连接
  54. loose-group_replication_group_seeds='centos01:33061,centos02:33061,centos03:33061'
  55. #组中成员访问列表
  56. loose-group_replication_bootstrap_group = off
  57. #不启⽤引导组
  58. loose-group_replication_single_primary_mode = ON
  59. ##loose-group_replication_enforce_update_everywhere_checks=ON 
  60. # =multi-primary

Master

  1. mysql> set SQL_LOG_BIN=0; #停掉⽇志记录
  2. mysql> grant replication slave on *.* to mgr@'%' identified by
  3. '123456';
  4. mysql> flush privileges;
  5. mysql> set SQL_LOG_BIN=1; #开启⽇志记录
  6. mysql> change master to master_user='mgr',master_password='123456' for
  7. channel 'group_replication_recovery';
  9. mysql> install PLUGIN group_replication SONAME 'group_replication.so';
  10. mysql> show plugins;
  11. # 单主模式
  12. 刧洇MGR敯䒳
  13. ⪭☽僜ⱷ⹇拨┕鳮鿥糌server_id⽰IP嫱䙬⟤䷊
  14. # 设置group_replication_bootstrap_group为ON是为了标示以后加⼊集群的服务器以这
  15. 台服务器为基准，以后加⼊的就不需要设置
  16. mysql> set global group_replication_bootstrap_group=ON;
  17. # 作为⾸个节点启动MGR集群
  18. mysql> start group_replication;
  19. mysql> set global group_replication_bootstrap_group=OFF;

Slave

  1. mysql> change master to master_user='mgr',master_password='123456' for
  2. channel 'group_replication_recovery';
  4. mysql> install PLUGIN group_replication SONAME 'group_replication.so';
  5. mysql> show plugins;
  6. # 其他节点
  7. mysql> set global
  8. group_replication_allow_local_disjoint_gtids_join=ON;
  9. mysql> start group_replication;