MySQL 57 MGR 集群搭建

发表于

最近看了一下mysql5.7的MGR集群挺不错的，有单主和多主模式，于是乎搭建测试了一下效果还不错，我指的不错是搭建和维护方面都比较简单。网上绝大多数都是单主模式，当然我这里也是，为了加深印象，特意记录一下搭建过程，等以后再去尝试多主模式，相信大家现在数据库的瓶颈基本都是在写，读写分离虽然是一种可行的解决方案，但是如果数据量很大，写一样会有问题，虽然有些解决方案能部署多个主节点，能同时进行读写，但是脑裂又是一个严重的问题，所以这里MGR集群内置了自动化脑裂防护机制又得到了很多人的青睐，这里MGR简称MySQL
Group Replication是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。注意本文这里不再阐述原理性的东西。
注意：我这里采用编译安装的方式，如果想简单直接yum安装mysql5.7也行，mysql编译安装需要的磁盘空间还是比较大的，一般在7G左右，所以要提前规划好，用三个节点比较接近生产环境，而且更直接清晰。
详细部署信息如下：

主机名 IP地址 安装软件 用途
apache 192.168.2.25 cmake、boost、mysql 节点
nginx 192.168.2.26 cmake、boost、mysql 节点
kibana 192.168.2.30 cmake、boost、mysql 节点

1、三台机器准备工作

点击(此处)折叠或打开

  1. rpm -qa mysql mariadb

    如果有则卸载即可！
    写入hosts文件映射关系，集群用得到
    192.168.2.25 apache
    192.168.2.26 nginx
    192.168.2.30 kibana
    2、安装依赖包

    点击(此处)折叠或打开

  1. yum install gcc gcc-c++ ncurses-devel -y

    3、安装cmake，下载地址：https://cmake.org/download/

    点击(此处)折叠或打开

  1. tar zxvf cmake-3.7.2.tar.gz

  2. cd make-3.7.2

  3. ./configure

  4. gmake && gmake install

    4、安装boost，因为mysql5.7需要，注意这里下载版本是1_59_0和mysql版本是对应的，如果你的MySQL版本和我的不一样，不添加-DWITH_BOOST这个参数时它会报错告诉你需要下载boost的哪个版本。

    点击(此处)折叠或打开

  1. tar zxvf boost_1_59_0.tar.gz

  2. cp -r boost_1_59_0 /usr/local/boost

    5、安装mysql5.7.17及初始化操作

    点击(此处)折叠或打开

  1. groupadd mysql

  2. useradd -M -s /sbin/nologin mysql -g mysql

  3. tar zxvf mysql-5.7.17.tar.gz

  4. cd mysql-5.7.17

  5. cmake -DCMAKE_INSTALL_PREFIX=/data/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_BOOST=/usr/local/boost

  6. make

  7. make install

  8. chown -R mysql.mysql /data/mysql

  9. mv /etc/my.cnf /etc/my.cnf.default

  10. cp /data/mysql/support-files/my-default.cnf /etc/my.cnf

  11. /data/mysql/bin/mysqld –initialize –user=mysql –basedir=/data/mysql –datadir=/data/mysql/data //注意初始化会生成一个随机的密码，请牢记

  12. echo “PATH=$PATH:/data/mysql/bin” >> /etc/profile

  13. source /etc/profile

  14. cp /data/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld

  15. chmod +x /etc/rc.d/init.d/mysqld

    以上步骤在三台机器上都需要执行
    6、开始搭建MGR集群环境，修改第一个节点的my.cnf文件，内容如下：

    点击(此处)折叠或打开

  1. For advice on how to change settings please see

  2. http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

  3. *** DO NOT EDIT THIS FILE. It’s a template which will be copied to the

  4. *** default location during install, and will be replaced if you

  5. *** upgrade to a newer version of MySQL.

  7. [mysqld]

  9. Remove leading # and set to the amount of RAM for the most important data

  10. cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

  11. innodb_buffer_pool_size = 128M

  13. Remove leading # to turn on a very important data integrity option: logging

  14. changes to the binary log between backups.

  15. log_bin

  17. These are commonly set, remove the # and set as required.

  18. basedir = /data/mysql

  19. datadir = /data/mysql/data

  20. port = 3306

  21. socket = /data/mysql/data/mysql.sock

  22. log-error = /data/mysql/data/mysqld.log

  23. pid-file = /data/mysql/data/mysqld.pid

  25. Remove leading # to set options mainly useful for reporting servers.

  26. The server defaults are faster for transactions and fast SELECTs.

  27. Adjust sizes as needed, experiment to find the optimal values.

  28. join_buffer_size = 128M

  29. sort_buffer_size = 2M

  30. read_rnd_buffer_size = 2M

  32. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

  34. Group Replication

  35. server_id = 1

  36. gtid_mode = ON

  37. enforce_gtid_consistency = ON

  38. master_info_repository = TABLE

  39. relay_log_info_repository = TABLE

  40. binlog_checksum = NONE

  41. log_slave_updates = ON

  42. log_bin = binlog

  43. binlog_format= ROW

  45. transaction_write_set_extraction = XXHASH64

  46. loose-group_replication_group_name = ‘ce9be252-2b71-11e6-b8f4-00212844f856’

  47. loose-group_replication_start_on_boot = off

  48. loose-group_replication_local_address = ‘192.168.2.25:33061’

  49. loose-group_replication_group_seeds =’192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061’

  50. loose-group_replication_bootstrap_group = off

  52. [client]

  53. socket = /data/mysql/data/mysql.sock

    启动mysql服务
    /etc/init.d/mysqld start

    点击(此处)折叠或打开

  1. set sql_log_bin=0;

  2. create user rpl_user@’%’;

  3. grant replication slave on *.* to rpl_user@’%’ identified by ‘rpl_pass’;

  4. flush privileges;

  5. set sql_log_bin=1;

  6. change master to master_user=’rpl_user’,master_password=’rpl_pass’ for channel ‘group_replication_recovery’;

  7. install PLUGIN group_replication SONAME ‘group_replication.so’;

  8. set global group_replication_bootstrap_group=ON;

  9. start group_replication;

  10. set global group_replication_bootstrap_group=OFF;

  11. select * from performance_schema.replication_group_members;

    显示结果如下：

    如果出现ONLINE，说明正常，这就是主节点，再搭建两个从节点。
    7、第二个节点加入集群，复制刚刚的第一个节点的主配置文件my.cnf，只需要修改两个地方就行，已经用红色标注

    点击(此处)折叠或打开

  1. For advice on how to change settings please see

  2. http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

  3. *** DO NOT EDIT THIS FILE. It’s a template which will be copied to the

  4. *** default location during install, and will be replaced if you

  5. *** upgrade to a newer version of MySQL.

  7. [mysqld]

  9. Remove leading # and set to the amount of RAM for the most important data

  10. cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

  11. innodb_buffer_pool_size = 128M

  13. Remove leading # to turn on a very important data integrity option: logging

  14. changes to the binary log between backups.

  15. log_bin

  17. These are commonly set, remove the # and set as required.

  18. basedir = /data/mysql

  19. datadir = /data/mysql/data

  20. port = 3306

  21. socket = /data/mysql/data/mysql.sock

  22. log-error = /data/mysql/data/mysqld.log

  23. pid-file = /data/mysql/data/mysqld.pid

  25. Remove leading # to set options mainly useful for reporting servers.

  26. The server defaults are faster for transactions and fast SELECTs.

  27. Adjust sizes as needed, experiment to find the optimal values.

  28. join_buffer_size = 128M

  29. sort_buffer_size = 2M

  30. read_rnd_buffer_size = 2M

  32. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

  34. Group Replication

  35. server_id = 2

  36. gtid_mode = ON

  37. enforce_gtid_consistency = ON

  38. master_info_repository = TABLE

  39. relay_log_info_repository = TABLE

  40. binlog_checksum = NONE

  41. log_slave_updates = ON

  42. log_bin = binlog

  43. binlog_format= ROW

  45. transaction_write_set_extraction = XXHASH64

  46. loose-group_replication_group_name = ‘ce9be252-2b71-11e6-b8f4-00212844f856’

  47. loose-group_replication_start_on_boot = off

  48. loose-group_replication_local_address = ‘192.168.2.26:33061’

  49. loose-group_replication_group_seeds =’192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061’

  50. loose-group_replication_bootstrap_group = off

  52. [client]

  53. socket = /data/mysql/data/mysql.sock

    第二个节点执行如下命令：

    点击(此处)折叠或打开

  1. set sql_log_bin=0;

  2. create user rpl_user@’%’;

  3. grant replication slave on *.* to rpl_user@’%’ identified by ‘rpl_pass’;

  4. set sql_log_bin=1;

  5. change master to master_user=’rpl_user’,master_password=’rpl_pass’ for channel ‘group_replication_recovery’;

  6. install plugin group_replication SONAME ‘group_replication.so’;

  7. set global group_replication_allow_local_disjoint_gtids_join=ON;

  8. start group_replication;

    显示结果如下：

    同理第三个节点加入操作方法也和第二个节点一样。
    截图如下：

    查询哪个是主节点：

    从上图来看很明显apache主机是主节点。
    测试步骤：
    1、在主库上创建一个库，然后创建表，在两个从库上查询数据是否同步？
    2、两个从库只能执行查询操作？
    2、手动关闭主库，确认两个从库其中一个是否会变成主库？而且是MEMBER_ID第一个字母按优先级排列的接管主库？
    日常维护步骤：
    1、如果从库某一节点关闭

    点击(此处)折叠或打开

  1. start group_replication;

    2、如果所有的库都关闭后，第一个库作为主库首先执行

    点击(此处)折叠或打开

  1. set global group_replication_bootstrap_group=ON;

  2. start group_replication;

    剩下的库直接执行即可！

    点击(此处)折叠或打开

  1. start group_replication;

    3、如果主库故障，会自动从两个从库选出一个主库，主库启动后再次执行如下命令后会变成从库

    点击(此处)折叠或打开

  1. start group_replication;

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%