mysql的分布式集群

一、实验环境

server_lue2:172.25.6.4
server_lue3:172.25.6.5  做第一个节点操作
server_lue4:172.25.6.6
首先，三台主机实验环境清空，重新初始化，
/etc/init.d/mysqld stop
cd /data/mysql   
rm -rf *
没有编译mysql的主机通过lue3主机远程传输/usr/local/mysql然后参考之前博客mysql数据库安装

 

二、lue3做第一个节点

• 修改配置文件

1.初始化  mysql --initialize --user=mysql
2.修改配置文件
vim /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
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.6.5:33061"
group_replication_group_seeds="172.25.6.5:33061,172.25.6.4:33061,172.25.6.6:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.6.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
3./etc/init.d/mysqld start

•  lue3登陆数据库配置

[root@lue3 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.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.04 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 (22.24 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 | 347f4abe-e1e4-11ea-8ef9-525400049682 | lue3.westos.com |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

 

三、lue2和lue4的配置

• 修改配置文件

1.初始化： mysqld --initialize --user=mysql
2.#lue2的my.cnf文件的配置
vim /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
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.6.4:33061"
group_replication_group_seeds="172.25.6.5:33061,172.25.6.4:33061,172.25.6.6:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.6.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
#退出保存
3.开启mysql
[root@lue2 mysql]# /etc/init.d/mysqld start

1.初始化：mysqld --initialize --user=mysql
2.#lue4的my.cnf的配置文件

vim /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
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.6.5:33061"
group_replication_group_seeds="172.25.6.5:33061,172.25.6.4:33061,172.25.6.6:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.6.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
#退出保存
3.开启
[root@lue4 support-files]#/etc/init.d/mysqld start

• 登陆lue2和lue4的数据库配置节点

lue2的节点配置 lue4配置与lue2的节点配置相同
[root@lue2 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.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.02 sec)

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