数据库的分布式集群

 前期准备

（三台虚拟中的操作一致，都需要初始化数据库！！）

为了是实验环境的纯净，我重新初始化了数据库。这一步可以根据大家的意愿看是否选择初始化。同时需要要注意：在三台虚拟机上的操作是一致的，某些需要修改的东西我会直接标注出来。

server1 ：172.25.14.1

server3 ：172.25.14.3       # 注意在/etc/my.cnf中需要修改IP，不能直接将server1的复制过去

server4 ：172.25.14.4      # 注意在/etc/my.cnf中需要修改IP，不能直接将server1的复制过去

• 清空/data/mysql中的文件，重新进行初始化

[root@server1 mysql]# rm -fr /data/mysql/*
[root@server1 mysql]# mysqld --initialize --user=mysql
2020-08-19T06:03:10.954694Z 0 [System] [MY-013169] [Server] /usr/local/lamp/mysql/bin/mysqld (mysqld 8.0.21) initializing of server in progress as process 26138
2020-08-19T06:03:10.962334Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-08-19T06:03:11.716212Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-08-19T06:03:13.373434Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 5*K>wcOsYQFL

 

• 编辑/etc/my.cnf

server3 和 server4在此处略有不同，group_replication_local_address="172.25.14.1:33061"要换成172.25.14.3:33061和172.25.14.4:33061

[mysqld]
basedir=/usr/local/lamp/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
#skip-grant-tables
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
default_authentication_plugin=mysql_native_password
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.14.1:33061"
group_replication_group_seeds="172.25.14.1:33061,172.25.14.3:33061,172.25.14.4:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.14.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
#
##
## include all files from the config directory
##
!includedir /etc/my.cnf.d

• 重启mysql

[root@server1 mysql]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/server1.err'.
. SUCCESS! 

server1中的操作

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

Copyright (c) 2000, 2020, 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> 
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 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='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

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.16 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 | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | aa3a349b-e1e1-11ea-9409-525400b49d93 | server1     |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

server3中的操作

• /etc/my.cnf中的内容

[mysqld]
basedir=/usr/local/lamp/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
#skip-grant-tables
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
default_authentication_plugin=mysql_native_password
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.14.3:33061"
group_replication_group_seeds="172.25.14.1:33061,172.25.14.3:33061,172.25.14.4:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.14.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

 

•  mysql -p

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

Copyright (c) 2000, 2020, 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';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (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='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.37 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8dbe9962-e1e7-11ea-b72f-525400f408ff | server3     |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
| group_replication_applier | aa3a349b-e1e1-11ea-9409-525400b49d93 | server1     |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.01 sec)

server4中的操作

• /etc/my.cnf中的内容

[mysqld]
basedir=/usr/local/lamp/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
#skip-grant-tables
server-id=3
gtid_mode=ON
enforce-gtid-consistency=ON

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
default_authentication_plugin=mysql_native_password
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.14.4:33061"
group_replication_group_seeds="172.25.14.1:33061,172.25.14.3:33061,172.25.14.4:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.14.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON

•   mysql -p

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

Copyright (c) 2000, 2020, 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';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 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='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql>  START GROUP_REPLICATION;
Query OK, 0 rows affected (3.62 sec)

mysql>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 5a7abaf5-e1f7-11ea-a92d-5254009c4d03 | server4     |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
| group_replication_applier | 8dbe9962-e1e7-11ea-b72f-525400f408ff | server3     |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
| group_replication_applier | aa3a349b-e1e1-11ea-9409-525400b49d93 | server1     |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

检验

在三台虚拟机中分别插入如下内容，查看表时在三台虚拟机中都能进行查看。说明数据库的分布式集群用完了

server1：INSERT INTO t1 VALUES (1,'Lyqiu');

server3：INSERT INTO t1 VALUES (2,'XuYuan');

server4：INSERT INTO t1 VALUES (3,'WESTOS');