体育彩票销售站联盟

MySQL 5.7 MGR 集群搭建

数据库开发2018-11-07 12:03:20

(点击上方公众号,可快速关注)


来源: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


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


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 -r boost_1_59_0 /usr/local/boost


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


groupadd mysql

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

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 -R mysql.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

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

# *** 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 *.* to rpl_user@'%' identified by 'rpl_pass';

flush privileges;

set sql_log_bin=1;

change master to master_user='rpl_user',master_password='rpl_pass' for channel 'group_replication_recovery';

install PLUGIN group_replication SONAME 'group_replication.so';

set global group_replication_bootstrap_group=ON;

start group_replication;

set global group_replication_bootstrap_group=OFF;

select * from performance_schema.replication_group_members;


显示结果如下:



如果出现ONLINE,说明正常,这就是主节点,再搭建两个从节点。


7、第二个节点加入集群,复制刚刚的第一个节点的主配置文件my.cnf,只需要修改两个地方就行,已经用红色标注


# For advice on how to change settings please see

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

# *** 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 *.* to rpl_user@'%' identified by 'rpl_pass';

set sql_log_bin=1;

change master to master_user='rpl_user',master_password='rpl_pass' for channel 'group_replication_recovery';

install plugin group_replication SONAME 'group_replication.so';

set global group_replication_allow_local_disjoint_gtids_join=ON;

start group_replication;


显示结果如下:



同理第三个节点加入操作方法也和第二个节点一样。


截图如下:



查询哪个是主节点:



从上图来看很明显apache主机是主节点。


测试步骤:


1、在主库上创建一个库,然后创建表,在两个从库上查询数据是否同步?


2、两个从库只能执行查询操作?


3、手动关闭主库,确认两个从库其中一个是否会变成主库?而且是MEMBER_ID第一个字母按优先级排列的接管主库?

日常维护步骤:


1、如果从库某一节点关闭


start group_replication;


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


set global group_replication_bootstrap_group=ON;

start group_replication;


剩下的库直接执行即可!


start group_replication;


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


start group_replication;



看完本文有收获?请转发分享给更多人

关注「数据库开发」,提升 DB 技能


淘口令复制以下红色内容,再打开手淘即可购买

范品社,使用¥极客T恤¥抢先预览(长按复制整段文案,打开手机淘宝即可进入活动内容)