MySQL MHA部署实战

淡淡的烟草味﹌ 2023-01-09 03:56 10阅读 0赞

# MySQL MHA部署 #

环境：CentOS7

master 192.168.1.4

slave1 192.168.1.5

slave2 192.168.1.2

mha 192.168.1.7

*  安装依赖

yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN
    
    安装epel yum源
    yum install epel-release

*  查找centos默认的mariadb
    
    `rpm -qa|grep mariadb`
 *  移除centos默认的mariadb
    
    `rpm -e mariadb-libs-5.5.41-2.el7_0.x86_64 --nodeps`
 *  解压mysql
    
    `tar xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar`
 *  依次安装
    
        rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
        rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
        rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
        rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm 
        rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm 
        rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm
 *  初始化mysql，初始化一个用户
    
    `mysqld --initialize --user=mysql`
 *  查看初始密码
    
    `cat /var/log/mysqld.log`
    
    9jwYrpb&3H>w
 *  设置系统默认启动mysql服务
    
    `systemctl start mysqld.service`
 *  设置新密码
    
    `set password=password('root');`
 *  关闭防火墙，禁止自动启动
    
        systemctl stop iptables
        systemctl stop firewalld
        systemctl disable firewalld.service
    
     *   
 *  编辑主库和从库配置文件/etc/my.cnf

server-id=1|2|3
    log_bin=mysql-bin
    relay_log=mysql-relay-bin
    sync-binlog=1
    binlog-ignore-db=performance_schema
    binlog-ignore-db=information_schema
    binlog-ignore-db=sys

*  重启mysql服务
    
    `systemctl restart mysqld`
 *  授权并刷新
    
        grant replication slave on *.* to 'root'@'%' identified by 'root';
        grant all privileges on *.* to 'root'@'%' identified by 'root';
        flush privileges;
 *  开启同步
    
    主库
    
        show master status;
        记录log和位置
        mysql-bin.000003	154
    
    从库设置主库：
    
        change master to master_host='192.168.1.4',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000003',master_log_pos=154;
    
     *   
 *  由于从库的虚拟机是克隆出来的，因此从库的/var/lib/mysql/auto.cnf中的server-uuid是和主库相同的，会提示
    
    Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work
    
    因此需要停止服务，删除该文件，重启服务。
    
        systemctl stop mysqld.service
        mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak
        systemctl start mysqld.service
 *  开启/关闭同步
    
    start slave;
    
    stop slave;

安装mha

*  在4个节点安装node
    
    `rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm`
 *  更新yum
    
    `yum -y update`
 *  在manager节点安装依赖
    
    `yum install -y perl-Log-Dispatch`
    
    `yum install -y perl-Parallel-ForkManager`
    
    这里安装失败，yum源中没有相关包，且安装最新epel后也没有包，只能手动从网站下载包安装
    
    http://www.rpmfind.net/linux/rpm2html/search.php?query=perl-mail-send&submit=Search+…&system=&arch=
    
    依次下载安装
    
    perl-Email-Date-Format-1.002-15.el7.noarch.rpm
    
    perl-MIME-Types-1.38-2.el7.noarch.rpm
    
    perl-MIME-Lite-3.030-1.el7.noarch.rpm
    
    perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
    
    perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
    
    perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
    
    perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
    
    以上包， 页面Ctrl+F搜索el7 ，选择x86\_64即可找到。
 *  在manager节点安装mha manager
    
    `rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm`
 *  配置ssh免密
    
    提示输入直接回车即可。
    
        #master  192.168.1.4下执行
        
        ssh-keygen -t rsa
        ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.5
        ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.2
        ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.7
        
        #slave1   192.168.1.5下执行
        ssh-keygen -t rsa
        ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.4
        ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.2
        ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.7
        
        #slave2   192.168.1.2下执行
        ssh-keygen -t rsa
        ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.4
        ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.5
        ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.7
        
        #mha      192.168.1.7下执行
        ssh-keygen -t rsa
        ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.4
        ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.5
        ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.1.2
 *  配置mha配置文件
    
        mkdir /etc/masterha 
        mkdir /etc/masterha/app1 
        cd /etc/masterha/ 
        vim app.cnf
    
        [server default]
        manager_log=/etc/masterha/mha.log
        manager_workdir=/etc/masterha/
        
        manager_log=/var/log/masterha/app1/manager.log
        master_binlog_dir=/var/lib/mysql
        #master_ip_online_change_script=/etc/masterha/master_ip_online_change
        # master_ip_online_change_script=/usr/local/bin/master_ip_online_change
        ping_interval=1
        remote_workdir=/tmp
        
        repl_user=root
        repl_password=root
        ssh_user=root
        user=root
        password=root
         
        [server1]
        hostname=192.168.1.4
        port=3306
         
        [server2]
        hostname=192.168.1.5
        port=3306
        check_repl_delay=0
         
        [server3]
        hostname=192.168.1.2
        port=3306
    
     *  1
 *  测试ssh连通
    
    `masterha_check_ssh --conf=/etc/masterha/app.cnf`
    
    此时提示MHA/NodeConst.pm did not return a true value at /usr/share/perl5/vendor\_perl/MHA/ManagerConst.pm line 25.
    
    检查NodeConst.pm文件，发现内容为空，于是卸载manager，卸载node，重新安装node，重新安装manager则正常连接。

校验时提示  
![在这里插入图片描述][20201009234922444.png_pic_center]

[error][/usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm, ln122] Got error when getting node version. Error:node version on 192.168.1.5 not found! Is MHA Node package installed

*  1

这里是没有安装好mhanode，检查/usr/share/perl5/vendor\_perl/MHA/ManagerUtil.pm文件发现是空的。

卸载后重新安装，发现有提示`有缺少的需求 libmysqlclient.so.18()`，但是之前安装node的时候被我忽略了，这个是上面mysql的mysql-community-libs-compat-5.7.28-1.el7.x86\_64安装掉了

卸载node`rpm -e mha4mysql-node-0.58-0.el7.centos.noarch`

安装libs-compat

`rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm`

安装node

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

*  校验server状态
    
    `masterha_check_repl --conf=/etc/masterha/app.cnf`
 *  启动
    
    `nohup masterha_manager --conf=/etc/masterha/app.cnf --remove_dead_master_conf --ignore_last_failover /tmp/mha_manager.log 2>&1 &`
 *  检测是否启动
    
    `masterha_check_status --conf=/etc/masterha/app.cnf`
    
    这里发现启动失败，检查/tmp/mha\_manager.log，发现提示Cannot write to ‘/var/log/masterha/app1/manager.log’: 没有那个文件或目录 at /usr/share/perl5/vendor\_perl/Log/Dispatch/File.pm
    
    这里的目录是在app.cnf中配置的，创建该目录即可
    
    `mkdir -p /var/log/masterha/app1/`

### 测试 ###

启动成功后  
systemctl stop mysqld  
systemctl start mysqld

重新启动后根据日志/var/log/masterha/app1/manager.log中记录的logbin位置，配置到master，再start slave;即可作为从服务器同步到master

change master to master\_host=‘192.168.1.4’,master\_port=3306,master\_user=‘root’,master\_password=‘root’,master\_log\_file=‘mysql-bin.000005’,master\_log\_pos=154;

参考

安装https://blog.csdn.net/ko0491/article/details/108572596

官方文档https://github.com/yoshinorim/mha4mysql-manager/wiki/Installation\#installing-mha-node

宕机恢复https://blog.csdn.net/qq\_34457768/article/details/79554586

[20201009234922444.png_pic_center]: /images/20221119/7a4011117a084f2da2e3a27bb765ebe3.png