MySQL - MHA high availability cluster architecture

MHA high availability configuration and failover

Article directory

Preface

At present, MHA is a relatively mature solution in the aspect of MySQL high availability. It is developed by Japanese DeNA company youshimaton (now working in Facebook company), and it is a set of excellent MySQL failover and master-slave upgrade high availability software. In the process of MySQL failover, MHA can automatically complete the database failover operation within 0-30 seconds, and in the process of failover, MHA can guarantee the consistency of data to the greatest extent, so as to achieve real high availability.

MHA also provides the function of online master database switching, which can safely switch the currently running master database to a new master database (by upgrading the slave database to the master database), which can be completed in about 0.5-2 seconds.

1, MHA features

  • In the process of automatic failover, the MHA view saves binary logs from the down master server to ensure that the data is not lost to the greatest extent
  • Using MySQL 5.5 semi synchronous replication can greatly reduce the risk of data loss

2, Composition of MHA

  • MHA Manager (management node)
  • MHA Node

MHA Manager can be deployed on a single machine to manage multiple master slave clusters, or on a single slave. The MHA Node runs on each MySQL server, and the MHA Manager will regularly detect the master node in the cluster. When the master fails, it can automatically promote the slave of the latest data to the new master, and then point all other slave to the new master again. The entire failover process is completely transparent to the application.

3, Problems in traditional Mysql master-slave architecture

  • Single point failure

4, MHA example

Topology:

Environmental Science:

1. 3 mysql servers (1 primary server, 2 secondary servers (one primary and one standby)

2. 1 Manager

3. Node components need to be installed for all nodes

Relevant installation package has been uploaded to Baidu online disk: link: https://pan.baidu.com/s/1ajmqtfififiup0vphsn6bndfq extraction code: f45d

centos7-1 main server 192.168.17.157
centos7-2 slave server (primary and secondary) 192.168.17.129
centos7-3 slave server 192.168.17.135
centos7-5 manager 192.168.17.158

1) Install MySQL database

Mysql installation is not covered here. Specific installation steps:

[https://blog.csdn.net/ML908/article/details/103859623]:

2) Configure MySQL with one master and two slaves

Primary server configuration

#Change the mysql main configuration file and start the service

[root@master ~]#  vim /etc/my.cnf

[mysqld]
server-id = 1
log_bin = master-bin
log-slave-updates = true

#Master-slave configuration, authorization (one is the user myslave used synchronously by the slave database, the other is the user mha monitored by the manager)

mysql> grant replication slave on *.* to 'myslave'@'192.168.17.%' identified by'123';
mysql> grant all privileges on *.* to 'mha'@'192.168.17.%' identified by 'manager';
mysql> flush privileges;

#The following three authorizations do not need to be added according to the theory, but when doing the case experiment environment, the master and slave of MySQL will report an error through MHA,
It is reported that two slave databases cannot connect to the master database through the host name, so all databases are authorized as follows

mysql> grant all privileges on *.* to 'mha' @'master' identified by'manager';
mysql> grant all privileges on *.* to 'mha' @'slave1z' identified by'manager';
mysql> grant all privileges on *.* to 'mha' @'slave2' identified by'manager';

Refreshing

mysql> flush privileges;

#View primary server binaries and synchronization points

mysql>show master status;

Configure from server

Another slave server has the same configuration, the only difference is to change the server id in the main configuration file my.cnf (the server id cannot be the same)

#Change the mysql main configuration file and start the service

[root@slave1z ~]#  vim /etc/my.cnf

[mysqld]
server-id = 2
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

#Master-slave configuration, authorization (one is the user myslave used synchronously by the slave database, the other is the user mha monitored by the manager)

mysql> grant replication slave on *.* to 'myslave'@'192.168.17.%' identified by'123';
mysql> grant all privileges on *.* to 'mha'@'192.168.17.%' identified by 'manager';
mysql> flush privileges;

#The following three authorizations do not need to be added according to the theory. However, when doing the case experiment environment, the MHA checks whether the MySQL master and slave have reported errors,
It is reported that two slave databases cannot connect to the master database through the host name, so all databases are authorized as follows

mysql> grant all privileges on *.* to 'mha' @'master' identified by'manager';
mysql> grant all privileges on *.* to 'mha' @'slave1z' identified by'manager';
mysql> grant all privileges on *.* to 'mha' @'slave2' identified by'manager';

#Perform synchronization

mysql> change master to master_host='192.168.17.157',master_user='myslave',master_password='123',master_log_file='master-bin.000001',master_log_pos=3041;
mysql> start slave;					//Enable slave synchronization

#View status (yes for IO and SQL threads indicates normal synchronization)

mysql> show slave status\G;

#Set slave to read-only mode

mysql> set global read_only=1;

#Verify master-slave replication


3) Install MHA software

#First, all servers (manger and mysql) are installed with MIA dependent environment

All servers install epel source, per environment

[ root@MHA ~] # yum install -y epel-release --nogpgcheck
[ root@MHA ~] # yum install -y perl-DBD-MySQL \         
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils MakeMaker \
perl-CPAN

All servers install node components

[root@master ~]# mount.cifs //192.168.17.1/mysql /abc
[root@master ~]# cd /abc
[root@master abc]# tar zxvf mha4mysql-node-0.57.tar.gz -C /opt
[root@master abc]# cd /opt/mha4mysql-node-0.57/
[root@master mha4mysql-node-0.57]# perl Makefile.PL 
[root@master mha4mysql-node-0.57]# make && make install

#Install the manager component on MHA Manager (Note: you must install the node component before installing the manager component)

[root@mha ~]# cd /abc
[root@mha abc]# tar zxvf mha4mysql-manager-0.57.tar.gz -C /opt
[root@mha abc]# cd /opt/mha4mysql-manager-0.57/
[root@mha mha4mysql-manager-0.57]# perl Makefile.PL
[root@mha mha4mysql-manager-0.57]# make && make install

After manager is installed, several tools will be generated under / usr/local/bin, mainly including the following:
Master? Check? SSH check the SSH configuration of MHA
Master check repl check MySQL replication status
Masterha? Manger script to start manager
Master? Check? Status? Detects the current MHA operation status
Master monitor detects whether the master is down
Master ABCD master ABCD switch control failover (automatic or manual)
Master? Conf? Host? Adds or removes configured server information
Master stop close manager

After node installation, several scripts will also be generated under / usr/local/bin (these tools are usually triggered by the script of MHAManager without human operation)
Mainly as follows:
Save? Binary? Logs save and copy the binary logs of the master
Apply ﹣ diff ﹣ relay ﹣ logs identify different relay log events and apply them to other slave s
Filter "mysqlbinlog" removes unnecessary ROLLBACK events (MHA no longer uses this tool)
Purge? Relay? Logs clear relay logs (does not block SQL threads)

4) Configure password free authentication

#Configure password free authentication to all database nodes on manager

[root@mha ~]# SSH keygen - t RSA / / asymmetric key form, no password authentication, all press enter
[root@mha ~]# ssh-copy-id 192.168.17.157
Are you sure you want to continue connecting (yes/no)? yes
root@192.168.17.157's password: 				//Enter the master server password

[root@mha ~]# SSH copy ID 192.168.17.129 / / the above steps are the same
[root@mha ~]# ssh-copy-id 192.168.17.135

#Configure password free authentication to database nodes slave1z and slave2 on the master

[root@master ~]# ssh-keygen -t rsa
[root@master ~]# ssh-copy-id 192.168.17.129
[root@master ~]# ssh-copy-id 192.168.17.135

#Password free authentication configured to database nodes master and slave 2 on slave1z

[root@slave1z ~]# ssh-keygen -t rsa 
[root@slave1z ~]# ssh-copy-id 192.168.17.157
[root@slave1z ~]# ssh-copy-id 192.168.17.135

#Password free authentication configured to database nodes master and slave1z on slave2

[root@slave2 ~]# ssh-keygen -t rsa
[root@slave2 ~]# ssh-copy-id 192.168.17.157
[root@slave2 ~]# ssh-copy-id 192.168.17.129

5) Configure MySQL MHA high availability

#Copy the relevant script on the manager node to the / usr/local/bin directory

[root@mha ~]# cp -ra /opt/mha4mysql-manager-0.57/samples/scripts/ /usr/local/bin
[root@mha ~]# ll /usr/local/bin/scripts/
//Total dosage 32
-rwxr-xr-x. 1 1001 1001  3648 5 Month 312015 master_ip_failover
-rwxr-xr-x. 1 1001 1001  9870 5 Month 312015 master_ip_online_change
-rwxr-xr-x. 1 1001 1001 11867 5 Month 312015 power_manager
-rwxr-xr-x. 1 1001 1001  1360 5 Month 312015 send_report

#Copy the above script of VIP management during automatic switch to the directory / usr/local/bin, where the script is used to manage VIP
[root@mha ~]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin/
[root@mha ~]# 
#Modify script (delete original content and copy directly)
[root@mha ~]# vim /usr/local/bin/master_ip_failover
............Ellipsis content
my (
  $command,        $ssh_user,         $orig_master_host,
  $orig_master_ip, $orig_master_port, $new_master_host,
  $new_master_ip,  $new_master_port,  $new_master_user,
  $new_master_password
);
//Add the following
my $vip = '192.168.17.200';
my $brdc = '192.168.17.255';
my $ifdev = 'ens33';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
my $exit_code = 0;
#my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
#my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";

#Create MHA software directory and copy configuration files

[root@mha ~]# mkdir /etc/masterha
[root@mha ~]# cp /opt/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha/

#Modify profile content (delete original content)

[root@mha ~]# vim /etc/masterha/app1.cnf 
[server default]
manager_log =/var/log/masterha/app1/manager.log		//manager log
manager_workdir=/var/log/masterha/app1			//manager working directory
master_binlog_dir=/usr/local/mysql/data 		//Location where master saves binlog
master_ip_failover_script=/usr/local/bin/master_ip_failover		//Set the switch script for automatic failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change 	//Set script for manual switching
password=manager		//Set the password of the root user in mysql, which is the password of the monitoring user created in the previous section
ping_interval=1
remote_workdir=/tmp
repl_password=123
repl_user=myslave
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.17.129 -s 192.168.17.135 shutdown_script=""
ssh_user=root
user=mha			//Set monitoring user root

[server1]
hostname=192.168.17.157
port=3306

[server2]
candidate_master=1
check_repl_delay=0
hostname= 192.168.17.129
port= 3306

[server3]
hostname=192.168.17.135
port=3306

#Test SSH key validation

[root@mha .ssh]#  masterha_check_ssh -conf=/etc/masterha/app1.cnf
............ellipsis
Mon Jan 13 15:43:41 2020 - [debug]   ok.
Mon Jan 13 15:43:42 2020 - [info] All SSH connection tests passed successfully.

#Test master-slave connection verification

[root@mha .ssh]#  masterha_check_repl -conf=/etc/masterha/app1.cnf
............ellipsis
Mon Jan 13 15:53:28 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.

If an error is reported: mysqlbinlog: [ERROR] unknown variable 'default character set = utf8'

resolvent:

Comment out the clinet segment default character set = utf8mb4 on all slave servers / etc/my.cnf

Change all character sets UTF8 to utf8mb4

#Configure virtual IP (Note: for the first configuration, you need to manually turn on virtual IP on the master)

[root@master ~]# /sbin/ifconfig ens33:1 192.168.17.200/24

MHA starts

[root@mha .ssh]#  nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
[1] 73161

– remove ﹣ dead ﹣ master ﹣ conf this parameter indicates that when the master-slave switch occurs, the ip of the old master database will be removed from the configuration file.
– manger log log storage location.
– ignore ﹣ last ﹣ failover by default, if the MHA detects a continuous outage, and between the two outages
If the interval is less than 8 hours, no Failover will occur. The reason for this limitation is to avoid ping pong effect. The
The parameter indicates that the file generated by the last MHA trigger switch is ignored. By default, when the MHA switch occurs, it will be recorded in the log
Directory, that is, the log app1.failover.complete file set above. If you find it the next time you switch
It is not allowed to trigger the switch if the file exists in the directory, unless the file is deleted after the first switch. For convenience,
This is set to – ignore > last > failover

Verification:

#View MHA status

[root@mha .ssh]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:52852) is running(0:PING_OK), master:192.168.17.157

#Check whether VIP address 192.168.17.200 of master exists? , this VIP address will not be
The manager node stops the MHA service and disappears.

6) Simulate master failover

#Enable monitoring observation logging

[root@mha .ssh]# tailf /var/log/masterha/app1/manager.log 

#Turn off the service and view the changes of the master

Algorithm for switching alternative master database:
1. Generally speaking, the judgment of the slave database is from the position/GTID. The data is different, which is closest to the slave of the master and becomes the alternative master.
2. In the case of consistent data, select the alternative main database according to the order of configuration files.
3. Set the weight (candidate ﹐ master = 1), and force the alternate master to be specified according to the weight.
1) By default, if a slave is 100m behind the master's relay logs, even if it has weight, it will fail.
2) If check? Repl? Delay = 0, even if there are many logs behind, it is forced to select it as the alternative primary.

#The client can connect to the database through virtual ip

#Now we can see the newly created tables on the new main database, and the original slave server also synchronizes the database

Published 64 original articles, won praise 41, visited 6496
Private letter follow

Tags: MySQL Database ssh vim

Posted on Mon, 13 Jan 2020 07:41:12 -0500 by loganbest