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 &