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

This article mainly shows you "how to build MHA in mysql". The content is simple and clear. I hope it can help you solve your doubts. Let me lead you to study and learn this article "how to build MHA in mysql".

Operating system:

Debian5.0.2

Database version:

Mysql5.0.51a

Structure:

Manager: 192.168.1.136 (M1)

Master: 192.168.1.20 3306 (N1)

Slave1: 192.168.1.20 3307 (N2)

Slave2: 192.168.1.20 3308 (N3)

Slave3: 192.168.1.20 3309 (N4)

Package preparation:

Mha4mysql-manager_0.55-1_all.deb (and corresponding binary package, source package)

Mha4mysql-node_0.54-1_all.deb

Ready:

① installation dependency package

Apt-get install libdbd-mysql-perl

Apt-get install libconfig-tiny-perl

Apt-get install liblog-dispatch-perl

Apt-get install libparallel-forkmanager-perl

Centos version (yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager)

② installs mha manager package and node package

M1:dpkg-I mha4mysql-manager_0.55-1_all.deb

N1:dpkg-I mha4mysql-node_0.54-1_all.deb

N2:dpkg-I mha4mysql-node_0.54-1_all.deb

N3:dpkg-I mha4mysql-node_0.54-1_all.deb

N4:dpkg-I mha4mysql-node_0.54-1_all.deb

③ host trust (mysql users are used here because of the company environment)

M1:

# su mysql

# ssh-keygen-t rsa

# cat ~ / .ssh/id_rsa.pub

Add the public key content to the corresponding users (here are mysql users) under. / ssh/authorized_keys to realize secret-free login from M1 to N1Magic N2Magi N3Magi N4.

In addition, there is a hole here, that is, the server side of secret-free login (N1Magi N2jinN3Magi N4), that is, the / etc/ssh/sshd_config file must open public key login.

The client (M1) of secret-free login (M1), that is, the / etc/ssh/ssh_config file, cannot close public key login (after this parameter is set, it still seems to be impossible to log in without secret in the company environment. Finally, the default configuration is used).

There is also mutual trust between N1 and N2, N3 and N4 nodes.

2016-12-4 22:44:12 ADD

Ssh clients must note the following parameters before they can log in without secret.

PreferredAuthentications password

③ creates a new mysql mha management account, and mysql repl replicates users

④ New mha working directory

M1: mkdir-p / opt/mha_manager/log chown-R mysql / opt/mha_manager

N1~N4:mkdir-p / opt/mha_node chown-R mysql / opt/mha_node

⑤ configuration manager

M1:

# mkdir-p / etc/mha

# cat / etc/mha/app1.conf

Manager_workdir=/opt/mha_manager_work

Manager_log=/opt/mha_manager_work/log/app1.log

Remote_workdir=/opt/mha_node

Change the owner to the ssh trust account for the above configuration file (here is mysql)

In addition (note for mysql installed in source code):

When mha remote login operates mysql, the mysql under / usr/local/mysql/bin is not used by default, and mysqlbinlog will report the corresponding error message.

At this time, we need to do the following

# ln-s / usr/local/mysql/bin/mysqlbinlog / usr/bin/mysqlbinlog

# ln-s / usr/local/mysql/bin/mysql / usr/bin/mysql

④ enables manager

It is important to note here that do not start in the background, that is, do not start in the form of masterha_manager-- conf=/etc/mha/app1.conf &, or you will report an error to exit. We start at the foreground, and then ctrl+z and bg are put into the background.

At this point, the whole construction process is complete.

Note:

① slave had better set up read_only=1 relay_log_purge=0 and delete relay_log using scheduled tasks.

After failover, ② mha will record the corresponding chang master to master_xxx and other information in app1.log, so we can use this information to manually

Suo Kang master

Connect to the new master

③ can use (masterha_master_switch-- master_state=alive-- conf=/etc/mha/app1.cnf) to manually switch the master, and then manually add the new master as the slave of the old master (step 2). Note that mha manager must be turned off (masterha_check_status-- conf=xxx)

④ We can configure the elegant script of vip after the main library goes down in app1.conf (

Master_ip_failover_script=/usr/bin/masterha_ip_failover

But for the first time you have to manually add vip to master.

Masterha_ip_failover script attached

Click (here) to collapse or open

#! / 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

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.1.111 Universe 24'

My $key ='0'

My $ssh_start_vip = "sudo / sbin/ifconfig eth2:$key $vip"

My $ssh_stop_vip = "sudo / sbin/ifconfig eth2:$key down"

# my $ssh_Bcast_arp = "/ usr/bin/arping-c 3-A 10.1.99.233"; # ARP reply mode to update neighbors. If not, the server will automatically wait for the vip cache to expire, and the VIP will be unavailable for a certain period of time.

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 {

Print "\ n\ nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\ n\ n"

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

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") {

My $exit_code = 10

Eval {

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

& start_vip ()

# & start_arp ()

$exit_code = 0

}

If ($@) {

Warn $@

Exit $exit_code

}

Exit $exit_code

}

Elsif ($command eq "status") {

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

Exit 0

}

Else {

& usage ()

Exit 1

}

}

Sub start_vip () {

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

}

# sub start_arp () {

# `ssh $ssh_user\ @ $new_master_host\ "$ssh_Bcast_arp\" `

#}

Sub stop_vip () {

`ssh-p56000 $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"

}

The above is all the content of the article "how to build MHA in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.