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

This article introduces you how to master and slave MHA in MYSQL architecture, the content is very detailed, interested friends can refer to, hope to be helpful to you.

MHA-GTID mode

MasterHigh Availability Manager and Toolsfor MySQL. Is a script management tool written in PERL voice, which is suitable for MySQL Replication environment and is designed to maintain the high availability of the master main library.

MHA is an automatic master failover and slave promotion package based on standard MySQL replication (asynchronous, semi-synchronous)

MHA Manager management nodes can be deployed on a separate server to manage multiple master-slave clusters, or on a single slave. MHA Manager probes node nodes in a cluster

MHA has two components.

MHA Manager (Management Node)

MHA node (data node)

Download address:

Https://github.com/yoshinorim/mha4mysql-manager/releases

Https://github.com/yoshinorim/mha4mysql-node/releases

The MHA Manager management node can be deployed on a single server to manage multiple master-slave clusters, or it can be deployed on a single slave. MHA Manager detects the node nodes in the cluster. When it is found that the master fails, it can automatically upgrade the slave with the latest data to the new master, and then direct all other slave to the new master.

The whole failover process is transparent to the application, and the MHA node data node can run on each Mysql server

The purpose of MHA is to maintain the high availability of the master library in MYSQL Replication. Its most important feature is that it can fix the difference logs between multiple slave, finally make all slave keep data consistent, and then choose one of them to act as the new master and point the other slave to it. When the master fails, the position number of the master library binlog can be read by comparing the slave O thread, and the nearest slave can be selected as the alternative master library. Other slave libraries can generate different relay logs by comparing with the alternative master database, apply the binlog saved from the original master to the alternative master database, and promote the alternative master database to master. Finally, apply the corresponding differential relay logs on other slave and start replication from the new master.

Environment configuration

\

Primary server 1

From server 1

From Server 2

OS

Centos el7.x86_64

Centos el7.x86_64

Centos el7.x86_64

IP

192.168.31.79

192.168.31.188

192.168.31.90

HOSTNAME

Mysql

Mysql2

Mysql3

Mysql-version

5.7.23

5.7.23

5.7.23

Database

TEST

Remarks 1

Vip 192.168.31.100

Remark 2

Management node

Remark 3

Candidate node

Remark 4

Name of single network card: enp0s3

Name of single network card: enp0s3

Name of single network card: enp0s3

1. Install Mysql

Install mysql on three nodes

At the same time, several tables are established under the main node database TEST and TEST database for testing purposes.

two。 Set up an account

All three nodes are running

Set up a replication account

Mysql > create user 'rep1'@'192.168.31.%' identified by' Oracle123'

Mysql > grant replication slave on *. * to 'rep1'@'192.168.31.%'

Mysql > flush privileges

Set up a management account

Mysql > create user 'dba'@'192.168.31.%' identified by' Oracle123'

Mysql > grant all privileges on *. * to 'dba'@'192.168.31.%'

Mysql > flush privileges

3. Edit configuration file

Primary server 1

[root@mysql ~] # vi / etc/my.cnf

Add the following content

Gtid_mode=on

Enforce_gtid_consistency=on

Log_bin=on

Binlog_format=row

Server-id=79

Log-bin = master-log

Relay-log = relay-log

Skip_name_resolve

From server 1

[root@mysql2 ~] # vi / etc/my.cnf

Gtid_mode=on

Enforce_gtid_consistency=on

Log_slave_updates=1

Server-id=188

Relay-log = relay-log

Log-bin = master-log

Skip_name_resolve

Relay_log_purge = 0

# # read_only = ON the slave library will be selected as the candidate master library, so cancel read_only

From Server 2

[root@mysql3 ~] # vi / etc/my.cnf

Gtid_mode=on

Enforce_gtid_consistency=on

Log_slave_updates=1

Server-id=90

Relay-log = relay-log

Log-bin = master-log

Skip_name_resolve

Relay_log_purge = 0

Read_only = ON

4. Backup and restore database

Primary server 1

Back up the TEST database

[root@mysql] # mysqldump-- single-transaction-uroot-pOracle123 TEST > TEST_20200316.sql

From server 1

[root@mysql2] # scp root@192.168.31.79:/root/TEST_20200316.sql.

Mysql > create database TEST

[root@mysql2] # mysql-uroot-pOracle123 TEST

< TEST_20200316.sql 从服务器2 [root@mysql3 ~]# scp root@192.168.31.79:/root/TEST_20200316.sql . mysql>

Create database TEST

[root@mysql3] # mysql-uroot-pOracle123 TEST

< TEST_20200316.sql 5.重启数据库 三个节点都重启 [root@mysql ~]# service mysql stop [root@mysql ~]# service mysql start 6.从服务器配置 从服务器两个节点都执行 [root@mysql2 bin]# mysql -uroot -p mysql>

Change master to master_host='192.168.31.79',master_user='rep1',master_password='Oracle123',master_port=3306,master_auto_position=1

Mysql > start slave

Mysql > show slave status\ G

Note: if there is an error in the above execution of change master, Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

The solution is as follows:

Mysql > change master to master_auto_position=0

Mysql > start slave

Note: if an error occurs in the execution of the above

Last_Error: Error 'Table' test4' already exists' on query. Default database: 'TEST'. Query: 'create table test4 (id integer,name varchar)'

The solution is as follows:

Main library: mysql > SHOW MASTER STATUS\ g-- get File Position

Execute from the library

Mysql > change master to master_host='192.168.31.79',master_user='rep1', master_password='Oracle123',master_port=3306,master_log_file='master-log.000001',master_log_pos=154

Primary server 1

[root@mysql] # mysql-uroot-p

Mysql > show master status

7. Verify master-slave synchronization

Primary server 1

Mysql > use TEST

Mysql > insert into test2 values (1)

Mysql > commit

From server 1

Mysql > use TEST

Mysql > select * from test2

From Server 2

Mysql > use TEST

Mysql > select * from test2

8. Mutual trust in the configuration of installing MHA

All three nodes are executed (including management nodes)

[root@mysql ~] # cd .ssh /

[root@mysql .ssh] # ssh-keygen-t dsa-P'- f id_dsa

[root@mysql .ssh] # cat id_dsa.pub > > authorized_keys

The master server receives the secret key

Master server operation

[root@mysql .ssh] # scp 192.168.31.188:/root/.ssh/id_dsa.pub. / id_dsa.pub_188

[root@mysql .ssh] # scp 192.168.31.90:/root/.ssh/id_dsa.pub. / id_dsa.pub_90

[root@mysql .ssh] # cat id_dsa.pub_188 > > authorized_keys

[root@mysql .ssh] # cat id_dsa.pub_90 > > authorized_keys

[root@mysql .ssh] # scp authorized_keys 192.168.31.188:/root/.ssh/

[root@mysql .ssh] # scp authorized_keys 192.168.31.90:/root/.ssh/

Three nodes are configured with host

All three nodes execute

[root@mysql ~] # vi / etc/hosts

Add as follows:

192.168.31.79 mysql

192.168.31.188 mysql2

192.168.31.90 mysql3

Verification

All three nodes test each other and succeed without entering a password.

[root@mysql .ssh] # ssh mysql2

[root@mysql .ssh] # ssh mysql3

9. Install MHA related rpm

NODE node installation

[root@mysql soft] # yum install-y perl-CPAN

[root@mysql soft] # yum install-y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

Manage node installation (i.e. from server 1)

If the package is missing, you can download it and install it.

Https://centos.pkgs.org/

[root@mysql2 ~] # yum install perl-Config-Tiny perl-Email-Date-Format perl-File-Remove perl-Log-Dispatch perl-Mail-Sender perl-Mail-Sendmail perl-MIME-Lite perl-MIME-Types perl-Module-Install perl-Module-ScanDeps perl-Parallel-ForkManager perl-YAML-y

[root@mysql2 soft] # yum install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

10. Configure MHA script

Every node

[root@mysql ~] # cd / usr/local/

[root@mysql local] # mkdir mha

Management node (slave server 1)

[root@mysql2 soft] # mkdir / usr/local/mha

[root@mysql2 mha] # vi mha.conf

The content is added as follows:

[server default]

User = dba

Password = Oracle123

Ssh_user = root

Repl_user = rep1

Repl_password = Oracle123

Ping_interval = 1

Ping_type = SELECT

Manager_workdir=/usr/local/mha

Manager_log=/usr/local/mha/manager.log

Remote_workdir=/usr/local/mha

Master_ip_failover_script= "/ usr/local/mha/master_ip_failover"

Master_ip_online_change_script= "/ usr/local/mha/master_ip_online_change"

Shutdown_script= ""

Report_script= ""

# check_repl_delay=0

[server1]

Hostname=mysql

Port=3306

Master_binlog_dir= "/ u01/data"

Candidate_master=1 # # give priority to selecting this node as a candidate node

[server2]

Hostname=mysql2

Port=3306

Master_binlog_dir= "/ u01/data"

Candidate_master=1

[server3]

Hostname=mysql3

Port=3306

Master_binlog_dir= "/ u01/data"

No_master = 1 # # do not select this node as a candidate node

[root@mysql2 mha] # vi master_ip_failover

Add the following content

#! / 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.31.100 IP 32 seconds; # Virtual IP, the ip details refer to step 12 to add IP, showing the result

My $key = "0"

My $int = "enp0s3"

# # my $ssh_start_vip = "/ sbin/ifconfig $int:$key $vip";-- this environment is centos7, so note this directive

# # my $ssh_stop_vip = "/ sbin/ifconfig $int:$key down";-- this instruction is invalid because the environment is centos7

My $ssh_start_vip = "/ sbin/ip addr add $vip dev $int"

My $ssh_stop_vip = "/ sbin/ip addr del $vip dev $int"

My $arp_effect = "/ sbin/arping-Uq-s192.168.31.100-I $int 192.168.31.255-c 3"; # Virtual IP and gatway

# my $test = "echo successfull > / tmp/test.txt"

$ssh_user = "root"

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

# $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 $@

Exit $exit_code

}

Exit $exit_code

}

Elsif ($command eq "status") {

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

# `ssh $ssh_user\ @ cluster1\ "$ssh_start_vip\" `

& status ()

Exit 0

}

Else {

& usage ()

Exit 1

}

}

# A simple system call that enable the VIP on the new master

Sub start_vip () {

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

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

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

}

# A simple system call that disable the VIP on the old_master

Sub stop_vip () {

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

}

Sub status () {

Print `ssh $ssh_user\ @ $orig_master_host\ "ip add show $int\" `

}

Sub usage {

Print

"Usage: master_ip_failover-- command=start | stop | stopssh | status-- orig_master_host=host-- orig_maste

R_ip=ip-orig_master_port=port-new_master_host=host-new_master_ip=ip-new_master_port=port\ n "

}

[root@mysql2 mha] # vi master_ip_online_change

The content is added as follows:

#! / usr/bin/env perl

Use strict

Use warnings FATAL = > 'all'

Use Getopt::Long

Use MHA::DBHelper

Use MHA::NodeUtil

Use Time::HiRes qw (sleep gettimeofday tv_interval)

Use Data::Dumper

My $_ tstart

My $_ running_interval = 0.1

My $vip = "192.168.31.100gam32"

My $if = "enp0s3"

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 drop_vip {

My $output = `ssh-o ConnectTimeout=15-o ConnectionAttempts=3 $orig_master_host / sbin/ip addr del $vip/32 dev $if`

}

Sub add_vip {

My $output = `ssh-o ConnectTimeout=15-o ConnectionAttempts=3 $new_master_host / sbin/ip addr add $vip/32 dev $if`

}

Sub current_time_us {

My ($sec, $microsec) = gettimeofday ()

My $curdate = localtime ($sec)

Return $curdate. "". Sprintf ("d", $microsec)

}

Sub sleep_until {

My $elapsed = tv_interval ($_ tstart)

If ($_ running_interval > $elapsed) {

Sleep ($_ running_interval-$elapsed)

}

}

Sub get_threads_util {

My $dbh = shift

My $my_connection_id = shift

My $running_time_threshold = shift

My $type = shift

$running_time_threshold = 0 unless ($running_time_threshold)

$type = 0 unless ($type)

My @ threads

My $sth = $dbh- > prepare ("SHOW PROCESSLIST")

$sth- > execute ()

While (my $ref = $sth- > fetchrow_hashref ()) {

My $id = $ref- > {Id}

My $user = $ref- > {User}

My $host = $ref- > {Host}

My $command = $ref- > {Command}

My $state = $ref- > {State}

My $query_time = $ref- > {Time}

My $info = $ref- > {Info}

$info = ~ s / ^\ s * (. *?)\ if defined ($info)

Next if ($my_connection_id = = $id)

Next if (defined ($query_time) & & $query_time

< $running_time_threshold ); next if ( defined($command) && $command eq "Binlog Dump" ); next if ( defined($user) && $user eq "system user" ); next if ( defined($command) && $command eq "Sleep" && defined($query_time) && $query_time >

= 1)

If ($type > = 1) {

Next if (defined ($command) & & $command eq "Sleep")

Next if (defined ($command) & & $command eq "Connect")

}

If ($type > = 2) {

Next if (defined ($info) & & $info = ~ m / ^ select / I)

Next if (defined ($info) & & $info = ~ m / ^ show / I)

}

Push @ threads, $ref

}

Return @ threads

}

Sub main {

If ($command eq "stop") {

# # Gracefully killing connections on the current master

# 1. Set read_only= 1 on the new master

# 2. DROP USER so that no app user can establish new connections

# 3. Set read_only= 1 on the current master

# 4. Kill current queries

# * Any database access failure will result in script die.

My $exit_code = 1

Eval {

# # Setting read_only=1 on the new master (to avoid accident)

My $new_master_handler = new MHA::DBHelper ()

# args: hostname, port, user, password, raise_error (die_on_error) _ or_not

$new_master_handler- > connect ($new_master_ip, $new_master_port

$new_master_user, $new_master_password, 1)

Print current_time_us (). "Set read_only on the new master.."

$new_master_handler- > enable_read_only ()

If ($new_master_handler- > is_read_only ()) {

Print "ok.\ n"

}

Else {

Die "Failed!\ n"

}

$new_master_handler- > disconnect ()

# Connecting to the orig master, die if any database error happens

My $orig_master_handler = new MHA::DBHelper ()

$orig_master_handler- > connect ($orig_master_ip, $orig_master_port

$orig_master_user, $orig_master_password, 1)

# # Drop application user so that nobody can connect. Disabling per-session binlog beforehand

$orig_master_handler- > disable_log_bin_local ()

# print current_time_us (). "Drpping app user on the orig master..\ n"

Print current_time_us (). "drop vip $vip..\ n"

# drop_app_user ($orig_master_handler)

& drop_vip ()

# # Waiting for N * 100milliseconds so that current connections can exit

My $time_until_read_only = 15

$_ tstart = [gettimeofday]

My @ threads = get_threads_util ($orig_master_handler- > {dbh})

$orig_master_handler- > {connection_id})

While ($time_until_read_only > 0 & & $# threads > = 0) {

If ($time_until_read_only% 5 = = 0) {

Printf

"% s Waiting all running d threads are disconnected.. (max% d milliseconds)\ n"

Current_time_us (), $# threads + 1, $time_until_read_only * 100

If ($# threads

< 5 ) { print Data::Dumper->

New ([$_])-> Indent (0)-> Terse (1)-> Dump. "\ n"

Foreach (@ threads)

}

}

Sleep_until ()

$_ tstart = [gettimeofday]

$time_until_read_only--

@ threads = get_threads_util ($orig_master_handler- > {dbh})

$orig_master_handler- > {connection_id})

}

# # Setting read_only=1 on the current master so that nobody (except SUPER) can write

Print current_time_us (). "Set read_only=1 on the orig master.."

$orig_master_handler- > enable_read_only ()

If ($orig_master_handler- > is_read_only ()) {

Print "ok.\ n"

}

Else {

Die "Failed!\ n"

}

# # Waiting for M * 100milliseconds so that current update queries can complete

My $time_until_kill_threads = 5

@ threads = get_threads_util ($orig_master_handler- > {dbh})

$orig_master_handler- > {connection_id})

While ($time_until_kill_threads > 0 & & $# threads > = 0) {

If ($time_until_kill_threads% 5 = = 0) {

Printf

"% s Waiting all running d queries are disconnected.. (max% d milliseconds)\ n"

Current_time_us (), $# threads + 1, $time_until_kill_threads * 100

If ($# threads

< 5 ) { print Data::Dumper->

New ([$_])-> Indent (0)-> Terse (1)-> Dump. "\ n"

Foreach (@ threads)

}

}

Sleep_until ()

$_ tstart = [gettimeofday]

$time_until_kill_threads--

@ threads = get_threads_util ($orig_master_handler- > {dbh})

$orig_master_handler- > {connection_id})

}

# # Terminating all threads

Print current_time_us (). "Killing all application threads..\ n"

$orig_master_handler- > kill_threads (@ threads) if ($# threads > = 0)

Print current_time_us (). "done."

$orig_master_handler- > enable_log_bin_local ()

$orig_master_handler- > disconnect ()

# # After finishing the script, MHA executes FLUSH TABLES WITH READ LOCK

$exit_code = 0

}

If ($@) {

Warn "Got Error: $@\ n"

Exit $exit_code

}

Exit $exit_code

}

Elsif ($command eq "start") {

# # Activating master ip on the new master

# 1. Create app user with write privileges

# 2. Moving backup script if needed

# 3. Register new master's ip to the catalog database

# We don't return error even though activating updatable accounts/ip failed so that we don't interrupt slaves' recovery.

# If exit code is 0 or 10, MHA does not abort

My $exit_code = 10

Eval {

My $new_master_handler = new MHA::DBHelper ()

# args: hostname, port, user, password, raise_error_or_not

$new_master_handler- > connect ($new_master_ip, $new_master_port

$new_master_user, $new_master_password, 1)

# # Set read_only=0 on the new master

$new_master_handler- > disable_log_bin_local ()

Print current_time_us (). "Set read_only=0 on the new master."

$new_master_handler- > disable_read_only ()

# # Creating an app user on the new master

# print current_time_us (). "Creating app user on the new master..\ n"

Print current_time_us (). "Add vip $vip on $if..\ n"

# create_app_user ($new_master_handler)

& add_vip ()

$new_master_handler- > enable_log_bin_local ()

$new_master_handler- > disconnect ()

# # Update master ip on the catalog database, etc

$exit_code = 0

}

If ($@) {

Warn "Got Error: $@\ n"

Exit $exit_code

}

Exit $exit_code

}

Elsif ($command eq "status") {

# do nothing

Exit 0

}

Else {

& usage ()

Exit 1

}

}

Sub usage {

Print

"Usage: master_ip_online_change-- command=start | stop | 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"

Die

}

11. Check statu

Management node

[root@mysql2 mha] # which masterha_check_ssh

[root@mysql2 mha] # chmod + x master_ip_online_change

[root@mysql2 mha] # chmod + x master_ip_failover

[root@mysql2 mha] # masterha_check_ssh-- conf=/usr/local/mha/mha.conf

[root@mysql2 mha] # masterha_check_repl-- conf=/usr/local/mha/mha.conf

twelve。 Add VIP to the main library

Primary server 1

[root@mysql ~] # ip addr add 192.168.31.100 dev enp0s3

[root@mysql ~] # ip addr show

Notice that it is 100,32.

13. Start MHA

Management node (i.e. slave server 1)

[root@mysql2 mha] # nohup masterha_manager-- conf=/usr/local/mha/mha.conf > / tmp/mha_manager.log

< /dev/null 2>

& 1 &

14. Verify MHA statu

Management node (i.e. slave server 1)

[root@mysql2 mha] # masterha_check_status-- conf=/usr/local/mha/mha.conf

15. Test verification

Primary server 1

[root@mysql ~] # service mysql stop

Note:

Every time you start a MHA switch, you need to delete the mha.failover.complete file in the management node, otherwise an error will be reported next time.

Slave server 1 (candidate node)

Check

[root@mysql2 mha] # tail-100f manager.log

[root@mysql2 mha] # ip addr show

Mysql > show slave status\ G

Mysql > show master status\ G

From Server 2

16. Subsequent operation

Restore the original master database and become the backup database for the current cluster environment

Now the main library operation

Mysql > show master status

Operation of the original main library

[root@mysql data] # service mysql start

[root@mysql data] # mysql-uroot-p

Mysql > change master to master_host='192.168.31.188',master_user='rep1', master_password='Oracle123',master_port=3306,master_log_file='master-log.000001',master_log_pos=409

Mysql > start slave

Mysql > show slave status\ G

On how to share the MYSQL architecture from the MHA here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.