linux--mysql(MHA high availability Architecture)

MHA high availability architecture

Note: one master and many slaves = odd

Gtid master-slave replication or semi synchronous replication should be done before MHA high availability experiment. This time, gtid master-slave replication is used

gtid master slave replication link: https://blog.csdn.net/Aplox/article/details/104670774

gtid master-slave copy link

Make master-slave copy of gtid on three virtual machines (based on the previous experiment to do read-write separation)

systemctl stop mysqld
cd /var/lib/mysql/
rm -fr *
vim /etc/my.cnf

Last write
server1 configuration file write

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON     Switch on master-slave replacement
log_bin=binlog           Open binary log

server2 configuration file write

server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON     Switch on master-slave replacement
log_bin=binlog           Open binary log

server3 configuration file write

server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON     Switch on master-slave replacement
log_bin=binlog           Open binary log

Security initialization

systemctl start mysqld
cat /var/log/mysqld.log | grep password
mysql_secure_installation

Write on server1
Log in to create a new user

mysql -uroot -pAPLOX111fox.
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.25.254.%' IDENTIFIED BY 'APLOX111fox.'; 
//Create users and authorize
flush privileges;
show master status;

Write on server2 and server3

mysql -uroot -pAPLOX111fox.
CHANGE MASTER TO MASTER_HOST='172.25.254.1',
MASTER_USER='repl',
MASTER_PASSWORD='APLOX111fox.',
MASTER_AUTO_POSITION=1;
start slave;
show slave status\G

MHA link

server4 is a new virtual machine
On server4:

yum install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm perl-* mha4mysql-node-0.58-0.el7.centos.noarch.rpm  -y

To generate ssh key, the management node does not need to enter a password, and transmits the generated public key and private key to the data node

ssh-keygen
ssh-copy-id server1
ssh-copy-id server2
ssh-copy-id server3

scp -r ~/.ssh server1:
scp -r ~/.ssh server2:
scp -r ~/.ssh server3:

Test: realize password free login
All virtual machines realize password free login

Pass the installation package of the node to the data node

scp -r mha4mysql-node-0.58-0.el7.centos.noarch.rpm server1:/root/
scp -r mha4mysql-node-0.58-0.el7.centos.noarch.rpm server2:/root/
scp -r mha4mysql-node-0.58-0.el7.centos.noarch.rpm server3:/root/


Data node installation

[root@server1 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm 
[root@server2 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm 
[root@server3 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

Create a directory on server4 and edit the configuration file

mkdir -p /etc/masterha
cd /etc/masterha/
vim master.cnf
[server default]
manager_workdir=/etc/masterha
manager_log=/var/log/masterha.log   # manager log file
master_binlog_dir=/etc/masterha
#master_ip_failover_script= /usr/local/bin/master_ip_failover
#master_ip_online_change_script= /usr/local/bin/master_ip_online_change

password=APLOX111fox.
user=root
ping_interval=1
remote_workdir=/tmp
repl_password=APLOX111fox.
repl_user=repl   #Copy account and password
#report_script=/usr/local/send_report
#secondary_check_script= /usr/local/bin/masterha_secondary_check -s server 03 -s server02
#shutdown_script=""
ssh_user=root       #System ssh user

[server1]
hostname=172.25.254.1
port=3306

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

[server3]
hostname=172.25.254.3
port=3306
no_master=1    #This node cannot be the master


ssh examination

masterha_check_ssh --conf=/etc/masterha/master.cnf


Check repl replication

masterha_check_repl --conf=/etc/masterha/master.cnf
Check result is NOT OK    
The reason is that root does not have permission to connect


Give root permission on server1

grant all on *.* to root@'%' identified by 'APLOX111fox.';

Retest: successful

Test whether it can be opened normally

nohup masterha_manager --conf=/etc/masterha/master.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha.log 2>&1 &


Close

masterha_stop --conf=/etc/masterha/master.cnf

Manual switching

Dead cut

[root@server1 ~] systemctl stop mysqld
[root@server4 ~] masterha_master_switch --master_state=dead  --conf=/etc/masterha/master.cnf --dead_master_host=172.25.254.1  --dead_master_port=3306 --new_master_host=172.25.254.2 --new_master_port=3306


Test on server3:

show slave status\G

The new master node is server2


Turn on server1 and add server1 to the master-slave replication

systemctl start mysqld
mysql -uroot -pAPLOX111fox.
CHANGE MASTER TO MASTER_HOST='172.25.254.1',
MASTER_USER='repl',
MASTER_PASSWORD='APLOX111fox.',
MASTER_AUTO_POSITION=1;
start slave;
show slave status\G

Show that the new master node is server2

Live cut

Delete fault file

  1. [root@server4 ~]# cd /etc/masterha/
  2. [root@server4 masterha]# ls
  3. [root@server4 masterha]# rm -fr master.failover.complete
  4. [root@server4 masterha]# ls

Input command auto replace

masterha_master_switch --conf=/etc/masterha/master.cnf --master_state=alive --new_master_host=172.25.254.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

View on server2:

show slave status\G

master node successfully switches back to server1

View the master node on server3, which is also server1

Semi-automatic

Open in the background of server4

  1. [root@server4 masterha]# nohup masterha_manager --conf=/etc/masterha/zp.cnf &> /dev/null &

On server1:

Turn off and it will turn on automatically (stop unexpectedly)

systemctl stop mysqld

Check that the master node is already server2 on server4


server1:

systemctl start mysqld
mysql -uroot -pAPLOX111fox.
CHANGE MASTER TO MASTER_HOST='172.25.254.1',
MASTER_USER='repl',
MASTER_PASSWORD='APLOX111fox.',
MASTER_AUTO_POSITION=1;
start slave;
show slave status\G

Using script to realize vip drift

At this time, there are two script files on server4:

vim master_ip_failover

vim master_ip_online_change



Add executable rights to two scripts


Delete last execution, add script execution

master_ip_failover_script= /usr/local/bin/master_ip_failover
master_ip_online_change_script= /usr/local/bin/master_ip_online_change

Configure vip for server2 master

Live cut

server4:

masterha_master_switch --conf=/etc/masterha/master.cnf --master_state=alive --new_master_host=172.25.254.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

server2 server3:

The master node has reached server1

On server1:

Fully automatic

server4:

nohup masterha_manager --conf=/etc/masterha/master.cnf &> /dev/null &

server1:

systemctl stop mysqld

server2 becomes the master node

179 original articles published, praised 0, and 3735 visitors
Private letter follow

Tags: ssh CentOS RPM MySQL

Posted on Mon, 09 Mar 2020 23:51:21 -0400 by phpdood