Use Python to monitor the synchronization status of MySQL _ MySQL

It is very easy to monitor the MySQL synchronization status using Python to monitor whether the MySQL database server is accessible and whether the master-slave synchronization is interrupted. Thanks to Python for bringing us such a simple, powerful, and fast development environment. The Py used in this article uses Python to monitor the MySQL synchronization status.

Using Python to monitor whether the MySQL database server is accessible and whether the master-slave synchronization is interrupted is very simple. Thanks to Python for bringing us such a simple, powerful, and fast development environment.

Python module used in this article
Use telnetlib to verify whether the server is accessible
Use SMTP to send a notification email to the administrator
Use the MySQL driver to access the database
Use optparse to extract command line parameters

Implementation principle
Use the optparse module to obtain command line parameters. Read the content of the defaults-file setting file (if any), use the parameter to overwrite the value of defaults-file (if passing parameters such as-host,-user,-).

It is too slow to directly connect to MySQL and wait for access. Therefore, telnet is used to verify the connectivity of the server. You can set the wait time, which is more controllable.

When the server works normally, use MySQL to connect to the server to obtain the master-slave synchronization status.

Obtain the abnormal status information of the server (the server cannot be accessed, the master-slave synchronization status is interrupted), send it to the administrator using SMTP, and send the exception information that causes the synchronization interruption to the administrator's mailbox together.

Slavecheckpoint. py

Coding = utf-8 "database synchronization status detection MySQL database synchronization replication status monitoring script. It can be used with crond in Linux for timed monitoring. If the synchronization status is abnormal, the administrator is notified by email, and the error message that causes synchronization interruption is also included in the email. the administrator can locate the exception immediately through the error message. Instance: python slavecheckpoint. py -- defaults-file =/etc/slave. cnf -- to = xxxx@abc.com === FILE: slave. cnf =========== [config] smtp_host = smtp.163.comfrom = Message Center
  
   
Host = localhost "import mysql. connectorfrom mysql. connector import errorcodeimport telnetlibimport smtplibfrom email. mime. text import MIMETextimport optparsefrom ConfigParser import ConfigParserimport OS, time, sysclass SlaveStatu: _ instance _ = None _ error _ = [] def _ init _ (self, * args, ** kwargs): self. _ config _ = {"host": "localhsot", "user": "root", "password": "", "port": 3306, "smtp_host ": "localhost", "smtp_user": "", "smtp_password": "", "from": "admin @ localhost", "": ""} # preferentially read the value in the setting file if not kwargs ["defaults_file"] is None: defaults_file = self. _ read_defaults_file _ (kwargs ["defaults_file"]) del kwargs ["defaults_file"] # use parameter settings to overwrite the value of the set File for key and val in kwargs. items (): if not val is None and len (val)> 0: self. _ config _ [key] = val def _ configParseMySQL _ (self): "extract database settings: return: dict" return {"host ": self. _ config _ ["host"], "port": self. _ config _ ["port"], "user": self. _ config _ ["user"], "password": self. _ config _ ["password"]} def _ configParseSMTP _ (self): "set to extract SMTP mail: return: dict "return {" smtp_host ": self. _ config _ ["smtp_host"], "smtp_user": self. _ config _ ["smtp_user"], "smtp_password": self. _ config _ ["smtp_password"], "from": self. _ config _ ["from"], "to": self. _ config _ ["to"]} def _ read_defaults_file _ (self, filePath): "load the file setting value: param filePath: Set the file path: return: "" section = "config" if OS. path. exists (filePath): cnf = ConfigParser () cnf. read (filePath) options = cnf. options (section) for key in options: self. _ config _ [key] = cnf. get (section, key) def telnet (self, host, port, timeout = 5): "test whether the server address and port are unblocked: param host: server address: param port: server Port: param timeout: test timeout: return: Boolean "" try: tel = telnetlib. telnet (host, port, timeout) tel. close () return True before T: return False def connect (self): "create database link" "try: config = self. _ configParseMySQL _ () if self. telnet (config ["host"], config ["port"]): self. _ instance _ = mysql. connector. connect (** config) return True else: raise Exception ("unable connect") failed t: self. _ error __. append ("unable to connect to server host: {host }:{ port }". format (host = config ["host"], port = config ["port"]) return False def isSlave (self): "normal database synchronization: return: none synchronization is not enabled, False synchronization is interrupted, and True synchronization is normal "" cur = self. _ instance __. cursor (dictionary = True) cur.exe cute ("show slave status") result = cur. fetchone () cur. close () if result: if result ["Slave_ SQL _Running"] = "Yes" and result ["Slave_IO_Running"] = "Yes": return True else: if result ["Slave_ SQL _Running"] = "No": self. _ error __. append (result ["Last_ SQL _Error"]) else: self. _ error __. append (result ["Last_IO_Error"]) return False def get_last_error (self): "get the first error message: return: String" if self. _ error __: return self. _ error __. pop (0) def every Y (self, title, message): "send message reminder: param title: message title: param message: message content: return: "msg = [title, message] pool = [] policy = policy_email (self. _ configParseSMTP _ () pool. append (sort y) for item in pool: item. ring (msg) def close (self): "close Database link" if self. _ instance __: self. _ instance __. close () class policy_email (object): def _ init _ (self, config): self. config = config def ring (self, message = []): subject = message. pop (0) messageBody = "". join (message) mailList = self. config ["to"]. split (";") datetime = time. strftime ("% Y-% m-% d % H: % M: % S") for to in mailList: body = """
   
Administrator{Admin}Hello:
            
   
If you receive this email, your database synchronization is abnormal. please handle it in time.
            
   
Exception information:
{Body}
            
   
{Date}
""". Format (admin = to, body = messageBody, date = datetime) msg = MIMEText (body, "html", "UTF-8") msg ["From"] = self. config ["from"] msg ["To"] = to msg ["Subject"] = subject smtp = smtplib. SMTP () smtp. connect (self. config ["smtp_host"]) if self. config. has_key ("smtp_user"): smtp. login (self. config ["smtp_user"], self. config ["smtp_password"]) smtp. sendmail (self. config ["from"], to, msg. as_string () smtp. quit () if _ name _ = "_ main _": # command line parameter list usage = "usage: mySQLStat [options] "" opt = optparse. optionParser (usage = usage) opt. add_option ("-H", "-- host", dest = "host", help = "MySQL host (default: localhost)") opt. add_option ("-u", "-- user", dest = "user", help = "MySQL user") opt. add_option ("-p", "-- password", dest = "password", help = "MySQL password") opt. add_option ("-P", "-- port", dest = "port", help = "MySQL port (default: 3306)") opt. add_option ("", "-- smtp_host", dest = "smtp_host", help = "SMTP host (default: localhost)") opt. add_option ("", "-- smtp_user", dest = "smtp_user", help = "SMTP user") opt. add_option ("", "-- smtp_password", dest = "smtp_password", help = "SMTP password") opt. add_option ("", "-- from", dest = "from", help = "Email from") opt. add_option ("", "-- to", dest = "to", help = "Email to") opt. add_option ("", "-- defaults-file", dest = "defaults_file", help = "config file path") (options, args) = opt. parse_args () options = options. _ dict _ Statu = SlaveStatu (** options) subject = "service center exception message reminder" if Statu. connect () is False or Statu. isSlave () is False: Statu. notify (subject, Statu. get_last_error () Statu. close ()
  

Server1.cnfSet file content

[Config] smtp_host = smtp. aliyun. comsmtp_user = xxxx@aliyun.comsmtp _ password = xxxxxxfrom = Management Center
  
   
Host = xxx. xxxuser = rootpassword = 123456
  

After completing the above configuration, we can add a task in the scheduled task to let the program monitor the MySQL server status for us.
CrontabSet

*/2 * * * * python slavecheckpoint.py --defaults-file=server1.cnf --to=dba@abc.com

Github Project address:Https://github.com/yagas/checkpoint.git