Two ways to manually implement MySQL dual-machine hot standby on Alibaba Cloud Server

1. Concept

1. The difference between hot backup and backup

Hot backup refers to: High Available (HA), while backup refers to Backup, a type of data backup. These are two different concepts, and the products they correspond to are also two completely different products in terms of functions. Hot backup mainly ensures business continuity, and the method of implementation is to transfer the failure point. The main purpose of backup is to prevent data loss by making a copy, so backup emphasizes data recovery rather than application failover.

2. What is hot standby?

In a broad sense, hot standby means using two servers to back up each other and jointly perform the same service for important services. When a server fails, another server can take over the service task, thus automatically ensuring that the system can continue to provide services without the need for human intervention.

In a narrow sense, hot standby is the use of two servers that back up each other to jointly perform the same service, one of which is the working server (Primary Server) and the other is the backup server (Standby Server). When the system is normal, the working machine provides services for the application system, and the backup machine monitors the operation of the working machine (usually through heartbeat diagnosis, and the working machine also detects whether the backup machine is normal). When the working machine becomes abnormal and cannot support the operation of the application system, the backup machine actively takes over the work of the working machine and continues to support key application services to ensure uninterrupted operation of the system. Hot standby is a high-availability solution for failures in IT core servers, storage, and network routing switches.

OK, let me introduce how to implement MySQL dual-machine hot standby on Alibaba Cloud Server. The details are as follows:

Since Alibaba Cloud Server does not support keepalive virtual IP, it is impossible to implement MySQL dual-machine hot standby through keepalive. There are two ways to implement Alibaba Cloud's dual-machine hot standby:

1. Purchase Alibaba Cloud's advanced version of the cloud database, which comes with a slave database. If the master database fails, it will automatically switch to the slave database.

2Here we mainly talk about the second method, deploying MySQL on two Alibaba Cloud servers respectively to achieve master-master synchronization of MySQL. I won’t focus on MySQL master-master synchronization here. You can search it on Baidu yourself. If you need it, I may write an article later. Assuming that MySQL master-master synchronization has been implemented, how to deal with it

Because springboot is used, you need to process the yml file, and then add a send email to multiple people. Here the host runs the scheduled task, and the backup machine can also run it accordingly if needed.

Because I used mysql deployed by docker, I used docker ps to check the status of mysql. If it is a normal deployment, you can use ps or log in to mysql directly to see if it is successful.

# coding: utf8
import subprocess
from email.mime.text import MIMEText
import smtplib
import os
#Because Java springboot is used here, if you switch to the standby machine, you need to modify some yml files YML_PATH = ''
NEW_YML = ''
msg_from = '' # sender's email address passwd = '' # enter the sender's email address authorization code msg_to = []
def check_mysql():
 res = subprocess.Popen('docker ps |grep mysql', stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=True)
 stdout, stderr = res.communicate()
 if stdout:
  res_status = subprocess.Popen("docker ps |grep mysql| awk '{print $7}'", stdout=subprocess.PIPE,
          stderr=subprocess.PIPE, shell=True)
  out, err = res_status.communicate()
  if out.strip() == "Up":
   print 'mysql is ok'
  else:
   print 'mysql is broken, switch standby machine'
   flag = judge_mysql_string()
   if flag:
    print 'Already connected to the backup machine, no need to switch'
   else:
    switch_mysql()
 else:
  print 'mysql is broken, switch standby machine'
  flag = judge_mysql_string()
  if flag:
   print 'Already connected to the backup machine, no need to switch'
  else:
   switch_mysql()
def switch_mysql():
 print '---copy yml----'
 subprocess.call('cp {} {}'.format(NEW_YML, YML_PATH), shell=True)
 subprocess.call('docker restart tomcat', shell=True)
 subject = 'mysql host failure'
 info = 'MySQL host failure, has been switched to the backup machine, please check the host problem and fix it in time, if you need to switch back,' \
 content = '<html><meta charset="UTF-8"><body><p style="color: red">{}</p> </body></html>'.format(info)
 send_email(subject, content)
 print '----end switch---'
def send_email(subject, content):
 msg = MIMEText(content, 'html', 'utf-8')
 msg['Subject'] = subject
 msg['From'] = msg_from
 msg['To'] = ','.join(msg_to)
 try:
  s = smtplib.SMTP_SSL("smtp.qq.com", 465)
  s.login(msg_from, passwd)
  s.sendmail(msg_from, msg_to, msg.as_string())
 except Exception as e:
  print e
 finally:
  s.quit()
def judge_mysql_string():
 with open(os.path.join(YML_PATH, 'application.yml'),'r') as f:
  res = f.read()
  if 'your ip' in res:
   return True
  else:
   return False
check_mysql()

Summarize

The above are two ways to manually implement MySQL dual-machine hot standby on Alibaba Cloud Server. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!