MySQL Cookbook, 4th Edition

Chapter 23. Monitoring the MySQL Server

23.0 Introduction

This chapter covers how to monitor the MySQL server using various command-line tools:

• The mysqladmin interface

• System variables

• Status variables

• Information and Performance Schemas

• Storage engines diagnostics

• Logfiles

This chapter doesn’t cover managing administrative tasks. Instead, it focuses on the server’s observability. Administrators or developers should evaluate outcomes from various command-line tools on the MySQL server carefully before taking action and modifying configuration changes listed in Chapter 22. Rather, this chapter discusses what you can find out, and how, by surveying the types of information available and how to use that information to answer questions. The purpose is not so much to consider specific monitoring problems, but to illustrate your options so you can begin to answer your questions, whatever they are. In the case of reactive monitoring on an issue, follow one of the following options:

1. Determine which of the available information sources pertain to the problem at hand.

2. Choose an approach for using the information: Are you asking a one-time question? If so, maybe a few interactive queries are sufficient. If you’re trying to solve an issue that may recur or for which you need continuous monitoring, a program-oriented approach is better. Will a script written entirely in SQL do the job, or do you need to write a program that queries the server and performs additional manipulation of the information obtained? ...