MySQL implementation MHA High availability

Environment:

• CentOS7.4 x 4
• Mha4mysql-manager x 1
• Mha4mysql-node x 3
• Node nodes build a master double slave
• Mha4mysql Code Hosting: https://code.google.com/archive/p/mysql-master-ha/

First, configuration MHA:

1, configure all hosts (including the manager host), realize Ssh-key can log on each other

• When ssh_user in a configuration file is not a root user:

  (1)需要手动创建node节点主机的文件夹，并授权node主机remote_workdir目录的权限给ssh_user用户(2)添加用户为MySQL同组用户，使其有读取mysql binary/relay log文件和relay_log.info文件的权限(3)授权Mysql日志目录的写权限

2. Configure MHA configuration file and install Mha4mysql-manager, Mha4mysql-node

• (1) Create and modify the configuration file/etc/mastermha/app1.cnf

  #配置文件可以放在任意路径下，因为后面命令调用配置文件时需要写出完整路径。[server default]user=mhauser            #mysql用户password=centos         #mysql用户密码manager_workdir=/data/mastermha/app1/           #Manager主机的工作目录manager_log=/data/mastermha/app1/manager.log    #Manager主机的日志remote_workdir=/data/mastermha/app1/            #nodo主机的工作目录ssh_user=root           #远程Linux主机系统用户repl_user=repluser      #主从复制时使用的mysql用户repl_password=centos    #主从复制时使用的mysql密码ping_interval=1         #健康检查间隔时间，单位为秒[server1]hostname=192.168.1.4    #mysql主机IPcandidate_master=1      #是否为备用主库[server2]hostname=192.168.1.6candidate_master=1      #是否为备用主库[server3]hostname=192.168.1.8

• (2) Installation of Mha4mysql-manager, Mha4mysql-node

#事先下载好manager和node，并且版本要一致！！！使用yum安装，需要启用epel源。yum install mha4mysql-manager-0.55-0.el6.noarch.rpm   

Two, main library configuration

• (1) Modify the MySQL configuration file

    vi/etc/my.cnf[mysqld]log-binserver_id=1innodb_file_per_ tableskip_name_resolve=1  

• (2) Install Mha4mysql-node

    yum Install mha4mysql-node-0.55-0.el6.noarch.rpm  

• (3) to create a MySQL user; master-slave synchronization requires a user, MHA needs a user.

  #MHA切换主库时需要修改Mysql运行时的全局变量, so all permissions are required and it is recommended to restrict the user's login IP to a specific host.    Mysql>show master logs;    Mysql>grant replication Slave on * * to ' repluser ' @ ' 192.168.1.% ' identified by ' CentOS ';  Mysql>grant all on * * to ' mhauser ' @ ' 192.168.1.% ' identified by ' CentOS ';  

three, from the library 1 (alternate main Library) configuration:

• (1) Modify the configuration file

    [mysqld]server_id=2 #备用主库需要启用二进制日志!!! Log-bin #备用主库需要启用二进制日志!!! Read_Only #备用主库需要启用二进制日志!!!relay_log_purge=0skip_name_resolve=1 #禁止名字解析innodb_file_per_table  

• (2) Install Mha4mysql-node

    yum install mha4mysql-node-0.55-0.el6.noarch.rpm  /pre> 

• (3) Set master-slave synchronization

    mysql>change master to master_host= ' 192.168.1.4 ', master_user= ' Repluser ', Master_password= ' CentOS ', master_log_file= ' mariadb-bin.000001 ', master_log_pos=245;  

Iv. Configuration from Library 2:

• (1) Modify the configuration file

  [mysqld]server_id=3read_onlyrelay_log_purge=0skip_name_resolve=1     #禁止名字解析innodb_file_per_table

• (2) Installation Mha4mysql-node

  yum install    mha4mysql-node-0.55-0.el6.noarch.rpm

• (3) Setting master-Slave synchronization

  mysql>CHANGE MASTER TO MASTER_HOST=‘192.168.1.4‘, MASTER_USER=‘repluser‘, MASTER_PASSWORD=‘centos‘, MASTER_LOG_FILE=‘mariadb-bin.000001‘, MASTER_LOG_POS=245;

V. Check and enable MHA

• (1) Check SSH connection

• (2) Checking node nodes

masterha_check_repl --conf=/etc/mastermha/app1.cnf

(3) The startup script exits when it detects that the main library cannot be connected and promotes the master library from the library.

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

Error log:/data/mastermha/app1/manager.log

When MHA a short period of time to switch the main library frequently, may error, delete/data/mastermha/app1/app1.failover.complete can!

MySQL implementation MHA High availability