Mysql Group Replication 简介及单主模式组复制配置【转】

-qa | grep mariadb rpm -e mariadb-libs --nodeps

 

   2.编译安装mysql5.7

#添加mysql用户组并添加mysql用户(不允许登录)
groupadd mysql
useradd -r -g mysql -s /bin/false -M mysql
#下载mysql源码包
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17.tar.gz
#安装编译工具和依赖包
yum install -y cmake make gcc gcc-c++
yum install -y ncurses-devel openssl-devel bison-devel libaio libaio-devel
# boost库安装
# 该步骤可以省略，在cmake阶段添加参数-DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost即可
wget http://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz
tar -zxvf boost_1_59_0.tar.gz -C /usr/local
mv /usr/local/boost_1_59_0 /usr/local/boost
cd /usr/local/boost
./bootstrap.sh
./b2 stage threading=multi link=shared
./b2 install threading=multi link=shared
#安装编译mysql
cd
tar -zxvf mysql-5.7.17.tar.gz
cd mysql-5.7.17
#使用cmake工具设置参数，新版的php都从configure改用cmake
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADTR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DMYSQL_USER=mysql -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DENABLE_DOWNLOADS=1 -DWITH_PARTRTION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=0 -DMYSQL_MAINTAINER_MODE=0 -DMITH_SSL:STRING=bundled -DWITH_ZLIB:STRING=bundled -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost
make && make install
#默认1个线程编译，可使用指定线程数加快编译
#make -j $(grep processor /proc/cpuinfo | wc -l) && make install
#将mysql目录权限给mysql用户
chown -Rf mysql:mysql /usr/local/mysql
#设置mysql的默认配置文件
cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
#把mysql命令加入查找路径
echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile
source /etc/profile

 

   防火墙和selinux设置

firewall-cmd --permanent --add-port=3307/tcp
firewall-cmd --permanent --add-port=3308/tcp
firewall-cmd --permanent --add-port=3309/tcp
firewall-cmd --permanent --add-port=3407/tcp
firewall-cmd --permanent --add-port=3408/tcp
firewall-cmd --permanent --add-port=3409/tcp
firewall-cmd --reload
setenforce 0
sed -i ‘s#enforcing#permissive#g‘ /etc/selinux/config

 

   3.部署组复制实例

mkdir /data
#初始化数据库，创建数据库
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/s1
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/s2
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/s3

 

   4.配置组复制实例S1

cat > /data/s1/my.cnf <<EOF
[mysqld]
# server configuration
user=mysql
datadir=/data/s1
basedir=/usr/local/mysql/
port=3307
socket=/usr/local/mysql/s1.sock
#复制框架
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
#组复制设置
#server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列
transaction_write_set_extraction=XXHASH64
#告知插件加入或创建组命名，UUID
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#server启动时不自启组复制
loose-group_replication_start_on_boot=off
#告诉插件使用IP地址，端口3407用于接收组中其他成员转入连接
loose-group_replication_local_address="127.0.0.1:3407"
#启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意
loose-group_replication_group_seeds="127.0.0.1:3407,127.0.0.1:3408,127.0.0.1:3409"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
EOF

 

   5.用户凭据

#启动mysql实例1服务
nohup mysqld --defaults-file=/data/s1/my.cnf >/data/s1/nohup.out 2>/data/s1/nohup.out &
#登录mysql s1
mysql -uroot -h127.0.0.1 -P3307 --skip-password
#修改root密码
alter user ‘root‘@‘localhost‘ identified by ‘123456‘;
#下面操纵不写入二进制日志，避免修改传递给其他实例，先关闭
set sql_log_bin=0;
#创建拥有replication slave权限mysql用户
create user rpl_user@‘%‘;
grant replication slave on *.* to rpl_user@‘%‘ identified by ‘rpl_pass‘;
flush privileges;
#开启二进制写入
set sql_log_bin=1;
#分布式恢复加入组的server执行第一步
#change master to语言将server配置为，在下次需要从其他成员恢复状态时，使用group_replication_recovery复制通道的给定凭证。
change master to master_user=‘rpl_user‘,master_password=‘rpl_pass‘ for channel ‘group_replication_recovery‘;
#建议每个mysql通过配置唯一主机名，通过DNS或本地设置。（相同可能导致无法恢复）

 

   6.启动组复制

#安装组复制插件
install plugin group_replication soname ‘group_replication.so‘;
#检测插件是否安装成功
show plugins;
+----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ ...... | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+
#server s1引导组，启动组复制程序（复制组只启动一次就行）
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;
#检测组是否创建并已加入新成员
select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 70bf7af3-1657-11e7-82a8-000c29433013 | test1 | 3307 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+

 

   7.测试

mysql> create database test;
2017-03-31T23:23:45.535115Z 8 [Note] Plugin group_replication reported: ‘Primary had applied all relay logs, disabled conflict detection‘
Query OK, 1 row affected (0.03 sec)
mysql> use test;
Database changed
mysql> create table t1(c1 int primary key,c2 text not null);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values (1 , ‘Luis‘);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
1 row in set (0.00 sec)
mysql> show binlog events;
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+ | binlog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 | | binlog.000001 | 123 | Previous_gtids | 1 | 150 | | | binlog.000001 | 150 | Gtid | 1 | 211 | SET @@SESSION.GTID_NEXT= ‘21355e09-16ea-11e7-bb6b-000c29433013:1‘ | | binlog.000001 | 211 | Query | 1 | 386 | ALTER USER ‘root‘@‘localhost‘ IDENTIFIED WITH ‘mysql_native_password‘ AS ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ | | binlog.000001 | 386 | Gtid | 1 | 447 | SET @@SESSION.GTID_NEXT= ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1‘ | | binlog.000001 | 447 | Query | 1 | 506 | BEGIN | | binlog.000001 | 506 | View_change | 1 | 645 | view_id=14910585094598745:1 | | binlog.000001 | 645 | Query | 1 | 710 | COMMIT | | binlog.000001 | 710 | Gtid | 1 | 771 | SET @@SESSION.GTID_NEXT= ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2‘ | | binlog.000001 | 771 | Query          | 1 | 861 | create database test | | binlog.000001 | 861 | Gtid | 1 | 922 | SET @@SESSION.GTID_NEXT= ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3‘ | | binlog.000001 | 922 | Query |         1 | 1044 | use `test`; create table t1(c1 int primary key,c2 text not null) | | binlog.000001 | 1044 | Gtid | 1 | 1105 | SET @@SESSION.GTID_NEXT= ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4‘ | | binlog.000001 | 1105 | Query | 1 | 1173 | BEGIN | | binlog.000001 | 1173 | Table_map | 1 | 1216 | table_id: 220 (test.t1) | | binlog.000001 | 1216 | Write_rows | 1 | 1258 | table_id: 220 flags: STMT_END_F | | binlog.000001 | 1258 | Xid | 1 | 1285 | COMMIT /* xid=40 */ | +---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+ 17 rows in set (0.00 sec)

 

   8.第二个实例添加(S2)     

cat > /data/s2/my.cnf <<EOF
[mysqld]
# server configuration
user=mysql
datadir=/data/s2
basedir=/usr/local/mysql/
port=3308
socket=/usr/local/mysql/s2.sock
#复制框架
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
#组复制设置
#server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列
transaction_write_set_extraction=XXHASH64
#告知插件加入或创建组命名，UUID
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#server启动时不自启组复制
loose-group_replication_start_on_boot=off
#告诉插件使用IP地址，端口3408用于接收组中其他成员转入连接
loose-group_replication_local_address="127.0.0.1:3408"
#启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意
loose-group_replication_group_seeds="127.0.0.1:3407,127.0.0.1:3408,127.0.0.1:3409"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
EOF

 

   9.用户凭证（S2）

#启动mysql实例2服务
nohup mysqld --defaults-file=/data/s2/my.cnf >/data/s2/nohup.out 2>/data/s2/nohup.out &
#登录mysql s1
mysql -uroot -h127.0.0.1 -P3308 --skip-password
#修改root密码
alter user ‘root‘@‘localhost‘ identified by ‘123456‘;
#下面操纵不写入二进制日志，避免修改传递给其他实例，先关闭
set sql_log_bin=0;
#创建拥有replication slave权限mysql用户
create user rpl_user@‘%‘;
grant replication slave on *.* to rpl_user@‘%‘ identified by ‘rpl_pass‘;
flush privileges;
#开启二进制写入
set sql_log_bin=1;
#分布式恢复加入组的server执行第一步
#change master to语言将server配置为，在下次需要从其他成员恢复状态时，使用group_replication_recovery复制通道的给定凭证。
change master to master_user=‘rpl_user‘,master_password=‘rpl_pass‘ for channel ‘group_replication_recovery‘;
#建议每个mysql通过配置唯一主机名，通过DNS或本地设置。（相同可能导致无法恢复）

 

   10.添加组

#安装组复制插件
install plugin group_replication soname ‘group_replication.so‘;
#检测插件是否安装成功
show plugins;
+----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ ...... | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+
#添加到复制组（不用再设置启动，该组已在是s1时启动）
set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;
#检测组是否创建并已加入新成员
select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 21355e09-16ea-11e7-bb6b-000c29433013 | test1 | 3307 | ONLINE       | | group_replication_applier | 25b39bc8-16ea-11e7-bc1e-000c29433013 | test1 | 3308 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+
   11.测试同步是否正常
mysql> show databases like ‘test‘;
+-----------------+
| Database (test) |
+-----------------+
| test |
+-----------------+

 

    问题： #在建立第二个mysql实例s2时，会有以下现象，s2一直处于RECOVERING状态。

+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 21355e09-16ea-11e7-bb6b-000c29433013 | test1 | 3307 | ONLINE |
| group_replication_applier | 25b39bc8-16ea-11e7-bc1e-000c29433013 | test1 | 3308 | RECOVERING |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

 

错误日志如下：

[ERROR] Slave I/O for channel ‘group_replication_recovery‘: error connecting to master ‘rpl_user@test1:3307‘ - retry-time: 60 retries: 1, Error_code: 2003
[ERROR] Plugin group_replication reported: ‘There was an error when connecting to the donor server. Check group replication recovery‘s connection credentials.‘

 

原因：mysql组复制用的是域名连接，我的主机设置名字为test1,但没有在hosts文件中声明其IP为127.0.0.1。（DNS就更没有了）导致s2无法正常访问s1。因而报错。 解决方法：修改/etc/hosts文件，追加127.0.0.1 对应主机名为test1。然后重新启动组复制就可以。     参考博客 http://wangwei007.blog.51cto.com/680.19/1907145  《Mysql Group Repaliation》京东翻译 参考博客 http://www.voidcn.com/blog/d6619309/article/p-6346153.html    转自 http://blog.csdn.net/hzsunshine/article/details/69132225

Mysql Group Replication 简介及单主模式组复制配置【转】

标签：cte   aaa   独立   from   ica   ever   configure   mysql复制   arc