Best Practices for MySQL Database Inspection: Methods, Classification, and Deep Inspection

The author, a MySQL operations engineer with seven years of experience, presents a personal methodology for MySQL database inspection, emphasizing that even a seemingly simple task can become a documented best practice.

Inspection methods are divided into three categories: manual ("human") inspection using commands such as df -h and mysql -u, scripted inspection that packages commands into reusable scripts (or Ansible playbooks) and generates HTML/CSV reports, and platform‑based inspection that automates scheduling, scoring, and alerting for instances scoring below 60.

By time dimension, inspections are split into daily checks—primarily monitoring warnings—and pre‑holiday checks, which are deeper and focus on CPU, RAM, disk usage, instance status, high‑availability status, replication status, and VIP health.

Pre‑holiday checks include specific items such as:

System level: CPU, RAM, disk space (alert when usage >70%).

Application level (MySQL instance): instance health, HA status, replication health, monitoring status, VIP status.

Deep inspection is defined as the combination of availability, reliability, performance, and analysis/suggestions. Availability inspection expands beyond basic health checks to include business‑level concerns like auto‑increment key exhaustion (e.g., int unsigned vs. bigint signed) and its impact on write capability.

Reliability inspection focuses on core parameter compliance, master‑slave parameter consistency, and alignment between runtime parameters and the my.cnf file. Example core parameters include:

binlog_format = row
binlog_row_image = full
gtid_mode = on
enforce-gtid-consistency = on
innodb_doublewrite = on
innodb_flush_log_at_trx_commit = 1
log_bin = mysql-bin
master_info_repository = table
sync_binlog = 1
...

Performance inspection covers common items such as missing primary keys, SQL performance (top slow queries, full‑table scans), index health (redundant, unused, low‑selectivity indexes), avoidance of the MyISAM engine (using

disabled_storage_engines=ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB,MyISAM

), and identification of top‑10 large tables with recommendations on size limits.

Analysis and suggestions encourage providing tenants with as much raw data as possible for their own performance tuning, while the author notes that deep SQL analysis may require specialized expertise.

In conclusion, the author invites feedback on this summarized MySQL inspection checklist.