mysql的全同步复制

| GPL     |

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

46 rows in set (0.00 sec)

5.开启全同步复制，创建一个表用于同步检测

 

mysql>  SET GLOBAL group_replication_bootstrap_group=ON;

Query OK, 0 rows affected (0.00 sec)

 

mysql> START GROUP_REPLICATION;

Query OK, 0 rows affected (2.80 sec)

 

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SELECT * FROM performance_schema.replication_group_members;

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

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

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

| group_replication_applier | c94d76cc-d80a-11e8-8c01-5254002ac7eb | server1     |        3306 | ONLINE       |

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

1 row in set (0.00 sec)

 

mysql> CREATE DATABASE test;

Query OK, 1 row affected (0.05 sec)

 

mysql> use test

Database changed

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);

Query OK, 0 rows affected (0.81 sec)

 

mysql> INSERT INTO t1 VALUES (1, ‘Luis‘);

Query OK, 1 row affected (0.14 sec)

 

mysql> select * from t1;

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

| c1 | c2   |

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

|  1 | Luis |

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

1 row in set (0.00 sec)

6.server2上做类似步骤

 

/etc/my.cnf

 

server_id=2

 

loose-group_replication_local_address= "172.25.254.2:24901"

 

/etc/my.cnf

 

server_id=3

 

loose-group_replication_local_address= "172.25.254.3:24901"

 

[root@server2 mysql]# mysql -u root -p123123

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 5.7.17-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

 

mysql> SET SQL_LOG_BIN=0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> GRANT REPLICATION SLAVE ON *.* TO myslave@‘%‘ IDENTIFIED BY ‘123123‘;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

 

mysql> reset master;

Query OK, 0 rows affected (0.45 sec)

 

mysql> SET SQL_LOG_BIN=1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> CHANGE MASTER TO MASTER_USER=‘myslave‘, MASTER_PASSWORD=‘123123‘ FOR CHANNEL ‘group_replication_recovery‘;

Query OK, 0 rows affected, 2 warnings (1.12 sec)

 

mysql> INSTALL PLUGIN group_replication SONAME ‘group_replication.so‘;

Query OK, 0 rows affected (0.23 sec)

 

mysql> SHOW PLUGINS;

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

| Name                       | Status   | Type               | Library              | License |

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

| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |

| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |

| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |

| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |

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

46 rows in set (0.00 sec)

 

mysql> START GROUP_REPLICATION;

 

Query OK, 0 rows affected (6.98 sec)

 

mysql> SELECT * FROM performance_schema.replication_group_members;

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

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

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

| group_replication_applier | c352b7e2-d80e-11e8-a945-525400de467d | server2     |        3306 | RECOVERING   |

| group_replication_applier | c94d76cc-d80a-11e8-8c01-5254002ac7eb | server1     |        3306 | ONLINE       |

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

2 rows in set (0.00 sec)

 

mysql> +---------------------------+--------------------------------------+-

 

server3:

 

[root@server3 mysql]# mysql -uroot -p123123

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 5.7.17-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

 

mysql> SET SQL_LOG_BIN=0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> GRANT REPLICATION SLAVE ON *.* TO myslave@‘%‘ IDENTIFIED BY ‘123123‘;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

 

mysql> reset master;

Query OK, 0 rows affected (0.46 sec)

 

mysql> SET SQL_LOG_BIN=1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> CHANGE MASTER TO MASTER_USER=‘myslave‘, MASTER_PASSWORD=‘123123‘ FOR CHANNEL ‘group_replication_recovery‘;

Query OK, 0 rows affected, 2 warnings (0.97 sec)

 

mysql> INSTALL PLUGIN group_replication SONAME ‘group_replication.so‘;

Query OK, 0 rows affected (0.20 sec)

 

mysql> Query OK, 0 rows affected (0.59 sec)

    -> ^C

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     |

| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |

| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |

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

46 rows in set (0.00 sec)

 

mysql>  START GROUP_REPLICATION;

Query OK, 0 rows affected (3.94 sec)

 

mysql> SELECT * FROM performance_schema.replication_group_members;

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

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

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

| group_replication_applier | 4c0ae162-d80f-11e8-8946-525400e512c6 | server3     |        3306 | ONLINE       |

| group_replication_applier | c352b7e2-d80e-11e8-a945-525400de467d | server2     |        3306 | ONLINE       |

| group_replication_applier | c94d76cc-d80a-11e8-8c01-5254002ac7eb | server1     |        3306 | ONLINE       |

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

3 rows in set (0.00 sec)

 

mysql>

7.测试

mysql的全同步复制

标签：active   选项   ali   pre   名称   ESS   lib   脑裂   防止