一、实验环境部署
三台服务器 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的操作