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

 mysqldump --master-data=1 --single-transaction -uroot -p --all-databases > mysql_mha_02.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

 


上一篇:mysql MHA 及多主复制


下一篇:php7.2安装ldap扩展模块