Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the process of building MHA under mysql". In the daily operation, I believe many people have doubts about the process of building MHA under mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "the process of building MHA under mysql". Next, please follow the editor to study!

Environment

1 master 2 slaves, manager in slave library

Main library: 192.168.137.201

Slave Library (manager): 192.168.137.202

Slave library: 192.168.137.203

Required software packages:

Epel-release-7-10.noarch.rpm

Mha4mysql-node-0.56.tar.gz

Mha4mysql-manager-0.56.tar.gz

Mysql-5.7.14-linux-glibc2.5-x86_64.tar.gz

Master-slave building

Create a user

Create user 'repl'@'%' identified by' repl'

Grant all on *. * to 'repl'@'%'

Create user 'root'@'%' identified by' root'

Grant all on *. * to 'root'@'%'

Establish master and slave

CHANGE MASTER TO

MASTER_HOST='192.168.137.201'

MASTER_USER='repl'

MASTER_PASSWORD='repl'

MASTER_PORT=3306

Master_auto_position=1

Configure ssh to trust each other

C1 server

[root@c1] # ssh-keygen-t rsa

[root@c1] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.137.201

[root@c1] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.137.202

[root@c1] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.137.203

C2 server

[root@c2] # ssh-keygen-t rsa

[root@c2] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.137.201

[root@c2] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.137.202

[root@c2] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.137.203

C3 server

[root@c3] # ssh-keygen-t rsa

[root@c3] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.137.201

[root@c3] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.137.202

[root@c3] # ssh-copy-id-I / root/.ssh/id_rsa.pub root@192.168.137.203

Install MHA softwar

Install the epel source (all nodes)

Centos6

Rpm-ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

Centos7

Rpm-ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-10.noarch.rpm

Install MHA softwar

Install dependency packages (all nodes)

Yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN net-tools-y

All epel,epel-debuginfo,epel-source of the epel source, enabled

Software download

MHA software download

Https://downloads.mariadb.com/MHA/

Https://github.com/yotoobo/linux/tree/master/mha

Install MHA-node (all nodes)

Tar zxf mha4mysql-node-0.56.tar.gz

Cd mha4mysql-node-0.56

Perl Makefile.PL

Make & & make install

Install MHA-manager (202)

Tar zxf mha4mysql-manager-0.56.tar.gz

Cd mha4mysql-manager-0.56

Perl Makefile.PL

Make & & make install

Configure MHA-manager-conf files (202)

Vi / data/mha/3306/log/mha.cnf

[server default]

Client_bindir=/usr/local/mysql/bin/

Manager_log=/data/mha/3306/log/manager.log

Manager_workdir=/data/mha/3306/log

Master_binlog_dir=/data/mysql/mysql3306/logs

Master_ip_failover_script=/usr/local/bin/master_ip_failover

Master_ip_online_change_script=/usr/local/bin/master_ip_online_change

Report_script=/usr/local/bin/send_report

# init_conf_load_script=/usr/local/bin/load_cnf

Remote_workdir=/data/mysql/mysql3306/tmp

# secondary_check_script= / usr/local/bin/masterha_secondary_check-s 192.168.137.201-s 192.168.137.202

User=root

Password=root

Ping_interval=3

Repl_user=repl

Repl_password=repl

Ssh_port=22

Ssh_user=root

# max_ping_errors=40

[server1]

Hostname=192.168.137.201

Port=3306

[server2]

# check_repl_delay=0

Hostname=192.168.137.202

Port=3306

[server3]

Candidate_master=1

Check_repl_delay=0

Hostname=192.168.137.203

Port=3306

Set the slave library readonly (all slave libraries)

Mysql > show global variables like "read_only%"

Mysql > flush tables with read lock

Mysql > set global read_only=1

Mysql > show global variables like "read_only%"

Copy script

Cp / usr/local/mha_manager/samples/scripts/master_ip_failover / usr/local/bin/

Cp / usr/local/mha_manager/samples/scripts/master_ip_online_change / usr/local/bin/

Cp / usr/local/mha_manager/samples/scripts/power_manager / usr/local/bin/

Cp / usr/local/mha_manager/samples/scripts/send_report / usr/local/bin/

Modify master_ip_failover script

There is no VIP switch in the original script

#! / usr/bin/env perl

# Copyright (C) 2011 DeNA Co.,Ltd.

#

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; either version 2 of the License, or

# (at your option) any later version.

#

# This program is distributed in the hope that it will be useful

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

# GNU General Public License for more details.

#

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc.

# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

# # Note: This is a sample script and is not complete. Modify the script based on your environment.

Use strict

Use warnings FATAL = > 'all'

Use Getopt::Long

Use MHA::DBHelper

My (

$command, $ssh_user, $orig_master_host, $orig_master_ip

$orig_master_port, $new_master_host, $new_master_ip, $new_master_port

);

My $vip='192.168.137.88/24'

My $key= "1"

My $ssh_start_vip = "/ sbin/ifconfig ens33:$key $vip"

My $ssh_stop_vip= "/ sbin/ifconfig ens33:$key down"

GetOptions (

'command=s' = >\ $command

'ssh_user=s' = >\ $ssh_user

'orig_master_host=s' = >\ $orig_master_host

'orig_master_ip=s' = >\ $orig_master_ip

'orig_master_port=i' = >\ $orig_master_port

'new_master_host=s' = >\ $new_master_host

'new_master_ip=s' = >\ $new_master_ip

'new_master_port=i' = >\ $new_master_port

);

Exit & main ()

Sub main {

If ($command eq "stop" | | $command eq "stopssh") {

# $orig_master_host, $orig_master_ip, $orig_master_port are passed.

# If you manage master ip address at global catalog database

# invalidate orig_master_ip here.

My $exit_code = 1

Eval {

Print "Disabling the VIP on old master: $orig_master_host\ n"

& stop_vip ()

$exit_code = 0

}

If ($@) {

Warn "Got Error: $@\ n"

Exit $exit_code

}

Exit $exit_code

}

Elsif ($command eq "start") {

# all arguments are passed.

# If you manage master ip address at global catalog database

# activate new_master_ip here.

# You can also grant write access (create user, set read_only=0, etc) here.

My $exit_code = 10

Eval {

Print "Enabling the VIP-$vip on the new master-$new_master_host\ n"

& start_vip ()

$exit_code = 0

}

If ($@) {

Warn $@

# If you want to continue failover, exit 10.

Exit $exit_code

}

Exit $exit_code

}

Elsif ($command eq "status") {

Print "Checking the Status of the script.. ok\ n"

# do nothing

Exit 0

}

Else {

& usage ()

Exit 1

}

}

Sub start_vip () {

`ssh $ssh_user\ @ $new_master_host\ "$ssh_start_vip\" `

}

Sub stop_vip () {

`ssh $ssh_user\ @ $orig_master_host\ "$ssh_stop_vip\" `

}

Sub usage {

Print

"Usage: master_ip_failover-- command=start | stop | stopssh | status-- orig_master_host=host-- orig_master_ip=ip-- orig_master_port=port-- new_master_host=host-- new_master_ip=ip-- new_master_port=port\ n"

}

Detect profile (202 nodes)

[root@c2] # masterha_check_repl-- conf=/data/mha/3306/log/mha.cnf

Add VIP to the main library

[root@c1 ~] # ifconfig ens33:1 192.168.137.88 Universe 16

Or / sbin/ip addr add 192.168.137.88ax 32 dev ens33

Delete VIP ifconfig ens33:1 down

/ sbin/ip addr del 192.168.137.88/32 dev ens33

MHA manager startup and shutdown

[root@c2] # nohup masterha_manager-- conf=/data/mha/3306/log/mha.cnf

< /dev/null >

/ data/mha/3306/log/manager.log 2 > & 1 &

[root@c2] # masterha_stop-- conf=/data/mha/3306/log/mha.cnf

Attention item

All master and slave data are identical, including business data and system table data, otherwise it will not be able to cut to the new master library during the failover.

When switching VIP, each server needs to have net-tools packages and be able to execute ifconfig

At this point, the study on "the process of building MHA under mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.