Shulou(Shulou.com)06/01 Report--

This article is mainly about how to build a high-availability architecture of Mysql_MHA. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on how to build a high-availability architecture of Mysql_MHA can bring you some practical help.

Mysql_MHA High availability Architecture Building

Top of the form

Bottom of the form

Environment and compatibility Pack

System environment: centos6.5

Mysql: mysql-5.5.6 database is installed with source code, so I won't introduce it here.

Host allocation:

Master: 192.168.0.101 node1 (main library)

Slave1: 192.168.0.102 node2 (standby primary library)

Slave2: 192.168.0.103 node3 (slave library + MHA control node)

See attachment for MHA compatibility package

Top of the form

Bottom of the form

Add password-free login and authenticate each other

+ the following operations are performed on all three nodes # +

# # add hosts and modify their hostnames

192.168.0.101node1

192.168.0.102node2

192.168.0.103node3

# # adding a key

Ssh-keygen-t rsa

Cat/root/.ssh/id_rsa.pub > / root/.ssh/authorized_keys

# # add the keys of all three nodes to authorized_keys, including your own key

# # execute the following command on three nodes to check ssh

Ssh node1 date

Ssh node2 date

Ssh node3 date

# # only after successful login for mutual authentication can you continue the follow-up operation

Top of the form

Bottom of the form

Install the MHA node package

+ the following operations are performed on all three nodes # +

# # updating yum to Ali Source

Mv/etc/yum.repos.d/CentOS-Base.repo / etc/yum.repos.d/CentOS-Base.repo.old

Wget-O/etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repo

Yum clean all

Yum makecache

Yum update

# # install Environment package

Yum installperl-DBD-MySQL perl perl-devel cpan

Rpm-ivhmha4mysql-node-0.56-0.el6.noarch.rpm

# # after mha node installation is completed, the following four scripts will be generated under / usr/bin: (these tools are usually triggered by mha manger scripts without manual operation)

/ usr/bin/save_binary_logs # # Save and copy the binary log of master

/ usr/bin/apply_diff_relay_logs # # identify differential relay log events and apply their differential events to other slave

/ usr/bin/filter_mysqlbinlog # # remove unnecessary ROLLBACK events (MHA no longer uses this tool)

/ usr/bin/purge_relay_logs # # clear the relay log (does not block SQL threads)

Top of the form

Bottom of the form

Install the MHA manager node package

[node3] # tar xf mha.tar.gz

[node3] # cd mha

# # Log in to node3 and try to install mha4mysql-manager-0.56-0.el6.noarch. Sure enough, a package missing perl module was reported.

# # sort it according to the installation order as follows:

Compat-db43-4.3.29-15.el6

Perl-Mail-Sender-0.8.16-3.el6

Perl-Mail-Sendmail-0.79-12.el6

Perl-Config-Tiny-2.12-7.1.el6

Perl-Parallel-ForkManager-0.7.9-1.el6

Perl-Log-Dispatch-2.27-1.el6

# when installing the perl-Log-Dispatch-2.27-1.el6 package, you will also encounter packages with modules that need to be installed. Sort them according to the installation order as follows:

Perl-TimeDate-1.16-11.1.el6

Perl-MIME-Types-1.28-2.el6

Perl-MailTools-2.04-4.el6

Perl-Email-Date-Format-1.002-5.el6

Perl-Params-Validate-0.92-3.el6

Perl-MIME-Lite-3.027-2.el6

Then you can install mha4mysql-manager-0.56-0.el6.noarch normally.

Rpm-ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

# # after mha manager installation is completed, some scripts will be generated under / usr/bin:

/ usr/bin/masterha_check_repl # # check mysql replication status

/ usr/bin/masterha_check_ssh # # check the ssh configuration of MHA

/ usr/bin/masterha_check_status # # check the current MHA running status

/ usr/bin/masterha_conf_host # # add or remove configured server information

/ usr/bin/masterha_manager # # launch MHA

/ usr/bin/masterha_master_monitor # # Monitoring master downtime

/ usr/bin/masterha_master_switch # # Control failover (automatic or manual)

/ usr/bin/masterha_secondary_check # # is also a script to monitor whether master is down

/ usr/bin/masterha_stop # # close MHA

Top of the form

Bottom of the form

Modify the database from master to slave

# # modify the values of server-id = in three database files my.cf to 1,2,3 in turn

[node1] # mysql-uroot

Mysql > show master status

/ / View master_log_file and Position values

# # add repl and monitor users to all three libraries

Mysql > GRANT REPLICATION SLAVE ON *. * TO'repl'@'192.168.0.%' IDENTIFIED BY '123456'

Mysql > grant all privileges on *. * to'monitor'@'192.168.0.%' identified by '123456'

Mysql > flush privileges

# # execute the following statement on node1

Mysql > install plugin rpl_semi_sync_master soname'semisync_master.so'

/ / install the semisync_master plug-in in the main library

Mysql > set global rpl_semi_sync_master_enabled=1

Mysql > set globalrpl_semi_sync_master_timeout=30000

/ / means that the main database is in a transaction, and if the waiting time exceeds 30000 milliseconds, it will be downgraded to normal mode and will no longer wait for backup database. If the primary database detects again and the standby library is restored, it will automatically return to the Semi-sync state again.

# # Operation on node2 and node3:

Mysql > CHANGE MASTER TO

MASTER_HOST='192.168.0.101'

MASTER_PORT=3306

MASTER_USER='repl'

MASTER_PASSWORD='123456'

MASTER_LOG_FILE='mysql-bin.000004'

MASTER_LOG_POS=0

/ / the values of parameters MASTER_LOG_FILE= and MASTER_LOG_POS= fill in the values of the showmaster status query just now

Mysql > install plugin rpl_semi_sync_slave soname'semisync_slave.so'

Mysql > set global rpl_semi_sync_slave_enabled=1

Mysql > flush privileges

Mysql > start slave

# after mysql5.5, mysql introduces semi-sync function to ensure the consistency of master and slave database data.

Semi-sync means that MASTER only needs to receive the return message from one of the SLAVE and will commit; otherwise, it will have to wait until it is switched to asynchronous before submitting.

Advantages:

When the transaction returns to the client successfully, the log must exist on at least two hosts.

MySQL's Semi-sync is suitable for small transactions, and the latency between the two hosts is small, so Semi-sync can achieve zero data loss with little performance loss.

Disadvantages:

The completion of a single transaction adds an additional wait delay, depending on the quality of the network.

Top of the form

Bottom of the form

Manager node configuration

[node3] #

[node3] # mkdir-p/etc/masterha / masterha/app1 / masterha/scripts

[node3] # cd/etc/masterha/

[node3] # vimapp1.cnf

/ / the app1.cnf file is provided at the end of the text. Leave these two parameter values blank here and change them to the values in the app1.cnf file when you add the VIP address.

Master_ip_failover_script= ""

Master_ip_online_change_script= ""

# Detection configuration

# # detecting ssh configuration

[node3] # exportPERL5LIB=$PERL5LIB:/usr/lib/perl5/vendor_perl/

[node3] # masterha_check_ssh-- conf=/etc/masterha/app1.cnf

# # detecting mysql configuration

[node3] # masterha_check_repl-- conf=/etc/masterha/app1.cnf

# # check the running status of MHA

[node3] # masterha_check_status-- conf=/etc/masterha/app1.cnf

# run MHA

[node3] # nohupmasterha_manager-- conf=/etc/masterha/app1.cnf

< /dev/null >

/ masterha/app1/manager.log 2 > & 1 &

[node3] # tail-f/masterha/app1/manager.log

[node3] # masterha_check_status-- conf=/etc/masterha/app1.cnf

Top of the form

Bottom of the form

Verification

# # check the slave information on the master library first

Mysql > show slave hosts

+-+

| | Server_id | Host | Port | Master_id | |

+-+

| | 3 | | 3306 | 1 |

| | 2 | | 3306 | 1 |

+-+

# # close the master library on node1, and then check whether it is the main library on the slave library of node2

Mysql > show slave hosts

+-+

| | Server_id | Host | Port | Master_id | |

+-+

| | 3 | | 3306 | 2 | |

+-+

# # restoring High availability Architecture

# # after failover succeeds, MHA will stop. This is the master-slave replication from slave1 to slave2. Assuming that the 192.168.0.101 database is restored, we need to restore the highly available architecture and let MHA start.

# # what you need to do at this time is to start the 192.168.0.101 database, execute the following command on manager, and find CHANGEMASTER for execution.

[node1] # grep-I "All other slaves should start replication from here" / masterha/app1/manager.log

Fri Aug 2512 All other slavesshould start replication from here 11 All other slavesshould start replication from here 40 2017-[info]. Statement should

Be: CHANGE MASTERTO MASTER_HOST='192.168.0.102', MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000012'

MASTER_LOG_POS=328, MASTER_USER='repl', MASTER_PASSWORD='123456'

/ / pay attention to the values of three MASTER_HOST= MASTER_LOG_FILE= MASTER_LOG_POS=

[node1] # mysql-uroot

Mysql > CHANGEMASTER TO MASTER_HOST='192.168.0.102', MASTER_LOG_FILE='mysql-bin.000012',MASTER_LOG_POS=328, MASTER_USER='repl', MASTER_PASSWORD='123456'

Mysql > slavestart

[node2] # mysql-uroot

Mysql > showslave hosts

+-+

| | Server_id | Host | Port | Master_id | |

+-+

| | 3 | | 3306 | 2 | |

| | 1 | | 3306 | 2 |

+-+

# enable MHA service, and then disable mysql simulation downtime on node2

[node3] # nohupmasterha_manager-- conf=/etc/masterha/app1.cnf

< /dev/null >

/ masterha/app1/manager.log 2 > & 1 &

# # need to delete the app1.failover.complete file, otherwise an error will be reported

[node3] # rm-f/masterha/app1/app1.failover.complete

[node2] # servicemysql3306 stop

[node1] # mysql-uroot

Mysql > showslave hosts

+-+

| | Server_id | Host | Port | Master_id | |

+-+

| | 3 | | 3306 | 1 |

+-+

# # add slave1 to the cluster by performing the above operation (restoring highly available architecture)

Top of the form

Bottom of the form

VIP address drift

# Note that all host Nic information must be eth0

# # add a virtual address to node1 first

[node1] # / sbin/ifconfig eth0:1 192.168.0.100/24

# # modifying app1.cnf configuration file

Execute when master_ip_failover_script=/etc/masterha/master_ip_failover # master failover

Execute when master_ip_online_change_script=/etc/masterha/master_ip_online_change # master switchover

# # Editing master_ip_failover and master_ip_online_change files

[node3] # cd/etc/masterha/

[node3] # vimmaster_ip_failover

[node3] # vimmaster_ip_online_change

/ / the content of the configuration file is at the end of the document

Top of the form

Bottom of the form

App1.cnf profile

App1.cnf

[server default]

User=monitor

Password=123456

Ping_interval=10

# repl_workdir=/masterha/app1

Repl_user=repl

Repl_password=123456

Ssh_user=root

Master_ip_failover_script=/etc/masterha/master_ip_failover

Master_ip_online_change_script=/etc/masterha/master_ip_online_change

# report_script= / etc/masterha/send_report

Shutdown_script= ""

Secondary_check_script=/usr/bin/masterha_secondary_check-s node2-s node1

Manager_workdir=/masterha/app1

Manager_log=/masterha/app1/manager.log

# remote_workdir=/masterha/app1

[server1]

Hostname=192.168.0.101

Port=3306

Master_binlog_dir=/data/mysql/data3306

Candidate_master=1

# check_repl_delay=0

[server2]

Hostname=192.168.0.102

Port=3306

Master_binlog_dir=/data/mysql/data3306

Candidate_master=1

# check_repl_delay=0

[server3]

Hostname=192.168.0.103

# port=3306

# master_binlog_dir=/data/mysql/data3306

No_master=1

# node3 does not participate in the election, only for slave library and manager use

Read_only=1

Top of the form

Bottom of the form

Profile master_ip_failover

Master_ip_failover

#! / usr/bin/env perl

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

My $key = "1"

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

My $ssh_stop_vip = "/ sbin/ifconfig eth0:$keydown"

My $exit_code = 0

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 SCRIPTTEST====$ssh_stop_vip==$ssh_start_vip===\ n\ n"

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

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

# If youmanage master ip address at global catalog database

# invalidate orig_master_ip here.

My$exit_code = 1

Eval {

Print "\ n\ n\ nFlowers *\ n"

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

Print "* *\ n\ n"

& stop_vip ()

$exit_code = 0

}

If ($@) {

Warn "Got Error: $@\ n"

Exit$exit_code

}

Exit$exit_code

}

Elsif ($command eq "start") {

# allarguments are passed.

# If youmanage master ip address at global catalog database

# activatenew_master_ip here.

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

My $exit_code = 10

Eval {

Print "\ n\ n\ nFlowers *\ n"

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

Print "* *\ n\ n"

& start_vip ()

$exit_code = 0

}

If ($@) {

Warn$@

Exit $exit_code

}

Exit$exit_code

}

Elsif ($command eq "status") {

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

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

Exit0

}

Else {

& usage ()

Exit1

}

}

# A simple system call that enable the VIP on the newmaster

Sub start_vip () {

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

}

# A simple system call that disable the VIP on theold_master

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=po

Rt-new_master_host=host-new_master_ip=ip-new_master_port=port\ n "

}

Top of the form

Bottom of the form

Profile master_ip_online_change

Master_ip_online_change

#! / usr/bin/env perl

Use strict

Use warnings FATAL = > 'all'

Use Getopt::Long

My $vip = '192.168.0.100 Universe 24'

My $key = "1"

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

My $ssh_stop_vip = "/ sbin/ifconfig eth0:$keydown"

My $exit_code = 0

My (

$command, $orig_master_is_new_slave,$orig_master_host

$orig_master_ip, $orig_master_port, $orig_master_user

$orig_master_password, $orig_master_ssh_user, $new_master_host

$new_master_ip, $new_master_port, $new_master_user

$new_master_password, $new_master_ssh_user

);

GetOptions (

'command=s' = >\ $command

'orig_master_is_new_slave' = >\ $orig_master_is_new_slave

'orig_master_host=s' = >\ $orig_master_host

'orig_master_ip=s' = >\ $orig_master_ip

'orig_master_port=i' = >\ $orig_master_port

'orig_master_user=s' = >\ $orig_master_user

'orig_master_password=s' = >\ $orig_master_password

'orig_master_ssh_user=s' = >\ $orig_master_ssh_user

'new_master_host=s' = >\ $new_master_host

'new_master_ip=s' = >\ $new_master_ip

'new_master_port=i' = >\ $new_master_port

'new_master_user=s' = >\ $new_master_user

'new_master_password=s' = >\ $new_master_password

'new_master_ssh_user=s' = >\ $new_master_ssh_user

);

Exit & main ()

Sub main {

# print "\ n\ nIN SCRIPTTEST====$ssh_stop_vip==$ssh_start_vip===\ n\ n"

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

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

# If youmanage master ip address at global catalog database

# invalidate orig_master_ip here.

My $exit_code = 1

Eval {

Print "\ n\ n\ nFlowers *\ n"

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

Print "* *\ n\ n"

& stop_vip ()

$exit_code = 0

}

If ($@) {

Warn "Got Error: $@\ n"

Exit$exit_code

}

Exit$exit_code

}

Elsif ($command eq "start") {

# allarguments are passed.

# If youmanage master ip address at global catalog database

# activatenew_master_ip here.

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

My $exit_code = 10

Eval {

Print "\ n\ n\ nFlowers *\ n"

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

Print "* *\ n\ n"

& start_vip ()

$exit_code = 0

}

If ($@) {

Warn$@

Exit$exit_code

}

Exit$exit_code

}

Elsif ($command eq "status") {

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

`ssh$orig_master_ssh_user\ @ $orig_master_host\ "$ssh_start_vip\" `

Exit0

}

Else {

& usage ()

Exit1

}

}

# A simple system call that enable the VIP on the newmaster

Sub start_vip () {

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

}

# A simple system call that disable the VIP on theold_master

Sub stop_vip () {

`ssh $orig_master_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=po

Rt-new_master_host=host-new_master_ip=ip-new_master_port=port\ n "

}

The method of building Mysql_MHA high availability architecture will first tell you about this. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.