开发者

MySQL8.0高可用MIC的实现

开发者 https://www.devze.com 2024-10-29 09:01 出处：网络 作者： 爱喝旺仔的昊昊
目录一、机器准备机器关闭防火墙二、环境准备安装mysql8.0.23修改配置文件启动并修改密码主服务器安装mysql-shell-8.0.23三、终端操作MySQL操作创建用户并授权MySQL-Shell操作进入mysql-shell连接主库信息配置集群创
相关专题：MySQL8.0 MICMySQL8.0高可用MIC
目录
  • 一、机器准备
    • 机器
    • 关闭防火墙
  • 二、环境准备
    • 安装mysql8.0.23
    • 修改配置文件
    • 启动并修改密码
    • 主服务器安装mysql-shell-8.0.23
  • 三、终端操作
    • MySQL操作
      • 创建用户并授权
    • MySQL-Shell操作
      • 进入mysql-shell
      • 连接主库信息
      • 配置集群
      • 创建一个集群
      • 查看主机群状态
      • 添加两个从节点
      • 查看节点
      • 清空集群
  • 四、故障恢复
    • 安装mysql-router
      • 修改配置文件
        • 首次启动
        • 修改配置文件
      • 启动
        • 停掉主库
          • 操作mysql-shell

          一、机器准备

          机器

          主机名

          ip地址

          角色

          软件

          mic-master

          192.168.252.148

          主节点

          mysql8.0.23 mysql-shell-8.0.23

          mic-node1

          192.168.252.142

          node1

          mysql8.0.23

          mic-node2

          192.168.252.145

          node2

          mysql8.0.23

          关闭防火墙

          systemctl stop firewalld
setenforce 0

          二、环境准备

          安装mysql8.0.23

          官方地址：MySQL :: Begin Your Download

          三台机器均安装

          1.安装工具
yum -y install perl-jsON perl-Test-Simple
2.获取安装包
wgetuteORKVwbK https://downloads.mysql.com/archives/get/p/23/file/mysql-community-server-8.0.23-1.el7.x86_64.rpm
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-client-8.0.23-1.el7.x86_64.rpm
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-client-plugins-8.0.23-1.el7.x86_64.rpm
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-devel-8.0.23-1.el7.x86_64.rpm
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-common-8.0.23-1.el7.x86_64.rpm
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-libs-8.0.23-1.el7.x86_64.rpm 
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-libs-compat-8.0.23-1.el7.x86_64.rpm
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-embedded-compat-8.0.23-1.el7.x86_64.rpm
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-test-8.0.23-1.el7.x86_64.rpm
3.安装
rpm -ivh mysql-community-common-8.0.23-1.el7.x86_64.rpm
编程客栈rpm -ivh mysql-community-client-plugins-8.0.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-8.0.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-embedded-compat-8.0.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-test-8.0.23-1.el7.x86_64.rpm

          修改配置文件

          所有节点修改进行修改配置文件

          添加配置的模版
server_id=[id]  #每一台的都不一样，要唯一
report_host=[当前服务器ip]
report_port=[mysql端口号]
loose-group_replication_ip_whitelist="[ip1],[ip2],[ip3]"

          进行修改

          vim /etc/my.cnf

          192.168.252.148：

          server-id=1
report_host=192.168.252.148
report_port=3306
loose-group_replication_ip_whitelist="192.168.252.148,192.168.252.142,192.168.252.145"

          MySQL8.0高可用MIC的实现

          192.168.252.142：

          server-id=php2
report_host=192.168.252.142
report_port=3306
loose-group_replication_ip_whitelist="192.168.252.148,192.168.252.142,192.168.252.145"

          MySQL8.0高可用MIC的实现

          192.168.252.145：

          server-id=3
report_host=192.168.252.145
report_port=3306
loose-group_replication_ip_whitelist="192.168.252.148,192.168.252.142,192.168.252.145"

          MySQL8.0高可用MIC的实现

          启动并修改密码

          systemctl start mysqld

          192.168.252.148：

          cat /var/log/mysqld.log | grep "password"
mysqladmin -uroot -p'Cu)ghjzfK3_J' password @Syh2025659

          MySQL8.0高可用MIC的实现

          MySQL8.0高可用MIC的实现

          192.168.252.142：

          cat /var/log/mysqld.log | grep "password"
mysqladmin -uroot -p'*qywTIZHh8-j' password '@Syh2025659'

          MySQL8.0高可用MIC的实现

          MySQL8.0高可用MIC的实现

          192.168.252.145：

          cat /var/log/mysqld.log | grep "password"
mysqladmin -uroot -p'Z+Ly31m>Y#sy' password '@Syh2025659'

          MySQL8.0高可用MIC的实现

          MySQL8.0高可用MIC的实现

          主服务器安装mysql-shell-8.0.23

          下载链接：https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.23-linux-glibc2.12-x86-64bit.tar.gz

          MySQL8.0高可用MIC的实现

          wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.23-linux-glibc2.12-x86-64bit.tar.gz
解压：
tar -xzf mysql-shell-8.0.23-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/
cd /usr/local	
mv mysql-shell-8.0.23-linux-glibc2.12-x86-64bi mysql-shell

          三、终端操作

          MySQL操作

          创建用户并授权

          三台机器均添加

          1.创建用户
create user syh identified by '@Syh2025659';
2.授权给用户
GRANT BACKUP_ADMIN, CLONE_ADMIN, CREATE USER, EXECUTE, FILE, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHUTDOWN, SUPER, SYSTEM_VARIABLES_ADMIN ON *.* TO 'syh'@'%' WITH GRANT OPTION;
GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'syh'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'syh'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'syh'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'syh'@'%' WITH GRANT OPTION;
GRANT ALL ON *.* to 'syh'@'%';
GRANT ALL PRIVILEGES on *.* to 'syh'@'%' WITH GRANT OPTION;
3.刷新权限
FLUSH PRIVILEGES;

          192.168.252.148:

          MySQL8.0高可用MIC的实现

          192.168.252.142:

          MySQL8.0高可用MIC的实现

          192.168.252.145:

          MySQL8.0高可用MIC的实现

          MySQL-Shell操作

          进入mysql-shell

          /usr/local/mysql-shell/bin/mysqlsh   #启动

          MySQL8.0高可用MIC的实现

          连接主库信息

          \c syh@192.168.252.148:3306

          MySQL8.0高可用MIC的实现

          配置集群

          dba.configureInstance('syh@192.168.252.148:3306');
dba.configureInstance('syh@192.168.252.142:3306');
dba.configureInstance('syh@192.168.252.145:3306');

          MySQL8.0高可用MIC的实现

          MySQL8.0高可用MIC的实现

          MySQL8.0高可用MIC的实现

          创建一个集群

          var cluster=dba.createCluster('mysqlcluster');

          MySQL8.0高可用MIC的实现

          查看主机群状态

          dba.getCluster().status();

          MySQL8.0高可用MIC的实现

          添加两个从节点

          var cluster=dba.getCluster();
cluster.addInstance('syh@192.168.252.142:3306');
cluster.addInstance('syh@192.168.252.145:3306');

          MySQL8.0高可用MIC的实现

          MySQL8.0高可用MIC的实现

          查看节点

          cluster.status();

          MySQL8.0高可用MIC的实现

          清空集群

          dba.dropMetadataSchema(); 清除所有集群

          MySQL8.0高可用MIC的实现

          四、故障恢复

          安装mysql-router

          192.168.252.148：

          wget https://downloads.mysql.com/archives/get/p/41/file/mysql-router-8.0.23-linux-glibc2.12-x86_64.tar.xz
tar xf mysql-router-8.0.23-linux-glibc2.12-x86_64.tar.xz -C /usr/local
cd /usr/local
mv mysql-router-8.0.23-linux-glibc2.12-x86_64 mysql-router

          修改配置文件

          首次启动

          192.168.252.148：

          cd /usr/local/mysql-router/bin
./mysqlrouter --bootstrap syh@192.168.252.148:3306 --user=root

          MySQL8.0高可用MIC的实现

          修改配置文件

          vim /usr/local/mysql-router/mysqlrouter.conf
#dynamic_state=/usr/local/mysql-router/bin/../var/lib/mysqlrouter/state.json
bootstrap_server_addresses=mysql://192.168.252.148:3306,mysql://192.168.252.142:3306,mysql://192.168.252.145:3306

          MySQL8.0高可用MIC的实现

          MySQL8.0高可用MIC的实现

          启动

          192.168.252.148：

          cd /usr/local/mysql-router/bin
./mysqlrouter -c ../mysqlrouter.conf &
netstat -tnpl

          MySQL8.0高可用MIC的实现

          停掉主库

          192.168.252.148：

          systemctl stop mysqld

          操作mysql-shell

          192.168.252.148：

          /usr/local/mysql-shell/bin/mysqlsh
\c syh@192.168.252.145:3306
var cluster=dba.getCluster();
cluster.status();

          MySQL8.0高可用MIC的实现

          192.168.252.148:

          systemctl start mysqld

          再次查看

          MySQL8.0高可用MIC的实现

          恢复！

          到此这篇关于MySQL8.0高可用MIC的实现的文章就介绍到这了,更多相关MySQL8.0高可用MIC内容请搜索编程客栈(www.cppcns.http://www.devze.comcom)以前的文章或继续浏览编程客栈下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！

          0

          上一篇:

          :下一篇

          更多 数据库 相关资讯：

          精彩评论

          暂无评论...
          登录 注册
          Qedev.com
          验证码 换一张
          取 消

          关注公众号

          热门标签

          图文推荐

          MySQL字符串日期格式转换的几种常用方法例子解析

          MySQL字符串日期格式转换的几种常用方法例子解析
          详解Sqlsugar调用Oracle的存储过程

          详解Sqlsugar调用Oracle的存储过程
          SpringBoot集成Mongodb的操作方法

          SpringBoot集成Mongodb的操作方法
          如何用PyMongo在Python中操作MongoDB的超完整指南

          如何用PyMongo在Python中操作MongoDB的超完整指南
          Vcenter清理/storage/archive空间的处理方式

          Vcenter清理/storage/archive空间的处理方式
          mysql插入带emoji表情的数据报错问题

          mysql插入带emoji表情的数据报错问题
          Mysql允许他人访问本机数据库的实现步骤

          Mysql允许他人访问本机数据库的实现步骤
          Docker安装MongoDB的过程(mongo.latest)

          Docker安装MongoDB的过程(mongo.latest)

          开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

          法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

          Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9