Mysql shell 安装及应用
mysql shell 是在 mysql5.7 推出的一款用来管理 mysql 的工具,使用它可以很方便的管理 mysql cluster 集群,一定程度上可以达到自动化的程度!下面就来安装 mysql shell 并用它来搭建一个 mysql 集群!
mysql 官网 mysql shell 文档
前几天学会了 docker,这次就使用 docker 创建该 mysql 集群好了
首先编写一个 MasterDockerFile 用来安装 mysql shell
FROM mysql RUN sed -i "s@http://deb.debian.org@http://mirrors.aliyun.com@g" /etc/apt/sources.list RUN apt-get update RUN apt-get install wget python lsb-release -y WORKDIR /usr/local/src RUN wget https://dev.mysql.com/get/mysql-apt-config_0.8.16-1_all.deb RUN echo '4' | dpkg -i ./mysql-apt-config_0.8.16-1_all.deb RUN apt-get update RUN apt-get install mysql-shell -y
然后我们创建 3 个 mysql 容器,在这里使用之前的docker-compose.yml修改一下,千万不要照搬!
version: "3"
services:
nginx:
container_name: nginx
build:
context: ./nginx
dockerfile: DockerFile
privileged: true
restart: always
networks:
- lnmp_cluster
ports:
- "80:80"
- "443:443"
volumes:
- ./nginx/conf.d:/etc/nginx/conf.d
- ./nginx/logs:/var/log/nginx
- ./www:/usr/share/nginx/html
depends_on:
- php
php:
container_name: php8
build:
context: ./php
dockerfile: DockerFile
privileged: true
restart: always
volumes:
- ./www:/usr/share/nginx/html
- ./php:/usr/local/etc
environment:
- TZ=Asia/Shanghai
networks:
- lnmp_cluster
links:
- mysql1
- mysql2
- mysql3
- redis
redis:
container_name: redis
image: redis
privileged: true
restart: always
volumes:
- ./db/redisdata:/data
- ./db/redisconf:/etc/redis
ports:
- "6379:6379"
networks:
- lnmp_cluster
command: redis-server /etc/redis/redis.conf
expose:
- "6379"
nodejs:
container_name: nodejs
image: node
privileged: true
#restart: always
tty: true
volumes:
- ./node/node_modules:/usr/local/lib/node_modules
- ./www:/usr/share/nginx/html
ports:
- "3000:3000"
networks:
- lnmp_cluster
expose:
- "3000"
mysql1:
container_name: mysql1
build:
context: ./db
dockerfile: MasterDockerFile
privileged: true
restart: always
volumes:
- ./db/myconf1:/etc/mysql/conf.d
- ./db/my_data1:/var/lib/mysql
ports:
- "3306:3306"
command: >
bash -c "
chmod 644 /etc/mysql/conf.d/*.cnf
&& /entrypoint.sh mysqld
"
environment:
MYSQL_ROOT_PASSWORD: 111111
MYSQL_DATABASE: test
MYSQL_USER: user
MYSQL_PASSWORD: user123
networks:
- lnmp_cluster
expose:
- "3306"
links:
- mysql2
- mysql3
mysql2:
container_name: mysql2
image: mysql
privileged: true
restart: always
volumes:
- ./db/myconf2:/etc/mysql/conf.d
- ./db/my_data2:/var/lib/mysql
ports:
- "3307:3306"
command: >
bash -c "
chmod 644 /etc/mysql/conf.d/*.cnf
&& /entrypoint.sh mysqld
"
environment:
MYSQL_ROOT_PASSWORD: 111111
MYSQL_DATABASE: test
MYSQL_USER: user
MYSQL_PASSWORD: user123
networks:
- lnmp_cluster
expose:
- "3306"
mysql3:
container_name: mysql3
image: mysql
privileged: true
restart: always
volumes:
- ./db/myconf3:/etc/mysql/conf.d
- ./db/my_data3:/var/lib/mysql
ports:
- "3308:3306"
command: >
bash -c "
chmod 644 /etc/mysql/conf.d/*.cnf
&& /entrypoint.sh mysqld
"
environment:
MYSQL_ROOT_PASSWORD: 111111
MYSQL_DATABASE: test
MYSQL_USER: user
MYSQL_PASSWORD: user123
networks:
- lnmp_cluster
expose:
- "3306"
networks:
lnmp_cluster:
name: lnmp_cluster
把环境启动起来,默认会在mysql1这个容器中安装好我们的mysql shell
然后进入 mysql1 的 shell
docker exec -it mysql1 /bin/bash
执行 mysqlsh 命令
root@7ff8ea685378:/usr/local/src# mysqlsh Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory MySQL Shell 8.0.22 Copyright (c) 2016, 2020, Oracle and/or its affiliates. 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 >
链接到主库中,即本机 3306 端口
\connect root@127.0.0.1:3306
MySQL JS > \connect root@127.0.0.1:3306 Creating a session to 'root@127.0.0.1:3306' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 16 Server version: 8.0.22 MySQL Community Server - GPL No default schema selected; type \use <schema> to set one.
使用 dba.configureInstance('root@127.0.0.1:3306') 来检查该仓库是否可以作为 cluster 的一个节点,如下显示则表示可以作为节点
MySQL 127.0.0.1:3306 ssl JS > dba.configureInstance('root@127.0.0.1:3306')
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as 7ff8ea685378: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 '7ff8ea685378:3306' is valid to be used in an InnoDB cluster.
The instance '7ff8ea685378:3306' is already ready to be used in an InnoDB cluster.
创建一个名为t 的 innodb cluster集群
var cluster = dba.createCluster("t");
A new InnoDB cluster will be created on instance '127.0.0.1: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 't' on '7ff8ea685378:3306'...
Adding Seed Instance...
Adding Instance 'ecf4ae3c41d7:3306'...
Adding Instance '7ff8ea685378:3306'...
Resetting distributed recovery credentials across the cluster...
NOTE: User 'mysql_innodb_cluster_2'@'%' already existed at instance '7ff8ea685378:3306'. It will be deleted and created again with a new password.
NOTE: User 'mysql_innodb_cluster_1'@'%' already existed at instance '7ff8ea685378:3306'. It will be deleted and created again with a new password.
Cluster successfully created based on existing replication group.
MySQL 127.0.0.1:3306 ssl JS >
这样我们的集群就已经创建好了,如此简单!?就是这么简单
接下来添加我们的其他两个 mysql 节点
cluster.addInstance({user: "root", host: "mysql3", port:3306, password: "111111"});
MySQL 127.0.0.1:3306 ssl JS > cluster.addInstance({user: "root", host: "mysql3", port:3306, password: "111111"});
NOTE: The target instance 'd560eb9c7100:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'd560eb9c7100:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): cluster.addInstance({user: "root", host: "mysql2", port:3306, password: "111111"});
Please pick an option out of [C]lone/[I]ncremental recovery/[A]bort (default Clone):
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
NOTE: Group Replication will communicate with other members using 'd560eb9c7100:33061'. Use the localAddress option to override.
Validating instance configuration at mysql3:3306...
This instance reports its own address as d560eb9c7100:3306
Instance configuration is suitable.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster...
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.
NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
* Waiting for clone to finish...
NOTE: d560eb9c7100:3306 is being cloned from 7ff8ea685378:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: d560eb9c7100:3306 is shutting down...
* Waiting for server restart... ready
* d560eb9c7100:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 69.05 MB transferred in 2 sec (34.53 MB/s)
State recovery already finished for 'd560eb9c7100:3306'
The instance 'mysql3:3306' was successfully added to the cluster.
MySQL 127.0.0.1:3306 ssl JS >
中间会让我们选择用什么方式来恢复数据,clone,incremental
这里选择默认选项 clone
等待同步之后子节点就拥有了和主节点一样的数据
把 mysql2 和 mysql3 都加入进来之后运行cluster.status()查看
MySQL 127.0.0.1:3306 ssl JS > cluster.status();
{
"clusterName": "t",
"defaultReplicaSet": {
"name": "default",
"primary": "7ff8ea685378:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"7ff8ea685378:3306": {
"address": "7ff8ea685378:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.22"
},
"d560eb9c7100:3306": {
"address": "d560eb9c7100:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.22"
},
"ecf4ae3c41d7:3306": {
"address": "ecf4ae3c41d7:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.22"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "7ff8ea685378:3306"
}
MySQL 127.0.0.1:3306 ssl JS >
这样就形成了一主两从的结构,接下来我们创建一个数据库试一试
在 mysql1 中创建server1的数据库,server1中创建users表
\sql create database server1 charset=utf8; \sql use server1; \sql create table users (`id` int(10) not null primary key auto_increment,`name` varchar(255) not null default '')ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
集群会自动将主节点的数据同步到子节点中,我们到子节点中看看是否有名为server1的数据库
链接 mysql2 数据库
MySQL JS > \connect root@mysql2:3306 Creating a session to 'root@mysql2:3306' Please provide the password for 'root@mysql2:3306': ****** Save password for 'root@mysql2:3306'? [Y]es/[N]o/Ne[v]er (default No): yes Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 60 Server version: 8.0.22 MySQL Community Server - GPL No default schema selected; type \use <schema> to set one. MySQL mysql2:3306 ssl JS > \sql show databases; +-------------------------------+ | Database | +-------------------------------+ | information_schema | | mysql | | mysql_innodb_cluster_metadata | | performance_schema | | server1 | | sys | | test | +-------------------------------+ 7 rows in set (0.0022 sec) MySQL mysql2:3306 ssl JS > \sql use server1; Query OK, 0 rows affected (0.0006 sec) MySQL mysql2:3306 ssl server1 JS > \sql show tables; +-------------------+ | Tables_in_server1 | +-------------------+ | users | +-------------------+ 1 row in set (0.0217 sec) MySQL mysql2:3306 ssl server1 JS >
mysql3 中也一样
MySQL mysql2:3306 ssl server1 JS > \connect root@mysql3:3306 Creating a session to 'root@mysql3:3306' Please provide the password for 'root@mysql3:3306': ****** Save password for 'root@mysql3:3306'? [Y]es/[N]o/Ne[v]er (default No): yes Fetching schema names for autocompletion... Press ^C to stop. Closing old connection... Your MySQL connection id is 25 Server version: 8.0.22 MySQL Community Server - GPL No default schema selected; type \use <schema> to set one. MySQL mysql3:3306 ssl JS > \sql show databases; +-------------------------------+ | Database | +-------------------------------+ | information_schema | | mysql | | mysql_innodb_cluster_metadata | | performance_schema | | server1 | | sys | | test | +-------------------------------+ 7 rows in set (0.0018 sec) MySQL mysql3:3306 ssl JS >
mysql shell 还有很多有用的功能,今天只是简单介绍一下,后面搭建高可用 mysql 集群时再详细介绍! 
小 A 空间 , 版权所有丨如未注明转载 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明mysql shell 安装及应用！
