DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Doris: Unifying SQL Dialects for a Seamless Data Query Ecosystem
  • Evaluating Performance Gains in MySQL Lock Scheduling Algorithms
  • Codify Your Cloud and Kubernetes With Crossplane and IaC
  • Troubleshooting Connection Issues When Connecting to MySQL Server

Trending

  • A Complete Guide to Modern AI Developer Tools
  • Event-Driven Architectures: Designing Scalable and Resilient Cloud Solutions
  • Testing SingleStore's MCP Server
  • A Developer's Guide to Mastering Agentic AI: From Theory to Practice
  1. DZone
  2. Data Engineering
  3. Databases
  4. Important Health Checks for Your MySQL Master-Slave Servers

Important Health Checks for Your MySQL Master-Slave Servers

In this article, we explain some basic health checks you can do on your MySQL master and slave nodes.

By 
Prasad Nagaraj user avatar
Prasad Nagaraj
·
Mar. 12, 20 · Analysis
Likes (4)
Comment
Save
Tweet
Share
8.2K Views

Join the DZone community and get the full member experience.

Join For Free

In a MySQL 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 fail, enabling the high availability framework to take corrective actions to ensure service availability.

You may also like: Pros and Cons of MySQL Replication Types

MySQL Master Server Health Checks

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

Ensure the MySQL Service Is Running

This can be done using a simple command like:

MySQL
 




xxxxxxxxxx
1


 
1
> pgrep mysqldOR
2
>service mysqld status



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:

Java
 




xxxxxxxxxx
1


 
1
Exit value=0 ⇒ Success
2
Exit value=1 ⇒ Failure
3
Exit-value=124 ⇒ Timeout



If the command times out, it means that the MySQL service is not responsive enough. We advise you to retry after some time 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.

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.

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 a read-only mode — the value of the variable read_only should be ‘ON’ in step-3.

Also, 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 can 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
 




xxxxxxxxxx
1


 
1
mysql> show slave status \G; *************************** 1. row *************************** 
2
Slave_IO_State: Waiting for master to send event Master_Host: 172.31.17.43
3
Master_User: repl_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: 
4
mysql-bin.000001 Read_Master_Log_Pos: 7510 Relay_Log_File: relay-log.000006 
5
Relay_Log_Pos: 414 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes 
6
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.


Further Reading

Jenkins Configure Master and Slave Nodes

MySQL Health (Apple) master

Published at DZone with permission of Prasad Nagaraj, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Doris: Unifying SQL Dialects for a Seamless Data Query Ecosystem
  • Evaluating Performance Gains in MySQL Lock Scheduling Algorithms
  • Codify Your Cloud and Kubernetes With Crossplane and IaC
  • Troubleshooting Connection Issues When Connecting to MySQL Server

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!