分布式集群（MySQL）

最新推荐文章于 2024-02-19 11:25:13 发布
最新推荐文章于 2024-02-19 11:25:13 发布
阅读量1.3k 收藏 2
点赞数
分类专栏： mysql 文章标签： mysql 分布式
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/deep_AELY/article/details/76794592
版权

一、保证MySQL环境的纯净
删除MySQL的目录里面的所有内容：（即/var/lib/mysql）
[root@server2 ~]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@server2 ~]# cd /var/lib/mysql

[root@server2 mysql]# ls
auto.cnf         ibdata1           mysql-bin.000003  mysql-bin.000009    private_key.pem   test
ca-key.pem       ib_logfile0       mysql-bin.000004  mysql-bin.000010    public_key.pem
ca.pem           ib_logfile1       mysql-bin.000005  mysql-bin.000011    server2-slow.log
client-cert.pem  mysql             mysql-bin.000006  mysql-bin.000012    server-cert.pem
client-key.pem   mysql-bin.000001  mysql-bin.000007  mysql-bin.index     server-key.pem
ib_buffer_pool   mysql-bin.000002  mysql-bin.000008  performance_schema  sys
[root@server2 mysql]# rm -fr *
[root@server2 mysql]# ls

二、更改MySQL配置

[root@server2 mysql]# vim /etc/my.cnf

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="5fc57337-7a83-11e7-8b68-525400848d33"
#loose-group_replication_start_on_boot=on
#loose-group_replication_local_address="172.25.39.2:24901"
#loose-group_replication_group_seeds="172.25.39.2:24901,172.25.39.3:24902,172.25.39.4:24903"
#loose-group_replication_bootstrap_group=off
#loose-group_replication_single_primary_mode=FALSE
#loose-group_replication_enforce_update_everywhere_check=TRUE
#group_replication_ip_whitelist='172.25.39.0/24,127.0.0.1/8'
#group_replication_allow_local_disjoint_gtids_join=on

三、MySQL的重启与应用

1）更改密码登录mysql，修改my.cnf文件
[root@server2 mysql]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]

[root@server2 mysql]# grep password /var/log/mysqld.log 
2017-08-06T08:37:17.141549Z 1 [Note] A temporary password is generated for root@localhost: sje?EMetK8=%
[root@server2 mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.17-log

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> alter user root@localhost identified by 'Westos+007';---更改密码
Query OK, 0 rows affected (0.00 sec)

mysql> select uuid();----随机获取uuid
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 5fc57337-7a83-11e7-8b68-525400848d33 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

2）获取uuid后更改配置，重启
[root@server2 mysql]# vim /etc/my.cnf

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="5fc57337-7a83-11e7-8b68-525400848d33"
loose-group_replication_start_on_boot=on（开启开机自启动）
loose-group_replication_local_address="172.25.39.2:24901"
loose-group_replication_group_seeds="172.25.39.2:24901,172.25.39.3:24902,172.25.39.4:24903"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_check=TRUE

[root@server2 mysql]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]

[root@server2 mysql]# mysql -pWestos+007
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
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 mysql@"%" identified by 'Westos+007';----------授权用户
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user='mysql',master_password="Westos+007" for channel 'group_replication_recovery';----说明组的用户密码
Query OK, 0 rows affected, 2 warnings (1.54 sec)

mysql> install plugin group_replication SONAME 'group_replication.so';--安装组插件
Query OK, 0 rows affected (0.47 sec)

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     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | 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> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set global group_replication_ip_whitelist='172.25.39.0/24,127.0.0.1/8';组的成员网段
Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;
Query OK, 0 rows affected (2.22 sec)

mysql> select * from performance_schema.replication_group_members;查看组成员
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 740789c9-7a82-11e7-8160-525400848d33 | server2     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.06 sec)

mysql> use test;
Database changed
mysql> create table t1 (c1 int primary key,c2 text not null);
Query OK, 0 rows affected (1.39 sec)

mysql> insert into t1 values (1,'lusi');
Query OK, 1 row affected (0.50 sec)

mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | lusi |
+----+------+
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    |         2 |         123 | Server ver: 5.7.17-log, Binlog ver: 4 |
| binlog.000001 | 123 | Previous_gtids |         2 |         150 |                                       |
| binlog.000001 | 150 | Stop           |         2 |         169 |                                       |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
3 rows in set (0.00 sec)

mysql> exit
Bye

四、在其他两个节点配置相同如下：

[root@server3 ~]# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]
[root@server3 ~]# cd /var/lib/mysql
[root@server3 mysql]# ls
auto.cnf         ibdata1           mysql-bin.000003    public_key.pem            sys
ca-key.pem       ib_logfile0       mysql-bin.000004    server3-relay-bin.000001  test
ca.pem           ib_logfile1       mysql-bin.000005    server3-relay-bin.000002
client-cert.pem  mysql             mysql-bin.index     server3-relay-bin.index
client-key.pem   mysql-bin.000001  performance_schema  server-cert.pem
ib_buffer_pool   mysql-bin.000002  private_key.pem     server-key.pem
[root@server3 mysql]# rm -fr *


[root@server3 mysql]# vim /etc/my.cnf

server-id=3
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="5fc57337-7a83-11e7-8b68-525400848d33"
loose-group_replication_start_on_boot=on
loose-group_replication_local_address="172.25.39.3:24901"
loose-group_replication_group_seeds="172.25.39.2:24901,172.25.39.3:24902,172.25.39.4:24903"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_check=TRUE
group_replication_ip_whitelist='172.25.39.0/24,127.0.0.1/8'
group_replication_allow_local_disjoint_gtids_join=on

[root@server3 mysql]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
[root@server3 mysql]# grep password /var/lo
local/ lock/ log/
[root@server3 mysql]# grep password /var/log/mysqld.log
2017-08-06T09:11:06.930834Z 1 [Note] A temporary password is generated for root@localhost: !yU)d9&Lx/Sl

[root@server3 mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-log

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> alter user root@localhost identified by 'Westos+007';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on  *.* to mysql@"%" identified by 'Westos+007';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user='mysql',master_password="Westos+007" for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (1.38 sec)

mysql> install plugin group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.19 sec)

mysql> set global group_replication_ip_whitelist='172.25.39.0/24,127.0.0.1/8';
Query OK, 0 rows affected (0.00 sec)


mysql> set global group_replication_allow_local_disjoint_gtids_join=on;
Query OK, 0 rows affected (0.00 sec)

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     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | 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> uninstall plugin validate_password;
Query OK, 0 rows affected (0.05 sec)

mysql> start group_replication;
Query OK, 0 rows affected (2.90 sec)
[root@server4 mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-log

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 mysql@"%" identified by 'Westos+007';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user root@localhost identified by 'Westos+007';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on  *.* to mysql@"%" identified by 'Westos+007';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user='mysql',master_password="Westos+007" for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.38 sec)

mysql> install plugin group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.46 sec)

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     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | 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.01 sec)

mysql>  set global group_replication_ip_whitelist='172.25.39.0/24,127.0.0.1/8';
Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;
ERROR 3096 (HY000): The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer.
mysql> set global group_replication_allow_local_disjoint_gtids_join=on;
Query OK, 0 rows affected (0.00 sec)

mysql> stop group_replication;
Query OK, 0 rows affected (11.63 sec)

mysql> uninstall plugin validate_password;
Query OK, 0 rows affected (0.05 sec)

mysql> start group_replication;
Query OK, 0 rows affected (2.90 sec)

mysql> exit
Bye

当所有配置全部做好，那么在server2上显示便是这样的了：

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2de90de6-7a87-11e7-ab7d-525400b17b31 | server3     |        3306 | ONLINE       |
| group_replication_applier | 54890852-7a87-11e7-ab58-52540054927a | server4     |        3306 | ONLINE       |
| group_replication_applier | 740789c9-7a82-11e7-8160-525400848d33 | server2     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

这个时候就可以去测试啦~
无论在哪个主机上写，都可以同时同步到其他主机……

deep_AELY
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL分布式集群部署
12-05
为5台机器全部安装64位centos6.4并配置好网络： 数据库Master主服务器（两台）：10.18.12.152，10.18.12.153 数据库Slave从服务器（两台，即sql及存储节点）：10.18.12.154，...MySQL-Proxy调度服务器：10.18.12.157
MySQL MGR 某个节点宕机之后开启组复制
小楼一夜听春雨，深巷明朝卖杏花
05-31 1275
环境如下 主机名 IP 系统 数据库版本 db01 192.168.179.102 CentOS7.4 5.7.30 db02 192.168.179.103 CentOS7.4 5.7.30 db03 192.168.1
Mysql 5.7 基于组复制(MySQL Group Replication) - 运维小结
weixin_30826095的博客
01-12 410
之前介绍了Mysq主从同步的异步复制(默认模式)、半同步复制、基于GTID复制、基于组提交和并行复制 (解决同步延迟)，下面简单说下Mysql基于组复制(MySQL Group Replication) 操作。 一、组复制 (MGR)介绍 MySQL Group Replication（简称MGR）是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。组复...
MGR报错 The START GROUP_REPLICATION command failed as the applier module failed to start.
最新发布
wangleshisei的博客
02-19 255
2、解决该问题的方法是执行reset master就行，然后重新在主节点和从节点开启通道。注意reset master 生产环境不要使用,这是集群初始搭建重置binlog。1、原因环境有脏数据，或者环境多此配置mgr失败。或者环境有搭建常规主从同步的脏数据（需要取消掉主从关系 reset slave all）。
mysql group replication的安装搭建
koby丶T的博客
11-22 3378
2016年12月mysql发布了5.7.17版本开始支持了group replication首先来看先传统复制和半同步复制和组复制的原理比较图 比较图 1.mysql传统复制是异步复制 2.mysql的半同步复制 3.mysql的组复制 部署组复制 看完原理我们就部署组复制，要安装和使用组复制必须要配置正确的mysqlse...
MySQL分区分库分表和分布式集群
浮尘笔记
04-29 937
数据量大就分表：减少每次查询的数据总量；并发高就分库：把并发请求分散到多个实例中去；分库分表的原理是通过一些 HASH算法 或者工具实现将一张数据表垂直或者水平进行物理切分。
centos7搭建mysql多主组复制(MGR)
村长在路上
07-31 651
配置主机名与ip映射 192.168.1.130 mgr1 192.168.1.210 mgr2 192.168.1.211 mgr3 三台上面的配置文件 my.cnf ##mgr1的my.cnf配置文件 [mysqld] user=mysql datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links = 0 #-------Public setup---------# max_connect_...
Mysql（二）Mysql的组复制
qq_39376481的博客
05-23 1607
Mysql的组复制 (一）在server1中配置和测试 配置： 1.在server1和server2中将mysqld关掉，并删除之前的数据文件，保持实验环境纯洁（在删除前查看uuid，此时查看uuid的原因是因为旧的uuid不会产生影响） 在server1上的操作： [root@server1 mysql]# systemctl stop mysqld [root@server1 mysql]# ...
mysql-group-replication 测试环境的搭建与排错
weixin_34306446的博客
03-30 197
mysql-group-replication 是由mysql-5.7.17这个版本提供的强一致的高可用集群解决方案   1、环境规划 主机ip 主机名 172.16.192.201 balm001 172.16.192.202 balm002 172.16.192.20...
MySQL大型分布式集群
01-21
MySQL大型分布式集群
MySQL大型分布式集群完整视频教程
08-23
MySQL大型分布式集群 MHA MMM 读写分离 集群 分片切片
mysql分布式集群架构视频
04-28
mysql分布式集群架构视频,包含linux上安装mysql，主从复制、主主复制、负载均衡、haproxy监控、高可用、keepalived、分库分表、数据库中间件-mycat、以及mycat的各种使用，自己学的这个视频，60多集，挺不错的
MySQL大型分布式集群(一)
fjping2008的博客
08-17 383
MySQL大型分布式集群解决针对大型网站架构中持久化部分，大量数据存储以及高并发访问所带来是数据读写问题。
MySQL-分布式架构-MyCAT
wwj0407的博客
08-22 419
1、MyCAT基础架构准备 MyCAT基础架构图 摘自oldguo 1.1 MyCAT基础架构准备 1.1.1 环境准备： 两台虚拟机 db01（10.0.0.51） db02（10.0.0.52） 每台创建四个mysql实例：3307 3308 3309 3310 1.1.2 删除历史环境： pkill mysqld rm -rf /data/330* mv /e...
Mysql分布式集群部署---MySQL集群Cluster将数据分成多个片段，每个片段存储在不同的服务器上
qq_759035366的博客
12-04 1485
在MySQL实现集群Cluster架构中，存储节点使用的存储引擎为NDB引擎。NDB引擎是一种分布式的基于内存的引擎，因此作为MySQL集群Cluster架构中的存储节点，内存一定要足够大。使用NDB引擎有以下优点：1、基于内存存储，没有磁盘I/O瓶颈，速度快。2、扩展性好，增加节点即可实现数据库集群Cluster的扩展。3、冗余性好，单个节点故障，集群还可以提供服务。同时，NDB引擎也有以下缺点：1、由于存储基于内存，因此在设备断电后数据即丢失。2、存储空间大小受内存大小限制。
Mysql分布式集群
youaremyalllove的博客
02-28 859
主从复制： 这个过程会产生时延： 网络延迟：可忽略 master多个线程在写binlog，但slave只有一个sql线程在重放 即使只有一个master线程在写，我们知道日志是顺序读写的，但relay log日志重放的过程中，是随机写的，也就是sql线程是个随机读写线程 =》解决方法：MTS（enhanced multi-threaded slave） MTS: 但也不能太多，如果突然断点，数据就全没了。 实现方式是在binlog event中增加必要的信息，
MySQL Group Replication 搭建
qq_31144297的博客
07-18 198
1.搭建环境： OS：Linux.el6uek.x86_64 MySQL：5.7.26 2.模式： 1 主 2 从 192.168.65.2 test2 192.168.65.3 test3 192.168.65.4 test4 3.必要参数： gtid_mode=on enforce-gtid-consistency=on binlog_gtid_simple_re...
Mysql学习-第四章（分布式架构篇）
tangshuai96的博客
07-23 1402
分布式数据库概念 分布式数据库是一种把数据分散存储在不同物理位置的数据库。 计算层：单机数据库中的sql层，用来对数据访问进行权限检查、路由访问，以及对计算结果的操作。 元数据层：记录了分布式数据库集群下有多少个存储节点，对应ip、端口等元数据信息是多少。当分布式数据库的计算层启动时，会先访问元数据层，获取所有集群信息，才能正确进行 SQL 的解析和路由等工作。另外，因为元数据信息存放在元数据层，那么分布式数据库的计算层可以有多个，用于实现性能的扩展。 存储层：用来存放数据，但存储层要和计算层在同一台服务
mysql分布式集群搭建
05-17
MySQL 分布式集群一般采用主从复制或者多主复制的方式进行。下面我来介绍一下具体的步骤： 1. 安装 MySQL 软件 在每个节点上安装 MySQL 软件，可以选择官网下载二进制安装包或者使用 yum/apt-get/aptitude 等软件包管理器进行安装。 2. 配置 MySQL 主从复制或多主复制 - 主从复制：将一个 MySQL 节点作为主节点，其他节点作为从节点。主节点上的数据修改会被同步到从节点上。 - 多主复制：每个节点都是主节点和从节点。当一个节点上的数据修改时，会同步到其他节点上。 具体配置步骤可以参考 MySQL 官方文档。 3. 同步数据 在主从复制中，需要将主节点上的数据同步到从节点上。可以使用 mysqldump 工具备份主节点上的数据，然后在从节点上使用 mysql 命令进行导入。 在多主复制中，每个节点都需要同步其他节点上的数据。可以使用 Galera Cluster、Percona XtraDB Cluster 等工具进行同步。 4. 配置负载均衡 当有多个 MySQL 节点时，可以使用负载均衡器进行流量分发，以提高集群的性能和可靠性。常用的负载均衡器有 HAProxy、Nginx 等。 5. 测试集群 在集群搭建完成后，需要进行测试以确保集群的正常运行。可以通过在不同节点上进行数据操作、故障模拟等方式进行测试。 以上是 MySQL 分布式集群的大致步骤，具体操作还需要根据实际情况进行调整。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值