基于MySQL 8.0搭建InnoDB Cluster集群-每日运维

基于MySQL 8.0搭建InnoDB Cluster集群

说明：

1）主机规划(IP+主机名)：

172.17.0.35 node01

172.17.0.29 node02

172.17.0.56 node03

2）软件版本

操作系统版本：CentOS 7.6

MySQL Server版本：8.0.32

MySQL Server安装目录：/app/

MySQL Data目录：/data/mysqldb/data

MySQL Shell版本：8.0.32

MySQL Shell安装目录：/app/

MySQL Router版本：8.0.32

MySQL Router安装目录：/app/

MySQL Router配置目录：/data/myrouter/6446

一、下载对应的软件包

cd /app

wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.32-linux-glibc2.12-x86_64.tar

wget https://cdn.mysql.com/archives/mysql-router/mysql-router-8.0.32-linux-glibc2.12-x86_64.tar.xz

wget https://cdn.mysql.com/archives/mysql-shell/mysql-shell-8.0.32-linux-glibc2.12-x86-64bit.tar.gz

一、系统准备

以下 6 小节操作需要在三台主机执行。

1、centos7 关闭防火墙

systemctl stop firewalld.service 或者 systemctl stop firewalld

systemctl disable firewalld.service 或者 systemctl disable firewalld

systemctl status firewalld

2、关闭 selinux

getenforce

setenforce 0

vim /etc/selinux/config

SELINUX=disabled

3、绑定 /etc/hosts 解析(示例如下)

vim /etc/hosts

172.17.0.35 node1

172.17.0.29 node2

172.17.0.30 node3

4、安装 yum 包

yum -y groupinstall “DeveLopment tools”

yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make

5、清理系统自带 MariaDB 环境

CentOS 7 版本的系统默认自带安装了 MariaDB，需要先清理。

查询已安装的mariadb

rpm -qa |grep mariadb

或

yum list installed | grep mariadb

卸载 mariadb 包，文件名为上述命令查询出来的文件

rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

yum -y remove mariadb-libs.x86_64

6、内核参数修改

[root@node1 ~]# cat>>/etc/sysctl.conf /etc/security/limits.conf /etc/pam.d/login /etc/profile select * from performance_schema.replication_group_members;

±--------------------------±-------------------------------------±------------±------------±-------------±------------±---------------±---------------------------+

±--------------------------±-------------------------------------±------------±------------±-------------±------------±---------------±---------------------------+ |

±--------------------------±-------------------------------------±------------±------------±-------------±------------±---------------±---------------------------+

4、其他节点加入组复制（其他节点执行）

Node2 和 Node3 执行

mysql -uroot -proot123 -P 3306 -S /data/mysqldb/socket/mysql.sock

先执行 reset master;

SET SQL_LOG_BIN=0;

CREATE USER repl@’%’ IDENTIFIED BY ‘repl’;

GRANT REPLICATION SLAVE ON . TO repl@’%’;

SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER=‘repl’, MASTER_PASSWORD=‘repl’ FOR CHANNEL ‘group_replication_recovery’;

START GROUP_REPLICATION;

5、启动完成之后在看 Group 信息

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;

mysql> select * from performance_schema.replication_group_member_stats\G;

mysql> select * from performance_schema.replication_group_members;

±--------------------------±-------------------------------------±------------±------------±-------------±------------±---------------±---------------------------+

±--------------------------±-------------------------------------±------------±------------±-------------±------------±---------------±---------------------------+

3 rows in set (0.00 sec)

6、连接 Node1 主库创建 root@’%’ 用户

节点1

mysql -uroot -proot123 -P 3306 -S /data/mysqldb/socket/mysql.sock

create user root@’%’ identified by ‘root@123’;

grant all privileges on . to root@’%’ with grant option;

flush privileges;

===================================

部署多实例需要添加如下两个参数(否则端口冲突)

mysqlx_port

mysqlx_socket

节点加入集群报错时，节点需要设置如下参数(使用默认密码插件时需要执行)

set global group_replication_recovery_get_public_key=on;

===========================

四、安装 MySQL Shell

初始化实例及创建纳管库，库名 mysql_innodb_cluster_metadata，只需要在一台中间件服务器安装即可，这里选择 Node3。

1、root 用户安装 MySQL Shell

rpm -ivh mysql-shell-8.0.32-1.el7.x86_64.rpm

–这里下载的是 rpm 包，如果你下载的是二进制版本，可直接解压配置环境变量即可。

tar -zxvf mysql-shell-8.0.32-linux-glibc2.12-x86-64bit.tar.gz

ln -s mysql-shell-8.0.32-linux-glibc2.12-x86-64bit mysql-shell

export PATH=$PATH:/app/mysql-shell/bin

2、连接数据库，添加纳管

在第一次连接，创建纳管时，需要从node1: primary 连接进去。否则会破坏 read_only（root 用户会破坏 super_read_only)。

mysqlsh root@172.17.0.35:3306 --py

添加纳管

dba.create_cluster(‘mysqlrouter’)

MySQL 172.17.0.35:3306 ssl Py > dba.create_cluster(‘mysqlrouter’)

A new InnoDB Cluster will be created on instance ‘172.17.0.35:3306’.

You are connected to an instance that belongs to an unmanaged replication group.

Do you want to setup an InnoDB Cluster based on this replication group? [Y/n]: Y

Creating InnoDB Cluster ‘mysqlrouter’ on ‘172.17.0.35:3306’…

Adding Seed Instance…

Adding Instance ‘172.17.0.35:3306’…

Adding Instance ‘172.17.0.29:3306’…

Adding Instance ‘172.17.0.30:3306’…

Resetting distributed recovery credentials across the cluster…

Cluster successfully created based on existing replication group.

Cluster:mysqlrouter

3、查看集群状态

c=dba.get_cluster()

c.status()

MySQL 172.17.0.35:3306 ssl Py > c=dba.get_cluster()

MySQL 172.17.0.35:3306 ssl Py > c.status()

{

`"clusterName": "mysqlrouter",`
"defaultReplicaSet": { "name": "default", "primary": "172.17.0.35:3306", "ssl": "DISABLED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "172.17.0.29:3306": { "address": "172.17.0.29:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.0.32" }, "172.17.0.30:3306": { "address": "172.17.0.30:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.0.32" }, "172.17.0.35:3306": { "address": "172.17.0.35:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.0.32" } }, "topologyMode": "Single-Primary" },
`"groupInformationSourceMember": "172.17.0.35:3306"`
}

MySQL 172.17.0.35:3306 ssl Py >

4、MySQL Shell 的其他方法

dba.help()

\help dba

查看方法具体使用：

dba.help(‘createCluster’);

MySQL Shell 工具集

\help util

5、删除纳管

本次不需要执行，只要删除元数据数据库即可，从 primary 连接。

dba.drop_metadata_schema()

6、查看纳管数据库

mysql_innodb_cluster_metadata

mysql> show databases;

±------------------------------+

| Database |

±------------------------------+

| information_schema |

| mysql |

| mysql_innodb_cluster_metadata |

| performance_schema |

| sys |

±------------------------------+

5 rows in set (0.00 sec)

mysql> use mysql_innodb_cluster_metadata;

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> show tables;

±----------------------------------------+

| Tables_in_mysql_innodb_cluster_metadata |

±----------------------------------------+

| async_cluster_members |

| async_cluster_views |

| clusters |

| clusterset_members |

| clusterset_views |

| clustersets |

| instances |

| router_rest_accounts |

| routers |

| schema_version |

| v2_ar_clusters |

| v2_ar_members |

| v2_clusters |

| v2_cs_clustersets |

| v2_cs_members |

| v2_cs_router_options |

| v2_gr_clusters |

| v2_instances |

| v2_router_rest_accounts |

| v2_routers |

| v2_this_instance |

±----------------------------------------+

21 rows in set (0.00 sec)

五、MySQL Router 安装配置

1、三节点安装 MySQL Router

在主节点服务器配置 mysql 用户执行

$ tar -xvf mysql-router-8.0.32-linux-glibc2.12-x86_64.tar.xz

$ mv mysql-router-8.0.32-linux-glibc2.12-x86_64 mysqlrouter

$ mkdir -p /data/mysqlrouter/mysqlrouter6446

2、初始化 MySQL Router

/app/mysqlrouter/bin/mysqlrouter --bootstrap root@172.17.0.35:3306 --directory /data/mysqlrouter/mysqlrouter6446 --conf-base-port 6446 --conf-use-sockets --force

/app/mysqlrouter/bin/mysqlrouter --bootstrap root@172.17.0.29:3306 --directory /data/mysqlrouter/mysqlrouter6446 --conf-base-port 6446 --conf-use-sockets --force

(注意：–conf-base-port 6446 这个参数是 mysqlrouter 参数默认是 6446)

[mysql@node1 data]$ /app/mysqlrouter/bin/mysqlrouter --bootstrap root@172.17.0.35:3306 --directory /data/mysqlrouter/mysqlrouter6446 --conf-base-port 6446 --conf-use-sockets --force

Please enter MySQL password for root:

Bootstrapping MySQL Router instance at ‘/data/mysqlrouter/mysqlrouter6446’…

Fetching Cluster Members

trying to connect to mysql-server at 172.17.0.35:3306

Creating account(s) (only those that are needed, if any)

Verifying account (using it to run SQL queries that would be run by Router)

Storing account in keyring

Adjusting permissions of generated files

Creating configuration /data/mysqlrouter/mysqlrouter6446/mysqlrouter.conf

MySQL Router configured for the InnoDB Cluster ‘mysqlrouter’

After this MySQL Router has been started with the generated configuration

`$ /app/mysqlrouter/bin/mysqlrouter -c /data/mysqlrouter/mysqlrouter6446/mysqlrouter.conf`
InnoDB Cluster ‘mysqlrouter’ can be reached by connecting to:

MySQL Classic protocol

Read/Write Connections: localhost:6446, /data/mysqlrouter/mysqlrouter6446/mysql.sock
Read/Only Connections: localhost:6447, /data/mysqlrouter/mysqlrouter6446/mysqlro.sock

MySQL X protocol

Read/Write Connections: localhost:6448, /data/mysqlrouter/mysqlrouter6446/mysqlx.sock
Read/Only Connections: localhost:6449, /data/mysqlrouter/mysqlrouter6446/mysqlxro.sock

==========================================================

3、启动 MySQL Router

[mysql@node1 mysqlrouter6446]$ cd /data/mysqlrouter/mysqlrouter6446

[mysql@node1 mysqlrouter6446]$ ./start.sh

[mysql@node1 mysqlrouter6446]$ PID 29067 written to ‘/data/mysqlrouter/mysqlrouter6446/mysqlrouter.pid’

stopping to log to the console. Continuing to log to filelog

–除了上面脚本启动外，按提示中如下命令也可以启动：

/app/mysqlrouter/bin/mysqlrouter -c /data/mysqlrouter/mysqlrouter6446/mysqlrouter.conf

查看日志

cat mysqlrouter.log

[mysql@node1 log]$ cat mysqlrouter.log

4、使用 Router 登录 mysql 测试

mysql -uroot -p -h172.17.0.35 -P 6446（6446是 mysqlrouer 读写端口）

[mysql@node1 mysqlrouter6446]$ mysql -uroot -p -h172.17.0.35 -P 6446

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2017

Server version: 8.0.32 MySQL Community Server - GPL

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> show variables like ‘%read_only%’;

±----------------------±------+

| Variable_name | Value |

±----------------------±------+

| innodb_read_only | OFF |

| read_only | OFF |

| super_read_only | OFF |

| transaction_read_only | OFF |

±----------------------±------+

4 rows in set (0.01 sec)

mysql -uroot -p -h172.17.0.35 -P 6447（6447是 mysqlrouer 只读端口）

[mysql@node1 mysqlrouter6446]$ mysql -uroot -p -h172.17.0.35 -P 6447

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 19229

Server version: 8.0.32 MySQL Community Server - GPL

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> show variables like ‘%read_only%’

-> ;

±----------------------±------+

| Variable_name | Value |

±----------------------±------+

| innodb_read_only | OFF |

| read_only | ON |

| super_read_only | ON |

| transaction_read_only | OFF |

±----------------------±------+

4 rows in set (0.00 sec)

======================================

5、MySQL Router systemctl 启动配置

vim /etc/systemd/system/mysqlrouter@3306.service

[Unit]

Description= Mysql router

Documentation=man:mysqlrouter(8)

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=root

Group=root

Type=forking

CPUQuota=100%

TimeoutSec=30

PermissionsStartOnly=true

PIDFile=/data/mysqlrouter/mysqlrouter6446/mysqlrouter.pid

ExecStart=/usr/bin/bash -c “/data/mysqlrouter/mysqlrouter6446/start.sh &”

ExecStop=/usr/bin/bash -c “/data/mysqlrouter/mysqlrouter6446/stop.sh &”

LimitNOFILE = 102400

Restart=no

6、只读参数设置

[mysql@node1 ~]$ ps -ef | grep mysql

root 29285 27052 0 20:21 pts/0 00:00:00 su - mysql

mysql 29286 29285 0 20:21 pts/0 00:00:00 -bash

mysql 29991 29286 0 20:24 pts/0 00:00:00 mysql -uroot -px xxxxx -P 33060 -S /data/mysqldb/socket/mysql.sock

root 32298 32195 0 20:33 pts/3 00:00:00 su - mysql

mysql 32300 32298 0 20:33 pts/3 00:00:00 -bash

mysql 32393 32300 0 20:33 pts/3 00:00:00 ps -ef

mysql 32394 32300 0 20:33 pts/3 00:00:00 grep --color=auto mysql

mysql 36378 1 0 14:25 ? 00:00:00 /bin/sh /app/mysql8.0.32/bin/mysqld_safe --defaults-file=/data/mysqldb/conf/mysql.conf --user=mysql

mysql 38891 36378 0 14:25 ? 00:01:49 /app/mysql8.0.32/bin/mysqld --defaults-file=/data/mysqldb/conf/mysql.conf --basedir=/app/mysql8.0.32 --datadir=/data/mysqldb/data --plugin-dir=/app/mysql8.0.32/lib/plugin --log-error=/data/mysqldb/log/mysqld.log --pid-file=/data/mysqldb/pid/mysqld.pid --socket=/data/mysqldb/socket/mysql.sock --port=3306

mysql 92606 1 0 18:00 ? 00:00:49 /app/mysqlrouter/bin/mysqlrouter -c /data/mysqlroutermysqlrouter6446/mysqlrouter.conf

[mysql@node1 log]$ vim /data/mysqldb/conf/mysql.conf

read_only=1

super_read_only=1

然后登录两个从库，执行从库只读设置（注意只在从库执行，主库不用执行）。

mysql> set global read_only=on;

Query OK, 0 rows affected (0.00 sec)

mysql> set global super_read_only=on;

Query OK, 0 rows affected (0.00 sec)

±-----------------±---------±-------------±-----------------±--------------------------------------------+

±-----------------±---------±-------------±-----------------±--------------------------------------------+

1 row in set (0.00 sec)

[mysql@node1 ~]$ mysqlsh

MySQL Shell 8.0.32

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.

MySQL JS >

###shell.connect(‘root@172.17.0.35:3306’)

MySQL JS > shell.connect(‘root@172.17.0.35:3306’)

Creating a session to ‘root@172.17.0.35:3306’

Fetching schema names for auto-completion… Press ^C to stop.

Your MySQL connection id is 2140

Server version: 8.0.32 MySQL Community Server - GPL

No default schema selected; type \use to set one.

ClassicSession:root@172.17.0.35:3306

MySQL 172.17.0.35:3306 ssl JS >

###dba.getCluster()

MySQL 172.17.0.35:3306 ssl JS > dba.getCluster()

Cluster:mysqlrouter

MySQL 172.17.0.35:3306 ssl JS > var cluster = dba.getCluster()

MySQL 172.17.0.35:3306 ssl JS > cluster.status()

{

"clusterName": "mysqlrouter",


"defaultReplicaSet": {
    "name": "default", 
    "primary": "172.17.0.35:3306", 
    "ssl": "DISABLED", 
    "status": "OK", 
    "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
    "topology": {
        "172.17.0.29:3306": {
            "address": "172.17.0.29:3306", 
            "memberRole": "SECONDARY", 
            "mode": "R/O", 
            "readReplicas": {}, 
            "replicationLag": "applier_queue_applied", 
            "role": "HA", 
            "status": "ONLINE", 
            "version": "8.0.32"
        }, 
        "172.17.0.30:3306": {
            "address": "172.17.0.30:3306", 
            "memberRole": "SECONDARY", 
            "mode": "R/O", 
            "readReplicas": {}, 
            "replicationLag": "applier_queue_applied", 
            "role": "HA", 
            "status": "ONLINE", 
            "version": "8.0.32"
        }, 
        "172.17.0.35:3306": {
            "address": "172.17.0.35:3306", 
            "memberRole": "PRIMARY", 
            "mode": "R/W", 
            "readReplicas": {}, 
            "replicationLag": "applier_queue_applied", 
            "role": "HA", 
            "status": "ONLINE", 
            "version": "8.0.32"
        }
    }, 
    "topologyMode": "Single-Primary"
},

"groupInformationSourceMember": "172.17.0.35:3306"

}

基于MySQL 8.0搭建InnoDB Cluster集群

查询已安装的mariadb

卸载 mariadb 包，文件名为上述命令查询出来的文件

rpm -ivh mysql-shell-8.0.32-1.el7.x86_64.rpm

–这里下载的是 rpm 包，如果你下载的是二进制版本，可直接解压配置环境变量即可。

tar -zxvf mysql-shell-8.0.32-linux-glibc2.12-x86-64bit.tar.gz

ln -s mysql-shell-8.0.32-linux-glibc2.12-x86-64bit mysql-shell

MySQL Router configured for the InnoDB Cluster ‘mysqlrouter’

After this MySQL Router has been started with the generated configuration

`$ /app/mysqlrouter/bin/mysqlrouter -c /data/mysqlrouter/mysqlrouter6446/mysqlrouter.conf`
InnoDB Cluster ‘mysqlrouter’ can be reached by connecting to:

MySQL Classic protocol

MySQL X protocol

相关文章

发布评论取消回复

大树

社区版oceanbase安装

oracle 查询改写

ORA-00600 internal error code, arguments [25026]

基于MySQL 8.0搭建InnoDB Cluster集群

查询已安装的mariadb

卸载 mariadb 包，文件名为上述命令查询出来的文件

rpm -ivh mysql-shell-8.0.32-1.el7.x86_64.rpm –这里下载的是 rpm 包，如果你下载的是二进制版本，可直接解压配置环境变量即可。

tar -zxvf mysql-shell-8.0.32-linux-glibc2.12-x86-64bit.tar.gz

ln -s mysql-shell-8.0.32-linux-glibc2.12-x86-64bit mysql-shell

MySQL Router configured for the InnoDB Cluster ‘mysqlrouter’ After this MySQL Router has been started with the generated configuration $ /app/mysqlrouter/bin/mysqlrouter -c /data/mysqlrouter/mysqlrouter6446/mysqlrouter.conf InnoDB Cluster ‘mysqlrouter’ can be reached by connecting to:

MySQL Classic protocol

MySQL X protocol

相关文章

发布评论 取消回复

大树

社区版oceanbase安装

oracle 查询改写

ORA-00600 internal error code, arguments [25026]

rpm -ivh mysql-shell-8.0.32-1.el7.x86_64.rpm

–这里下载的是 rpm 包，如果你下载的是二进制版本，可直接解压配置环境变量即可。

MySQL Router configured for the InnoDB Cluster ‘mysqlrouter’

After this MySQL Router has been started with the generated configuration

`$ /app/mysqlrouter/bin/mysqlrouter -c /data/mysqlrouter/mysqlrouter6446/mysqlrouter.conf`
InnoDB Cluster ‘mysqlrouter’ can be reached by connecting to:

发布评论取消回复