Mysql 主从复制+MHA搭建

水深无声 2022-12-06 15:52 229阅读 0赞

**Mysql半双工主从复制**  
[https://blog.csdn.net/qq\_35349982/article/details/108558868][https_blog.csdn.net_qq_35349982_article_details_108558868]  
**继续主从复制之后的MHA搭建**

master-1 192.168.182.128

slave-1 192.168.182.132

slave-2 192.168.182.130

mha-1 192.168.182.133

# 准备工作 #

# 关闭防火墙 #

# 1.机器都要做关闭防火墙： #

systemctl stop firewalld.service
    
    systemctl disable firewalld.service
    
    systemctl status firewalld.service

# 2. ssh互通环境 #

实现远程控制及数据管理功能：

在Manager节点生成密钥对儿，并设置其可远程连接本地主机后， 将私钥文件及authorized\_keys文件复制给余下的所有节点即可。

#master (在root目录下创建密钥，不需要输密钥，一路回车键)
    ssh-keygen -t rsa
    #一定要用root用户执行，否则权限不够
    #复制给其他节点的时候，还要再输入服务器的密码
    ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.182.132
    ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.182.130
    ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.182.133
    ##复制给其他节点
    #salve1 
    ssh-keygen -t rsa
    ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.182.130
    ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.182.128
    ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.182.133
    #salve2
    ssh-keygen -t rsa
    ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.182.132
    ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.182.128
    ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.182.133
    #mh1
    ssh-keygen -t rsa
    ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.182.132
    ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.182.128
    ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.182.130

**补充**

#查看生成公钥
    cat ~/.ssh/id_rsa.pub

参考:

[https://www.jianshu.com/p/4d3888e116b2][https_www.jianshu.com_p_4d3888e116b2]

# MHA搭建 #

前提:ssh互通环境

## 1. **MHA安装之前需要先安装这两个查询依赖，不然可能会安装失败** ##

yum install perl-ExtUtils-MakeMaker
    yum install perl-Test-Simple

## 2. **四个节点都需安装node（mha服务额外安装一个mannger服务）** ##

yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

**master节点与连两个slave节点需要安装perl包（三个节点都执行**）

#（进入mysql授权命令）MHA的管理账号
    grant all privileges on *.* to 'mhamanger'@'%' identified by 'mhamanger';
    flush privileges;

## 3. **在MHA节点上安装（二选一）** ##

**有两个安装方法：选一个就行，我是用的方案二，安装的特别慢**

*  安装MHA方案一：

**安装本地的mha4mysql-manager-0.58-0.el7.centos.noarch.rpm的文件**

yum install perl-DBD-MySQL -y
    rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
    yum install -y epel-release
    #安装perl包依赖的时候，可能会报错，执⾏以下命令即可：
    sed -i 's#https://#http://#g' /etc/yum.repos.d/epel*repo
    yum install -y perl-Config-Tiny perl-Log-Dispatch perl-ParallelForkManager
    perl-Time-HiRes
    rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

*  安装MHA方案二：

**去官网下载，tar的安装包，初始化安装**

yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
    # 去下载manger的版本 https://github.com/yoshinorim/mha4mysql-manager/releases
    ##mha4mysql-manager-0.58.tar.gz
    cd /usr/local/src
    ##解压
    tar xf mha4mysql-node-0.57.tar.gz
    #进目录
    cd mha4mysql-node-0.57
    ##下面这步可能会失败，要安装一个 perl
    perl Makefile.PL
    ##初始化
    make && make install

## 4. **创建配置文件** ##

#配置⽂件⽬录
    mkdir -p /etc/mha
    #日志目录
    mkdir -p /var/log/mha/app1
    #创建配置文件
    vi /etc/mha/app1.cnf

[server default]
    manager_log=/var/log/mha/app1/manager
    manager_workdir=/var/log/mha/app1
    master_binlog_dir=/var/lib/mysql
    password=root
    ping_interval=2
    repl_password=root
    repl_user=root
    ssh_user=root
    user=root
    [server1]
    hostname=192.168.182.128
    port=3306
    [server2]
    hostname=192.168.182.132
    port=3306
    [server3]
    hostname=192.168.182.130
    port=3306

## **5.对节点进行检测( 验证SSH通信)** ##

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

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qbhMR0rP-1600064360006)(D:\\拉钩笔记\\lagouNode\\4Stage\\Mysql(上)\]\\pic\\ssh通信.png)][img-qbhMR0rP-1600064360006_D_lagouNode_4Stage_Mysql_pic_ssh_.png]

如果最后一行显示为`[info]All SSH connection tests passed successfully.`则表示成功

## **5.检查管理的MySQL复制集群的连接配置参数是否OK** ##

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

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zaMRZdFR-1600064360008)(D:\\拉钩笔记\\lagouNode\\4Stage\\Mysql(上)\]\\pic\\集群验证.png)][img-zaMRZdFR-1600064360008_D_lagouNode_4Stage_Mysql_pic_.png]

## **6.开启MHA Manager监控** ##

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

–remove\_dead\_master\_conf //该参数代表当发生主从切换后，老的主库的ip将会从配置文件中移除。

–manger\_log //日志存放位置

*  **关闭监控**

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

## **7.查看master节点状态** ##

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

**查看日志（报错的话，方便查日志）**

```shell
    tail -200 /etc/mha_master/manager.log 
    ```

## **8.模拟宕机状态** ##

##在master节点上运行
    systemctl stop mysqld
    ## 观察mhamanger节点⽇志：
    tail -f /var/log/mha/app1/manager

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-C1CTewaH-1600064360010)(D:\\拉钩笔记\\lagouNode\\4Stage\\Mysql(上)\]\\pic\\master切换.png)][img-C1CTewaH-1600064360010_D_lagouNode_4Stage_Mysql_pic_master_.png]

## 9. **主机修复后，需要在mha中从新添加服务器节点** ##

## master节点
    systemctl start mysqld

##mha的配置文件，从新添加修复后的服务器节点，然后重新启动MHA服务器
    [server1]
    hostname=192.168.182.128
    port=3306

# 完整的配置 #

*  MHA的配置

[server default]
    manager_log=/var/log/mha/app1/manager
    manager_workdir=/var/log/mha/app1
    master_binlog_dir=/var/lib/mysql
    password=root
    ping_interval=2
    repl_password=root
    repl_user=root
    ssh_user=root
    user=root
    [server1]
    hostname=192.168.182.128
    port=3306
    [server2]
    hostname=192.168.182.132
    port=3306
    
    [server3]
    hostname=192.168.182.130
    port=3306

*  master节点的配置

[mysqld]
    #
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
    #
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    log_bin=mysql-bin
    server-id=1
    sync-binlog=1
    binlog-ignore-db=preformance_schema
    binlog-ignore-db=information_schema
    binlog-ignore-db=sys
    rpl_semi_sync_master_enabled=1
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

*  slave1的配置

[mysqld]
    #
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
    #
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backup
    log_bin=mysql-bin
    server-id=2
    relay_log=mysql-replay-bin
    read_only=1
    relay_log_purge=0
    binlog-ignore-db=preformance_schema
    binlog-ignore-db=information_schema
    binlog-ignore-db=sys
    rpl_semi_sync_slave_enabled=1
    
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

*  slave2的配置

[mysqld]
    #
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
    #
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    log_bin=mysql-bin
    server-id=3
    relay_log=mysql-replay-bin
    read_only=1
    relay_log_purge=0
    binlog-ignore-db=preformance_schema
    binlog-ignore-db=information_schema
    binlog-ignore-db=sys
    rpl_semi_sync_slave_enabled=1
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

参考

https://blog.csdn.net/qq_23830637/article/details/106968615
    
    ####mha官网
    https://github.com/yoshinorim/mha4mysql-manager/releases
    #####mha安装前rpm
    https://www.cnblogs.com/ding2016/p/9102052.html

# 问题： #

1.  **解决Enter passphrase for key**

#ssh-agent是用于管理密钥，ssh-add用于将密钥加入到ssh-agent中，SSH可以和ssh-agent通信获取密钥，
    eval `ssh-agent` 
    ssh-add

> https://blog.csdn.net/superbfly/article/details/75287741

1.  **ssh重启失败**

Failed to issue method call: Unit ssh.service failed to load: No such file or directory.

systemctl enable sshd.service
    
    systemctl start sshd.service
    
    systemctl status sshd.service

> https://blog.csdn.net/mengjiangtao/article/details/24195523

manager_log=/var/log/mha/app1/manager
    manager_workdir=/var/log/mha/app1
    master_binlog_dir=/var/lib/mysql
    password=root
    ping_interval=2
    repl_password=root
    repl_user=root
    ssh_user=root
    user=root
    [server1]
    hostname=192.168.182.128
    port=3306
    [server2]
    hostname=192.168.182.132
    port=3306
    [server3]
    hostname=192.168.182.130
    port=3306
    #将来可不可以成为master候选节点/主节点
    #candidate_master=1
    # master候选人，master挂掉后，这个节点成为master
    # condidate_master=1
    
    ################以下的了解即可
    #设置master 保存binlog的位置，以便MHA可以找到master的日志
    master_binlog_dir=/opt/mysql_data
    manager_workdir=/var/log/mha_master/app1.log
    #设置master 保存binlog的位置，以便MHA可以找到master的日志，我这里的也就是mysql的数据目录
    manager_log=/var/log/mha_master/manager.log  
    #设置自动failover时候的切换脚本
    master_ip_failover_script="/etc/mha_master/scripts/master_ip_failover"
    #设置手动切换时候的切换脚本
    master_ip_online_change_script="/etc/mha_master/scripts/master_ip_online_change"
    #//设置发生切换后发送的报警的脚本
    report_script="/etc/mha_master/scripts/send_report"
    #设置远端mysql在发生切换时binlog的保存位置
    remote_workdir=/tmp
    ##实现多路由监测Master的可用性
    secondary_check_script= /usr/local/bin/masterha_secondary_check -s 10.0.20.201 -s 10.0.20.202 -s 10.0.20.203 -s 10.0.20.204
    shutdown_script=""
    ################以下的了解即可

参考

https://blog.csdn.net/qq\_23830637/article/details/106968615  
https://www.cnblogs.com/panwenbin-logs/p/8306906.html\#autoid-0-0-0  
http://www.ttlsa.com/mysql/step-one-by-one-deploy-mysql-mha-cluster/  
https://www.cnblogs.com/shengdimaya/p/6949882.html  
https://blog.csdn.net/qq\_23830637/article/details/106968615  
https://www.cnblogs.com/panwenbin-logs/p/8306906.html\#autoid-0-0-0  
http://www.ttlsa.com/mysql/step-one-by-one-deploy-mysql-mha-cluster/  
https://www.cnblogs.com/shengdimaya/p/6949882.html

[https_blog.csdn.net_qq_35349982_article_details_108558868]: https://blog.csdn.net/qq_35349982/article/details/108558868
[https_www.jianshu.com_p_4d3888e116b2]: https://www.jianshu.com/p/4d3888e116b2
[img-qbhMR0rP-1600064360006_D_lagouNode_4Stage_Mysql_pic_ssh_.png]: /images/20221123/f594139a5ff0420eaaf3d0df719ffa04.png
[img-zaMRZdFR-1600064360008_D_lagouNode_4Stage_Mysql_pic_.png]: /images/20221123/701bff5bf6b04294b8ff9a3c2282b9bf.png
[img-C1CTewaH-1600064360010_D_lagouNode_4Stage_Mysql_pic_master_.png]: /images/20221123/6509fab987144164bf4a71de323c9137.png