MySQL之GroupReplication初体验
https://dev.mysql.com/doc/refman/5.7/en/group-replication.html基本的架构一主两从
master：192.168.100.41
slave：192.168.100.42
slave：192.168.100.43
下载软件官方网站
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-devel-5.7.17-1.el6.x86_64.rpm
mysql-community-embedded-5.7.17-1.el6.x86_64.rpm
mysql-community-embedded-devel-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
安装好后初始化数据库
/usr/sbin/mysqld--initilaize
mysql -uroot -p''
密码在mysqld.log中
登陆后需要更改root密码，否则是任何事情也干不了！
mysql> alter user 'root'@'localhost' identified by 'root';
#每个主机分别创建复制账号：
mysql> CREATE USER rpluser@'%';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpluser@'%' IDENTIFIED BY 'rplpass';
#三台mysql分别添加GR复制插件
MySQL的插件位置在/usr/lib64/mysql/plugin下（RPM安装），不同的安装方式位置不同
# ll
total 50708
-rwxr-xr-x 1 root root 86038 Nov 28 22:30 adt_null.so
-rwxr-xr-x 1 root root 40650 Nov 28 22:30 auth_socket.so
-rwxr-xr-x 1 root root 670808 Nov 28 22:31 connection_control.so
drwxr-xr-x 2 root root 4096 Jan 22 17:07 debug
-rwxr-xr-x 1 root root 15645986 Nov 28 22:32 group_replication.so
-rwxr-xr-x 1 root root 343512 Nov 28 22:30 ha_example.so
-rwxr-xr-x 1 root root 685137 Nov 28 22:30 innodb_engine.so
-rwxr-xr-x 1 root root 761474 Nov 28 22:30 keyring_file.so
-rwxr-xr-x 1 root root 293528 Nov 28 22:31 keyring_udf.so
-rwxr-xr-x 1 root root 637537 Nov 28 22:30 libmemcached.so
-rwxr-xr-x 1 root root7945238 Nov 28 22:31 libpluginmecab.so
-rwxr-xr-x 1 root root 16892 Nov 28 22:31 locking_service.so
-rwxr-xr-x 1 root root 49314 Nov 28 22:31 mypluglib.so
-rwxr-xr-x 1 root root 38158 Nov 28 22:30 mysql_no_login.so
-rwxr-xr-x 1 root root 22598217 Nov 28 22:32 mysqlx.so
-rwxr-xr-x 1 root root 46913 Nov 28 22:31 rewrite_example.so
-rwxr-xr-x 1 root root 619923 Nov 28 22:31 rewriter.so
-rwxr-xr-x 1 root root 693981 Nov 28 22:31 semisync_master.so
-rwxr-xr-x 1 root root 152509 Nov 28 22:31 semisync_slave.so
-rwxr-xr-x 1 root root 205419 Nov 28 22:30 validate_password.so
-rwxr-xr-x 1 root root 346295 Nov 28 22:31 version_token.so
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
。。。。。。省略部分输出。。。。。。
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| group_replication | ACTIVE | GROUP REPLICATION| group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
#binlog 格式为ROW
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
#配置文件内容
server-id = 1#每个主机分别改为不同值
user=mysql
explicit_defaults_for_timestamp
socket=mysql1.sock
port = 3306
# binlog
log-bin=mysql-bin
binlog-format = ROW
log-slave-updates = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
binlog-checksum = NONE
slave-parallel-workers = 0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#GroupRepliation
gtid-mode = on
enforce_gtid_consistency = on
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ef6efed8-e084-11e6-b61e-005056ba4b95"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.100.41:3308"#每个主机分别改为不同值，为本机IP
loose-group_replication_group_seeds= "192.168.100.41:3308,192.168.100.42:3308,192.168.100.43:3308"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=FALSE#multi master mode
loose-group_replication_enforce_update_everywhere_checks= TRUE
#添加第一个节点
mysql> CHANGE MASTER TO MASTER_USER='rpluser', MASTER_PASSWORD='rplpass' FOR CHANNEL 'group_replication_recovery';
mysql> set global group_replication_bootstrap_group=on;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=off;
#group_replication_bootstrap_group参数设置的原因请见官方解释：
group_replication_bootstrap_group
Description: Specify if this member will bootstrap the group. This option must only be set in one server and only once starting the group for the first time or restarting the entire group. After the group has been bootstrapped, the user is advised to set this option to OFF. It should be set to OFF both dynamically and in the configuration files. Starting two servers or restarting one server with this option set while the group is running may lead to an artificial split brain situation, where two independent groups with the same name are bootstrapped.
Type: Boolean
Accepted Input: ON, OFF
Default: OFF
Dynamic: Yes
Scope: Global
mysql> select * from replication_group_members ;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | ef6efed8-e084-11e6-b61e-005056ba4b95 | master.andylhz.com | 3306 | ONLINE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
#添加第二个节点
mysql> CHANGE MASTER TO MASTER_USER='rpluser', MASTER_PASSWORD='rplpass' FOR CHANNEL 'group_replication_recovery';
mysql> start group_replication;
mysql> select * from replication_group_members ;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | 8bae3e81-e087-11e6-8b92-005056ab8dd9 | client.andylhz.com | 3306 | ONLINE |
| group_replication_applier | ef6efed8-e084-11e6-b61e-005056ba4b95 | master.andylhz.com | 3306 | ONLINE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
#添加第三个节点
mysql> CHANGE MASTER TO MASTER_USER='rpluser', MASTER_PASSWORD='rplpass' FOR CHANNEL 'group_replication_recovery';
mysql> start group_replication;
mysql> select * from replication_group_members ;
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| group_replication_applier | 8bae3e81-e087-11e6-8b92-005056ab8dd9 | client.andylhz.com| 3306 | ONLINE |
| group_replication_applier | ef6efed8-e084-11e6-b61e-005056ba4b95 | master.andylhz.com| 3306 | ONLINE |
| group_replication_applier | fd6dfd5a-e089-11e6-bc06-005056abc6d3 | client1.andylhz.com | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
多主模式：
随意停止一台，不影响剩下的主机之间的复制
重新加入集群只要 startgroup_replication 即可
任意节点可写
单主模式：
主节点挂掉后，配置文件中第二个配置的IP的主机为主
只有主节点可写，其余节点只读且超级用户也只读 mysql> create table abcselect * from mysql.user;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
只剩单独一个节点自动变为读写
页:
[1]