数据库的分布式集群

一、实验环境部署

三台服务器
server1:172.25.7.2  ##server1作为第一个节点操作
server2:172.25.7.3
server3:172.25.7.4
----------------------------------------------------------------------------
三台主机实验环境清空，重新初始化，
没有编译mysql的主机远程下载server1:/usr/local/mysql然后参考之前博客mysql数据库安装
----------------------------------------------------------------------------
/etc/ini.d/mysqld stop
rm -fr /data/mysql/*

二、配置第一个节点server1

 1.修改配置文件

清空环境后
<1>mysqld --initialize --user=mysql   ##数据库初始化
------------------------------------------------------------------
<2>[root@server1 ~]#vi /etc/my.cnf
[mysqld]
gtid_mode=ON
enforce-gtid-consistency=ON
basedir=/usr/local/mysql             ##mysql的绝对路径
datadir=/data/mysql                  ##初始化后目录路径
socket=/data/mysql/mysql.sock
#skip-grant-tables
server-id=1                          ##每台主机id不同

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.7.2:33061"    ##server1的ip
group_replication_group_seeds="172.25.7.2:33061,172.25.7.3:33061,172.25.7.4:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.7.0/24,127.0.0.1/8" ##组设置网段以及本机
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
------------------------------------------------------------------------------
<3>开启服务
[root@server1 ~]# /etc/init.d/mysqld start

2.登陆数据库配置server1节点

mysql> SET SQL_LOG_BIN=0;                               ##关闭二进制日志
Query OK, 0 rows affected (0.01 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.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 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> 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.15 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;

三、配置server2和server3节点

  1.修改配置文件

清空环境后
<1>mysqld --initialize --user=mysql   ##数据库初始化
------------------------------------------------------------------
<2>[root@server2 ~]#vi /etc/my.cnf
[mysqld]
gtid_mode=ON
enforce-gtid-consistency=ON
basedir=/usr/local/mysql             ##mysql的绝对路径
datadir=/data/mysql                  ##初始化后目录路径
socket=/data/mysql/mysql.sock
#skip-grant-tables
server-id=2                          ##每台主机id不同,server2是2，server3是-id=3

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.7.3:33061"    ##server2的ip
                                      ##在server3中修改ip为172.25.7.4:33061
group_replication_group_seeds="172.25.7.2:33061,172.25.7.3:33061,172.25.7.4:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.7.0/24,127.0.0.1/8" ##组设置网段以及本机
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
------------------------------------------------------------------------------
<3>开启服务
[root@server2 ~]# /etc/init.d/mysqld start

2.登陆数据库配置server2和server3节点

登陆成功server2和server3操作相同
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.01 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.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 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> START GROUP_REPLICATION;              ##与server1不同的是不操作此命令前后
Query OK, 0 rows affected (3.05 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
##操作成功MEMBER_STATE显示是ONLINE 

四、测试建立库三台主机共享

在server1中建立Horizon,并且插入1，carry
mysql> CREATE DATABASE Horizon;
Query OK, 1 row affected (0.02 sec)

mysql> USE Horizon;
Database changed

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t1 VALUES (1, 'carry');
Query OK, 1 row affected (0.04 sec)
-------------------------------------------------------------------
在server2中给Horizon插入2，ley
mysql> INSERT INTO t1 VALUES (1, 'carry');
-------------------------------------------------------------------
在server3中查看Horizon
mysql> USE Horizon;
mysql> SELECT * FROM Horizon.t1;
+----+-------+
| c1 | c2    |
+----+-------+
|  1 | carry |
|  2 | ley   |
+----+-------+
2 rows in set (0.00 sec)     ##在server3里面可以看见server1和server2的操作