Mysql组复制-简单集群搭建
主机分配:
主机名 | IP地址 |
---|---|
MGR-node1 | 192.168.182.100 |
MOR-node2 | 192.168.182.101 |
MGR-node3 | 192.168.182.102 |
一 环境准备
编辑三台主机的hosts文件:重点:一定要注意设置好主机名以及hosts文件要不然mysql会找不到对应的机器
192.168.182.100 MGR-node1
192.168.182.101 MGR-node2
192.168.182.102 MGR-node3
系统版本查看
[aaa@qq.com ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
为了方便实验,关闭所有节点的防火墙
[aaa@qq.com ~]# systemctl stop firewalld
[aaa@qq.com ~]# firewall-cmd --state
not running
[aaa@qq.com ~]# cat /etc/sysconfig/selinux |grep "SELINUX=disabled"
SELINUX=disabled
[aaa@qq.com ~]# setenforce 0
setenforce: SELinux is disabled
[aaa@qq.com ~]# getenforce
Disabled
二 在三个节点上安装Mysql5.7
下载mysql官方源
[aaa@qq.com ~]# yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
安装MySQL 5.7
[aaa@qq.com ~]# yum install -y mysql-community-server
启动MySQL服务器和MySQL的自动启动
[aaa@qq.com ~]# systemctl start mysqld.service
[aaa@qq.com ~]# systemctl enable mysqld.service
核对三台主机的mysql版本
[aaa@qq.com ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using EditLine wrapper
查看初始密码并登录
[aaa@qq.com ~]# cat /var/log/mysqld.log|grep 'A temporary password'
2020-07-18T06:54:38.562194Z 1 [Note] A temporary password is generated for aaa@qq.com: <Ezzv8w+pX7,
使用上面查看的密码<Ezzv8w+pX7, 登录mysql,并重置密码为123456
[aaa@qq.com ~]# mysql -p'<Ezzv8w+pX7,'
进入数据库设置简单密码策略
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set password=password("123456");
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
三、MGR组复制环境部署 (多写模式)
这里有几个命令大家可能会用到,在部署环境命令错误时,需要初始化mysql。
# systemctl stop mysqld
# rm -rf /var/lib/mysql
# systemctl start mysqld
执行完,需要重新查看初始密码登入并修改密码
注意:3台节点的操作略有不同,不建议克隆操作
1) MGR-node1 配置
[aaa@qq.com ~]# cp /etc/my.cnf /etc/my.cnf.bak
[aaa@qq.com ~]# >/etc/my.cnf
[aaa@qq.com ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.182.100:24901"
loose-group_replication_group_seeds= "192.168.182.100:24901,192.168.182.101:24901,192.168.182.102:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="192.168.182.0/24,127.0.0.1/8"
注意下图的几点:
修改完成后保存
重启mysql服务
[aaa@qq.com ~]# systemctl restart mysqld
登录mysql进行相关设置操作
[aaa@qq.com ~]# mysql -p123456
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO aaa@qq.com'%' IDENTIFIED BY 'aaa@qq.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> reset master;
Query OK, 0 rows affected (0.19 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='aaa@qq.com' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.33 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
...............
...............
| 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> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.34 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 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 42ca8591-34bb-11e9-8296-005056ac6820 | MGR-node1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
最后一栏保证是ONLINE 才是正常的!
创建一个测试库
mysql> CREATE DATABASE xingming CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.03 sec)
mysql> use xingming;
Database changed
mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.24 sec)
mysql> insert into xingming.ziliao values(1,"maliu"),(2,"tangwu"),(3,"lisi"),(4,"zhangsan");
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from xingming.ziliao;
+----+----------+
| id | name |
+----+----------+
| 1 | maliu |
| 2 | tangwu |
| 3 | lisi |
| 4 | zhangsan |
+----+----------+
4 rows in set (0.00 sec)
2) MGR-node2 配置
[aaa@qq.com ~]# cp /etc/my.cnf /etc/my.cnf.bak
[aaa@qq.com ~]# >/etc/my.cnf
[aaa@qq.com ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.182.101:24901"
loose-group_replication_group_seeds= "192.168.182.100:24901,192.168.182.101:24901,192.168.182.102:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="192.168.182.0/24,127.0.0.1/8"
重启mysql服务
[aaa@qq.com ~]# systemctl restart mysqld
登录mysql进行相关设置操作
[aaa@qq.com ~]# mysql -p123456
.........
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO aaa@qq.com'%' IDENTIFIED BY 'aaa@qq.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.17 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='aaa@qq.com' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.21 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.20 sec)
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
.............
.............
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (6.25 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 8af18ff4-c8c3-11ea-bb9d-000c29e59105 | mgr-node1 | 3306 | ONLINE |
| group_replication_applier | b9bfc6e9-c8c7-11ea-b043-000c29c3f2a0 | mgr-node2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
查看下,发现已经将MGR-node1节点添加的数据同步过来了
mysql> use xingming
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from ziliao;
+----+----------+
| id | name |
+----+----------+
| 1 | maliu |
| 2 | tangwu |
| 3 | lisi |
| 4 | zhangsan |
+----+----------+
4 rows in set (0.01 sec)
3) MGR-node3 配置
[aaa@qq.com ~]# cp /etc/my.cnf /etc/my.cnf.bak
[aaa@qq.com ~]# >/etc/my.cnf
[aaa@qq.com ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 3
gtid_mode = on
enforce_gtid_consistency = on
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.182.102:24901"
loose-group_replication_group_seeds= "192.168.182.100:24901,192.168.182.101:24901,192.168.182.102:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="192.168.182.0/24,127.0.0.1/8"
重启mysql服务
[aaa@qq.com ~]# systemctl restart mysqld
登录mysql进行相关设置操作
[aaa@qq.com ~]# mysql -p123456
..........
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO aaa@qq.com'%' IDENTIFIED BY 'aaa@qq.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> reset master;
Query OK, 0 rows affected (0.10 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='aaa@qq.com' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.27 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
.............
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (4.54 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 8af18ff4-c8c3-11ea-bb9d-000c29e59105 | mgr-node1 | 3306 | ONLINE |
| group_replication_applier | 8af6f48d-c8c3-11ea-8105-000c2908dcc4 | mgr-node3 | 3306 | ONLINE |
| group_replication_applier | b9bfc6e9-c8c7-11ea-b043-000c29c3f2a0 | mgr-node2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
验证一下是否同步数据
mysql> use xingming
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from ziliao;
+----+----------+
| id | name |
+----+----------+
| 1 | maliu |
| 2 | tangwu |
| 3 | lisi |
| 4 | zhangsan |
+----+----------+
4 rows in set (0.00 sec)
只要组状态栏里都是online就说明是正常的
大功告成,可以测试一下是否一个节点写入,同时更新
node2节点操作
mysql> select * from ziliao;
+----+----------+
| id | name |
+----+----------+
| 1 | maliu |
| 2 | tangwu |
| 3 | lisi |
| 4 | zhangsan |
+----+----------+
4 rows in set (0.00 sec)
mysql> insert into xingming.ziliao values(11,"beijing"),(12,"shanghai"),(13,"anhui");
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
node1查看
mysql> select * from xingming.ziliao;
+----+----------+
| id | name |
+----+----------+
| 1 | maliu |
| 2 | tangwu |
| 3 | lisi |
| 4 | zhangsan |
| 11 | beijing |
| 12 | shanghai |
| 13 | anhui |
+----+----------+
7 rows in set (0.00 sec)
mysql> update xingming.ziliao set id=100 where name="anhui"; ##更新一个数据
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
在node3上验证
mysql> select * from ziliao;
+-----+----------+
| id | name |
+-----+----------+
| 1 | maliu |
| 2 | tangwu |
| 3 | lisi |
| 4 | zhangsan |
| 11 | beijing |
| 12 | shanghai |
| 100 | anhui |
+-----+----------+
7 rows in set (0.00 sec)
验证成功,说明mysql的gtid组复制多点写入搭建就成功了,
下面分享一些常见报错和解决方法
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 8af18ff4-c8c3-11ea-bb9d-000c29e59105 | mgr-node1 | 3306 | ONLINE |
| group_replication_applier | 8af6f48d-c8c3-11ea-8105-000c2908dcc4 | mgr-node3 | 3306 | ONLINE |
| group_replication_applier | b9bfc6e9-c8c7-11ea-b043-000c29c3f2a0 | mgr-node2 | 3306 | RECOVERING |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
原因:如果my.cnf没问题的话,那就是hosts文件和主机名了。
当配置了错误的组信息,也无法插入数据时,
可以使用如下命令重新配置组信息
mysql> stop group_replication; ##先停止组复制集群
mysql> reset master; ##初始化master
mysql> start group_replication; ##开始组复制集群
mysql> SELECT * FROM performance_schema.replication_group_members; ##查看组复制状态
-------------------------当你发现自己突然嫉妒起其他人时,就请安静下来学习吧!------------------------
上一篇: 前端网站实现图片放大功能