Play MySQL in Centos7 half-sync and KEEPALIVED+MHA (ii)

A semi-synchronous configuration of MySQL was completed in the previous article.

Link: in Centos7 to play MySQL semi-sync and Keepalived+mha (one), this article continues to complete the configuration of mha+keepalived.

Role	Ip	OS	Mysql	MHA
Mha-manager	192.168.1.92	CentOS 7	No	Mha-manager Mha-node
Mysql-master	192.168.1.151	CentOS 7	Mysql-master	Mha-node
Mysql-slave01	192.168.1.152	CentOS 7	Mysql-slave	Mha-node
Mysql-slave02	192.168.1.153	CentOS 7	Mysql-slave	Mha-node

Install Mha-node on all servers.

Installation dependencies

Yum install perl-dbd-mysql Perl-module-install cpan perl-dbi-y

Pull Mha-node installation package from Git

Mkdir/root/softcd/root/softgit Clone Https://github.com/kevin-hao/mha-node.gitcd Mha-node/perl Makefile.plmake && make Install

Again, four servers are all installed node.

Configure MHA Manager below

Installation dependencies

Yum install-y perl perl-config-tiny perl-email-date-forma perl-log-dispatch perl-mime-liteperl-mime-types Perl-mail-sender perl-mail-sendmail perl-mailtools perl-parallel-forkmanagerperl-params-validate perl-Time-HiRes Perl-timedate

Pull Mha-manager installation package from Git

Cd/root/softgit Clone CD Mha-managerperl makefile.plmake && make install

Configuring the Hosts File

vim/etc/hosts192.168.1.92 mha-manager192.168.1.151 mysql-master192.168.1.152 mysql-slave01192.168.1.153 MYSQL-SLAVE02 scp/etc/hosts [email protected]:/etc/scp/etc/hosts [Email protected]:/etc/scp/etc/hosts [em Ail protected]:/etc/

In the main library MySQL and participate in the election from the library to configure MHA monitoring users and REPL synchronization users, not to participate in the election from the library can not be configured.

Mysql> Grant all privileges on *. * to ' MHA ' @ ' 192.168.1.% ' identified by ' 123456 ';mysql> grant replication Slave on * . * to ' repl ' @ ' 192.168.1.% ' identified by ' REPL ';mysql> flush privileges;

Note: About participating in the election from the library also to configure these two users because the main library down, from the library to promote the main library, REPL is to continue and remaining from the library to maintain the master-slave relationship, MHA monitoring the user is for, MHA can continue to monitor, so that the restored original repository as a new standby to go online.

Specification MHA Directory

Mkdir-p/application/mha/confmkdir-p/application/mha/workstatus/app1mkdir-p/application/mha/logscp/root/soft/ mha-manager/samples/conf/*/application/mha/conf/mkdir-p/application/mha/workstatus/app1cd/application/mha/conf /CP app1.cnf App1.cnf.ori

The next file is very important and relates to the ability to start MHA.

Vim APP1.CNF

File contents

[Server default]port=3306user=mhapassword=123456repl_user=replrepl_password=replremote_workdir=/var/log/mha/ App1master_binlog_dir=/var/lib/mysql #这个是Master The Binlog directory of the-mysql master repository is not considered to be a local directory. Manager_workdir=/application/mha/workstatus/app1manager_log=/application/mha/logs/app1.log[server1]hostname= Mysql-master[server2]hostname=mysql-slave01candidate_master=1 #参与主库选举check_repl_delay =0 #默认情况下如果一个slave落后master 100M relay logs words, MHA will not select the slave as a new master, because for this slave the recovery takes a long time, by setting check_repl_delay=0, MHA trigger switch when selecting a new master, the replication delay will be ignored, which is useful for hosts with Candidate_master=1, because the candidate must be new in the process of switching master[server3]hostname= Mysql-slave02no_master=1 #设置之后不参与切换主, and you do not need to configure REPL here from the library, otherwise you need to configure.

Configuring the Global configuration file

CP masterha_default.cnf Masterha_default.cnf.oldvim Masterha_default.cnf[server Default]log-level=debugcheck_repl_ Delay=1check_repl_filter=1ping_interval=5ping_type=connect Set Relay Log Purge method (on each slave)

Configure relay log cleanup on all salve

mysql> set global relay_log_purge=0;

Add an automatic cleanup script in crontab example below

VIM/ETC/CRON.D/PURGE_RELAY_LOGS0 4 * * */usr/bin/purge_relay_logs--user=root--password=xxx--disable_relay_log_ Purge--port=3306--workdir=/var/lib/mysql/>>/application/mha/logs/purge_relay_logs.log 2>&1

Check the configuration with Masterha_check_ssh and MASTERHA_CHECK_REPL respectively

Masterha_check_ssh--conf=/application/mha/conf/app1.cnf

See [INFO] All SSH connection tests passed successfully. The typeface indicates that SSH is a pass.

Masterha_check_repl--conf=/application/mha/conf/app1.cnf

See MySQL Replication Health is OK. The typeface indicates that synchronization between databases is not a problem.

Start

Nohup masterha_manager--conf=/application/mha/conf/app1.cnf--remove_dead_master_conf--ignore_last_failover </ Dev/null 2>&1 &

Start parameter explanation:

–remove_dead_master_conf This parameter indicates that the old master library's IP will be removed from the configuration file when the master-slave switch occurs.

–ignore_last_failover by default, if the MHA detects a continuous outage and two downtime is less than 8 hours, the failover is not performed, and the reason for this limitation is to avoid the ping-pong effect. This parameter represents ignoring the file generated by the last MHA trigger switch, which, by default, is MHA in the log directory after the switchover, and this example is/application/mha/workstatus/ App1 generate App1.failover.complete file, the next time you switch the file will not be allowed to trigger the switch, unless you delete the file after the switch, for convenience, this is set to –ignore_last_failover.

Check MHA Manager Status

Masterha_check_status--conf=/application/mha/conf/app1.cnfapp1 (pid:43568) is running (0:PING_OK), Master: Mysql-master

Test

Turn off the database on the 151, tail-f the Mha-manager above the 92/application/mha/logs/app1.log can view the switch log

After the switch is completed to 153 MySQL above use show slave status\g; see if Master has become 152.

Some instructions on testing, in the test process if you want to verify that the data is lost, you can use Sysbench for pressure measurement, through the installation of Percona-toolkit pt-table-checksum to check data consistency, if there is inconsistent data found, You can use the Pt-table-sync tool to fix it.

About three Tests two times

When the main library is down, fix it and start on 151. Use on Mha-manager server

Grep-i "All other slaves should start" Manager.log

(No lock table required) or login to the new Main library 152 above show master status, (Need lock table and unlock) to view location and Binlog file information.

Change Master operation on 151

Change Master to master_host= ' 192.168.1.152 ', master_user= ' repl ', master_password= ' repl ', master_port=3306,master_ Log_file= ' master-bin.00000x ', master_log_pos=xxxx;

then start slave; View synchronization information show slave status\g;.

Go back to Mha-manager server and stop MHA

Masterha_stop--conf=/application/mha/conf/app1.cnf

Then edit the APP1.CNF, because the operation is the use of –remove_dead_master_conf this parameter, found Server1 disappeared, added, and put

Candidate_master=1

Check_repl_delay=0

Add to master, as a library from the new Main library, and participate in the election.

Then start MHA and complete a round of tests.

---------------------------Split Line---------------------------------

Configure keepalived below, first stop MHA

Masterha_stop--conf=/application/mha/conf/app1.cnf

Install keepalived on 151 and 152.

Yum install-y gcc kernel-devel openssl-devel popt-develyum install-y keepalived

Vim/etc/keepalived/keepalived.conf

The contents are as follows:

! configuration file for keepalivedglobal_defs {   notification_email  {        [email protected]   }    notification_email_from [email protected]   smtp_server smtp.126.com    smtp_connect_timeout 30   router_id lvs_devel}vrrp_script monitor_ mysql {                 script  "/etc/keepalived/scripts/monitor_mysql.sh"                  interval 1                  weight 2                 }track_script {                 monitor_mysql                         }vrrp_instance vi_1  {    state BACKUP    #主备都配置为BACKUP模式      interface eno16777736   #虚拟机是这个, use IP ADDR to view your network card     virtual_router_id  51    #主备必须相同     priority 100    advert_int  1    authentication {        auth_type  PASS     #主备必须相同         auth_pass 1111      #主备必须相同     }    virtual_ipaddress {         192.168.1.150      #也可以配置多个虚拟IP           #192.168.200.18    }} 

Configuring the Monitor MySQL script

Note: Here is one of the ways to turn off keepalived, you can also configure the MHA master_ip_failover script, specify the keepalived related settings, and then add in APP1.CNF

master_ip_failover_script=/path/master_ip_failover to control the keepalived process.

Mkdir-p/etc/keepalived/scripts/vim/etc/keepalived/scripts/monitor_mysql.sh

The contents are as follows:

#!/bin/bashmysql=mysqlmysql_host=127.0.0.1mysql_port=3306mysql_user=rootmysql_password=www.vcolco.comcheck_ Time=3#mysql  is working mysql_ok is 1 , mysql down mysql_ok  is 0MYSQL_OK=1function check_mysql_helth  () {$MYSQL  -h$mysql_host -u$mysql_user  -p$MYSQL_PASSWORD -P$MYSQL_PORT -e  "show status;"  >/dev/null 2>&1if [ $? = 0 ] ;then      MYSQL_OK=1else     MYSQL_OK=0fi     return  $MYSQL _ok}while [  $CHECK _time -ne 0 ]do     let  "CHECK_TIME  -= 1 "     check_mysql_helthif [  $MYSQL _ok = 1 ]  ; then     CHECK_TIME=0     exit 0fiif  [  $MYSQL _ok -eq 0 ] &&  [  $CHECK _time -eq 0 ]then     pkill  Keepalivedexit 1fisleep 1done

Start the keepalived of 151 and 152

Systemctl Start keepalived

View IP on 151 and 152 respectively

IP A

It is found that 151 of high priority is using 150 of this VIP (virtual IP), and 152 does not.

Test keepalived can use Pkill keepalived 151 of the keepalived process. Then go to 152 above using IP A to see if the VIP is drifting.

To this MySQL half-sync +mha+keepalived has been configured to complete.

This article is from the "Small Brick book" blog, please be sure to keep this source http://newbye.blog.51cto.com/5197506/1893099

Play MySQL in Centos7 half-sync and KEEPALIVED+MHA (ii)