1、环境

节点	主机名	ip地址	操作系统版本
节点一	mysql1	10.5.11.70	centos 7.5
节点二	mysql2	10.5.11.71	centos 7.5
节点三	mysql3	10.5.11.72	centos 7.5

1.1、主机名设置

 hostnamectl set-hostname mysql1
 hostnamectl set-hostname mysql2
 hostnamectl set-hostname mysql3

关闭终端，重新登录

2、软件安装

2.1 mysql1


rpm -e postfix
rpm -e mariadb-libs
rpm -ivh mysql-router-community-8.0.20-1.el7.x86_64.rpm
rpm -ivh mysql-shell-8.0.20-1.el7.x86_64.rpm

tar xvf mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar

rpm -ivh mysql-community-common-5.7.30-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.30-1.el7.x86_64.rpm 
rpm -ivh mysql-community-libs-compat-5.7.30-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-5.7.30-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-5.7.30-1.el7.x86_64.rpm

三个节点安装步骤同上

3、节点mysql1配置

3.1、创建工作目录

mkdir -p /home/mysql/data

chown -R mysql.mysql /home/mysql

3.2、创建配置文件

vi /home/mysql/my.cnf

[mysqld]

pid-file        = /home/mysql/mysqld.pid
socket          = /home/mysql/mysqld.sock
datadir         = /home/mysql/data
log-error = /home/mysql/mysqld.log
log_bin=ON
secure-file-priv= NULL
symbolic-links=0

server_id=1
max_connections=4096
character-set-server=utf8mb4
default_authentication_plugin=mysql_native_password
default_storage_engine=INNODB
innodb_buffer_pool_size=12G
innodb_log_file_size=256M
innodb_log_buffer_size=12M
innodb_flush_log_at_trx_commit=2
lower_case_table_names=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
binlog_checksum=NONE
log_slave_updates=ON
binlog_format=ROW
expire_logs_days=1
skip-host-cache
skip-name-resolve

3.3、修改目录权限

chown -R mysql.mysql /home/mysql

3.4、初始化数据库

 mysqld --defaults-file=/home/mysql/my.cnf  --initialize-insecure --user=mysql

3.5 启动数据库

修改启动脚本
vi /lib/systemd/system/mysqld.service

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking

PIDFile=/var/run/mysqld/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Execute pre and post scripts as root
PermissionsStartOnly=true


# Start main service
ExecStart=/usr/sbin/mysqld --daemonize --defaults-file=/home/mysql/my.cnf

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 5000

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

systemctl deamon-reload
systemctl enable mysqld.service
systemctl start mysqld.service

3.6 创建用户并授权

mysql -uroot -p -S /home/mysql/mysqld.socket
grant all privileges  on *.* to root@'%' identified by password;
flush privileges;
quit

4、节点mysql2配置

4.1、创建工作目录

mkdir -p /home/mysql/data

chown -R mysql.mysql /home/mysql

4.2、创建配置文件

vi /home/mysql/my.cnf

[mysqld]

pid-file        = /home/mysql/mysqld.pid
socket          = /home/mysql/mysqld.sock
datadir         = /home/mysql/data
log-error = /home/mysql/mysqld.log
log_bin=ON
secure-file-priv= NULL
symbolic-links=0

server_id=2
max_connections=4096
character-set-server=utf8mb4
default_authentication_plugin=mysql_native_password
default_storage_engine=INNODB
innodb_buffer_pool_size=12G
innodb_log_file_size=256M
innodb_log_buffer_size=12M
innodb_flush_log_at_trx_commit=2
lower_case_table_names=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
binlog_checksum=NONE
log_slave_updates=ON
binlog_format=ROW
expire_logs_days=1
skip-host-cache
skip-name-resolve

4.3、修改目录权限

chown -R mysql.mysql /home/mysql

4.4、初始化数据库

 mysqld --defaults-file=/home/mysql/my.cnf  --initialize-insecure --user=mysql

4.5 启动数据库

修改启动脚本
vi /lib/systemd/system/mysqld.service

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking

PIDFile=/var/run/mysqld/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Execute pre and post scripts as root
PermissionsStartOnly=true


# Start main service
ExecStart=/usr/sbin/mysqld --daemonize --defaults-file=/home/mysql/my.cnf

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 5000

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

systemctl deamon-reload
systemctl enable mysqld.service
systemctl start mysqld.service

4.6 创建用户并授权

mysql -uroot -p -S /home/mysql/mysqld.socket
grant all privileges  on *.* to root@'%' identified by password;
flush privileges;
quit

5、节点mysql3配置

5.1、创建工作目录

mkdir -p /home/mysql/data

chown -R mysql.mysql /home/mysql

5.2、创建配置文件

vi /home/mysql/my.cnf

[mysqld]

pid-file        = /home/mysql/mysqld.pid
socket          = /home/mysql/mysqld.sock
datadir         = /home/mysql/data
log-error = /home/mysql/mysqld.log
log_bin=ON
secure-file-priv= NULL
symbolic-links=0

server_id=3
max_connections=4096
character-set-server=utf8mb4
default_authentication_plugin=mysql_native_password
default_storage_engine=INNODB
innodb_buffer_pool_size=12G
innodb_log_file_size=256M
innodb_log_buffer_size=12M
innodb_flush_log_at_trx_commit=2
lower_case_table_names=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
binlog_checksum=NONE
log_slave_updates=ON
binlog_format=ROW
expire_logs_days=1
skip-host-cache
skip-name-resolve

5.3、修改目录权限

chown -R mysql.mysql /home/mysql

5.4、初始化数据库

 mysqld --defaults-file=/home/mysql/my.cnf  --initialize-insecure --user=mysql

5.5 启动数据库

修改启动脚本
vi /lib/systemd/system/mysqld.service

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking

PIDFile=/var/run/mysqld/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Execute pre and post scripts as root
PermissionsStartOnly=true


# Start main service
ExecStart=/usr/sbin/mysqld --daemonize --defaults-file=/home/mysql/my.cnf

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 5000

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

systemctl deamon-reload
systemctl enable mysqld.service
systemctl start mysqld.service

5.6 创建用户并授权

mysql -uroot -p -S /home/mysql/mysqld.socket
grant all privileges  on *.* to root@'%' identified by password;
flush privileges;
quit

6、集群配置

6.1 集群条件检查和配置

mysqlsh --uri 'root@mysql1'
dba.checkInstanceConfiguration()
dba.configLocalInstance()

mysqlsh --uri 'root@mysql2'
dba.checkInstanceConfiguration()
dba.configLocalInstance()

mysqlsh --uri 'root@mysql3'
dba.checkInstanceConfiguration()
dba.configLocalInstance()

6.2 创建集群

mysqlsh --uri 'root@mysql1'
var c = dba.createCluster('bag',{multiPrimary:true})
c.addInstance('root@mysql2')
c.addInstance('root@mysql3')

6.3 创建路由配置文件,启动路由

mysqlrouter -B 'root@mysql1' --user=mysqlrouter

systemctl enable mysqlrouter
systemctl start mysqlrouter

7、故障恢复

7.1 情形一

7.1.1 故障描述

mysql集群有三个节点，重启后节点一、节点二恢复集群，节点三无法加入集群，且数据不断往节点三写入

mysql5.7 集群节点全部重启后是不会自动启动集群，需要执行dba.rebootClusterFromCompleteOutage(),而在此之前节点三不断写入，导致节点三的状态与节点一、二不一致，最终节点一二加入集群，而节点三不能加入集群

7.1.2 恢复步骤

7.1.2.1 停止节点三的写入，将节点三的数据导出


mysqldump -uroot -p -S /home/mysql3/mysqld.sock --triggers --routines --events --databases test > test.sql

7.1.2.2 重启节点一、二,导入节点三的数据

在命令行执行

reset master

导入数据

mysql -uroot -p -S /home/mysql1/mysqld.sock < test.sql

7.1.2.3 拉起集群

[root@localhost home]# mysqlsh --uri 'root@localhost:3316'
MySQL Shell 8.0.20

Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:3316'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 6
Server version: 5.7.30-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:3316 ssl  JS > var c  = dba.getCluster()
Dba.getCluster: This function is not available through a session to a standalone instance (metadata exists, instance belongs to that metadata, but GR is not active) (RuntimeError)
 MySQL  localhost:3316 ssl  JS > dba.rebootClusterFromCompleteOutage()
Restoring the default cluster from complete outage...

The instance 'localhost.localdomain:3326' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

The instance 'localhost.localdomain:3336' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

NOTE: Group Replication will communicate with other members using 'localhost.localdomain:33161'. Use the localAddress option to override.

WARNING: Instance 'localhost.localdomain:3316' cannot persist Group Replication configuration since MySQL version 5.7.30 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
localhost.localdomain:3316 was restored.
Rejoining 'localhost.localdomain:3326' to the cluster.
NOTE: Group Replication will communicate with other members using 'localhost.localdomain:33261'. Use the localAddress option to override.

WARNING: Instance 'localhost.localdomain:3326' cannot persist Group Replication configuration since MySQL version 5.7.30 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
Rejoining 'localhost.localdomain:3336' to the cluster.
NOTE: Group Replication will communicate with other members using 'localhost.localdomain:33361'. Use the localAddress option to override.

WARNING: Instance 'localhost.localdomain:3336' cannot persist Group Replication configuration since MySQL version 5.7.30 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
The cluster was successfully rebooted.

<Cluster:bag>
 MySQL  localhost:3316 ssl  JS > var c  = dba.getCluster()
 MySQL  localhost:3316 ssl  JS > c.status()
{
    "clusterName": "bag", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost.localdomain:3316", 
        "ssl": "DISABLED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost.localdomain:3316": {
                "address": "localhost.localdomain:3316", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost.localdomain:3326": {
                "address": "localhost.localdomain:3326", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost.localdomain:3336": {
                "address": "localhost.localdomain:3336", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "localhost.localdomain:3316"
}
 MySQL  localhost:3316 ssl  JS >

7.1.2.4 恢复方法二

停止集群写入
重启全部mysqld进程
mysqlsh 登录到写入数据的实例，执行

dba.rebootClusterFromCompleteOutage()

数据库 mysql5.7 innodb cluster

本博客所有文章除特别声明外，均采用 CC BY-SA 3.0协议。转载请注明出处！