MySQL high-availability deployment MHA

MHA Introduction

• MHA consists of two parts: MHA Manager (Management node) and MHA node (data node). MHA Manager can be deployed individually on a separate machine to manage multiple master-slave clusters or on a single slave node.
• MHA node runs on each MySQL server, MHA Manager periodically probes the master node in the cluster, and when master fails, it automatically promotes the slave of the latest data to the new master, then all other slave are re-fingered To the new master. The entire failover process is completely transparent to the application.

Environment preparation

服务器(安装MHA):172.20.29.201主服务器:172.20.29.202从服务器A:172.20.29.203从服务器B:172.20.29.204

1. Synchronization Time

#定义计划任务crontab -e         */5 * * * *     /usr/sbin/ntpdate 172.20.0.1 &> /dev/null   #每5分钟自动同步时间至服务器

2. Turn off the firewall, SELINUX

iptables -vnL                   #查看防火墙状态systemctl stop firewall         #关闭防火墙getenforce                      #查看SELinux状态setenforce 0                    #关闭SElinux

3. Configuring the SSH public key (based on key authentication)

ssh-keygen -t rsa -P ‘‘ -f "/root/.ssh/id_rsa"  #生成私钥ssh-copy-id 172.20.29.201       #复制公钥至本机#四台主机共用一把钥匙    scp -pr /root/.ssh 172.20.29.202:/root/ scp -pr /root/.ssh 172.20.29.203:/root/scp -pr /root/.ssh 172.20.29.204:/root/

4. Install the Mha-node package on all servers

yum install mha4mysql-node-0.56-0.el6.noarchmysql5.5

MHA Server Configuration

 #MHA服务器上安装MHA包 (Note install dependent Epel source) Yum Install mha4mysql-manager-0.56-0.el6.noarch# Create and modify MHA configuration file mkdir/etc/mha/-pvim /ETC/MHA/APP1.CNF [Server default] User=mhauser #连接每一台数据库的用户 password=centos #连接每一台数据库的密码 m Anager_workdir=/data/mastermha/app1/manager_log=/data/mastermha/app1/manager.log REMOTE_WORKDIR=/DATA/MASTERMHA /app1/master_binlog_dir=/data/binlogs/#指定管理的二进制文件目录 ssh_user=root #使用什么账号来连接每台主机 repl_user=mhauser #ssh协议连接的用户 repl_password=centos #ssh协议连接的密码 ping_interval=1 #多长时间去探测每个节点 [Server1] hostname=172 .20.29.202 #主服务器地址 candidate_master=1 #设置为主服务器 [server2] hostname=172.20.29.203 #从服务器地址 candidate_maste R=1 #设置主服务器宕机立刻升级为主服务器 [Server3] hostname=172.20.29.204 #从服务器地址 # Check that each host SSH protocol is in effect Masterha_check_ssh--conf=/etc /MHA/APP1.CNF #检查每一台主机复制是否正常masterha_check_repl--conf=/etc/mha/app1.cnf# to open the MHA cluster (note that the front desk execution, long-term foreground execution, recommended to run on the host) Masterha_manager--conf=/etc/mha/app1.cnf 

Primary server configuration.

#创建文件夹用于存放二进制日志文件(注意文件夹权限)mkdir /data/binlogschown mysql.mysql /data/binlogs/chmod 770 /data/binlogs/#修改mysql配置文件vim /etc/my.cnf        server_id= 11           #主服务器ID标识        log_bin=/data/binlogs/master-bin    #配置文件里指定二进制日志文件存放目录        binlog_format=row       #以行模式复制数据        skip_name_resolve=1       #做名字解析        rpl_semi_sync_master_enabled=ON  #开启半同步插件#授权从服务器登录本机数据库grant replication slave,replication client on *.* to ‘jian‘@‘172.20.29.%‘ identified by ‘centos‘;#授权所有权限给MHA管理所有服务器grant all on *.* to [email protected]‘172.20.29.%‘ identified by ‘centos‘;#刷新系统权限列表flush privileges;

Configuration from Server A

#创建文件夹用于存放二进制日志文件(注意文件夹权限)mkdir /data/binlogschown mysql.mysql /data/binlogs/chmod 770 /data/binlogs/#修改mysql配置文件vim /etc/my.cnf     server-id = 12                      #从服务器ID标识log-bin=/data/binlogs/master-bin    #配置文件里指定二进制日志文件存放目录read_only=on                        #配置从服务器只读relay_log_purge=0                   #中继日志不清除skip_name_resolve=1                 #做名字解析rpl_semi_sync_slave_enabled=ON;     #开启半同步插件#从库建立与主库的连接change master to MASTER_HOST=‘172.20.29.202‘,MASTER_USER =‘jian‘,MASTER_PASSWORD =‘centos‘,MASTER_LOG_FILE =‘master-bin.000001‘,MASTER_LOG_POS =245;start slave;        #开启IO线程show slave status\G #查看IO线程    Slave_IO_Running: Yes   #这两项全部为yes,成功;否则失败.      Slave_SQL_Running: Yes

Configuration from Server B

#创建文件夹用于存放二进制日志文件(注意文件夹权限)mkdir /data/binlogschown mysql.mysql /data/binlogs/chmod 770 /data/binlogs/#修改mysql配置文件vim /etc/my.cnf     server-id = 13                      #从服务器ID标识    log-bin=/data/binlogs/master-bin    #配置文件里指定二进制日志文件存放目录    read_only=on                        #配置从服务器只读    relay_log_purge=0                   #中继日志不清除    skip_name_resolve=1                 #做名字解析#建立连接之前建议清理线程缓存    stop slave;         reset slave;#从库建立与主库的连接change master to MASTER_HOST=‘172.20.29.202‘,MASTER_USER =‘jian‘,MASTER_PASSWORD =‘centos‘,MASTER_LOG_FILE =‘master-bin.000001‘,MASTER_LOG_POS =245;start slave;        #开启IO线程show slave status\G #查看IO线程    Slave_IO_Running: Yes   #这两项全部为yes,成功;否则失败.      Slave_SQL_Running: Yes

Test

• Assuming the primary server is down

• The MHA program will automatically switch from server A to the primary server and stop the MHA program

  • Subsequent enablement of cluster management
• Note: This article simply records the environment deployment process for MHA.

MySQL high-availability deployment MHA