Important Health Checks for your MySQL Master-Slave Servers

栏目: IT技术 · 发布时间: 4年前

内容简介:In aMySQL master-slave high availability (HA) setup, it is important to continuously monitor the health of the master and slave servers so you can detect potential issues and take corrective actions. In this blog post, we explain some basic health checks y

In aMySQL master-slave high availability (HA) setup, it is important to continuously monitor the health of the master and slave servers so you can detect potential issues and take corrective actions. In this blog post, we explain some basic health checks you can do on your MySQL master and slave nodes to ensure your setup is healthy. The monitoring program or script must alert the high availability framework in case any of the health checks fails, enabling the high availability framework to take corrective actions in order to ensure service availability.

MySQL Master Server Health Checks

We recommended that your MySQL master monitoring program or scripts runs at frequent intervals. Assuming that the monitoring script is running on the same server as your MySQL server, you can check for the following:

  1. Ensure the MySQL service is running

    This can be done using a simple command like:

    > pgrep mysqld

    OR

    >service mysqld status
  2. Ensure you can connect to MySQL and do a simple query

    We recommended having a short timeout for these commands so you can quickly detect if MySQL is unresponsive. This can be achieved from a call like:

    /usr/bin/timeout 5 mysql -u testuser -ptestpswd -e 'select * from mysql.test’

    Be sure to examine the exit value of the above command:

    Exit value=0 ⇒ Success

    Exit value=1 ⇒ Failure

    Exit-value=124 ⇒ Timeout

    If the command times out, it means that the MySQL service is not responsive enough. We advice you retry after some time so as to avoid false negative results. If the exit code indicates a failure, the return code from MySQL will tell us the failure reason. One example of a failure is the ‘Too many connections’ error from MySQL which happens if the number of connections to the server exceeds your ‘max_connections’ configuration value.

  3. Ensure the MySQL master is running in read-write mode

    You can use the following command to ensure the MySQL master is running in read-write mode:

    /usr/bin/timeout 5 mysql -u testuser -ptestpswd -e "SELECT @@global.read_only"

    The master is expected to be always running in read-write mode, and hence, the value of  read_only should be ‘OFF’.

    It is also possible to club this step with step 2, and instead of doing the test query ‘select * from mysql.test, we can just do the query to get the read_only value.

Important Health Checks for your MySQL Master-Slave Servers Click To Tweet

MySQL Slave Server Health Checks

You can run the monitoring for your MySQL slaves at a lesser frequency compared to the master, as they are not handling data writes. The first 3 steps for your slave health check can be the same as that of the master, except that we need to ensure the slave is running in read-only mode - the value of the variable read_only should be ‘ON’ in step-3.

In addition, we can do more checks on the slave to ensure its replication status is healthy, such as:

  1. The slave is configured to replicate from the right master.

  2. The slave’s connection to the master is healthy.

  3. The slave is able to apply the master events it has received.

It’s possible to check for all the above using the ‘show slave status’ command. For example:

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.31.17.43

Master_User: repl_user

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 7510

Relay_Log_File: relay-log.000006

Relay_Log_Pos: 414

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

******************Truncated*********************************
  • The Master_Host value indicates the master server is configured for replication.

  • For the Slave_IO_Running value, “Yes” indicates that the slave has connected to the master and is receiving the replication stream.

  • For the Slave_SQL_Running value, “Yes” indicates that the slave’s applier is running and able to apply all the events received from the master.

In this blog post, we discussed some simple checks that can detect if there are basic issues in your MySQL master and slave servers. In general, the failure detection mechanism in a high availability setup is a complex subject and needs a robust high availability framework through which health check monitoring should be implemented. You can learn more about the details of our high availability framework in our MySQL High Availability Framework Explained – Part I: Introduction blog post.

Important Health Checks for your MySQL Master-Slave Servers

+1

Share

Shares 0


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

新机器的灵魂

新机器的灵魂

Tracy Kidder / 龚益、高宏志 / 机械工业出版社华章公司 / 2011-10 / 45.00元

计算机从1981年开始发生巨大的变化。《新机器的灵魂》完整地记录下了当时一家公司齐心协力把一种新的小型计算机推向市场的过程中所发生的一系列戏剧性的、充满戏剧色彩的、激动人心的故事。 本书以美国通用数据公司研制鹰电子计算机的全过程为主线,对美国计算机工业的发展和管理中鲜为人知的侧面,作了条理清晰、颇具诗情画意的描述。 你想知道一代新型计算机怎样诞生,精明干练而又富于幽默感的工程技术人员怎......一起来看看 《新机器的灵魂》 这本书的介绍吧!

HTML 压缩/解压工具
HTML 压缩/解压工具

在线压缩/解压 HTML 代码

CSS 压缩/解压工具
CSS 压缩/解压工具

在线压缩/解压 CSS 代码

随机密码生成器
随机密码生成器

多种字符组合密码