MySQL5.7中如何安裝及配置MHA架構

下文給大家帶來有關MySQL5.7中如何安裝及配置MHA架構內容，相信大家一定看過類似的文章。我們給大家帶來的有何不同呢？一起來看看正文部分吧，相信看完MySQL5.7中如何安裝及配置MHA架構你一定會有所收獲。

前期準備

云服務器版本：CentOS Linux release 7.6.1810 (Core)

mysql_mha_manager : 192.168.10.215

mysql_mha_01      : 192.168.10.216

mysql_mha_02      : 192.168.10.217

mysql_mha_03      : 192.168.10.218

一. 安裝MHA工具

1. mysql_mha_manager云服務器

安裝依賴

yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm -y

yum install perl-DBD-MySQL -y

yum install perl-Config-Tiny -y

yum install perl-Log-Dispatch perl-Parallel-ForkManager -y

yum install perl-Parallel-ForkManager -y

安裝node

rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm

安裝manager

rpm -ivh mha4mysql-manager-0.57-0.el7.noarch.rpm

2. mysql_mha_01云服務器

安裝依賴

yum install perl-DBD-MySQL -y

安裝node

rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm

3. mysql_mha_02云服務器

安裝依賴

yum install perl-DBD-MySQL -y

安裝node

rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm

4. mysql_mha_03云服務器

安裝依賴

yum install perl-DBD-MySQL -y

安裝node

rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm

二. ssh免密

1. mysql_mha_manager

192.168.10.215

ssh-keygen -t rsa

ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.216

ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.217

ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.218

2. mysql_mha_01

192.168.10.216

ssh-keygen -t rsa

ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.217

ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.218

3. mysql_mha_02

192.168.10.217

ssh-keygen -t rsa

ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.216

ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.218

4. mysql_mha_03

192.168.10.218

ssh-keygen -t rsa

ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.216

ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.217

三. 安裝mysql

所有機器創建帳號

groupadd mysql

useradd -g mysql mysql

初次啟動報錯

[root@mysql_mha_01 tmp]# service mysqld start

Starting MySQL.2019-03-12T14:27:25.072241Z mysqld_safe error: log-error set to '/data/mysql/logs/mysql.log', however file don't exists. Create writable for user 'mysql'.

 ERROR! The server quit without updating PID file (/data/mysql/run/mysql.pid).

解決方案：

初次啟動mysql：sudo -u mysql service mysqld start

再次啟動mysql：service mysqld start|stop|restart

四. 修改hosts文件

mysql_mha_01/02/03、mysql_mha_manager

vi /etc/hosts

192.168.10.216 mysql_mha_01

192.168.10.217 mysql_mha_02

192.168.10.218 mysql_mha_03

五. mysql主從配置

mysql_mha_01/02/03關閉防火墻

systemctl stop firewalld.service

1. mysql_mha_01

grant replication slave,replication client ON *.*  to 'repl'@'192.168.10.%' identified by 'repl';

flush privileges;

mysqldump --master-data=2 --single-transaction -uroot -p --all-databases > mysql_mha_01.sql

more mysql_mha_01.sql

CHANGE MASTER TO MASTER_LOG_FILE='mysql_mha01-bin.000003', MASTER_LOG_POS=1448;

scp mysql_mha_01.sql root@192.168.10.217:/tmp

scp mysql_mha_01.sql root@192.168.10.218:/tmp

2. mysql_mha_02

mysql -uroot -p < mysql_mha_01.sql

mysql -uroot -p

CHANGE MASTER TO MASTER_HOST='192.168.10.216',

MASTER_USER='repl', MASTER_PASSWORD='repl',

MASTER_LOG_FILE='mysql_mha01-bin.000003', MASTER_LOG_POS=1448;

start slave;

show slave status\G

3. mysql_mha_03

mysql -uroot -p < mysql_mha_01.sql

mysql -uroot -p

CHANGE MASTER TO MASTER_HOST='192.168.10.216',

MASTER_USER='repl', MASTER_PASSWORD='repl',

MASTER_LOG_FILE='mysql_mha01-bin.000003', MASTER_LOG_POS=1448;

start slave;

show slave status\G

六. mha的manager配置文件

1. mysql_mha_manager云服務器

mkdir -p /etc/mha

vi /etc/mha/mha.cnf

[server default]

manager_workdir=/etc/mha/

manager_log=/etc/mha/manager.log

master_binlog_dir=/data/mysql/binlog/

user=root

password=123456

ping_interval=1

remote_workdir=/tmp

repl_password=repl

repl_user=repl

secondary_check_script= /usr/bin/masterha_secondary_check -s 192.168.10.217 -s 192.168.10.218 --user=root --master_host=192.168.10.216 --master_ip=192.168.10.216 --master_port=3306

master_ip_failover_script="/etc/mha/scripts/master_ip_failover"

#master_ip_online_change_script="/etc/mha/scripts/master_ip_online_change"

#shutdown_script=""

ssh_user=root

[server1]

hostname=192.168.10.216

port=3306

candidate_master=1

check_repl_delay=0

[server2]

hostname=192.168.10.217

port=3306

candidate_master=1

check_repl_delay=0

[server3]

hostname=192.168.10.218

port=3306

七. 檢查SSH免密、復制狀態

1. 檢查SSH免密

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

2. 檢查復制

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

錯誤1

描述

Tue Mar 12 12:50:46 2019 - [info]   Connecting to root@192.168.10.217(mysql_mha_02:22)..

Can't exec "mysqlbinlog": No such file or directory at /usr/share/perl5/vendor_perl/MHA/BinlogManager.pm line 106.

mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options at /usr/bin/apply_diff_relay_logs line 493.

解決方案

mysql_mha_01/02/03

ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog

錯誤2

描述

Tue Mar 12 13:13:13 2019 - [info]   Connecting to root@192.168.10.217(mysql_mha_02:22)..

mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'

mysqlbinlog version command failed with rc 7:0, please verify PATH, LD_LIBRARY_PATH, and client options at /usr/bin/apply_diff_relay_logs line 493.

解決方案

mysql_mha_01/02/03

修改my.cnf

[client]

#default-character-set = utf8mb4

錯誤3

描述

Testing mysql connection and privileges..sh: mysql: command not found

mysql command failed with rc 127:0!

 at /usr/bin/apply_diff_relay_logs line 375.

         main::check() called at /usr/bin/apply_diff_relay_logs line 497

         eval {...} called at /usr/bin/apply_diff_relay_logs line 475

         main::main() called at /usr/bin/apply_diff_relay_logs line 120

Tue Mar 12 13:25:40 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln208] Slaves settings check failed!

Tue Mar 12 13:25:40 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln416] Slave configuration failed.

Tue Mar 12 13:25:40 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/bin/masterha_check_repl line 48.

Tue Mar 12 13:25:40 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Tue Mar 12 13:25:40 2019 - [info] Got exit code 1 (Not master dead).

解決方案

mysql_mha_01/02/03

ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

八. MHA啟動與停止

1. 啟動manager

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

2. 停止manager

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

3. 檢查manager

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

九. 測試

1. 查看狀態

[root@mysql_mha_manager mha]# masterha_check_status --conf=/etc/mha/mha.cnf

mha (pid:27991) is running(0:PING_OK), master:mysql_mha_01

2. 關閉mysql_mha_01數據庫

service mysqld stop

3. 查看日志和mha狀態

日志

----- Failover Report -----

mha: MySQL Master failover mysql_mha_01(192.168.10.216:3306) to mysql_mha_02(192.168.10.217:3306) succeeded

Master mysql_mha_01(192.168.10.216:3306) is down!

Check MHA Manager logs at mysql_mha_manager:/etc/mha/manager.log for details.

Started automated(non-interactive) failover.

The latest slave mysql_mha_02(192.168.10.217:3306) has all relay logs for recovery.

Selected mysql_mha_02(192.168.10.217:3306) as a new master.

mysql_mha_02(192.168.10.217:3306): OK: Applying all logs succeeded.

mysql_mha_03(192.168.10.218:3306): This host has the latest relay log events.

Generating relay diff files from the latest slave succeeded.

mysql_mha_03(192.168.10.218:3306): OK: Applying all logs succeeded. Slave started, replicating from mysql_mha_02(192.168.10.217:3306)

mysql_mha_02(192.168.10.217:3306): Resetting slave info succeeded.

Master failover to mysql_mha_02(192.168.10.217:3306) completed successfully.

狀態

[root@mysql_mha_manager mha]# masterha_check_status --conf=/etc/mha/mha.cnf

mha is stopped(2:NOT_RUNNING).

4. 查看 slave 狀態

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.10.217

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql_mha02-bin.000003

          Read_Master_Log_Pos: 154

               Relay_Log_File: mysql_mha_03-relay-bin.000002

                Relay_Log_Pos: 326

        Relay_Master_Log_File: mysql_mha02-bin.000003

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

對于上文關于MySQL5.7中如何安裝及配置MHA架構，大家覺得是自己想要的嗎？如果想要了解更多相關，可以繼續關注我們的行業資訊板塊。