How to verify if a slave running in MySQL 5.7

by Pythian Marketing
1 min read
Jul 18, 2016 12:00:00 AM
Most people know that you can use SHOW SLAVE STATUS to verify if a slave is running in MySQL. Most people also know that you need to check both Slave_IO_Running and Slave_SQL_Running. https://gist.github.com/dtest/86db850f26f19d8c7bb81b95f6241f31#file-show-slave-status-sql A few years ago, I responded to a question on dba.stackexchange.com on how to verify if a slave is running outside of SHOW SLAVE STATUS. Prior to MySQL 5.7, you could get this information from SHOW GLOBAL STATUS: https://gist.github.com/dtest/86db850f26f19d8c7bb81b95f6241f31#file-mysql-56-sql Keep in mind that certain versions of 5.1 might give false information if the Slave_IO_Thread was not running. But if you upgrade to MySQL 5.7 and your application relies on anything other than SHOW SLAVE STATUS output, it might be broken. In MySQL 5.7, you would get this: https://gist.github.com/dtest/86db850f26f19d8c7bb81b95f6241f31#file-without_56_compat-sql The reason is that MySQL is moving away from the information_schema GLOBAL_STATUS and SESSION_STATUS tables in preference for performance_schema. The correct way to get the status of slave running in MySQL 5.7 outside of SHOW SLAVE STATUS is to use the new replication-based performance_schema tables: https://gist.github.com/dtest/86db850f26f19d8c7bb81b95f6241f31#file-mysql-57-sql An alternative to using the new performance_schema tables is to utilize a temporary compatibility variable: show_compatibility_56. https://gist.github.com/dtest/86db850f26f19d8c7bb81b95f6241f31#file-with_56_compatibility-sql

Conclusion

If your applications (monitoring or other) rely on checking that slaves are running by utilizing the information schema GLOBAL_STATUS table, upgrading to MySQL 5.7 will break it. You have a few choices:
  • Use SHOW SLAVE STATUS directly and parse the output of Slave_IO_Running and Slave_SQL_Running. This requires application changes.
  • Use the new performance_schema replication tables performance_schema.replication_connection_status and performance_schema.replication_applier_status. This also requires an application change. And it should be noted that there are other tables for multi-master setups!
  • Use the compatibility variable show_compatibility_56. This is dynamic and doesn't require application changes. However, this variable is immediately deprecated and will go away in a future release, so you will be required to update your application at some point anyway.
On this page
When SHOW SLAVE STATUS and the error log Disagree
MySQL

When SHOW SLAVE STATUS and the error log Disagree

Apr 25, 2008 12:00:00 AM 3 min read
When SHOW SLAVE STATUS lies

When SHOW SLAVE STATUS lies

Apr 6, 2008 12:00:00 AM 3 min read
MySQL 5.7 Multi-threads replication operation tips
MySQL

MySQL 5.7 Multi-threads replication operation tips

Feb 16, 2018 12:00:00 AM 2 min read

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

Follow Us     Pythian-LinkedIn   Pythian-Twitter   Pythian-FB

 

Pythian-logo-White

© 2025 All rights reserved.