What's included

This chapter covers the core features and capabilities of MySQL Admin Scripts.

What's included

mysql-admin-scripts/
├── README.md
├── LICENSE
├── sql/                                  # diagnostics to run in a session
│   ├── 01_user_grant_audit.sql                 Over-privileged & passwordless accounts
│   ├── 02_slow_query_analysis.sql              Statement digests, missing-index signals
│   ├── 03_innodb_status.sql                    Buffer pool hit ratio, dirty pages, locks
│   ├── 04_table_and_index_sizes.sql            Biggest tables, fragmentation, dead indexes
│   ├── 05_replication_status.sql               GTID, replica lag, thread state
│   └── 06_processlist_and_locks.sql            Live queries, long txns, blocking trees
├── scripts/                              # bash automation (set -euo pipefail)
│   ├── mysql_backup.sh                         Consistent mysqldump + gzip + rotation
│   ├── mysql_restore.sh                        Safe restore with integrity check
│   ├── xtrabackup_full.sh                      Percona XtraBackup hot backup + prepare
│   ├── check_replication.sh                    Cron/monitor replica health check
│   └── create_app_user.sh                      Least-privilege scoped app user
├── config/
│   └── my.cnf.reference                        Annotated 8.0 tuning reference
├── docs/
│   ├── BACKUP-RESTORE.md                        Logical vs physical, PITR, restore drills
│   ├── REPLICATION-SETUP.md                     End-to-end GTID replication
│   ├── INNODB-TUNING.md                         Buffer pool, redo log, durability
│   ├── SLOW-QUERY-ANALYSIS.md                   performance_schema + slow log workflow
│   └── USER-GRANT-MANAGEMENT.md                 Least-privilege model & roles
└── examples/
    └── sample_schema.sql                        Synthetic schema + data to test against

Prerequisites

• MySQL 8.0 or MariaDB 10.3+ (most queries also work on MySQL 5.7;

version-specific notes are inline).

• The standard mysql and mysqldump clients.
• For scripts/xtrabackup_full.sh: Percona XtraBackup (8.0 for MySQL 8.0,

2.4 for 5.7).

• performance_schema enabled (default in 8.0) for sql/02, 03, 06.
• Credentials are never stored in this kit. Every script reads them from a

MySQL *defaults-extra-file* ([client] section, chmod 600) so passwords

never hit the command line, ps, or your shell history.

A defaults file looks like:

[client]
user     = admin_user
password = "your-password-here"
host     = db01.example.com

Quick start (5 minutes)

Follow this guide to get MySQL Admin Scripts up and running in your environment.

Quick start (5 minutes)

# 0) Make the scripts executable after unzipping.
chmod +x scripts/*.sh

# 1) Load the demo schema into a scratch database.
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS adminkit_demo;"
mysql -u root -p adminkit_demo < examples/sample_schema.sql

# 2) Audit accounts for over-privilege (read-only).
mysql -u root -p < sql/01_user_grant_audit.sql

# 3) Take a consistent, compressed, rotated backup.
./scripts/mysql_backup.sh -c ~/.mysql-backup.cnf -d adminkit_demo -o /backups -r 7

# 4) Create a least-privilege application user (prints the SQL for review).
./scripts/create_app_user.sh -u app_user -d adminkit_demo -H '10.0.%'

File-by-file guide

sql/ — paste-and-run diagnostics

01, 04 (sizes), 05, 06 are read-only. 02/03 read performance_schema.

Start with 01 (security), 02 (slow queries), and 03 (InnoDB health). On a

replica, 05 plus scripts/check_replication.sh tell you if replication is

healthy.

scripts/ — automation

• mysql_backup.sh — mysqldump --single-transaction (consistent, no write

lock on InnoDB) piped through gzip, with integrity check and retention pruning.

• mysql_restore.sh — verifies the archive, confirms before overwriting, and

can redirect a dump into a _staging schema.

• xtrabackup_full.sh — physical hot backup (--backup then --prepare),

capturing the GTID position so the copy can seed a replica.

• check_replication.sh — exits 0/1/2 (OK/WARN/CRIT) for monitoring, across

the source/replica naming change.

• create_app_user.sh — generates a scoped, strong-password CRUD or

read-only account; prints the SQL by default, executes only with -e.

config/my.cnf.reference

Annotated tuning reference (buffer pool sizing, redo log, durability, binlog,

GTID, observability) sized around a 32 GB OLTP server. Read and adapt; do not

paste blindly.

docs/ — the runbooks

Backup/restore (incl. point-in-time recovery and the monthly restore drill),

end-to-end GTID replication setup, InnoDB tuning, slow-query analysis, and the

least-privilege user/grant model with MySQL 8.0 roles.

examples/sample_schema.sql

A synthetic e-commerce schema (~20k customers, ~100k orders, ~200k line items)

built with recursive CTEs, deliberately missing one index so the diagnostics and

EXPLAIN have something real to find.