Mysql MHA搭建方法与步骤参考

最新推荐文章于 2024-09-13 22:38:14 发布

田大叔

最新推荐文章于 2024-09-13 22:38:14 发布

阅读量21

点赞数

文章标签： mysql 数据库

1.简介
　MHA（Master HA）是一款用于Mysql的开源高可用软件，其为Mysql主从复制架构提供了自动主节点失败切换（Automating Master Failover）。当MHA监控到Mysql Master节点发生故障时，其将拥有最新数据的Mysql Slave节点提升为新的Mysql Master节点。
期间，MHA通过其他从节点获取额外信息避免发生一致性相关的问题。通过MHA，还可以进行Mysql Master节点的在线切换。
MHA作为一款较为成熟的Mysql高可用方案，最初由日本人yoshinorim（原就职于DeNA，后就职于FaceBook）开发的比较成熟的 MySQL 高可用方案。MHA能在数十秒内实现故障切换，并在该过程中尽可能保证数据的一致性。

2.环境
1）MHA软件
MHA manager:mha4mysql-manager-0.56-0.noarch.rpm
MHA node:mha4mysql-node-0.56-0.noarch.rpm

2）服务器
192.168.88.110 manager
192.168.88.111 master
192.168.88.112 slave1 (backup master)
192.168.88.113 slave2
192.168.88.114 slave3

3）OS和DB
Centos 7.3
Mysql 5.7

3.准备
1）各数据库节点（192.168.88.111~114）安装好Mysql 5.7。

2）更改5台服务器/etc/hosts文件，添加如下信息。
192.168.88.110 manager
192.168.88.111 master
192.168.88.112 slave1
192.168.88.113 slave2
192.168.88.114 slave3

3）配置所有服务器间ssh免密登录
--master
# ssh-keygen -t rsa

# ssh-copy-id 192.168.88.110
# ssh-copy-id 192.168.88.112
# ssh-copy-id 192.168.88.113
# ssh-copy-id 192.168.88.114

--slave1
# ssh-keygen -t rsa

# ssh-copy-id 192.168.88.111
# ssh-copy-id 192.168.88.110
# ssh-copy-id 192.168.88.113
# ssh-copy-id 192.168.88.114

--slave2
# ssh-keygen -t rsa

# ssh-copy-id 192.168.88.111
# ssh-copy-id 192.168.88.112
# ssh-copy-id 192.168.88.110
# ssh-copy-id 192.168.88.114

--slave3
# ssh-keygen -t rsa

# ssh-copy-id 192.168.88.111
# ssh-copy-id 192.168.88.112
# ssh-copy-id 192.168.88.110
# ssh-copy-id 192.168.88.113

# manager
# ssh-keygen -t rsa

# ssh-copy-id 192.168.88.111
# ssh-copy-id 192.168.88.112
# ssh-copy-id 192.168.88.113
# ssh-copy-id 192.168.88.114

4）Mysql主从搭建
--master
# vi /etc/my.cnf
[mysqld]
server-id = 1
log-bin = master-log
relay-log = relay-log
skip_name_resolve

# systemctl start mysqld

# mysql -uroot -p -S...
mysql> grant replication slave on *.* to slave@'192.168.88.%' identified by "123";
mysql> show master status;

# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT && service iptables save

--slave1
# vi /etc/my.cnf
[mysqld]
server-id = 2
relay-log = relay-log
log-bin = master-log
read_only = ON
relay_log_purge = 0
skip_name_resolve
log_slave_updates = 1

# mysql -uroot -p -S...
mysql> grant replication slave on *.* to slave@'192.168.88.%' identified by "123";
mysql> CHANGE MASTER TO MASTER_HOST='192.168.88.111', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=5001, MASTER_USER='slave',MASTER_PASSWORD='123';
mysql> slave start;
mysql> show slave status\G

# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT && service iptables save

--slave2
# vi /etc/my.cnf
[mysqld]
server-id = 3
relay-log = relay-log
log-bin = master-log
read_only = ON
relay_log_purge = 0
skip_name_resolve
log_slave_updates = 1

# mysql -uroot -p -S...
mysql> CHANGE MASTER TO MASTER_HOST='192.168.88.111', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=5001, MASTER_USER='slave',MASTER_PASSWORD='123';
mysql> slave start;
mysql> show slave status\G

# itables -I INPUT -p tcp --dport 3306 -j ACCEPT && service iptables save

--slave3
# vi /etc/my.cnf
[mysqld]
server-id = 4
relay-log = relay-log
log-bin = master-log
read_only = ON
relay_log_purge = 0
skip_name_resolve
log_slave_updates = 1

# itables -I INPUT -p tcp --dport 3306 -j ACCEPT && service iptables save

4.MHA安装配置
1）manager节点上安装MHA manager和MHA node软件
# yum localinstall -y mha4mysql-node-0.56-0.noarch
# yum localinstall -y mha4mysql-manager-0.56-0.noarch.rpm

2）master和slave1~3上安装MHA node软件
# yum localinstall -y mha4mysql-node-0.56-0.el6.norch.rpm

3）master和slave1~3节点上创建MHA管理用户并授权
# mysql -uroot -p -S...
mysql> grant all on *.* to mha@'192.168.88.%' identified by '123456';

4）manager上创建MHA目录并编辑配置文件
# mkdir /mha
# vi /etc/masterha_default.cnf
[server default]
#MHA管理用户
user=mha
password=123456
manager_workdir=/mha
manager_log=/mha/manager.log
remote_workdir=/mha

#ssh免密钥登录的账号
ssh_user=root

#mysql复制帐号
repl_user=slave
repl_password=123

#用于检测master是否正常ping时长间隔
ping_interval= 1

[server1]
hostname=master
master_binlog_dir=/data/mysql

#master候选人
candidate_master=1

[server2]
hostname=slave1
master_binlog_dir=/data/mysql
candidate_master=1

[server3]
hostname=slave2
master_binlog_dir=/data/mysql
#非master候选人
no_master=1

[server4]
hostname=slave3
master_binlog_dir=/data/mysql
#非master候选人
no_master=1

5）manager节点验证SSH互信
# masterha_check_ssh --conf=/etc/masterha_default.cnf
最后报出下列信息表示SSH互信测试成功。
All SSH connection tests passed successfully.

6）manager节点验证Mysql复制
# masterha_check_repl --conf=/etc/masterha_default.cnf
最后报出下列信息表示Mysql复制测试成功。
MySQL Replication Health is OK.

7）manager开启MHA管理程序
# masterha_manager --conf=/etc/masterha_default.cnf &
或
# masterha_manager --remove_dead_master_conf --conf=/etc/masterha_default.cnf &

# tail -f /mha/manager.log
最后显示下列信息表示MHA正常开启，至此，MHA搭建成功。
Starting ping health check on db2(192.168.88.111:3306)..
Ping succeeded, sleeping until it doesn't respond..

5.MHA测试
关闭master节点上mysql服务器，并监控manager节点上MHA日志。
# tail -f /mha/manager.log
masterha_default: MySQL Master failover master to slave1 succeeded
Master master is down!
Check MHA Manager logs at monitor:/mha/manager.log for details.

Started automated(non-interactive) failover.
The latest slave slave1(192.168.88.112:3306) has all relay logs for recovery.
Selected slave1 as a new master.
master: OK: Applying all logs succeeded.
slave3: This host has the latest relay log events.
slave2: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
slave3: OK: Applying all logs succeeded. Slave started, replicating from slave1.
slave2: OK: Applying all logs succeeded. Slave started, replicating from slave1.
slave1: Resetting slave info succeeded.
Master failover to slave1(192.168.88.112:3306) completed successfully.
日志显示，主从自动切换成功。

--slave2
# mysql -uroot -p -S...
mysql> show slave status\G;

--slave3
# mysql -uroot -p -S...
mysql> show slave status\G;
slave2和slave3显示，以成功将master切换为slave1

--slave1
# mysql -uroot -p -S...
mysql> show slave status\G;
slave1显示，slave已停止。
ERROR:
No query specified
至此，Centos7+MHA搭建测试完成。

原创作者: lhdzbj 转载于: https://blog.51cto.com/lhdzbj/11534299