MHA高可用Mysql

MHA:

对主节点进行监控,可实现自动故障转移至其他从节点; 通过提升某一从节点为新的主节点完成高可用;

环境准备:

node7: 192.168.2.90,MySQL Manager
node5: 192.168.2.50,MySQL Master Node
node4: 192.168.2.30,MySQL Slave Node
node6: 192.168.2.70,MySQL Slave Node

安装配置MySQL Replication

node5:

# vim /etc/my.cnf
innodb_file_per_table= 1
skip_name_resolve= 1
log-bin= master-bin
relay-log= relay-bin
server_id= 1

# systemctl start mariadb.service
# mysql
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000004 | 245 | | |
+-------------------+----------+--------------+------------------+
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘repluser‘@‘192.168.2.%‘ IDENTIFIED BY ‘replpass‘;
mysql> FLUSH PRIVILEGES;

创建一个管理员权限的用户,并且能远程连接
mysql> GRANT ALL ON *.* TO ‘mhauser‘@‘192.168.2.%‘ IDENTIFIED BY ‘mhapass‘;
mysql> FLUSH PRIVILEGES;

node4:

# vim /etc/my.cnf
innodb_file_per_table = 1
skip_name_resolve = 1
log-bin = master-bin
relay-log= relay-bin
server_id = 2
read_only = 1
relay_log_purge = 0

# systemctl start mariadb.service
mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.2.50‘,MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘replpass‘,MASTER_LOG_FILE=‘master-bin.000004‘,MASTER_LOG_POS=245
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

node6:

# vim /etc/my.cnf
innodb_file_per_table = 1
skip_name_resolve = 1
log-bin = master-bin
relay-log= relay-bin
server_id = 2
read_only = 1
relay_log_purge = 0

# systemctl start mariadb.service
mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.2.50‘,MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘replpass‘,MASTER_LOG_FILE=‘master-bin.000004‘,MASTER_LOG_POS=245
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

安装mha4mysql:

mha4mysql-node: https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
mha4mysql-master: https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

node7:

# ssh-keygen -t rsa -P ‘‘
# cat .ssh/id_rsa.pub > .ssh/authorized_keys
# chmod 600 .ssh/authorized_keys
# scp -p .ssh/id_rsa .ssh/authorized_keys node4:/root/.ssh
# scp -p .ssh/id_rsa .ssh/authorized_keys node5:/root/.ssh
# scp -p .ssh/id_rsa .ssh/authorized_keys node6:/root/.ssh

# yum install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm -y
# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

查看安装后生成的文件:
# rpm -ql mha4mysql-manager

# mkdir /etc/masterha
# vim /etc/masterha/app1.conf
[server default]
user=mhauser
password=mhapass
manager_workdir=/data/masterha/app1
manager_log=/data/masterha/app1/manager.log
remote_workdir=/data/masterha/app1
ssh_user=root
repl_user=repluser
repl_password=replpass
ping_interval=1

[server1]
hostname=192.168.2.50
#ssh_port=22022
#no_master=1

[server2]
hostname=192.168.2.30
#ssh_port=22022
candidate_master=1
#no_master=1

[server3]
hostname=192.168.2.70
#ssh_port=22022
#no_master=1

node4:

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

node5:

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

node6:

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

测试:

检测各节点间ssh互信通信配置是否OK:

# masterha_check_ssh --conf=/etc/masterha/app1.cnf

输出信息最后一行类似如下信息,表示其通过检测:

[info] All SSH connection tests passed successfully.

检测mysql复制是否OK:

# masterha_check_repl --conf=/etc/masterha/app1.cnf

输出信息最后一行类似如下信息,表示其通过检测:

MySQL Replication Health is OK.

上面检测都没问题的话,下面就可以启动服务了。

我们先测试一下能不能启动:

masterha_manager --conf=/etc/masterha/app1.cnf

没报错就表示运行正常。

这时候如果我们将主节点node5宕机:
node7上会显示如下信息:

Creating /data/masterha/app1 if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to master-bin.000003

在从节点node6上:

mysql> SHOW SLAVE STATUS\G
会显示主节点切换到了node4:
Master_Host: 192.168.2.30

node4上再运行SHOW SLAVE STATUS就没用了,要使用SHOW MASTER STATUS;并且read_only变量自动关掉了

mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 245 | | |
+-------------------+----------+--------------+------------------+
mysql> SHOW GLOBAL VARIABLES LIKE ‘read_only‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+

假如node5重新上线,那它只能以从节点工作:

# vim /etc/my.cnf
  read_only = 1
  relay_log_purge = 0
mysql> CHANGE MASTER TO  MASTER_HOST=‘192.168.2.30‘, MASTER_USER=‘repluser‘, MASTER_PASSWORD=‘replpass‘, MASTER_LOG_FILE=‘master-bin.000003‘, MASTER_LOG_POS=245;

注意:真实环境中切记要备份新的主节点数据导入进node5再让它上线:

MHA常用相关命令:

masterha_check_ssh --conf=/etc/masterha/app1.cnf
masterha_check_repl --conf=/etc/masterha/app1.cnf
masterha_check_status --conf=/etc/masterha/app1.cnf
masterha_check_stop --conf=/etc/masterha/app1.cnf

启动mha如下
nohup masterha_manager --conf=/mha/app1.cnf --ignore_last_failover < /dev/null >/mha/manager.log 2>&1 &

MHA高可用Mysql

上一篇:windows XP 获取网卡MAC和IP地址


下一篇:mysql 主备 主从 主主模式介绍