MySQL集群架构

解压

[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 

此时VIP就会指定跑到新选取出来的master的主机上