马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
来源:split_two blog.chinaunix.net/uid-23916356-id-5765908.html
最近看了一下mysql5.7的MGR集群挺不错的,有单主和多主模式,于是乎搭建测试了一下效果还不错,我指的不错是搭建和维护方面都比较简单。网上绝大多数都是单主模式,当然我这里也是,为了加深印象,特意记录一下搭建过程,等以后再去尝试多主模式,相信大家现在数据库的瓶颈基本都是在写,读写分离虽然是一种可行的解决方案,但是如果数据量很大,写一样会有问题,虽然有些解决方案能部署多个主节点,能同时进行读写,但是脑裂又是一个严重的问题,所以这里MGR集群内置了自动化脑裂防护机制又得到了很多人的青睐,这里MGR简称MySQL Group Replication是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。注意本文这里不再阐述原理性的东西。 注意:我这里采用编译安装的方式,如果想简单直接yum安装mysql5.7也行,mysql编译安装需要的磁盘空间还是比较大的,一般在7G左右,所以要提前规划好,用三个节点比较接近生产环境,而且更直接清晰。 详细部署信息如下: 1、三台机器准备工作 rpm -qa mysql mariadb
如果有则卸载即可! 写入hosts文件映射关系,集群用得到 192.168.2.25 apache 192.168.2.26 nginx 192.168.2.30 kibana 2、安装依赖包 yum install gcc gcc-c++ ncurses-devel -y
tar zxvf cmake-3.7.2.tar.gz cd make-3.7.2 ./configure gmake&& gmake install
4、安装boost,因为mysql5.7需要,注意这里下载版本是1_59_0和mysql版本是对应的,如果你的MySQL版本和我的不一样,不添加-DWITH_BOOST这个参数时它会报错告诉你需要下载boost的哪个版本。 tar zxvf boost_1_59_0.tar.gz cp-rboost_1_59_0/usr/local/boost
5、安装mysql5.7.17及初始化操作 groupadd mysql useradd-M-s/sbin/nologin mysql-gmysql tar zxvf mysql-5.7.17.tar.gz cd mysql-5.7.17 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 make make install chown-Rmysql.mysql/data/mysql mv/etc/my.cnf/etc/my.cnf.default cp/data/mysql/support-files/my-default.cnf/etc/my.cnf /data/mysql/bin/mysqld--initialize--user=mysql--basedir=/data/mysql--datadir=/data/mysql/data//注意初始化会生成一个随机的密码,请牢记 echo"PATH=$PATH:/data/mysql/bin">> /etc/profile source/etc/profile cp/data/mysql/support-files/mysql.server/etc/rc.d/init.d/mysqld chmod+x/etc/rc.d/init.d/mysqld
以上步骤在三台机器上都需要执行 6、开始搭建MGR集群环境,修改第一个节点的my.cnf文件,内容如下: # For advice on how to change settings please see # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. basedir= /data/mysql datadir= /data/mysql/data port= 3306 socket= /data/mysql/data/mysql.sock log-error= /data/mysql/data/mysqld.log pid-file= /data/mysql/data/mysqld.pid # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES # Group Replication server_id= 1 gtid_mode= ON enforce_gtid_consistency= ON master_info_repository= TABLE relay_log_info_repository= TABLE binlog_checksum= NONE log_slave_updates= ON log_bin= binlog binlog_format= ROW transaction_write_set_extraction= XXHASH64 loose-group_replication_group_name= 'ce9be252-2b71-11e6-b8f4-00212844f856' loose-group_replication_start_on_boot= off loose-group_replication_local_address= '192.168.2.25:33061' loose-group_replication_group_seeds='192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061' loose-group_replication_bootstrap_group= off [client] socket= /data/mysql/data/mysql.sock
启动mysql服务 /etc/init.d/mysqld start set sql_log_bin=0; create user rpl_user@'%'; grant replication slave on *.* torpl_user@'%'identified by'rpl_pass'; flush privileges; set sql_log_bin=1; change master tomaster_user='rpl_user',master_password='rpl_pass'forchannel'group_replication_recovery'; install PLUGIN group_replication SONAME'group_replication.so'; set globalgroup_replication_bootstrap_group=ON; start group_replication; set globalgroup_replication_bootstrap_group=OFF; select *from performance_schema.replication_group_members;
显示结果如下: 如果出现ONLINE,说明正常,这就是主节点,再搭建两个从节点。 7、第二个节点加入集群,复制刚刚的第一个节点的主配置文件my.cnf,只需要修改两个地方就行,已经用红色标注 # For advice on how to change settings please see # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. basedir= /data/mysql datadir= /data/mysql/data port= 3306 socket= /data/mysql/data/mysql.sock log-error= /data/mysql/data/mysqld.log pid-file= /data/mysql/data/mysqld.pid # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES # Group Replication server_id= 2 gtid_mode= ON enforce_gtid_consistency= ON master_info_repository= TABLE relay_log_info_repository= TABLE binlog_checksum= NONE log_slave_updates= ON log_bin= binlog binlog_format= ROW transaction_write_set_extraction= XXHASH64 loose-group_replication_group_name= 'ce9be252-2b71-11e6-b8f4-00212844f856' loose-group_replication_start_on_boot= off loose-group_replication_local_address= '192.168.2.26:33061' loose-group_replication_group_seeds='192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061' loose-group_replication_bootstrap_group= off [client] socket= /data/mysql/data/mysql.sock
第二个节点执行如下命令: set sql_log_bin=0; create user rpl_user@'%'; grant replication slave on *.* torpl_user@'%'identified by'rpl_pass'; set sql_log_bin=1; change master tomaster_user='rpl_user',master_password='rpl_pass'forchannel'group_replication_recovery'; install plugin group_replication SONAME'group_replication.so'; set globalgroup_replication_allow_local_disjoint_gtids_join=ON; start group_replication;
显示结果如下: 同理第三个节点加入操作方法也和第二个节点一样。 截图如下: 查询哪个是主节点: 从上图来看很明显apache主机是主节点。 测试步骤: 1、在主库上创建一个库,然后创建表,在两个从库上查询数据是否同步? 2、两个从库只能执行查询操作? 3、手动关闭主库,确认两个从库其中一个是否会变成主库?而且是MEMBER_ID第一个字母按优先级排列的接管主库? 日常维护步骤: 1、如果从库某一节点关闭 start group_replication;
2、如果所有的库都关闭后,第一个库作为主库首先执行 set globalgroup_replication_bootstrap_group=ON; start group_replication;
剩下的库直接执行即可! start group_replication;
3、如果主库故障,会自动从两个从库选出一个主库,主库启动后再次执行如下命令后会变成从库 start group_replication;
|