Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1070463
  • 博文数量: 83
  • 博客积分: 159
  • 博客等级: 上尉
  • 技术积分: 2221
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-15 17:08
个人简介

……致我那曾经苦逼的岁月……

文章分类
文章存档

2018年(1)

2017年(7)

2016年(13)

2014年(1)

2013年(12)

2012年(27)

2011年(22)

分类: 系统运维

2017-06-06 17:25:26

最近看了一下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,下载地址:

点击(此处)折叠或打开

  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.
  6. [mysqld]
  7. # Remove leading # and set to the amount of RAM for the most important data
  8. # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
  9. # innodb_buffer_pool_size = 128M
  10. # Remove leading # to turn on a very important data integrity option: logging
  11. # changes to the binary log between backups.
  12. # log_bin
  13. # These are commonly set, remove the # and set as required.
  14. basedir = /data/mysql
  15. datadir = /data/mysql/data
  16. port = 3306
  17. socket = /data/mysql/data/mysql.sock
  18. log-error = /data/mysql/data/mysqld.log
  19. pid-file = /data/mysql/data/mysqld.pid
  20. # Remove leading # to set options mainly useful for reporting servers.
  21. # The server defaults are faster for transactions and fast SELECTs.
  22. # Adjust sizes as needed, experiment to find the optimal values.
  23. # join_buffer_size = 128M
  24. # sort_buffer_size = 2M
  25. # read_rnd_buffer_size = 2M
  26. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  27. # Group Replication
  28. server_id = 1
  29. gtid_mode = ON
  30. enforce_gtid_consistency = ON
  31. master_info_repository = TABLE
  32. relay_log_info_repository = TABLE
  33. binlog_checksum = NONE
  34. log_slave_updates = ON
  35. log_bin = binlog
  36. binlog_format= ROW
  37. transaction_write_set_extraction = XXHASH64
  38. loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'
  39. loose-group_replication_start_on_boot = off
  40. loose-group_replication_local_address = '192.168.2.25:33061'
  41. loose-group_replication_group_seeds ='192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061'
  42. loose-group_replication_bootstrap_group = off
  43. [client]
  44. 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.
  6. [mysqld]
  7. # Remove leading # and set to the amount of RAM for the most important data
  8. # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
  9. # innodb_buffer_pool_size = 128M
  10. # Remove leading # to turn on a very important data integrity option: logging
  11. # changes to the binary log between backups.
  12. # log_bin
  13. # These are commonly set, remove the # and set as required.
  14. basedir = /data/mysql
  15. datadir = /data/mysql/data
  16. port = 3306
  17. socket = /data/mysql/data/mysql.sock
  18. log-error = /data/mysql/data/mysqld.log
  19. pid-file = /data/mysql/data/mysqld.pid
  20. # Remove leading # to set options mainly useful for reporting servers.
  21. # The server defaults are faster for transactions and fast SELECTs.
  22. # Adjust sizes as needed, experiment to find the optimal values.
  23. # join_buffer_size = 128M
  24. # sort_buffer_size = 2M
  25. # read_rnd_buffer_size = 2M
  26. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  27. # Group Replication
  28. server_id = 2
  29. gtid_mode = ON
  30. enforce_gtid_consistency = ON
  31. master_info_repository = TABLE
  32. relay_log_info_repository = TABLE
  33. binlog_checksum = NONE
  34. log_slave_updates = ON
  35. log_bin = binlog
  36. binlog_format= ROW
  37. transaction_write_set_extraction = XXHASH64
  38. loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'
  39. loose-group_replication_start_on_boot = off
  40. loose-group_replication_local_address = '192.168.2.26:33061'
  41. loose-group_replication_group_seeds ='192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061'
  42. loose-group_replication_bootstrap_group = off
  43. [client]
  44. 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;






阅读(17583) | 评论(6) | 转发(7) |
给主人留下些什么吧!~~

他不是傻姑2017-11-29 09:28:36

在吗?不开心啊

split_two2017-11-10 17:05:43

多主模式基本和单主模式一样搭建,只是在每个节点安装group_replication.so复制插件的时候执行如下两个命令即可!
set global group_replication_single_primary_mode=FALSE;
set global group_replication_enforce_update_everywhere_checks=TRUE;

qa_public2017-09-18 14:54:46

厉害了

92docker2017-06-11 21:14:36

哈哈   曾哥 。。。。。。

split_two2017-06-09 10:47:28

onlyword:手动点个赞,学习一下,最近也想研究研究5.7这个多主,大会上看着推得挺多的。不知道好不好用

谢谢,现在一般都是单主模式,我想试试它的多主模式。你要研究出来了说一下啊!

回复 | 举报