MySQL MHA部署實戰

NNNathan發表於2020-10-09
MySql

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

  • 測試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則正常連線。

​ 校驗時提示
在這裡插入圖片描述

[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

​ 這裡是沒有安裝好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安裝掉了

​ 解除安裝noderpm -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

相關文章