MySQL集群搭建

最新推荐文章于 2023-06-16 08:34:48 发布

千吾

最新推荐文章于 2023-06-16 08:34:48 发布

阅读量897

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/yuanpan1987/article/details/103765688

版权

mysql 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

准备CentOS环境

准备3台机器，操作系统是CentOS7

如果使用VirtualBox的端口转发SSH连接虚拟机内的CentOS系统有问题可以参考文末Trouble Shooting CentOS7的SSH很慢的问题

更新系统（非必要）

查看系统信息：

[root@localhost ~]# uname -a
Linux localhost.localdomain 3.10.0-1062.el7.x86_64 #1 SMP Wed Aug 7 18:08:02 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

查看内核版本：

[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.7.1908 (Core)
[root@localhost ~]#

先更新下系统

yum update

更新后再查看下，看是否有不一样。

配置静态IP

三台机器的名称和ip如下：

机器名称	ip地址
vm101	192.168.0.101
vm102	192.168.0.102
vm103	192.168.0.103

CentOS7设置静态ip地址参考文末Trouble Shooting CentOS7配置静态ip地址

配置机器名称

在第1台机器上执行：
hostnamectl set-hostname vm101

在第2台机器上执行：
hostnamectl set-hostname vm102

在第3台机器上执行：
hostnamectl set-hostname vm103

配置HOSTS文件

修改每台机器的hosts
vi /etc/hosts
在文件末尾添加：

192.168.0.101   vm101
192.168.0.102   vm102
192.168.0.103   vm103

参考文章：
mysql-innodb-cluster-production-hostname

安装MySQL

下载安装文件

到这个地址 https://dev.mysql.com/downloads/repo/yum/ 下载rpm文件到centos中，或者将下载文件的文件的连接地址copy出来，然后在centos中通过wget命令来下载。

如果没有安装wget命令，则通过如下命令进行安装：

yum install wget

通过wget下载mysql的rpm安装包

wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

Adding the MySQL Yum Repository

yum localinstall mysql80-community-release-el7-3.noarch.rpm

Installing MySQL

yum install mysql-community-server

Starting the MySQL Server

service mysqld start

查看MySQL安装后的临时root密码

grep 'temporary password' /var/log/mysqld.log

登录MySQL

mysql -uroot -p

修改root的密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '1234';

这里遇到了一个错误：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 是因为密码1234太过于简单了。那就先设置一个复杂的密码。然后修改MySQL的密码要求，然后再改个简单的密码。

更改root可以从外网访问

查看mysql库的user表

mysql> use mysql
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 host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

执行如下修改语句，让root用户可以从外网访问
update user set host = '%' where user = 'root';

重启MySQL
systemctl restart mysqld

配置防火墙

firewall-cmd --state
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

以上步骤，在三台机器上都要执行。

参考文章：
Installing MySQL on Linux Using the MySQL Yum Repository

部署MySQL集群

安装MySQL Shell

在第一台机器vm101（ip为：192.168.0.101）上安装MySQL Shell
yum install mysql-shell

安装完之后，在命令行运行mysqlsh就进入到了MySQL Shell的操作界面

参考文章：
Installing MySQL Shell on Linux

创建集群的internal用户

在每台机器的MySQL中创建用户：

create user 'ic'@'%' identified by '1234';
grant all on *.* to 'ic'@'%' with grant option;

参考文章：
User Accounts Created by InnoDB Cluster

配置实例

在vm101的MySQL Shell上执行：

dba.configureInstance('ic@vm101:3306',{clusterAdmin:"'icadmin'@'%'", clusterAdminPassword:'1234'});

会提示输入ic的密码，根据提示操作，然后会出现如下的提示：

+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable                 | Current Value | Required Value | Note                                             |
+--------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum          | CRC32         | NONE           | Update the server variable                       |
| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |
| server_id                | 1             | <unique ID>    | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]:y
Do you want to restart the instance after configuring it? [y/n]: y

根据提示，有4个配置不符合配置集群的要求，需要修改。接下来修改MySQL的配置文件
vi /etc/my.cnf
在mysqld的配置项下增加如下配置：

enforce_gtid_consistency=ON
gtid_mode=ON
server_id=1

server_id的值只能是数字

同样，我们在vm102，和vm103上进行类似的修改
vm102上的/etc/my.cnf

enforce_gtid_consistency=ON
gtid_mode=ON
server_id=2

vm103上的/etc/my.cnf

enforce_gtid_consistency=ON
gtid_mode=ON
server_id=3

修改完之后重启MySQL
systemctl restart mysqld.service

再回到vm101上的MySQL Shell命令行中执行:

dba.configureInstance('ic@vm101:3306');

这次的命令中没有带{clusterAdmin:"'icadmin'@'%'", clusterAdminPassword:'1234'}，因为上一次执行命令的时候已经创建了icadmin用户了

执行后出现如下则提示本实例已经准备好做集群配置了

 MySQL  JS > dba.configureInstance('ic@vm101:3306');
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as vm101:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

The instance 'vm101:3306' is valid for InnoDB cluster usage.
The instance 'vm101:3306' is already ready for InnoDB cluster usage.
 MySQL  JS >

接下来，继续再vm101的MySQL Shell中执行：

dba.configureInstance('ic@vm102:3306',{clusterAdmin:"'icadmin'@'%'", clusterAdminPassword:'1234'});

出现提示如下：

 MySQL  JS > dba.configureInstance('ic@vm102:3306',{clusterAdmin:"'icadmin'@'%'", clusterAdminPassword:'1234'});
Please provide the password for 'ic@vm102:3306': ****
Save password for 'ic@vm102:3306'? [Y]es/[N]o/Ne[v]er (default No): y
Configuring MySQL instance at vm102:3306 for use in an InnoDB cluster...

This instance reports its own address as vm102:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

NOTE: Some configuration options need to be fixed:
+-----------------+---------------+----------------+----------------------------+
| Variable        | Current Value | Required Value | Note                       |
+-----------------+---------------+----------------+----------------------------+
| binlog_checksum | CRC32         | NONE           | Update the server variable |
+-----------------+---------------+----------------+----------------------------+

Do you want to perform the required configuration changes? [y/n]: y

Cluster admin user 'icadmin'@'%' created.
Configuring instance...
The instance 'vm102:3306' was configured for InnoDB cluster usage.
 MySQL  JS >

提示中指出有一项不符合配置，并提示你是否进行更改，输入y，同意进行更改。然后再输入命令:

dba.configureInstance('ic@vm102:3306');

提示如下表示实例已经配置好了：

 MySQL  JS > dba.configureInstance('ic@vm102:3306');
Configuring MySQL instance at vm102:3306 for use in an InnoDB cluster...

This instance reports its own address as vm102:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

The instance 'vm102:3306' is valid for InnoDB cluster usage.
The instance 'vm102:3306' is already ready for InnoDB cluster usage.
 MySQL  JS >

以上同样的方法对vm103进行配置（注意：是在vm101的MySQL Shell中对vm103进行配置的）

参考文章:
Configuring Production Instances

创建集群

在vm101的MySQL Shell中以此执行：

var cluster = dba.createCluster('testCluster')

如果出现如下提示:

 MySQL  JS > var cluster = dba.createCluster('testCluster')
Dba.createCluster: The shell must be connected to a member of the InnoDB cluster being managed (LogicError)
 MySQL  JS >

则执行：

\connect ic@vm101

然后再执行集群创建语句：

var cluster = dba.createCluster('testCluster')

结果出现了一个错误：

 MySQL  vm101:33060+ ssl  JS > var cluster = dba.createCluster('testCluster')
A new InnoDB cluster will be created on instance 'vm101:3306'.

Validating instance at vm101:3306...

This instance reports its own address as vm101:3306

Instance configuration is suitable.
Creating InnoDB cluster 'testCluster' on 'vm101:3306'...

Adding Seed Instance...
ERROR: Unable to start Group Replication for instance 'vm101:3306'. Please check the MySQL server error log for more information.
Dba.createCluster: Group Replication failed to start: MySQL Error 3092 (HY000): vm101:3306: The server is not configured properly to be an active member of the group. Please see more details on error log. (RuntimeError)
 MySQL  vm101:33060+ ssl  JS >

查看MySQL的日志/var/log/mysqld.log，发现可能是端口33061不通导致的，于是来配置防火墙：

firewall-cmd --zone=public --add-port=33061-33065/tcp --permanent
firewall-cmd --reload

我们把33061到33065的端口都放开

然后再在vm101的MySQL Shell中执行集群创建语句：

var cluster = dba.createCluster('testCluster')

依然出现错误，查资料得知是selinux的设置导致的。
关闭selinux的命令是：setenforce 0
如果要永久关闭selinux，需要修改文件/etc/selinux/config如下：

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

注意：记得在三台机器上都关掉selinux

然后再在vm101的MySQL Shell中执行集群创建语句：

var cluster = dba.createCluster('testCluster')

出现如下提示则表示集群创建成功了

 MySQL  vm101:33060+ ssl  JS > var cluster = dba.createCluster('testCluster')
A new InnoDB cluster will be created on instance 'vm101:3306'.

Validating instance at vm101:3306...

This instance reports its own address as vm101:3306

Instance configuration is suitable.
Creating InnoDB cluster 'testCluster' on 'vm101:3306'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

 MySQL  vm101:33060+ ssl  JS >

参考文章:
Creating the Cluster

往集群中添加实例

在vm101的MySQL Shell中依次执行：

cluster.addInstance('icadmin@vm102')
cluster.addInstance('icadmin@vm103')

根据提示操作输入密码
当出现
Please select a recovery method [C]lone/[A]bort (default Abort):C
的时候，选择C

执行完之后会出现成功的提示C

参考文章:
Adding Instances to a Cluster

安装MySQL Router

安装

在vm101上执行命令:

yum install mysql-router

每台机器上执行

MySQL Router对接InnoDB Cluster

vm101:
mysqlrouter --bootstrap icadmin@vm101 --user=mysqlrouter

vm102:
mysqlrouter --bootstrap icadmin@vm102 --user=mysqlrouter

vm103:
mysqlrouter --bootstrap icadmin@vm103 --user=mysqlrouter

会启动四个端口：

# Bootstrapping system MySQL Router instance...

- Checking for old Router accounts
  - No prior Router accounts found
- Creating mysql account 'mysql_router1_a2c8ptn0j1my'@'%' for cluster management
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /etc/mysqlrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB cluster 'testCluster'

After this MySQL Router has been started with the generated configuration

    $ /etc/init.d/mysqlrouter restart
or
    $ systemctl start mysqlrouter
or
    $ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

the cluster 'testCluster' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

## MySQL X protocol

- Read/Write Connections: localhost:64460
- Read/Only Connections:  localhost:64470


Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'
[root@vm101 ~]#

四个端口的说明:

6446 - 对于传统MySQL协议读写会话，MySQL路由器将传入连接重定向到Primary服务器实例。
6447 - 对于传统MySQL协议只读会话，MySQL路由器将传入连接重定向到其中一个从服务器实例。
64460 - 对于X协议读写会话，MySQL路由器将传入连接重定向到Primary服务器实例。
64470 - 对于X协议只读会话，MySQL路由器将传入连接重定向到其中一个从服务器实例。

启动mysqlrouter

systemctl start mysqlrouter

每台机器上执行

参考文章：
Installing MySQL Router on Linux

测试下集群

在vm101上通过6446端口登录集群

mysql -uroot -p1234 -hlocalhost -P6446

创建一个数据库

CREATE SCHEMA `testdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
use testdb;

然后在vm102和vm103节点上登录MySQL，查看数据库

show databases;

发现在刚刚在vm101数据库在已经同步过来了

mysql> show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
| testdb                        |
+-------------------------------+
6 rows in set (0.00 sec)

在vm101上创建表，并插入一条数据

CREATE TABLE `testdb`.`person` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`));

INSERT INTO `testdb`.`person` (`name`, `age`) VALUES ('yuanpan', '32');

同样，在另外两台机器上查看，数据已经过去了

管理集群

集群的重启

比如上周使用上面的方法在虚拟机中搭建完了集群。这周来重启虚拟机，其中的MySQL都是停止的。将三台机器的MySQL启动起来之后，进入vm101的MySQL Shell，然后使用如下的命令重启集群：

mysqlsh
\connect ic@vm101
var cluster = dba.rebootClusterFromCompleteOutage();

查看集群状态

var cluster = dba.getCluster()
cluster.status()

启动mysqlrouter

systemctl start mysqlrouter

参考文章：
Working with InnoDB Cluster

Trouble Shooting

一、CentOS7通过VirtualBox端口转发ssh登录很慢的问题

修改/etc/ssh/sshd_config文件

vi /etc/ssh/sshd_config

修改文件中的两个值如下：

UseDNS no
GSSAPIAuthentication no

二、CentOS7配置静态ip地址

VirtualBox中的内部网络环境设置

输入：ip addr检查内部网络是否有分配ip。在本环境中内部网卡名字是enp0s8，有mac地址，但是没有ip地址，需要手工设定。

查看网卡配置：more /etc/sysconfig/network-scripts/ifcfg-enp0s8

修改配置为如下：

TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=static
IPADDR=192.168.0.101
NETMASK=255.255.255.0
GATEWAY=192.168.0.1
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=enp0s8
UUID=c56b0c11-9558-49b1-bb2a-755696386069
DEVICE=enp0s8
ONBOOT=yes

主要修改的是：

BOOTPROTO=static
IPADDR=192.168.0.101
NETMASK=255.255.255.0
GATEWAY=192.168.0.1
ONBOOT=yes

这样就设置了静态ip地址为192.168.0.101了，然后重启网络
systemctl restart network

三、修改MySQL密码要求

SHOW VARIABLES LIKE 'validate_password%';
查看密码相关要求

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+

如果是刚安装完MySQL后第一次登录，那么可能不让你执行show variables的操作，这个时候可以跳过这个查看命令，直接进行下面的修改

进行如下修改：

set global validate_password.policy='LOW';
set global validate_password.length=4;
set global validate_password.special_char_count=0;
set global validate_password.mixed_case_count=0;

然后就可以给root修改一个简单密码了
ALTER USER 'root'@'localhost' IDENTIFIED BY '1234';