• 【Mysql MHA】CentOS7.6+Mysql8.0.16 入坑


    1.防火墙

    firewall-cmd --add-port=3306/tcp --permanent
    firewall-cmd --reload

    2.SELINUX

    sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
    setenforce 0

    3.软件包需求

    yum install -y perl-Class-Load.noarch perl-DBD-MySQL
    
    
    #以下需下载,地址: https://pkgs.org/
    yum install -y perl-Log-Dispatch-2.41-1.of.el7.noarch.rpm   
    yum install -y perl-Config-Tiny-2.14-7.el7.noarch.rpm
    yum install -y perl-Parallel-ForkManager-1.06-1.of.el7.noarch.rpm
    
    #MHA包需下载,地址 https://github.com/yoshinorim
    mha4mysql-manager-0.58-0.el7.centos.noarch.rpm #建议安装在独立服务器,也可选择任一从库安装
    mha4mysql-node-0.58-0.el7.centos.noarch.rpm #安装在所有节点

    4.密码认证插件需要用 mysql_native_password

    [mysqld]
    default_authentication_plugin=mysql_native_password
    
    alter user rep identified with 'mysql_native_password' by 'rep';
    alter user root identified with 'mysql_native_password' by 'root ';

    5. 检查配置是否成功

    建议所有从库都置为只读模式 read_only=1,但这实际并不影响MHA搭建。

    [root@mysql3 soft]# masterha_check_repl --conf=/etc/masterha_default.cnf 
    Mon Jun 10 05:38:47 2019 - [info] Reading default configuration from /etc/masterha_default.cnf..
    Mon Jun 10 05:38:47 2019 - [info] Reading application default configuration from /etc/masterha_default.cnf..
    Mon Jun 10 05:38:47 2019 - [info] Reading server configuration from /etc/masterha_default.cnf..
    Mon Jun 10 05:38:47 2019 - [info] MHA::MasterMonitor version 0.58.
    Mon Jun 10 05:38:47 2019 - [info] GTID failover mode = 1
    Mon Jun 10 05:38:47 2019 - [info] Dead Servers:
    Mon Jun 10 05:38:47 2019 - [info] Alive Servers:
    Mon Jun 10 05:38:47 2019 - [info]   mysql1(192.168.20.81:3306)
    Mon Jun 10 05:38:47 2019 - [info]   mysql2(192.168.20.82:3306)
    Mon Jun 10 05:38:47 2019 - [info]   mysql3(192.168.20.83:3306)
    Mon Jun 10 05:38:47 2019 - [info]   mysql4(192.168.20.84:3306)
    Mon Jun 10 05:38:47 2019 - [info] Alive Slaves:
    Mon Jun 10 05:38:47 2019 - [info]   mysql2(192.168.20.82:3306)  Version=8.0.16 (oldest major version between slaves) log-bin:enabled
    Mon Jun 10 05:38:47 2019 - [info]     GTID ON
    Mon Jun 10 05:38:47 2019 - [info]     Replicating from 192.168.20.81(192.168.20.81:3306)
    Mon Jun 10 05:38:47 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
    Mon Jun 10 05:38:47 2019 - [info]   mysql3(192.168.20.83:3306)  Version=8.0.16 (oldest major version between slaves) log-bin:enabled
    Mon Jun 10 05:38:47 2019 - [info]     GTID ON
    Mon Jun 10 05:38:47 2019 - [info]     Replicating from 192.168.20.81(192.168.20.81:3306)
    Mon Jun 10 05:38:47 2019 - [info]     Not candidate for the new Master (no_master is set)
    Mon Jun 10 05:38:47 2019 - [info]   mysql4(192.168.20.84:3306)  Version=8.0.16 (oldest major version between slaves) log-bin:enabled
    Mon Jun 10 05:38:47 2019 - [info]     GTID ON
    Mon Jun 10 05:38:47 2019 - [info]     Replicating from 192.168.20.81(192.168.20.81:3306)
    Mon Jun 10 05:38:47 2019 - [info]     Not candidate for the new Master (no_master is set)
    Mon Jun 10 05:38:47 2019 - [info] Current Alive Master: mysql1(192.168.20.81:3306)
    Mon Jun 10 05:38:47 2019 - [info] Checking slave configurations..
    Mon Jun 10 05:38:47 2019 - [info]  read_only=1 is not set on slave mysql2(192.168.20.82:3306).
    Mon Jun 10 05:38:47 2019 - [info] Checking replication filtering settings..
    Mon Jun 10 05:38:47 2019 - [info]  binlog_do_db= , binlog_ignore_db= 
    Mon Jun 10 05:38:47 2019 - [info]  Replication filtering check ok.
    Mon Jun 10 05:38:47 2019 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
    Mon Jun 10 05:38:47 2019 - [info] Checking SSH publickey authentication settings on the current master..
    Mon Jun 10 05:38:47 2019 - [info] HealthCheck: SSH to mysql1 is reachable.
    Mon Jun 10 05:38:47 2019 - [info] 
    mysql1(192.168.20.81:3306) (current master)
     +--mysql2(192.168.20.82:3306)
     +--mysql3(192.168.20.83:3306)
     +--mysql4(192.168.20.84:3306)
    
    Mon Jun 10 05:38:47 2019 - [info] Checking replication health on mysql2..
    Mon Jun 10 05:38:47 2019 - [info]  ok.
    Mon Jun 10 05:38:47 2019 - [info] Checking replication health on mysql3..
    Mon Jun 10 05:38:47 2019 - [info]  ok.
    Mon Jun 10 05:38:47 2019 - [info] Checking replication health on mysql4..
    Mon Jun 10 05:38:47 2019 - [info]  ok.
    Mon Jun 10 05:38:47 2019 - [warning] master_ip_failover_script is not defined.
    Mon Jun 10 05:38:47 2019 - [warning] shutdown_script is not defined.
    Mon Jun 10 05:38:47 2019 - [info] Got exit code 0 (Not master dead).
    
    MySQL Replication Health is OK.

    6.以上我用的GTID 复制模式

    [mysqld]
    enforce_gtid_consistency=ON
    gtid_mode=on
    --从库执行如下命令,启动复制
    change master to master_host='192.168.20.81', master_port=3306, master_auto_position=1; start slave user='rep' password='rep';

    7.MHA 相关命令

    masterha_check_ssh --conf=/etc/masterha_default.cnf
    masterha_check_repl --conf=/etc/masterha_default.cnf
    
    masterha_master_switch        #可用来执行 fail over/online master switch
    # For master failover
    masterha_master_switch --master_state=dead
        --global_conf=/etc/masterha_default.cnf
        --conf=/usr/local/masterha/conf/app1.cnf --dead_master_host=host1
    # For online master switch
        masterha_master_switch --master_state=alive
        --global_conf=/etc/masterha_default.cnf
        --conf=/usr/local/masterha/conf/app1.cnf
        
    masterha_master_monitor --global_conf=/etc/masterha_default.cnf --conf=/usr/local/masterha/app1.cnf
    
    masterha_secondary_check -s secondary_host1 -s secondary_host2 .. --user=ssh_username --master_host=host --master_ip=ip --master_port=port
    
    masterha_manager --global_conf=/etc/masterha_default.cnf --conf=/usr/local/masterha/conf/app1.cnf

    # 以上的--global_conf=/etc/masterha_default.cnf 参数均可省略

    8.MHA 在线切换测试

    [root@mysql3 soft]# masterha_master_switch --master_state=alive --conf=/etc/masterha_default.cnf 
    Mon Jun 10 06:33:03 2019 - [info] MHA::MasterRotate version 0.58.
    Mon Jun 10 06:33:03 2019 - [info] Starting online master switch..
    Mon Jun 10 06:33:03 2019 - [info] 
    Mon Jun 10 06:33:03 2019 - [info] * Phase 1: Configuration Check Phase..
    Mon Jun 10 06:33:03 2019 - [info] 
    Mon Jun 10 06:33:03 2019 - [info] Reading default configuration from /etc/masterha_default.cnf..
    Mon Jun 10 06:33:03 2019 - [info] Reading application default configuration from /etc/masterha_default.cnf..
    Mon Jun 10 06:33:03 2019 - [info] Reading server configuration from /etc/masterha_default.cnf..
    Mon Jun 10 06:33:03 2019 - [info] GTID failover mode = 1
    Mon Jun 10 06:33:03 2019 - [info] Current Alive Master: mysql1(192.168.20.81:3306)
    Mon Jun 10 06:33:03 2019 - [info] Alive Slaves:
    Mon Jun 10 06:33:03 2019 - [info]   mysql2(192.168.20.82:3306)  Version=8.0.16 (oldest major version between slaves) log-bin:enabled
    Mon Jun 10 06:33:03 2019 - [info]     GTID ON
    Mon Jun 10 06:33:03 2019 - [info]     Replicating from 192.168.20.81(192.168.20.81:3306)
    Mon Jun 10 06:33:03 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
    Mon Jun 10 06:33:03 2019 - [info]   mysql3(192.168.20.83:3306)  Version=8.0.16 (oldest major version between slaves) log-bin:enabled
    Mon Jun 10 06:33:03 2019 - [info]     GTID ON
    Mon Jun 10 06:33:03 2019 - [info]     Replicating from 192.168.20.81(192.168.20.81:3306)
    Mon Jun 10 06:33:03 2019 - [info]     Not candidate for the new Master (no_master is set)
    Mon Jun 10 06:33:03 2019 - [info]   mysql4(192.168.20.84:3306)  Version=8.0.16 (oldest major version between slaves) log-bin:enabled
    Mon Jun 10 06:33:03 2019 - [info]     GTID ON
    Mon Jun 10 06:33:03 2019 - [info]     Replicating from 192.168.20.81(192.168.20.81:3306)
    Mon Jun 10 06:33:03 2019 - [info]     Not candidate for the new Master (no_master is set)
    
    It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on mysql1(192.168.20.81:3306)? (YES/no): YES
    Mon Jun 10 06:34:00 2019 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
    Mon Jun 10 06:34:00 2019 - [info]  ok.
    Mon Jun 10 06:34:00 2019 - [info] Checking MHA is not monitoring or doing failover..
    Mon Jun 10 06:34:00 2019 - [info] Checking replication health on mysql2..
    Mon Jun 10 06:34:00 2019 - [info]  ok.
    Mon Jun 10 06:34:00 2019 - [info] Checking replication health on mysql3..
    Mon Jun 10 06:34:00 2019 - [info]  ok.
    Mon Jun 10 06:34:00 2019 - [info] Checking replication health on mysql4..
    Mon Jun 10 06:34:00 2019 - [info]  ok.
    Mon Jun 10 06:34:00 2019 - [info] Searching new master from slaves..
    Mon Jun 10 06:34:00 2019 - [info]  Candidate masters from the configuration file:
    Mon Jun 10 06:34:00 2019 - [info]   mysql1(192.168.20.81:3306)  Version=8.0.16 log-bin:enabled
    Mon Jun 10 06:34:00 2019 - [info]     GTID ON
    Mon Jun 10 06:34:00 2019 - [info]   mysql2(192.168.20.82:3306)  Version=8.0.16 (oldest major version between slaves) log-bin:enabled
    Mon Jun 10 06:34:00 2019 - [info]     GTID ON
    Mon Jun 10 06:34:00 2019 - [info]     Replicating from 192.168.20.81(192.168.20.81:3306)
    Mon Jun 10 06:34:00 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
    Mon Jun 10 06:34:00 2019 - [info]  Non-candidate masters:
    Mon Jun 10 06:34:00 2019 - [info]   mysql3(192.168.20.83:3306)  Version=8.0.16 (oldest major version between slaves) log-bin:enabled
    Mon Jun 10 06:34:00 2019 - [info]     GTID ON
    Mon Jun 10 06:34:00 2019 - [info]     Replicating from 192.168.20.81(192.168.20.81:3306)
    Mon Jun 10 06:34:00 2019 - [info]     Not candidate for the new Master (no_master is set)
    Mon Jun 10 06:34:00 2019 - [info]   mysql4(192.168.20.84:3306)  Version=8.0.16 (oldest major version between slaves) log-bin:enabled
    Mon Jun 10 06:34:00 2019 - [info]     GTID ON
    Mon Jun 10 06:34:00 2019 - [info]     Replicating from 192.168.20.81(192.168.20.81:3306)
    Mon Jun 10 06:34:00 2019 - [info]     Not candidate for the new Master (no_master is set)
    Mon Jun 10 06:34:00 2019 - [info]  Searching from candidate_master slaves which have received the latest relay log events..
    Mon Jun 10 06:34:00 2019 - [info] 
    From:
    mysql1(192.168.20.81:3306) (current master)
     +--mysql2(192.168.20.82:3306)
     +--mysql3(192.168.20.83:3306)
     +--mysql4(192.168.20.84:3306)
    
    To:
    mysql2(192.168.20.82:3306) (new master)
     +--mysql3(192.168.20.83:3306)
     +--mysql4(192.168.20.84:3306)
    
    Starting master switch from mysql1(192.168.20.81:3306) to mysql2(192.168.20.82:3306)? (yes/NO): yes
    Mon Jun 10 06:35:02 2019 - [info] Checking whether mysql2(192.168.20.82:3306) is ok for the new master..
    Mon Jun 10 06:35:02 2019 - [info]  ok.
    Mon Jun 10 06:35:02 2019 - [info] ** Phase 1: Configuration Check Phase completed.
    Mon Jun 10 06:35:02 2019 - [info] 
    Mon Jun 10 06:35:02 2019 - [info] * Phase 2: Rejecting updates Phase..
    Mon Jun 10 06:35:02 2019 - [info] 
    FLUSH NO_WRITE_TO_BINLOG TABLES is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
    Mon Jun 10 06:35:53 2019 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
    Mon Jun 10 06:35:53 2019 - [info] Executing FLUSH TABLES WITH READ LOCK..
    Mon Jun 10 06:35:53 2019 - [info]  ok.
    Mon Jun 10 06:35:53 2019 - [info] Orig master binlog:pos is binlog.000017:1285.
    Mon Jun 10 06:35:53 2019 - [info]  Waiting to execute all relay logs on mysql2(192.168.20.82:3306)..
    Mon Jun 10 06:35:53 2019 - [info]  master_pos_wait(binlog.000017:1285) completed on mysql2(192.168.20.82:3306). Executed 0 events.
    Mon Jun 10 06:35:53 2019 - [info]   done.
    Mon Jun 10 06:35:54 2019 - [info] Getting new master's binlog name and position..
    Mon Jun 10 06:35:54 2019 - [info]  binlog.000015:235
    Mon Jun 10 06:35:54 2019 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='mysql2 or 192.168.20.82', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx';
    Mon Jun 10 06:35:54 2019 - [info] Setting read_only=0 on mysql2(192.168.20.82:3306)..
    Mon Jun 10 06:35:54 2019 - [info]  ok.
    Mon Jun 10 06:35:54 2019 - [info] 
    Mon Jun 10 06:35:54 2019 - [info] * Switching slaves in parallel..
    Mon Jun 10 06:35:54 2019 - [info] 
    Mon Jun 10 06:35:54 2019 - [info] -- Slave switch on host mysql3(192.168.20.83:3306) started, pid: 9450
    Mon Jun 10 06:35:54 2019 - [info] 
    Mon Jun 10 06:35:54 2019 - [info] -- Slave switch on host mysql4(192.168.20.84:3306) started, pid: 9451
    Mon Jun 10 06:35:54 2019 - [info] 
    Mon Jun 10 06:35:54 2019 - [info] Log messages from mysql3 ...
    Mon Jun 10 06:35:54 2019 - [info] 
    Mon Jun 10 06:35:54 2019 - [info]  Waiting to execute all relay logs on mysql3(192.168.20.83:3306)..
    Mon Jun 10 06:35:54 2019 - [info]  master_pos_wait(binlog.000017:1285) completed on mysql3(192.168.20.83:3306). Executed 0 events.
    Mon Jun 10 06:35:54 2019 - [info]   done.
    Mon Jun 10 06:35:54 2019 - [info]  Resetting slave mysql3(192.168.20.83:3306) and starting replication from the new master mysql2(192.168.20.82:3306)..
    Mon Jun 10 06:35:54 2019 - [info]  Executed CHANGE MASTER.
    Mon Jun 10 06:35:54 2019 - [info]  Slave started.
    Mon Jun 10 06:35:54 2019 - [info] End of log messages from mysql3 ...
    Mon Jun 10 06:35:54 2019 - [info] 
    Mon Jun 10 06:35:54 2019 - [info] -- Slave switch on host mysql3(192.168.20.83:3306) succeeded.
    Mon Jun 10 06:35:54 2019 - [info] Log messages from mysql4 ...
    Mon Jun 10 06:35:54 2019 - [info] 
    Mon Jun 10 06:35:54 2019 - [info]  Waiting to execute all relay logs on mysql4(192.168.20.84:3306)..
    Mon Jun 10 06:35:54 2019 - [info]  master_pos_wait(binlog.000017:1285) completed on mysql4(192.168.20.84:3306). Executed 0 events.
    Mon Jun 10 06:35:54 2019 - [info]   done.
    Mon Jun 10 06:35:54 2019 - [info]  Resetting slave mysql4(192.168.20.84:3306) and starting replication from the new master mysql2(192.168.20.82:3306)..
    Mon Jun 10 06:35:54 2019 - [info]  Executed CHANGE MASTER.
    Mon Jun 10 06:35:54 2019 - [info]  Slave started.
    Mon Jun 10 06:35:54 2019 - [info] End of log messages from mysql4 ...
    Mon Jun 10 06:35:54 2019 - [info] 
    Mon Jun 10 06:35:54 2019 - [info] -- Slave switch on host mysql4(192.168.20.84:3306) succeeded.
    Mon Jun 10 06:35:54 2019 - [info] Unlocking all tables on the orig master:
    Mon Jun 10 06:35:54 2019 - [info] Executing UNLOCK TABLES..
    Mon Jun 10 06:35:54 2019 - [info]  ok.
    Mon Jun 10 06:35:54 2019 - [info] All new slave servers switched successfully.
    Mon Jun 10 06:35:54 2019 - [info] 
    Mon Jun 10 06:35:54 2019 - [info] * Phase 5: New master cleanup phase..
    Mon Jun 10 06:35:54 2019 - [info] 
    Mon Jun 10 06:35:54 2019 - [info]  mysql2: Resetting slave info succeeded.
    Mon Jun 10 06:35:54 2019 - [info] Switching master to mysql2(192.168.20.82:3306) completed successfully.

    执行过程中提供了几次YES输入,还有几个重要的语句:

    检查阶段:
    master 执行 FLUSH NO_WRITE_TO_BINLOG TABLES
    搜寻candidate_master=1 的slave 节点,选择其为新master 
    
    执行阶段:
    FLUSH NO_WRITE_TO_BINLOG TABLES is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
    Locking all tables on the orig master to reject updates from everybody (including root):
    Executing FLUSH TABLES WITH READ LOCK..
    All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='mysql2 or 192.168.20.82', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx';
    Setting read_only=0 on mysql2(192.168.20.82:3306)..
    Unlocking all tables on the orig master:
    Executing UNLOCK TABLES..

    9.在线切换注意点

    1. 会从 candidate_master=1的slave节点中选择新master
    2.被选择的新master 修改为read_only=0
    3.原来的master 修改为read_only=1
    4. 切换后,原master 不会转为新master 的 slave。
    5.配置文件不会发生改变,所以重启需注意。
     

    10.将原master 改为新master 的slave

    --将原master 修改为新master 的从库
    change master to  master_host='192.168.20.82',master_port=3306,master_auto_position=1;
    start slave user='rep' password='rep';
    show slave status G
    

    11. 切换回原来的master

    # masterha_master_switch --master_state=alive --conf=/etc/masterha_default.cnf 
    
    同样的需要把node2 改为slave
    change master to  master_host='192.168.20.81',master_port=3306,master_auto_position=1;
    start slave user='rep' password='rep';
    show slave status G
    

      

  • 相关阅读:
    实现移动端上拉加载效果
    前端日常常用git命令
    css中单位em和rem的区别
    Sass框架的应用
    flex(弹性盒模型)布局写一个移动端
    windows和mac下分别配置虚拟主机
    把简单的二维数组变成一维数组
    go异常处理
    nginx中在超全局变量$_SERVER中增加变量
    阿里云接口异常-Can not find endpoint to access
  • 原文地址:https://www.cnblogs.com/plluoye/p/10996152.html
Copyright © 2020-2023  润新知