mysql replation_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安装)，不同的安装方式位置不同

[root@master plugin]# 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 root  7945238 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   |

+---------------+-------+

#配置文件内容

[mysqld]

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       |

+---------------------------+--------------------------------------+---------------------+-------------+--------------+

多主模式：

随意停止一台，不影响剩下的主机之间的复制

重新加入集群只要 start  group_replication 即可

任意节点可写

单主模式：

主节点挂掉后，配置文件中第二个配置的IP的主机为主

只有主节点可写，其余节点只读且超级用户也只读 mysql> create table abc  select * from mysql.user;

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

只剩单独一个节点自动变为读写