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

Environment:

Build MySQL MHA with three servers

Master node: 192.168.157.128 CentOS 7.6

Database: mysql-5.7.27-linux-glibc2.12-x86_64

Slave node: 192.168.157.129 CentOS 7.6

Database: mysql-5.7.27-linux-glibc2.12-x86_64

Management node: 192.168.157.130 CentOS 7.6

Database: mysql-5.7.27-linux-glibc2.12-x86_64

1 and 3 servers all install the operating system CentOS 7.6,

2. MySQL installation and master-slave settings

All three machines have to do:

Turn off the firewall:

Systemctl stop firewalld.service

Systemctl disable firewalld.service

Systemctl status firewalld.service

# turn off selinux

Setenforce 0

PS:

Temporary shutdown:

[root@localhost ~] # getenforce

Enforcing

[root@localhost ~] # setenforce 0

[root@localhost ~] # getenforce

Permissive

Permanently close:

[root@localhost ~] # vi / etc/sysconfig/selinux

Change SELINUX=enforcing to SELINUX=disabled

Restart the service reboot

# Delete pre-installed mysql

Rpm-qa | grep mariadb

Rpm-e-- nodeps mariadb-libs-5.5.60-1.el7_5.x86_64

# decompression

Tar zxvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz-C / opt

# rename

Mv mysql-5.7.27-linux-glibc2.12-x86_64 mysql

# New mysql user

Useradd mysql

Passwd mysql

#

# initialization

Mkdir-p / opt/mysql/data

Chown-R mysql:mysql / opt/

Chown-R mysql:mysql / opt/

# create related folders

Mkdir-p / opt/mysql/tmp

Mkdir-p / opt/mysql/log

# change folder permissions

Chown-R mysql:mysql / opt/mysql

# create mysql.err file

[root@mha02 opt] # cd mysql/log

[root@mha02 log] # ll

Total 0

[root@mha02 log] # touch mysql.err

[root@mha02 log] # chown-R mysql:mysql mysql.err

# initialize the database

Mysql/bin/mysqld-initialize-user=mysql-basedir=/opt/mysql/-datadir=/opt/mysql/data/

# simple configuration

Ln-s / opt/mysql/bin/mysql / usr/bin

Ln-s / opt/mysql/bin/mysqladmin / usr/bin

Ln-s / opt/mysql/bin/mysqldump / usr/bin

# mysql configuration file, the server-id of each server should be different

Vi / etc/my.cnf

[client]

Port=3306

Socket=/opt/mysql/tmp/mysql.sock

[mysqld]

User=mysql

Datadir=/opt/mysql/data

Basedir=/opt/mysql

Socket=/opt/mysql/tmp/mysql.sock

Log-error=/opt/mysql/log/mysql.err

Pid-file=/opt/mysql/tmp/mysqld.pid

# id should be different for each machine

Server-id = 186

# set the dual master time to ensure that another main library operation can be written to

Log_slave_updates

# the slave library will not restart with the master library

Skip-slave-start

# enable gtid mode, which is unique to 5.7

Gtid_mode = on

Enforce_gtid_consistency = 1

Log_slave_updates = 1

# semi-synchronous replication mode

Plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

Loose_rpl_semi_sync_master_enabled = 1

Loose_rpl_semi_sync_slave_enabled = 1

Loose_rpl_semi_sync_master_timeout = 5000

# specify binlog and relay addresses

Log-bin = / opt/mysql/log/mysql-bin

Relay-log = / opt/mysql/log/mysql-relay-bin

# do not synchronize which libraries, a better way to write

Replicate-wild-ignore-table=mysql.%

Replicate-wild-ignore-table=test.%

Replicate-wild-ignore-table=information_schema.%

# Open the database

/ opt/mysql/bin/mysqld_safe-defaults-file=/etc/my.cnf &

# Log in to the database and change the root password

Mysql-p

Previously initialized password

Mysql > alter user root@'localhost' identified by '123456'

Mysql > flush privileges

Add root Telnet users:

Mysql > create user root@'%' identified by '123456'

Query OK, 0 rows affected (0.01 sec)

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

Mysql > flush privileges

Shut down the MySQL service:

[root@mha01 bin] # mysqladmin-uroot-p-S / opt/mysql/tmp/mysql.sock shutdown

Enable the MySQL service:

/ opt/mysql/bin/mysqld_safe-defaults-file=/etc/my.cnf &

The MySQL service is also installed on the remaining two servers.

# Log in to mysql and configure accounts in all three mysql. Note that you cannot use mysql as a binlog replication library, otherwise the newly created slave account will report an error

# create a slave account

Mysql > grant replication slave,replication client on *. * to 'repl'@'%' identified by' 123456'

# create a mha management account

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

# execute on the slave node

Mysql > set global read_only=1

# because the slave library can be upgraded to the master library at any time, it cannot be written in the configuration file

# MHA needs to use relay-log to verify whether the slave data read and write is consistent, do not write to the configuration file, all mysql nodes execute

Mysql > set global relay_log_purge=0

# set master and slave

1. Check the master in the main database

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000002 | 932 | 58d16735-bdae-11e9-b8d3-000c29f97b79:1-8 | |

+-+

1 row in set (0.00 sec)

2. Slave library execution

Mysql > change master to

Master_host='192.168.157.128'

Master_user='repl'

Master_password='123456'

Master_log_file='mysql-bin.000002'

Master_log_pos=932

Start slave with GTID:

Mysql > change master to

Master_host='192.168.157.128'

Master_user='repl'

Master_password='123456'

Master_auto_position=1

Mysql > start slave

Query OK, 0 rows affected (0.02 sec)

Mysql > show slave status\ G

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

When you see two YES, it represents master-slave success.

3. Set up MHA

Three hosts ssh trust each other

Note: all three hosts need to be done, and the local SSH authentication is required.

# because mha requires manager to log in to ssh without secret.

Ssh-keygen-t rsa

Ssh-copy-id-I / .ssh/id_rsa.pub root@192.168.157.128

Ssh-copy-id-I / .ssh/id_rsa.pub root@192.168.157.129

Ssh-copy-id-I / .ssh/id_rsa.pub root@192.168.157.130

MHA installation configuration:

All three machines have to do:

Install it in the / opt directory:

Install the perl modules required for MHA node on all nodes as follows:

# CentOS 6 download Point

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

# CentOS 7 download Point

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

# install the required controls

# yum install-y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

Yum install-y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-YAML-Tiny perl-PAR-Dist perl-Module-ScanDeps perl-Module-CoreList perl-Module-Build perl-CPAN perl-CPANPLUS perl-File-Remove perl-Module-Install

# download node and manager software

# node git address: https://github.com/yoshinorim/mha4mysql-node

# manager git address: https://github.com/yoshinorim/mha4mysql-manager

# node all machines must be installed

# manager only requires the installation of the management node

Yum-y install git

Git clone https://github.com/yoshinorim/mha4mysql-node.git

# manage node installation

Git clone https://github.com/yoshinorim/mha4mysql-manager.git

Install MHA

Cd / opt/mha4mysql-node

Perl Makefile.PL

Make & & make install

Cd / opt/mha4mysql-manager

Perl Makefile.PL

Make & & make install

Note: an error similar to Can't locate inc/Module/XXX.pm in @ INC may occur in perl Makefile.PL

This is due to the lack of components, so you only need to install the appropriate accessories

Example: yum-y install perl-Module-XXX generally has a corresponding name plug-in, just install it

# node has been configured, and then the operation of the manager node

Configure the Manager node. The following steps are all Manager node operations.

# create a new MHA profile

Mkdir / var/log/mha/app1-p

Touch / var/log/mha/app1/manager.log

Vi / etc/masterha.cnf

[server default]

# Monitoring users

User=monitor

Password=123456

# ssh users

Ssh_user=root

# slave users

Repl_user=repl

Repl_password=123456

# ping failed three times to determine disconnection

Ping_interval=3

# use other hosts to ping to determine whether the host itself is faulty

Secondary_check_script=masterha_secondary_check-s 192.168.157.128-s 192.168.157.129-s 192.168.157.130

# related script locations must be self-built. There is no such location by default.

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

# specify MHA log directory

Manager_workdir=/var/log/mha/app1

Manager_log=/var/log/mha/app1/manager.log

# specify bin-log location

Master_binlog_dir=/opt/mysql/log/

Remote_workdir=/var/log/mha/mysqltmp

[server1]

Hostname=192.168.157.128

Port=3306

[server2]

Hostname=192.168.157.129

Port=3306

# whether to ignore the backwardness and make him a master

Candidate_master=1

Check_repl_delay=0

[server3]

Hostname=192.168.157.130

# will never be a master. Mostly used for manager

No_master=1

Port=3306

# configure VIP

# to prevent brain fissure, it is recommended that the production environment use scripts to manage virtual ip instead of using keepalived

Vi / usr/local/bin/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

);

# define VIP variables

# modify VIP address here, pre-selected VIP address

My $vip = '192.168.157.197Universe 24'

My $key ='1'

# modify the name of the Nic after ifconfig, and don't change it anywhere else

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 {

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 ()

$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 $ssh_user\ @ $new_master_host\ "$ssh_start_vip\" `

}

Sub stop_vip () {

Return 0 unless ($ssh_user)

`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"

}

# configure alarm email script

# install email

Yum install mailx-y

Vi / etc/mail.rc

# modify the mail configuration file and add it at the end of the configuration file

# email address

Set from=ywzj_002@163.com

# smtp address of the mailbox

Set smtp=smtp.163.com

# user name of the mailbox

Set smtp-auth-user=ywzj_002

# this is not an email password, but an authorization code

Set smtp-auth-password=pousheng123

Set smtp-auth=login

Vi / usr/local/bin/send_report

#! / bin/bash

Source / root/.bash_profile

Orig_master_host= `echo "$1" | awk-F ='{print $2}'`

New_master_host= `echo "$2" | awk-F ='{print $2}'`

New_slave_hosts= `echo "$3" | awk-F ='{print $2}'`

Subject= `echo "$4" | awk-F ='{print $2}'`

Body= `echo "$5" | awk-F ='{print $2}'`

Email= "ywzj_001@163.com"

Tac / var/log/mha/app1/manager.log | sed-n 2p | grep 'successfully' > / dev/null

If [$?-eq 0]

Then

Messages= `echo-e "MHA $subject master / slave switch succeeded\ nmaster:$orig_master_host-- >

$new_master_host\ n $body\ nThe current slave library: $new_slave_hosts "`

Echo "$messages" | mail-s "Mysql instance is down, MHA $subject is switched successfully" $email >

> / tmp/mailx.log 2 > & 1

Else

Messages= `echo-e "MHA $subject master-slave switch failed\ nmaster:$orig_master_host-- >

$new_master_host\ n $body "`

Echo "$messages" | mail-s "" Mysql instance down, MHA $subject switching failed "" $email "

> > / tmp/mailx.log 2 > & 1

Fi

# configure VIP replacement script

Vi / usr/local/bin/master_ip_online_change

# pre-selected VIP address

Vip= `echo '192.168.157.197max 24``

Key= `echo '1``

Command= `echo "$1" | awk-F ='{print $2}'`

Orig_master_host= `echo "$2" | awk-F ='{print $2}'`

New_master_host= `echo "$7" | awk-F ='{print $2}'`

Orig_master_ssh_user= `echo "${12}" | awk-F ='{print $2}'`|

New_master_ssh_user= `echo "${13}" | awk-F ='{print $2}'`|

# modify the name of the Nic

Stop_vip= `echo "ssh root@$orig_master_host / usr/sbin/ifconfig ens33:$key down" `

Start_vip= `echo "ssh root@$new_master_host / usr/sbin/ifconfig ens33:$key $vip" `

If [$command = 'stop']

Then

Echo-e "\ n\ n\ n *\ n"

Echo-e "Disabled thi VIP-$vip on old master: $orig_master_host\ n"

$stop_vip

If [$?-eq 0]

Then

Echo "Disabled the VIP successfully"

Else

Echo "Disabled the VIP failed"

Fi

Echo-e "* *\ n\ n\ n"

Fi

If [$command = 'start'-o $command =' status']

Then

Echo-e "\ n\ n\ n *\ n"

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

$start_vip

If [$?-eq 0]

Then

Echo "Enabled the VIP successfully"

Else

Echo "Enabled the VIP failed"

Fi

Echo-e "* *\ n\ n\ n"

Fi

# Grant script permissions

# * represents the three scripts created earlier

Chmod 555 / usr/local/bin/*

Chmod 555 / usr/local/bin/master_ip_failover

Chmod 555 / usr/local/bin/master_ip_online_change

Chmod 555 / usr/local/bin/send_report

# Test whether the MHA settings are correct

Masterha_check_repl-conf=/etc/masterha.cnf

. . .

MySQL Replication Health is OK.

Finally, the display of OK is normal, otherwise you have to look at the error message, according to the previous operation configuration, there will be basically no errors.

# configure VIP to the network port in the main library instead of the Manager management port

/ usr/sbin/ifconfig ens33:1 192.168.157.197/24

Note: if installation is minimized, yum ifconfig is required

Yum-y install net-tools

# operate on Manager node

# start MHA

Nohup masterha_manager-conf=/etc/masterha.cnf-remove_dead_master_conf-ignore_last_failover

< /dev/null >

/ var/log/mha/app1/manager.log 2 > & 1 &

# check status

[root@mha03 bin] # masterha_check_status-- conf=/etc/masterha.cnf

Masterha (pid:15546) is running (0:PING_OK), master:192.168.157.128

# display OK is normal

Test the switching function and the downtime of the main library:

1. Online switching

# MHA online switching is another method provided by MHA in addition to automatic monitoring switching, which is mostly used for such as hardware upgrade, MySQL database migration and so on. This method provides fast switching and elegant blocking writes without shutting down the original server. the whole switching process is about 0.5-2s, which greatly reduces the downtime.

Note: mha monitoring needs to be turned off in order to run

A, the vip on the old master has taken effect correctly

B. The sql_IO and sql_sql processes of each salve node database are normal (that is, YES)

Show slave status\ G

C, MHA script cannot be run. If it is already under monitoring, you need to stop it.

Masterha_stop-conf=/etc/masterha.cnf

# perform switching

# need to enter the IP of the new master

Masterha_master_switch-conf=/etc/masterha.cnf-master_state=alive-new_master_host=192.168.157.129-orig_master_is_new_slave-running_updates_limit=10000-interactive=0

Masterha_master_switch-conf=/etc/masterha.cnf-master_state=alive-new_master_host=192.168.157.128-orig_master_is_new_slave-running_updates_limit=10000-interactive=0

# basic steps of MHA online switching:

A. Check the MHA configuration settings and confirm the current master

B. Decide on a new master

C. Blocking writes to the current master

D. Wait for all slave servers to complete synchronization with existing master

E, grant write permission in the new master, and switch slave libraries in parallel

F. Reset the slave from the original master to the new master

G. Online switching will not delete the old master configuration in the / etc/masterha.cnf configuration file.

Note: if you switch manually, if you want to run the original configuration directly, you need to change the address of master to the new IP in the configuration file, and the IP of the original Master host is written to slave.

2. Automatic switching

First: to achieve automatic Failover, you must start MHA Manager first, otherwise you can't switch automatically.

A, kill the main library mysql process, simulate the failure of the main library, and carry out automatic failover operation.

B. Check the MHA switching log to understand the whole switching process.

Tailf / var/log/mha/app1/manager.log

Second: you can see the whole switching process of MHA from the above output, which includes the following steps:

1)。 Profile check phase, which checks the entire cluster profile configuration

2)。 Master handling of downtime, which includes virtual ip removal operation and host shutdown operation (no power_manager script is defined, so the machine will not shut down)

3)。 Copy the relay log of the difference between dead maste and the latest slave, and save it to the specific directory of MHA Manger

4)。 Identify the slave with the latest updates

5)。 Apply binary log events (binlog events) saved from master (this information is important for joining the cluster after repairing the faulty master)

6)。 Upgrade a slave to a new master for replication

7)。 Make other slave connect to the new master for replication

Third: after the handover is completed, pay attention to the following changes:

1. Vip automatically switches from the original master to the new master. At the same time, the monitoring process of the manager node automatically exits.

2. Generate an app1.failover.complete file in the log directory (/ var/log/mha/app1)

3. The original old master configuration in the etc/mha/app1.cnf configuration file is deleted.

4. After switching once, the MHA monitor program will stop. You need to modify the configuration file and restart it again.

# you will find that the 192.168.157.129 slave library has been promoted to the master library at this time, and MHA Manager has stopped running.

Important: restore the main library to the queue

1) modify the manager configuration file (only for automatic switching, online switching will not delete the configuration)

# add the following to / etc/masterha.cnf

[server1]

Hostname=192.168.157.128

Candidate_master=1

Port=3306

Master_binlog_dir=/opt/mysql/log/

# due to the previous downtime of the master library, MHA will automatically delete the information of the master library that is down in the configuration file.

# if you need to rejoin the master library recovery, you need to add the original master library information to the configuration file and join it as a slave library

2) repair the old master, and then set it to slave

Information similar to the following can be found in the MHA log of automatic switching time:

# means that if the Master host is repaired, CHANGE MASTER can be performed on the repaired Master as the new slave library.

[root@mha03 app1] # cat manager.log | grep CHANGE

[root@mha03 app1] # pwd

/ var/log/mha/app1

# GTID is not enabled

Sat May 27 14:59:17 2017-[info] All other slaves should start replication from here. Statement

Should be: CHANGE MASTER TO MASTER_HOST='10.0.40.187', MASTER_PORT=3306

MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=120, MASTER_USER='repl'

MASTER_PASSWORD='xxx'

# enable GTID

Sat May 19 05:00:48 2018-[info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.40.187', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx'

Thu Aug 15 15:31:18 2019-[info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.157.129', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx'

Thu Aug 15 15:31:19 2019-[info] Executed CHANGE MASTER.

Execute the following command in the old master: (which one should be executed according to the output above, the difference is a log location and an automatic location)

# GTID is not enabled. Enter the log-file and log-pos of the log.

Mysql > change master to master_host='10.0.40.187',master_user='repl',master_password='123456',master_log_file='mysql-bin.0000124',master_log_pos=120

# if GTID is enabled, you can only use MASTER_AUTO_POSITION=1, otherwise an error will be reported

Mysql > change master to master_host='10.0.40.187',master_user='repl',master_password='123456',MASTER_AUTO_POSITION=1

CHANGE MASTER TO MASTER_HOST='192.168.157.129', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123456'

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.157.129', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123456'

Query OK, 0 rows affected, 2 warnings (0.02 sec)

Mysql > start slave

Query OK, 0 rows affected (0.01 sec)

Mysql > show slave status\ G

Zhengzhou Infertility Hospital: http://jbk.39.net/yiyuanzaixian/zztjyy/

# in this way, the data is synchronized to the old master. At this point, the old master has rejoined the cluster and become a slave role in the mha cluster.

Restart the monitoring process on the manger node

Nohup masterha_manager-conf=/etc/masterha.cnf-remove_dead_master_conf-ignore_last_failover

< /dev/null >

/ var/log/mha/app1/manager.log 2 > & 1 &

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.