• centos7 mysql+MHA高可用安装


    https://dzone.com/articles/consul-proxysql-and-mysql-ha?utm_medium=feed&utm_source=feedpress.me&utm_campaign=Feed:%20dzone

     https://boke.wsfnk.com/archives/537.html

    https://www.aliyun.com/jiaocheng/132307.html?spm=5176.100033.2.6.51e94d54AD4NcE

    https://qiniu.wsfnk.com/%E7%AC%AC12%E8%AF%BE-4%E4%BC%81%E4%B8%9A%E5%B8%B8%E8%A7%81MySQL%E6%9E%B6%E6%9E%84%E5%BA%94%E7%94%A8%E5%AE%9E%E6%88%98%E4%B9%8BMHA%E6%9E%B6%E6%9E%84.pdf

    一、配置主从同步

     

    1.1 本例中主从ip及端口

    Master:192.168.20.50:3306

    Slave1:192.168.31.3:3306 (候选master)

    Slave2:192.168.31.2:3306

    1.2、安装mysql

    wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
    yum -y install mysql57-community-release-el7-10.noarch.rpm
    yum -y install mysql-community-server
    systemctl start mysqld.service
    grep "password" /var/log/mysqld.log
    mysql -uroot -p  #登录
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'My2019..';
    #因为安装了Yum Repository,以后每次yum操作都会自动更新,需要把这个卸载掉
    yum -y remove mysql57-community-release-el7-10.noarch

    1.3.Master配置参数

    [mysqld]
    
    user=mysql
    pid_file               = /var/lib/mysql/mysqld.pid
    socket                 = /var/lib/mysql/mysql.sock
    port                    = 3306
    basedir                 = /usr
    datadir                 = /var/lib/mysql
    tmpdir                 = /tmp
    log-bin                 = master-bin
    log-bin-index           = master-bin.index
    server_id               = 1
    innodb_log_file_size         = 256M
    expire-logs-days = 1
    validate_password=off #取消密码验证

     #mysql5.6已上的特性,开启gtid,必须主从全开
     gtid_mode = on
     enforce_gtid_consistency = 1
     log_slave_updates = 1

    
    

    #开启半同步复制 否则自动切换主从的时候会报主键错误
    plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
    loose_rpl_semi_sync_master_enabled = 1
    loose_rpl_semi_sync_slave_enabled = 1
    loose_rpl_semi_sync_master_timeout = 5000

    1.4.在master创建一个复制用户

    主节点:
    mysql> create user repl_user; 所有主机上授权: mysql> grant replication slave on *.* to repl_user identified by 'tt123456'; mysql> flush privileges; mysql>grant all on *.* to root identified by 'my123456'; #很重要

    1.5. Slave1 配置参数

    [mysqld]


    pid_file = /var/lib/mysql/mysqld.pid
    socket = /var/lib/mysql/mysql.sock


    basedir=/usr
    port=3306
    user=mysql
    tmpdir= /tmp
    server_id= 2
    relay_log_index = slave_relay_bin.index
    relay_log= slave_relay_bin
    innodb_log_file_size= 256M
    expire-logs-days = 1   

    log-bin = mysql-bin (候选需要配)
    log-bin-index = mysql-bin.index (候选需要配)
    read_only=1
    relay_log_purge=0   #(一主一丛不需要此项,两从及以上建议开次参数,防止切换为成主库的从库自动删除中继日志后,无法给其他从库应用这部分日志)

    validate_password=off #取消密码验证

     gtid_mode = on
     enforce_gtid_consistency = 1
     log_slave_updates = 1

    #开启半同步复制 否则自动切换主从的时候会报主键错误
    plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"(候选需要配)
    loose_rpl_semi_sync_master_enabled = 1(候选需要配)
    loose_rpl_semi_sync_slave_enabled = 1(候选需要配)
    loose_rpl_semi_sync_master_timeout = 5000(候选需要配)

    1.6.连接Master和Slave

     参考:https://blog.csdn.net/lichangzai/article/details/50423906

    mysql> CHANGE MASTER TO

    MASTER_HOST='192.168.31.3',
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION=1,
    MASTER_USER='repl_user',
    MASTER_PASSWORD='tt123456';

    #master_log_file='master-bin.000007',#5.6后不需要指定
    #master_log_pos=194;


    mysql> start slave;
    mysql> show slave statusG;

     1.7查看半同步是否开启

    master:
    mysql> show status like 'Rpl_semi_sync_master_status';
    +-----------------------------+-------+
    | Variable_name              | Value |
    +-----------------------------+-------+
    | Rpl_semi_sync_master_status | ON    |
    +-----------------------------+-------+
    
    slave:
    mysql> show status like 'Rpl_semi_sync_slave_status';
    +----------------------------+-------+
    | Variable_name              | Value |
    +----------------------------+-------+
    | Rpl_semi_sync_slave_status | ON    |
    +----------------------------+-------+

    二、配置ssh公钥互信

    2.1. 本例中manager节点和node节点ip

    manager:192.168.20.50

    node1:192.168.20.50

    node2:192.168.31.3

    node3:192.168.31.2

    注:manager节点可以安装独立的服务器上,本例为了节省机器,manager安装在了主库(192.168.20.50)上.

    2.2. 配置manager和node各节点间的root用户的ssh公钥互信

    在三个 mysql 节点分别执行如下操作:(三个都有,包括自己ssh自己)
    ssh-keygen -t rsa 
    ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.20.50
    ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.31.2
    ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.31.3

    三、安装 MHA 包

    3.1. MHA安装

     manager和node节点安装:

       #先安装依赖  

          wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
          rpm -ivh epel-release-latest-7.noarch.rpm 
       yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager

       wget https://qiniu.wsfnk.com/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
       rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

    manager上安装:

        wget https://qiniu.wsfnk.com/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

       rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

    3.2、MHA Manager 端配置

       配置主配置文件

     vi /etc/mha/app1.cnf
    [server default]
    manager_workdir=/var/log/mha/app1
    manager_log=/var/log/mha/app1/manager.log

    user=root #mysql用户
    password=my123456 #mysql密码
    ssh_user=root
    repl_user=repl_user
    repl_password=tt123456
    ping_interval=1
    #master_binlog_dir= /var/lib/mysql,/var/log/mysql
    #secondary_check_script=masterha_secondary_check -s 192.168.20.50 -s 192.168.31.3 -s 192.168.31.2
    #master_ip_failover_script="/etc/mha/scripts/master_ip_failover"
    #master_ip_online_change_script="/etc/mha/scripts/master_ip_online_change"
    #report_script="/etc/mha/scripts/send_report"

    
    [server1]#这里模块不注释,ssh测试会报错,不过没影响
    hostname=192.168.20.50
    port=3306
    master_binlog_dir=/var/lib/mysql
    candidate_master=1
    check_repl_delay=0
    
    [server2]
    hostname=192.168.31.3
    port=3306
    master_binlog_dir=/var/lib/mysql
    candidate_master=1  #如果候选master有延迟的话,relay日志超过100m,failover切换不能成功,加上此参数后会忽略延迟日志大小。
    check_repl_delay=0
    
    [server3]
    hostname=192.168.31.2
    port=3306
    master_binlog_dir=/var/lib/mysql
    ignore_fail=1  #如果这个节点挂了,mha将不可用,加上这个参数,slave挂了一样可以用
    no_master=1  #从不将这台主机转换为master
    PS:以上注释不能写在配置文件里,不然会报错

    四、运行

     4.1. 检查SSH配置

    masterha_check_ssh --conf=/etc/masterha/app1/app1.cnf

    4.2检查MHA当前配置

    masterha_check_repl --conf=/etc/masterha/app1/app1.cnf

     4.3启动mha

    nohup masterha_manager --conf=/etc/masterha/app1/app1.cnf   --remove_dead_master_conf --ignore_last_failover < /dev/null >  /var/log/mha/app1/manager.log  2>&1 &

    4.4查看状态

    masterha_check_status --conf=/etc/masterha/app1/app1.cnf

    4.5关闭mha

    masterha_stop  --conf=/etc/masterha/app1/app1.cnf

    4.6查看日志

    tail -f  /etc/masterha/app1/mha_manager.log

     4.7从库从新加入新主

     grep "CHANGE MASTER TO MASTER"  /var/log/mha/app1/manager.log | tail -1

    五、Failover应用场景测试

    自动failover测试

    应用场景1:master dead后,MHA当时已经开启,候选Master库(Slave)会自动failover为Master.

              后面我的主库改为192.168.31.3,在这机器执行:systemctl stop mysqld.service

              然后192.168.20.50执行:mysql> show master statusG;

        

            从库机器192.168.31.2执行:mysql> show slave statusG;

            

          最后把原主库192.168.31.3修复成一个新的slave:

    #查看具体修复语句
    # grep "CHANGE MASTER TO MASTER"  /etc/masterha/app1/manager.log | tail -1    CHANGE MASTER TO MASTER_HOST='192.168.20.50',MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000009', MASTER_AUTO_POSITION=1,MASTER_USER='repl_user', MASTER_PASSWORD='xxx';

    mysql> CHANGE MASTER TO MASTER_HOST='192.168.20.50',MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000009', MASTER_AUTO_POSITION=1,MASTER_USER='repl_user', MASTER_PASSWORD='tt123456';

    mysql>start slave;

    mysql>show slave statusG;

    验证。

       

          

    报错

    1.replicates is not defined in the configuration file!

    masterha_check_repl --conf=/etc/masterha/app1/app1.cnf 
    Thu Jan 31 11:36:27 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    Thu Jan 31 11:36:27 2019 - [info] Reading application default configuration from /etc/masterha/app1/app1.cnf..
    Thu Jan 31 11:36:27 2019 - [info] Reading server configuration from /etc/masterha/app1/app1.cnf..
    Thu Jan 31 11:36:27 2019 - [info] MHA::MasterMonitor version 0.58.
    Thu Jan 31 11:36:28 2019 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln671] Master 192.168.20.50:3306 from w
    hich slave 192.168.31.2(192.168.31.2:3306) replicates is not defined in the configuration file!
    Thu Jan 31 11:36:28 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking confi
    gurations.  at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 329.
    Thu Jan 31 11:36:28 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring ser
    vers.
    Thu Jan 31 11:36:28 2019 - [info] Got exit code 1 (Not master dead).

    解决:配置文件里面没有manager的主机信息,添加上去。
    [server1]
    hostname=192.168.20.50
    port=3306
    master_binlog_dir=/var/lib/mysql
    candidate_master=1
    check_repl_delay=0

    2、[error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm

    [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking confi
    gurations. Argument "1  #M-dM-;M-^NM-dM-8M-
    M-eM-0M-^FM-hM-?M-^YM-eM-^OM-0M-d..." isn't numeric in numeric ge (>=) at /usr/s
    hare/perl5/vendor_perl/MHA/ServerManager.pm line 1157.
    Thu Jan 31 14:39:52 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring ser
    vers.
    解决:
    配置文件不能有中文注释。。
  • 相关阅读:
    Money-去哪了每日站立会议
    Money去哪了- 每日站立会议
    Money-去哪了每日站立会议
    Money去哪了- 每日站立会议
    Money去哪了- 每日站立会议
    Money去哪了 个人计划列表
    软件需求NABCD分析-Money去哪了
    满足小学生在线答题,记录错题及更正错题的四则运算
    11.21 个人总结
    11.20 个人总结
  • 原文地址:https://www.cnblogs.com/kevincaptain/p/10333891.html
Copyright © 2020-2023  润新知