SxSQLyze: MySQL and MariaDB database analysis | Christophe Casalegno / Brain Override

SxSQLyze (StackX SQL Analyzer)

SxSqlyze is a GPL-licensed Bash script for quickly analysing a MySQL or MariaDB instance on Linux.

In a few seconds, it provides an accurate overview of the databases: sizes, fragmentation, storage engines, indexes, etc.

Main features

– Database and table sizes.
– The N largest tables across all databases.
– Estimated daily growth (MB/day hint).
– Breakdown of storage engines (InnoDB, MyISAM…).
– Fragmentation detection (reclaimable data_free).
– Character sets and collations by database.
– Analysis of index sizes by table.
– Full analysis of all databases (`–analyze-all`).
– Choice of display unit (MB or GB).

Usage

After downloading the script, it is very simple to use.

./sxsqlyze.sh --databases

Note: personally, I copy this script to /usr/local/bin under the name sxsqlyze and use it directly as a standard command.

Options

SxSqlyze v1.2 – diagnostic rapide MySQL/MariaDB
Options disponibles :
  --databases                     Affiche la taille de toutes les bases
  --tables <db>                   Affiche la taille des tables d'une base
  --top <N>                       Top N des plus grosses tables
  --growth-hint <db>              Estimation de croissance quotidienne
  --engine-stats <db>             Répartition des moteurs de stockage
  --table-fragmentation <db>      Espace récupérable (fragmentation)
  --charset-collation <db>        Jeux de caractères et collations
  --index-usage <db>              Taille des index par table
  --analyze-all                   Analyse complète sur toutes bases
  --unit mb|gb                    Unité de sortie (par défaut: gb)

Examples

– List the size of every database:

./sxsqlyze.sh --databases

– Display the tables in the “crm” database:

./sxsqlyze.sh --tables crm

– Show the five largest tables on the server:

./sxsqlyze.sh --top 5

– Analyse fragmentation in the “shop” database:

./sxsqlyze.sh --table-fragmentation shop

– Change the display unit to MB:

./sxsqlyze.sh --databases --unit mb

Todo list

– Add more analysis functions.
– Add possible export formats.
– Add a `–quick` mode to automatically target large databases.

Resources

– Script: sxsqlyze.sh