MySQL high-availability MHA architecture built

Preface: First introduce the MHA, quoted from the network.

MHA (Master high availability) is currently a relatively mature solution for MySQL's highly available areas.

The software 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. All other slave are then re-directed to the new master. The entire failover process is completely transparent to the application.

During the MHA automatic failover process, MHA attempts to save the binary log from the main server of the outage, with maximum assurance that the data is not lost, but this is not always possible. For example, if the primary server hardware fails or cannot be accessed via SSH, MHA cannot save the binary logs and only fail over to lose the latest data. Using MySQL 5.5 's semi-synchronous replication can greatly reduce the risk of data loss. MHA can be combined with semi-synchronous replication. If only one slave has received the latest binary log, MHA can apply the latest binary logs to all other slave servers, thus guaranteeing data consistency across all nodes.

at present MHA mainly supports a master multi-slave architecture, to build MHA, requires a replication There must be at least three database servers in the cluster , one primary and two from, one serving as master, one serving as the standby master, and the other acting as the slave library.

Body:

First, the system environment

System: CENTOS6

MySQL version: 5.6.20

Ip	Host Name	State
10.10.1.12	DB-01	Main
10.10.0.5	DB-02	Candidate Master
10.10.0.25	DFS-01	Preparation

Second, prepare

MySQL master-slave installation can refer to my previous blog (different environment, reference can be): http://wangwq.blog.51cto.com/8711737/1956819

Master and candidate main libraries add semi-synchronous

To install the plugin:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

Boot module:

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

To set the timeout period:

mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;

To install the plugin:

msyql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';

Boot module:

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

View relevant information before starting

Mysql> Show variables like ' rpl% ';

A few users to use:

Mysql>grant all privileges on * * to [e-mail protected] '% ' identified by ' 9UQZ51ERAKFIEKNHO3NW '; \ \ Add MHA Admin user, give all library permissions.           Mysql>grant replication Slave on * * to [email protected] '% ' identified by ' Mysqlsync '; \ \ Add MySQL sync user mysql>flush privileges;

MySQL Delete the account that the user is empty

Mysql>delete from Mysql.user where user= '; Mysql>flush privileges;

If you do not delete the empty account, the error will be followed.

Third, host add Hosts file

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/03/edf4898a30c745f6275335f48a405fd1.png "title=" Hosts23333.png "alt=" Edf4898a30c745f6275335f48a405fd1.png "/>

Iv. Uploading MHA files

mha4mysql-node-0.54-0.el6.noarch.rpm

mha4mysql-manager-0.55-0.el6.noarch.rpm

V. Installation of MHA

node is installed on each MySQL server and the manager is used to manage it, which is installed in the standby repository.

To install node nodes:

Resolving dependencies

Yum Install-y Perl-dbd-mysql

Install node

RPM-IVH mha4mysql-node-0.54-0.el6.noarch.rpm

To install the manager:

Resolving dependencies

Yum Install-y epel-release

Yum install-y perl-config-tiny perl-log-dispatch perl-parallel-forkmanager perl-dbd-mysql perl-Time-HiRes

The script requires a dependency package

Yum-y Install expect MAILX

Installing the manager

RPM-IVH mha4mysql-manager-0.55-0.el6.noarch.rpm

Vi. Configuring the Manager

1. Configuration files

MHA Management Service is deployed on 10.10.0.25 , this server is MySQL from the library. the MHA Manager configuration file needs to be created manually.

Vi/etc/masterha/masterha_default.cnf[server Default]user=hk_mhaappassword=9uqz51erakfieknssh_user=rootrepl_user =mysqlsyncrepl_password=mysqlsyncping_interval=30manager_workdir=/var/log/masterha/app1manager_log=/var/log/ Masterha/app1/app1.logmaster_ip_failover_script=/etc/masterha/scripts/master_ip_failover_script.shreport_ script=/etc/masterha/scripts/email.sh[server1]hostname=10.10.1.12master_binlog_dir=/datacandidate_master=1[ Server2]hostname=10.10.0.5master_binlog_dir=/datacandidate_master=1[server3]hostname=10.10.0.25master_binlog_ Dir=/datano_master=1

2. Add a monitoring script

Main Library Failover script:

Cat /etc/masterha/scripts/master_ip_failover_script.sh#!/bin/bashpara_num=$#[email protected]#--orig _master_ip the host that currently contains the VIP #--command executes the command #--ssh_userssh the user #--new_master_ip ready to configure the VIP Host # log path log=/tmp/remote.logfor  i in  $parado #command keywordecho  $i  | grep command &>/dev /nullend=$?if [  $end  -eq 0 ]thencommand=$ (echo  $i |cut -d\= -f2) #echo   $COMMANDFI #orig_master_ip keywordecho  $i  | grep orig_master_ip &>/ dev/nullend1=$?if [  $end 1 -eq 0 ]thenorig_master_ip=$ (echo  $i |cut -d\=  -F2) #echo   $orig _master_ipfi#ssh_user keywordecho  $i |grep ssh_user &>/dev /nullend2=$?if [  $end 2 -eq 0 ]thenssh_user=$ (echo  $i |cut -d\= -f2) # echo  $ssh _userfi#new_master_ip keywordecho  $i |grep new_master_ip &>/dev/ nullend3=$?if [  $end 3 -eq 0 ]thennew_master_ip=$ (echo  $i |cut -d\= -f2) #echo   $new _master_ipfidone# Connect the new master, configure Vipconnection_start ()  {/usr/bin/expect -c  set timeout 30spawn ssh - l  $ssh _user  $new _master_ipexpect \ "*#\" send    \ "sudo /etc/init.d/ Keepalived start\r\ "expect \" *#\ "send    \" exit\r\ ""} >>  $log # Connect Old master, remove Vipconnection_stop ()  {/usr/bin/expect -c  "set timeout 30spawn ssh -l   $SSH _user  $orig _master_ipexpect \ "*#\" send    \ "sudo /etc/init.d/ Keepalived stop\r\ "expect \" *#\ "send    \" exit\r\ ""} >>  $log # Connect current master, view status Connection_status ()  {/usr/bin/expect -c  "set timeout 30spawn ssh  -l  $ssh _user  $orig _master_ipexpect \ "*#\" send    \ "sudo /etc/init.d/ Keepalived status\r\"Expect \" *#\ "send    \" exit\r\ ""} 2>&1case  $command  instart) Connection_start;; Stopssh|stop) connection_stop;; status) Connection_status;; Esac

Mail script:

Cat/etc/masterha/scripts/email.sh#!/bin/bash[email Protected][email Protected][email Protected]result_body=${body ##*=}subject=$ (Echo ${body%%body*}) result_subject=$ (Echo ${subject##*=}) echo $result _body |mail-s "$result _subject" -R $mail _from $admin _email

Assigning Execute permissions to Scripts

chmod +x/etc/masterha/scripts/*

Seven, Test MHA

1. Verify that SSH mutual authentication is successful:

Masterha_check_ssh--conf=/etc/masterha/masterha_default.cnf

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/02/6e0037355a9f843c99dd9b9648885cc4.png "title=" Chek_ Ssh1.png "alt=" 6e0037355a9f843c99dd9b9648885cc4.png "/>

Note: It is important to note that this machine must also add its own secret key, or it will be error.

2. Verifying MySQL master-slave replication

Masterha_check_repl--conf=/etc/masterha/masterha_default.cnf

Error 1:

650) this.width=650; "src=" Https://s5.51cto.com/oss/201711/02/4d6551816415699e1cadd58947aa62f0.png "title=" Master _check_repl_err1.png "alt=" 4d6551816415699e1cadd58947aa62f0.png "/>

Thu June 1:20:37-[error][/usr/local/perl5/vender_perl/mha/servermanger.pm, ln255] Got MySQL error when Connectin G 10.10.0.25 (10.10.0.25:3306): 1045:Access denied for user ' hk_mhaap ' @ ' DB-01 ' (using Password:yes) QL crash. Check MySQL server setings.

AT/USR/SHARE/PERL5/VENDER_PERL/MHA/SERVERMANGER.PM Line 251

Thu June 1:20:37-[error][/usr/share/perl5/vender_perl/mha/servermanger.pm, ln263] Got fatal error, stopping opera tions

Thu June 1:20:37-[error][/usr/share/perl5/vender_perl/mha/servermanger.pm, ln386] error happend on checking confi Gurations.

AT/USR/SHARE/PERL5/VENDER_PERL/MHA/SERVERMANGER.PM Line 300

Thu June 1:20:37-[error][/usr/share/perl5/vender_perl/mha/servermanger.pm, ln482] error happened on monitoring SE RVers.

Thu June 1:20:37-[info] Got exit code 1 (not master dead).

MySQL Replication Health is not ok!

===== Reason:the user's empty permission setting exists in MySQL's table (mentioned above):

Mysql>delete from Mysql.user where user= '; Mysql>flush privileges;

Error 2:

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/02/81fa502cb8ea9e70fbc537788a9635c9.png "title=" Master _check_repl_err2.png "alt=" 81fa502cb8ea9e70fbc537788a9635c9.png "/>

Can ' t exec "Mysqlbinlog": No such file or directory at/usr/share/perl5/vender_perl/mha/servermanger.pm line 99.

Mysqlbinlog Version Not found!

At/usr/bin/apply_diff_relay_logs Line 482

Thu June 1:20:37-[error][/usr/share/perl5/vender_perl/mha/servermanger.pm, ln195] slaves settings Check failed!

Thu June 1:20:37-[error][/usr/share/perl5/vender_perl/mha/servermanger.pm, ln375] Slave configuration failed.

Thu June 1:20:37-[error][/usr/share/perl5/vender_perl/mha/servermanger.pm, ln386] error happened on check Configu Rations. At/usr/bin/masterha_check_repl Line 48

Thu June 1:20:37-[error][/usr/share/perl5/vender_perl/mha/servermanger.pm, ln482] error happened on monitoring SE RVers.

Thu June 1:20:37-[info] Got exit code 1 (not master dead).

MySQL Replication Health is not ok!

= = = Reason: did not find the Mysqlbinlog command, setting environment variables here does not work, need to do a soft link:

Ln-s/usr/local/mysql/bin/mysqlbinlog/usr/bin/mysqlbinlog

Test success:

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/02/2e0adf465e63ecd14ddbc0ca80442cae.png "title=" Check_ Repl_ok.png "alt=" 2e0adf465e63ecd14ddbc0ca80442cae.png "/>

Verify OK, the command behind running keepalived is automatically run by the script, and can be ignored, regardless of the output content.

Eight, start the service

Nohup Masterha_manager--conf=/etc/masterha/masterha_default.cnf >/tmp/mha_manager.log </dev/null 2>&1 &

To view logs:

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/02/4eceae8c17db1cc37e672f9b36f3e348.png "title=" Master _log1.png "alt=" 4eceae8c17db1cc37e672f9b36f3e348.png "/>

Because some features do not add scripts, there is a warning.

Nine, keepalived installation configuration

Wget tar xzf keepalived-1.3.5.tar.gzcd keepalived-1.3.5./configure--prefix=/home/szjruser/keepalivedyum Install Openssl-devel./configure--prefix=/home/szjruser/keepalivedmakemake installln-s/home/szjruser/keepalived/sbin/ Keepalived/usr/sbin/keepalivedcp/home/szjruser/keepalived/etc/sysconfig/keepalived/etc/sysconfig/mkdir-p ~/ keepalived/lock/subsysmkdir/etc/keepalivedcp/home/szjruser/keepalived/etc/keepalived/keepalived.conf/etc/ Keepalived/chkconfig--add keepalivedchkconfig keepalived onchmod 755/etc/init.d/keepalived

To modify a configuration file:

10.10.1.12

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/02/3e3a9126b00769f9bd7d45c2ecbec552.png "style=" float: none; "title=" Keepalived01.png "alt=" 3e3a9126b00769f9bd7d45c2ecbec552.png "/>

10.10.0.5

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/02/ebe316a94e2caa37bb860092726eb298.png "style=" float: none; "title=" Keepalived02.png "alt=" Ebe316a94e2caa37bb860092726eb298.png "/>

Start the service:

/etc/init.d/keepalived start

This article is from the "I am not my" blog, please be sure to keep this source http://wangwq.blog.51cto.com/8711737/1978509

MySQL high-availability MHA architecture built