目录
另外两台也是这样安装，但也可以直接远程把配置好的数据库传输过去
注意从默认开启了写的功能，也就是可以在数据库写入数据，但不会在主上查看到，这会导致数据的不一致性
可以关闭该功能，在从的配置文件里添加super_read_only=on这个参数
此时在130主机这台新的从设备上就可以没看到数据，此时数据被拉平了
注意130主机设置了延迟复制，而不用配置多线程回放，即使配置了也没用
在主的主机的配置文件添加参数，千万不要重启，会报错，要安装插件
在slave数据库上，指定110为主机，并添加模块，重启io线程,(两台都做)
在master还正常工作的情况下，在mha里进行切换（选项都选yes）
之后再mha的主机上，此时mha就自动监测master，如果挂了会自动切换
如果故障的master要恢复，则也同样需要指定新master，以slave身份恢复
MySQL的介绍
MySQL是一个开源的关系型数据库管理系统，由瑞典MySQL AB公司开发，属于Oracle旗下产品。它是一个流行的关系型数据库，具有以下特点：
开源性：MySQL是一个开源的关系型数据库，允许用户自由使用和修改其源代码。
高性能：通过采用多种优化技术，如索引、查询缓存和高效的存储引擎，MySQL能够快速检索数据并处理高并发请求。
简单易用：MySQL易于安装和配置，具有良好的兼容性，支持多种操作系统，如Windows、Linux、macOS等。
安全性：提供多层次的安全措施，包括密码保护、基于角色的访问控制、SSL连接加密等，以保护数据免受未授权访问和安全威胁。
可扩展性：通过主次复制、分片和分区等技术，MySQL可以实现数据的扩展，从而提高系统的高可用性和性能。
跨平台性：MySQL可以在不同平台上运行，包括Windows、Linux、macOS等。
支持多种存储引擎：MySQL支持多种存储引擎，如InnoDB、MyISAM等，每种引擎都有特定的优势和用途。
此外，MySQL还支持SQL，这是一种用于处理关系型数据库中数据的高级非编程化语言，允许用户通过编写SQL语句来查询、更新和管理数据库中的数据。SQL的核心部分包括数据定义、数据操作和数据控制等功能，通过这些功能用户可以定义数据库结构、操作数据以及控制对数据的访问权限
基本原理
连接管理和安全性：MySQL服务器监听TCP/IP连接，每个客户端连接都会在服务器上有一个线程与之对应。
查询缓存：MySQL会缓存查询结果，如果查询被标记为可缓存，那么MySQL会在内存中保存这个查询的结果。
解析器和优化器：解析器负责理解查询的语法和语义，优化器负责制定查询计划。
查询优化：优化器会创建一个执行计划，它描述了如何在存储引擎中检索数据。
表管理和存储引擎：MySQL中的数据是由存储引擎处理的，常用的存储引擎包括InnoDB（事务型数据库）和MyISAM（非事务型数据库）。
事务和锁定：MySQL支持事务，允许多个事务同时对数据进行读写操作，通过锁定机制保持数据的一致性和完整性。
物理存储：MySQL将数据存储在文件系统中，通过表结构定义和存储引擎API与存储引擎通信。
复制和集群：MySQL支持主从复制和集群，以提供高可用性和扩展性
MySQL的部署
源码安装，从官网下载需要的版本，通过xftp等传输给虚拟机
解压
[root@mysql-1 ~]# tar zxf mysql-boost-5.7.44.tar.gz 安装编译时所需要的插件
[root@mysql-1 ~]# yum install cmake gcc-c++ openssl-devel ncurses-devel.x86_64 -y
已加载插件：langpacks, product-id, search-disabled-repos,
: subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
rhel7 | 2.8 kB 00:00
(1/2): rhel7/group | 628 kB 00:00
(2/2): rhel7/primary | 2.1 MB 00:00
rhel7 5230/5230
正在解决依赖关系
--> 正在检查事务
---> 软件包 cmake.x86_64.0.2.8.12.2-2.el7 将被 安装
--> 正在处理依赖关系 libarchive.so.13()(64bit)，它被软件包 cmake-2.8.12.2-2.el7.x86_64 需要
---> 软件包 gcc-c++.x86_64.0.4.8.5-44.el7 将被 安装
--> 正在处理依赖关系 libstdc++-devel = 4.8.5-44.el7，它被软件包 gcc-c++-4.8.5-44.el7.x86_64 需要
--> 正在处理依赖关系 gcc = 4.8.5-44.el7，它被软件包 gcc-c++-4.8.5-44.el7.x86_64 需要
--> 正在处理依赖关系 libmpfr.so.4()(64bit)，它被软件包 gcc-c++-4.8.5-44.el7.x86_64 需要
--> 正在处理依赖关系 libmpc.so.3()(64bit)，它被软件包 gcc-c++-4.8.5-44.el7.x86_64 需要
---> 软件包 ncurses-devel.x86_64.0.5.9-14.20130511.el7_4 将被 安装
---> 软件包 openssl-devel.x86_64.1.1.0.2k-19.el7 将被 安装
--> 正在处理依赖关系 zlib-devel(x86-64)，它被软件包 1:openssl-devel-1.0.2k-19.el7.x86_64 需要
--> 正在处理依赖关系 krb5-devel(x86-64)，它被软件包 1:openssl-devel-1.0.2k-19.el7.x86_64 需要
--> 正在检查事务
---> 软件包 gcc.x86_64.0.4.8.5-44.el7 将被 安装
--> 正在处理依赖关系 cpp = 4.8.5-44.el7，它被软件包 gcc-4.8.5-44.el7.x86_64 需要
--> 正在处理依赖关系 glibc-devel >= 2.2.90-12，它被软件包 gcc-4.8.5-44.el7.x86_64 需要
---> 软件包 krb5-devel.x86_64.0.1.15.1-50.el7 将被 安装
--> 正在处理依赖关系 libkadm5(x86-64) = 1.15.1-50.el7，它被软件包 krb5-devel-1.15.1-50.el7.x86_64 需要
--> 正在处理依赖关系 libverto-devel，它被软件包 krb5-devel-1.15.1-50.el7.x86_64 需要
--> 正在处理依赖关系 libselinux-devel，它被软件包 krb5-devel-1.15.1-50.el7.x86_64 需要
--> 正在处理依赖关系 libcom_err-devel，它被软件包 krb5-devel-1.15.1-50.el7.x86_64 需要
--> 正在处理依赖关系 keyutils-libs-devel，它被软件包 krb5-devel-1.15.1-50.el7.x86_64 需要
---> 软件包 libarchive.x86_64.0.3.1.2-14.el7_7 将被 安装
---> 软件包 libmpc.x86_64.0.1.0.1-3.el7 将被 安装
---> 软件包 libstdc++-devel.x86_64.0.4.8.5-44.el7 将被 安装
---> 软件包 mpfr.x86_64.0.3.1.1-4.el7 将被 安装
---> 软件包 zlib-devel.x86_64.0.1.2.7-18.el7 将被 安装
--> 正在检查事务
---> 软件包 cpp.x86_64.0.4.8.5-44.el7 将被 安装
---> 软件包 glibc-devel.x86_64.0.2.17-317.el7 将被 安装
--> 正在处理依赖关系 glibc-headers = 2.17-317.el7，它被软件包 glibc-devel-2.17-317.el7.x86_64 需要
--> 正在处理依赖关系 glibc-headers，它被软件包 glibc-devel-2.17-317.el7.x86_64 需要
---> 软件包 keyutils-libs-devel.x86_64.0.1.5.8-3.el7 将被 安装
---> 软件包 libcom_err-devel.x86_64.0.1.42.9-19.el7 将被 安装
---> 软件包 libkadm5.x86_64.0.1.15.1-50.el7 将被 安装
---> 软件包 libselinux-devel.x86_64.0.2.5-15.el7 将被 安装
--> 正在处理依赖关系 libsepol-devel(x86-64) >= 2.5-10，它被软件包 libselinux-devel-2.5-15.el7.x86_64 需要
--> 正在处理依赖关系 pkgconfig(libsepol)，它被软件包 libselinux-devel-2.5-15.el7.x86_64 需要
--> 正在处理依赖关系 pkgconfig(libpcre)，它被软件包 libselinux-devel-2.5-15.el7.x86_64 需要
---> 软件包 libverto-devel.x86_64.0.0.2.5-4.el7 将被 安装
--> 正在检查事务
---> 软件包 glibc-headers.x86_64.0.2.17-317.el7 将被 安装
--> 正在处理依赖关系 kernel-headers >= 2.2.1，它被软件包 glibc-headers-2.17-317.el7.x86_64 需要
--> 正在处理依赖关系 kernel-headers，它被软件包 glibc-headers-2.17-317.el7.x86_64 需要
---> 软件包 libsepol-devel.x86_64.0.2.5-10.el7 将被 安装
---> 软件包 pcre-devel.x86_64.0.8.32-17.el7 将被 安装
--> 正在检查事务
---> 软件包 kernel-headers.x86_64.0.3.10.0-1160.el7 将被 安装
--> 解决依赖关系完成
依赖关系解决
==========================================================================================================================================
Package 架构 版本 源 大小
==========================================================================================================================================
正在安装:
cmake x86_64 2.8.12.2-2.el7 rhel7 7.0 M
gcc-c++ x86_64 4.8.5-44.el7 rhel7 7.2 M
ncurses-devel x86_64 5.9-14.20130511.el7_4 rhel7 713 k
openssl-devel x86_64 1:1.0.2k-19.el7 rhel7 1.5 M
为依赖而安装:
cpp x86_64 4.8.5-44.el7 rhel7 6.0 M
gcc x86_64 4.8.5-44.el7 rhel7 16 M
glibc-devel x86_64 2.17-317.el7 rhel7 1.1 M
glibc-headers x86_64 2.17-317.el7 rhel7 690 k
kernel-headers x86_64 3.10.0-1160.el7 rhel7 9.0 M
keyutils-libs-devel x86_64 1.5.8-3.el7 rhel7 37 k
krb5-devel x86_64 1.15.1-50.el7 rhel7 273 k
libarchive x86_64 3.1.2-14.el7_7 rhel7 319 k
libcom_err-devel x86_64 1.42.9-19.el7 rhel7 32 k
libkadm5 x86_64 1.15.1-50.el7 rhel7 179 k
libmpc x86_64 1.0.1-3.el7 rhel7 51 k
libselinux-devel x86_64 2.5-15.el7 rhel7 187 k
libsepol-devel x86_64 2.5-10.el7 rhel7 77 k
libstdc++-devel x86_64 4.8.5-44.el7 rhel7 1.5 M
libverto-devel x86_64 0.2.5-4.el7 rhel7 12 k
mpfr x86_64 3.1.1-4.el7 rhel7 203 k
pcre-devel x86_64 8.32-17.el7 rhel7 480 k
zlib-devel x86_64 1.2.7-18.el7 rhel7 50 k
事务概要
==========================================================================================================================================
安装 4 软件包 (+18 依赖软件包)
总下载量：53 M
安装大小：120 M
Downloading packages:
------------------------------------------------------------------------------------------------------------------------------------------
总计 114 MB/s | 53 MB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : mpfr-3.1.1-4.el7.x86_64 1/22
正在安装 : libmpc-1.0.1-3.el7.x86_64 2/22
正在安装 : cpp-4.8.5-44.el7.x86_64 3/22
正在安装 : libcom_err-devel-1.42.9-19.el7.x86_64 4/22
正在安装 : libstdc++-devel-4.8.5-44.el7.x86_64 5/22
正在安装 : libsepol-devel-2.5-10.el7.x86_64 6/22
正在安装 : libarchive-3.1.2-14.el7_7.x86_64 7/22
正在安装 : libkadm5-1.15.1-50.el7.x86_64 8/22
正在安装 : zlib-devel-1.2.7-18.el7.x86_64 9/22
正在安装 : libverto-devel-0.2.5-4.el7.x86_64 10/22
正在安装 : keyutils-libs-devel-1.5.8-3.el7.x86_64 11/22
正在安装 : pcre-devel-8.32-17.el7.x86_64 12/22
正在安装 : libselinux-devel-2.5-15.el7.x86_64 13/22
正在安装 : krb5-devel-1.15.1-50.el7.x86_64 14/22
正在安装 : kernel-headers-3.10.0-1160.el7.x86_64 15/22
正在安装 : glibc-headers-2.17-317.el7.x86_64 16/22
正在安装 : glibc-devel-2.17-317.el7.x86_64 17/22
正在安装 : gcc-4.8.5-44.el7.x86_64 18/22
正在安装 : gcc-c++-4.8.5-44.el7.x86_64 19/22
正在安装 : 1:openssl-devel-1.0.2k-19.el7.x86_64 20/22
正在安装 : cmake-2.8.12.2-2.el7.x86_64 21/22
正在安装 : ncurses-devel-5.9-14.20130511.el7_4.x86_64 22/22
验证中 : gcc-c++-4.8.5-44.el7.x86_64 1/22
验证中 : kernel-headers-3.10.0-1160.el7.x86_64 2/22
验证中 : cmake-2.8.12.2-2.el7.x86_64 3/22
验证中 : glibc-devel-2.17-317.el7.x86_64 4/22
验证中 : pcre-devel-8.32-17.el7.x86_64 5/22
验证中 : libselinux-devel-2.5-15.el7.x86_64 6/22
验证中 : keyutils-libs-devel-1.5.8-3.el7.x86_64 7/22
验证中 : libverto-devel-0.2.5-4.el7.x86_64 8/22
验证中 : zlib-devel-1.2.7-18.el7.x86_64 9/22
验证中 : ncurses-devel-5.9-14.20130511.el7_4.x86_64 10/22
验证中 : cpp-4.8.5-44.el7.x86_64 11/22
验证中 : libkadm5-1.15.1-50.el7.x86_64 12/22
验证中 : libarchive-3.1.2-14.el7_7.x86_64 13/22
验证中 : glibc-headers-2.17-317.el7.x86_64 14/22
验证中 : gcc-4.8.5-44.el7.x86_64 15/22
验证中 : 1:openssl-devel-1.0.2k-19.el7.x86_64 16/22
验证中 : libmpc-1.0.1-3.el7.x86_64 17/22
验证中 : libsepol-devel-2.5-10.el7.x86_64 18/22
验证中 : krb5-devel-1.15.1-50.el7.x86_64 19/22
验证中 : mpfr-3.1.1-4.el7.x86_64 20/22
验证中 : libstdc++-devel-4.8.5-44.el7.x86_64 21/22
验证中 : libcom_err-devel-1.42.9-19.el7.x86_64 22/22
rhel7/productid | 1.6 kB 00:00:00
已安装:
cmake.x86_64 0:2.8.12.2-2.el7 gcc-c++.x86_64 0:4.8.5-44.el7 ncurses-devel.x86_64 0:5.9-14.20130511.el7_4
openssl-devel.x86_64 1:1.0.2k-19.el7
作为依赖被安装:
cpp.x86_64 0:4.8.5-44.el7 gcc.x86_64 0:4.8.5-44.el7 glibc-devel.x86_64 0:2.17-317.el7
glibc-headers.x86_64 0:2.17-317.el7 kernel-headers.x86_64 0:3.10.0-1160.el7 keyutils-libs-devel.x86_64 0:1.5.8-3.el7
krb5-devel.x86_64 0:1.15.1-50.el7 libarchive.x86_64 0:3.1.2-14.el7_7 libcom_err-devel.x86_64 0:1.42.9-19.el7
libkadm5.x86_64 0:1.15.1-50.el7 libmpc.x86_64 0:1.0.1-3.el7 libselinux-devel.x86_64 0:2.5-15.el7
libsepol-devel.x86_64 0:2.5-10.el7 libstdc++-devel.x86_64 0:4.8.5-44.el7 libverto-devel.x86_64 0:0.2.5-4.el7
mpfr.x86_64 0:3.1.1-4.el7 pcre-devel.x86_64 0:8.32-17.el7 zlib-devel.x86_64 0:1.2.7-18.el7
完毕！
[root@mysql-1 ~]# yum install libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm -y
已加载插件：langpacks, product-id, search-disabled-repos,
: subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
正在检查 libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm: libtirpc-devel-0.2.4-0.16.el7.x86_64
libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm 将被安装
正在解决依赖关系
--> 正在检查事务
---> 软件包 libtirpc-devel.x86_64.0.0.2.4-0.16.el7 将被 安装
--> 解决依赖关系完成
依赖关系解决
==============================================================
Package
架构 版本 源 大小
==============================================================
正在安装:
libtirpc-devel
x86_64 0.2.4-0.16.el7
/libtirpc-devel-0.2.4-0.16.el7.x86_64 214 k
事务概要
==============================================================
安装 1 软件包
总计：214 k
安装大小：214 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : libtirpc-devel-0.2.4-0.16.el7.x86_64 1/1
验证中 : libtirpc-devel-0.2.4-0.16.el7.x86_64 1/1
已安装:
libtirpc-devel.x86_64 0:0.2.4-0.16.el7
完毕！
指定模块
[root@mysql-1 ~]# cd mysql-5.7.44/
[root@mysql-1 mysql-5.7.44]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
> -DMYSQL_DATADIR=/data/mysql \
> -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DWITH_EXTRA_CHARSETS=all \
> -DDEFAULT_CHARSET=utf8mb4 \
> -DDEFAULT_COLLATION=utf8mb4_unicode_ci \
> -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/
检测环境，编译
root@mysql-1 mysql-5.7.44]# make -j2
[root@mysql-1 mysql-5.7.44# make install生成启动脚本
[root@mysql-1 ~]# cd /usr/local/mysql/support-files/
[root@mysql-1 support-files]# cp mysql.server /etc/init.d/mysqld
修改环境变量
[root@mysql-1 ~]# vim ~/.bash_profile
[root@mysql-1 ~]# source ~/.bash_profile 创建用户
[root@mysql-1 ~]# useradd -s /sbin/nologin -M mysql
创建目录给予权限
[root@mysql-1 ~]# mkdir /data/mysql -p
[root@mysql-1 ~]# chown mysql.mysql /data/mysql/
修改配置文件
[root@mysql-1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=110
初始化数据库，并启动
[root@mysql-1 ~]# mysqld --initialize --user=mysql
2024-08-29T08:20:41.224768Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-29T08:20:42.331220Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-29T08:20:42.494269Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-29T08:20:42.566460Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 951e174c-65df-11ef-ae8f-000c29f3b041.
2024-08-29T08:20:42.569056Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-29T08:20:42.716352Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-29T08:20:42.716382Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-29T08:20:42.717623Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-29T08:20:42.873194Z 1 [Note] A temporary password is generated for root@localhost: =+td.U9O+:bA
[root@mysql-1 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-1.err'.
. SUCCESS!
数据库安全初始化
[root@mysql-1 ~]# mysql_secure_installation
此时就可以登录进来了
[root@mysql-1 ~]# mysql -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.44 Source distribution
Copyright (c) 2000, 2023, 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 '\h' for help. Type '\c' to clear the current input statement.
另外两台也是这样安装，但也可以直接远程把配置好的数据库传输过去
在已安装mysql的110主机上
[root@mysql-1 ~]# rsync -al /usr/local/mysql root@172.25.254.120:/usr/local/
The authenticity of host '172.25.254.120 (172.25.254.120)' can't be established.
ECDSA key fingerprint is SHA256:pS8ccv8gcPFiUThX6snvNIjIMzT4AeMwxULyTuEVIdE.
ECDSA key fingerprint is MD5:4d:86:cc:f2:a0:b9:ac:49:76:43:7e:9e:99:52:17:9b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.254.120' (ECDSA) to the list of known hosts.
root@172.25.254.120's password: 在120主机上
[root@mysql-2 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql-2 ~]# mkdir -p /data/mysql
[root@mysql-2 ~]# chown mysql.mysql /data/mysql/
[root@mysql-2 ~]# vim /etc/my.cnf
初始化
[root@mysql-2 ~]# mysqld --initialize --user=mysql
2024-08-29T08:40:22.309359Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-29T08:40:22.640573Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-29T08:40:22.684978Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-29T08:40:22.741280Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 548e7a42-65e2-11ef-8439-000c295d6781.
2024-08-29T08:40:22.743095Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-29T08:40:23.243517Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-29T08:40:23.243562Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-29T08:40:23.244158Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-29T08:40:23.323594Z 1 [Note] A temporary password is generated for root@localhost: fJyOWlROB9*6
启动服务
[root@mysql-2 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-2.err'.
SUCCESS!
安全初始化
[root@mysql-2 ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
The existing password for the user account root has expired. Please set a new password.
New password:
Re-enter new password:
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: no
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : no
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!另一台同理，不做演示
MySQL的主从复制
在主里修改配置文件，并重启服务
[root@mysql-1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=110
log-bin=mysql-bin
[root@mysql-1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!在数据库里配置用户权限
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'lee';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)
可以查看
在从的数据库里指定主的主机
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.110',MASTER_USEER='repl',MASTER_PASSWORD='lee',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=595;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
开启从
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
可以查看状态，可以看出此时的主为110主机
测试
在主上创建库和表，并插入数据
mysql> create database mqw;
Query OK, 1 row affected (0.00 sec)
mysql> create table mqw.userlist ( username varchar(20) not null,password varchar(50) not null );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO mqw.userlist VALUE ('mqw','123');
Query OK, 1 row affected (0.02 sec)
mysql> select * from mqw.userlist;
+----------+----------+
| username | password |
+----------+----------+
| mqw | 123 |
+----------+----------+
1 row in set (0.00 sec)
而在从的主机数据库上可以查看到
注意从默认开启了写的功能，也就是可以在数据库写入数据，但不会在主上查看到，这会导致数据的不一致性
可以关闭该功能，在从的配置文件里添加super_read_only=on这个参数
备份数据和数据拉平
在主上备份数据
[root@mysql-1 ~]# mysqldump -uroot -p mqw > mqw.sql
Enter password: 复制到新的主机上
[root@mysql-1 ~]# scp mqw.sql root@172.25.254.130:/mnt/
在新的从主机上拉平数据
[root@mysql-3 mnt]# mysql -uroot -p123 -e "create database mqw;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-3 mnt]# mysql -uroot -p123 mqw < mqw.sql
mysql: [Warning] Using a password on the command line interface can be insecure.此时在130主机这台新的从设备上就可以没看到数据，此时数据被拉平了
在指定主的主机
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.110',
MASTER_USSER='repl',MASTER_PASSWORD='lee', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1237;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
pos是在主的主机上查看的
开启功能
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
查看
延迟复制
在从上配置
查看状态，此时该从主机就会延迟60秒在复制主的数据
慢日志查询
开启慢日志
mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec可以查看，此时慢日志开启了
mysql> SHOW VARIABLES like "slow%";
+---------------------+------------------------------+
| Variable_name | Value |
+---------------------+------------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/mysql-2-slow.log |
+---------------------+------------------------------+
3 rows in set (0.00 sec)此时就会对查询超过10秒的查询进行记录
mysql> SHOW VARIABLES like "long%";
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql的并行复制（多线程回放）
注意130主机设置了延迟复制，而不用配置多线程回放，即使配置了也没用
在从上修改配置文件 ，并重启
[root@mysql-2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=120
gtid_mode=ON
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
[root@mysql-2 ~]# /etc/init.d/mysqld restart 查看
gitd模式
在所有主机的配置文件里都添加参数，并重启
[root@mysql-1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=110
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
[root@mysql-1 ~]# /etc/init.d/mysqld restart
在从上停止slave，重新指定开启gtid功能，两台从都做
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.110',MASTER_USER='repl',MASTER_PASSWORD='lee',MASTER_AUTO_POSITION=1;
mysql> start slave;
查看状态，功能以开启
MySQL的半同步模式
在主的主机的配置文件添加参数，千万不要重启，会报错，要安装插件
[root@mysql-1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=110
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1
在数据库里安装半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
查看插件情况
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
-> FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.00 sec)打开半同步功能
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
可以查看半同步功能状态，此时已经开启
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 se从和主的配置一样，但从要重启io线程
[root@mysql-3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=130
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1
在数据库里安装插件，并开启
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)
重启io线程
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
查看状态，此时表示开启了
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
另一台 同理，不演示
测试
在从上都关闭io线程
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)此时在主上写入数据，他就会一直卡住，并超时
当从的io线程开启后，就会恢复
MySQL高可用组复制（MGR）
MHA（Master High Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。 MHA 的出现就是解决MySQL 单点的问题。 MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。 MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用
MHA 的组成 MHA由两部分组成:MHAManager (管理节点) MHA Node (数据库节点), MHA Manager 可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台 slave 节点上。 MHA Manager 会定时探测集群中的 master 节点。 当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master， 然后将所有其他的 slave 重新指向新的 master
MHA 的特点 自动故障切换过程中，MHA从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失 使用半同步复制，可以大大降低数据丢失的风险，如果只有一个slave已经收到了最新的二进制日 志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数 据一致性 目前MHA支持一主多从架构，最少三台服务，即一主两从
部署
把之前的配置删除重来，从也要做
[root@mysql-1 ~]# /etc/init.d/mysqld stop
[root@mysql-1 ~]# rm -fr /data/mysql/*
修改配置文件，从也要做
[root@mysql-1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=110
gtid_mode=ON
enforce-gtid-consistency=ON
初始化，从也要做
[root@mysql-1 ~]# mysqld --user=mysql --initialize
2024-09-05T18:07:44.009040Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-09-05T18:07:44.251353Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-09-05T18:07:44.280760Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-09-05T18:07:44.340110Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: bfd27c85-6bb1-11ef-a0d3-000c29f3b041.
2024-09-05T18:07:44.341239Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-09-05T18:07:44.751886Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-09-05T18:07:44.751905Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-09-05T18:07:44.752381Z 0 [Warning] CA certificate ca.pem is self signed.
2024-09-05T18:07:44.807013Z 1 [Note] A temporary password is generated for root@localhost: AQehmp%*x1s=
开启服务，slave也做
[root@mysql-1 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-1.err'.
SUCCESS!
安全初始化（修改数据库密码）slave也做
[root@mysql-1 ~]# mysql_secure_installation
登录数据库，创建用户，并给于权限，添加模块
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'lee';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
在slave数据库上，指定110为主机，并添加模块，重启io线程,(两台都做)
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.110',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='lee',
-> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
可查看是否开启
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
重启克隆出一台主机，用来做mha
在网上下载，通过xftp等传输给Linux
解压
[root@mha ~]# unzip MHA-7.zip
Archive: MHA-7.zip
creating: MHA-7/
inflating: MHA-7/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
inflating: MHA-7/mha4mysql-manager-0.58.tar.gz
inflating: MHA-7/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
inflating: MHA-7/perl-Config-Tiny-2.14-7.el7.noarch.rpm
inflating: MHA-7/perl-Email-Date-Format-1.002-15.el7.noarch.rpm
inflating: MHA-7/perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
inflating: MHA-7/perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
inflating: MHA-7/perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
inflating: MHA-7/perl-MIME-Lite-3.030-1.el7.noarch.rpm
inflating: MHA-7/perl-MIME-Types-1.38-2.el7.noarch.rpm
inflating: MHA-7/perl-Net-Telnet-3.03-19.el7.noarch.rpm
inflating: MHA-7/perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
在MHA上生成密钥，并传输给其他主机（其他两台也要传输）
[root@mha ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:WFC22qV4QVypMsAfUFzg+HwlHt3oxQPX18058Mm6oLQ root@mha
The key's randomart image is:
+---[RSA 2048]----+
| ..++==.o..o..+|
| ooo+.o.* +o*|
| .o..*.= = =.|
| o+B.O . .. |
| *oS... . |
| o. o . . |
| E . |
| |
| |
+----[SHA256]-----+
[root@mha ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.110
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '172.25.254.110 (172.25.254.110)' can't be established.
ECDSA key fingerprint is SHA256:pS8ccv8gcPFiUThX6snvNIjIMzT4AeMwxULyTuEVIdE.
ECDSA key fingerprint is MD5:4d:86:cc:f2:a0:b9:ac:49:76:43:7e:9e:99:52:17:9b.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@172.25.254.110's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'root@172.25.254.110'"
and check to make sure that only the key(s) you wanted were added.
安装软件包
[root@mha ~]# cd MHA-7/
[root@mha MHA-7]# yum install *.rpm -y
在主上允许登录远程用户，主配置了，从就不用配置了
mysql> create user root@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant ALL on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)
检测
[root@mysql-3 ~]# mysql -p123 -h172.25.254.110
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.44-log Source distribution
Copyright (c) 2000, 2023, 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 '\h' for help. Type '\c' to clear the current input statement.
mysql>
把模块复制到其他主机
[root@mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.110:/mnt
mha4mysql-node-0.58-0.el7.c 100% 35KB 16.8MB/s 00:00
[root@mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.120:/mnt
mha4mysql-node-0.58-0.el7.c 100% 35KB 23.2MB/s 00:00
[root@mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.130:/mnt
mha4mysql-node-0.58-0.el7.c 100% 35KB 15.2MB/s 00:00 在主机中安装该模块 （都要）
[root@mysql-1 mnt]# yum install *rpm -y
创建目录，解压模块，复制（生成配置文件）
[root@mha ~]# mkdir /etc/masterha
[root@mha ~]# cd MHA-7/
[root@mha MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz
[root@mha MHA-7]# cd mha4mysql-manager-0.58/samples/conf/
[root@mha conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app1.cnf
编辑配置文件
[root@mha ~]# vim /etc/masterha/app1.cnf
[server default]
user=root
password=123
ssh_user=root
master_binlog_dir= /data/mysql
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 172.25.254.110 -s 172.25.254.121
ping_interval=3
# master_ip_failover_script= /script/masterha/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
# master_ip_online_change_script= /script/masterha/master_ip_online_change
[server default]
manager_workdir=/var/log/masterha/app1
manager_log=/etc/masterha/app1/manager.log
[server1]
hostname=172.25.254.110
candidate_master=1
[server2]
hostname=172.25.254.120
candidate_master=1
check_repl_delay=0
[server3]
hostname=172.25.254.130
no_master=1
~ 在120主机上添加一个子接口
[root@mysql-2 ~]# ip a a 172.25.254.121/24 dev eth0
为了方便，直接把私钥传给其他主机，
[root@mha ~]# cd .ssh/
[root@mha .ssh]# scp id_rsa root@172.25.254.110:/root/.ssh/
id_rsa 100% 1675 1.7MB/s 00:00
[root@mha .ssh]# scp id_rsa root@172.25.254.120:/root/.ssh/
id_rsa 100% 1675 1.1MB/s 00:00
[root@mha .ssh]# scp id_rsa root@172.25.254.130:/root/.ssh/
id_rsa 100% 1675 1.6MB/s 00:00 检测网络及ssh免密，此时为成功
[root@mha ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
Fri Sep 6 03:23:38 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Sep 6 03:23:38 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Fri Sep 6 03:23:38 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Fri Sep 6 03:23:38 2024 - [info] Starting SSH connection tests..
Fri Sep 6 03:24:00 2024 - [debug]
Fri Sep 6 03:23:39 2024 - [debug] Connecting via SSH from root@172.25.254.120(172.25.254.120:22) to root@172.25.254.110(172.25.254.110:22)..
Warning: Permanently added '172.25.254.110' (ECDSA) to the list of known hosts.
Fri Sep 6 03:23:49 2024 - [debug] ok.
Fri Sep 6 03:23:49 2024 - [debug] Connecting via SSH from root@172.25.254.120(172.25.254.120:22) to root@172.25.254.130(172.25.254.130:22)..
Fri Sep 6 03:23:59 2024 - [debug] ok.
Fri Sep 6 03:24:00 2024 - [debug]
Fri Sep 6 03:23:38 2024 - [debug] Connecting via SSH from root@172.25.254.110(172.25.254.110:22) to root@172.25.254.120(172.25.254.120:22)..
Fri Sep 6 03:23:49 2024 - [debug] ok.
Fri Sep 6 03:23:49 2024 - [debug] Connecting via SSH from root@172.25.254.110(172.25.254.110:22) to root@172.25.254.130(172.25.254.130:22)..
Fri Sep 6 03:23:59 2024 - [debug] ok.
Fri Sep 6 03:24:01 2024 - [debug]
Fri Sep 6 03:23:39 2024 - [debug] Connecting via SSH from root@172.25.254.130(172.25.254.130:22) to root@172.25.254.110(172.25.254.110:22)..
Warning: Permanently added '172.25.254.110' (ECDSA) to the list of known hosts.
Fri Sep 6 03:23:50 2024 - [debug] ok.
Fri Sep 6 03:23:50 2024 - [debug] Connecting via SSH from root@172.25.254.130(172.25.254.130:22) to root@172.25.254.120(172.25.254.120:22)..
Warning: Permanently added '172.25.254.120' (ECDSA) to the list of known hosts.
Fri Sep 6 03:24:00 2024 - [debug] ok.
Fri Sep 6 03:24:01 2024 - [info] All SSH connection tests passed successfully.
在两台从上也开启log-bin=mysql-bin
[root@mysql-2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=120
gtid_mode=ON
enforce-gtid-consistency=ON
log-bin=mysql-bin
~
[root@mysql-2 ~]# /etc/init.d/mysqld restart
检测数据主从复制情况
[root@mha ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf
Fri Sep 6 03:31:09 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Sep 6 03:31:09 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Fri Sep 6 03:31:09 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Fri Sep 6 03:31:09 2024 - [info] MHA::MasterMonitor version 0.58.
Creating directory /var/log/masterha/app1.. done.
Fri Sep 6 03:31:11 2024 - [info] GTID failover mode = 1
Fri Sep 6 03:31:11 2024 - [info] Dead Servers:
Fri Sep 6 03:31:11 2024 - [info] Alive Servers:
Fri Sep 6 03:31:11 2024 - [info] 172.25.254.110(172.25.254.110:3306)
Fri Sep 6 03:31:11 2024 - [info] 172.25.254.120(172.25.254.120:3306)
Fri Sep 6 03:31:11 2024 - [info] 172.25.254.130(172.25.254.130:3306)
Fri Sep 6 03:31:11 2024 - [info] Alive Slaves:
Fri Sep 6 03:31:11 2024 - [info] 172.25.254.120(172.25.254.120:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Fri Sep 6 03:31:11 2024 - [info] GTID ON
Fri Sep 6 03:31:11 2024 - [info] Replicating from 172.25.254.110(172.25.254.110:3306)
Fri Sep 6 03:31:11 2024 - [info] Primary candidate for the new Master (candidate_master is set)
Fri Sep 6 03:31:11 2024 - [info] 172.25.254.130(172.25.254.130:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Fri Sep 6 03:31:11 2024 - [info] GTID ON
Fri Sep 6 03:31:11 2024 - [info] Replicating from 172.25.254.110(172.25.254.110:3306)
Fri Sep 6 03:31:11 2024 - [info] Not candidate for the new Master (no_master is set)
Fri Sep 6 03:31:11 2024 - [info] Current Alive Master: 172.25.254.110(172.25.254.110:3306)
Fri Sep 6 03:31:11 2024 - [info] Checking slave configurations..
Fri Sep 6 03:31:11 2024 - [info] read_only=1 is not set on slave 172.25.254.120(172.25.254.120:3306).
Fri Sep 6 03:31:11 2024 - [info] read_only=1 is not set on slave 172.25.254.130(172.25.254.130:3306).
Fri Sep 6 03:31:11 2024 - [info] Checking replication filtering settings..
Fri Sep 6 03:31:11 2024 - [info] binlog_do_db= , binlog_ignore_db=
Fri Sep 6 03:31:11 2024 - [info] Replication filtering check ok.
Fri Sep 6 03:31:11 2024 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Fri Sep 6 03:31:11 2024 - [info] Checking SSH publickey authentication settings on the current master..
Fri Sep 6 03:31:11 2024 - [info] HealthCheck: SSH to 172.25.254.110 is reachable.
Fri Sep 6 03:31:11 2024 - [info]
172.25.254.110(172.25.254.110:3306) (current master)
+--172.25.254.120(172.25.254.120:3306)
+--172.25.254.130(172.25.254.130:3306)
Fri Sep 6 03:31:11 2024 - [info] Checking replication health on 172.25.254.120..
Fri Sep 6 03:31:11 2024 - [info] ok.
Fri Sep 6 03:31:11 2024 - [info] Checking replication health on 172.25.254.130..
Fri Sep 6 03:31:11 2024 - [info] ok.
Fri Sep 6 03:31:11 2024 - [warning] master_ip_failover_script is not defined.
Fri Sep 6 03:31:11 2024 - [warning] shutdown_script is not defined.
Fri Sep 6 03:31:11 2024 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
MHA的故障切换
在mha的配置文件里添加MySQL主从复制的用户和密码
手动故障切换
在master还正常工作的情况下，在mha里进行切换（选项都选yes）
[root@mha ~]# masterha_master_switch \
> --conf=/etc/masterha/app1.cnf \
> --master_state=alive \
> --new_master_host=172.25.254.120 \
> --new_master_port=3306 \
> --orig_master_is_new_slave \
> --running_updates_limit=10000
此时查看110主机时，已经是从，主变成了120主机
如果要切换回来则在mha里把IP换成110主机，其他不变
故障切换
把master的主机停掉，模拟故障
[root@mysql-1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
此时在mha的主机上切换（选线都选yes）
[root@mha ~]# masterha_master_switch \
> --master_state=dead \
> --conf=/etc/masterha/app1.cnf \
> --dead_master_host=172.25.254.110 \
> --dead_master_port=3306 \
> --new_master_host=172.25.254.120 \
> --new_master_port=3306 \
> --ignore_last_failover
检查，此时130主机的master被指定到120主机上
如果要恢复110主机，先启动，在指定master
[root@mysql-1 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.120',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
此时110主机就会以slave的身份恢复
自动切换
在配置是要先看看是否有锁文件，要把它删掉
之后再mha的主机上，此时mha就自动监测master，如果挂了会自动切换
[root@mysql-mha ]# masterha_manager --conf=/etc/masterha/app1.cn开启自动监测后会生成日志，可以查看
[root@mysql-mha masterha]# cat /etc/masterha/manager.log如果故障的master要恢复，则也同样需要指定新master，以slave身份恢复
mha添加VIP功能
传输文件
并把两个文件放到bin底下，给予执行权限
[root@mha ~]# cp master_ip_failover master_ip_online_change /usr/local/bin/
[root@mha ~]# chmod +x /usr/local/bin/master_ip_*
修改文件的内容
[root@mha ~]# vim /usr/local/bin/master_ip_failover 
[root@mha ~]# vim /usr/local/bin/master_ip_online_change 
在配置文件里添加脚本位置，进行调用
[root@mha ~]# vim /etc/masterha/app1.cnf
在master上添加VIP
[root@mysql-2 ~]# ip a a 172.25.254.100/24 dev eth0
启动监控，并打入后台
[root@mysql-mha ~]# masterha_manager --conf=/etc/masterha/app1.cnf & 关闭master的服务
[root@mysql-1 ~]# /etc/init.d/mysqld stop 
扫一扫
2075
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
