DEV Community

Thesius Code
Thesius Code

Posted on • Originally published at datanest-stores.pages.dev

MySQL Admin Scripts

#database #performance #postgres #sql

MySQL Admin Scripts

A complete MySQL administration toolkit covering the tasks that eat up your week — replication setup, automated backups with verification, user and privilege management, health monitoring, and performance diagnostics. Every script is parameterized, idempotent, and tested against MySQL 8.0+ and MariaDB 10.6+. Run them directly or integrate them into cron and your CI/CD pipeline.

Key Features

  • Replication setup scripts for source-replica, multi-source, and GTID-based topologies with automated failover preparation
  • Backup automation using mysqldump, mysqlpump, and Percona XtraBackup with compression, encryption, and rotation
  • User management templates implementing least-privilege roles for application, reporting, monitoring, and DBA access
  • Health monitoring queries covering InnoDB buffer pool efficiency, query cache utilization, thread states, and lock contention
  • Performance diagnostics leveraging Performance Schema and sys schema views to identify slow queries and resource bottlenecks
  • Table maintenance scripts for OPTIMIZE TABLE, ANALYZE TABLE, and partition management on large datasets
  • Connection killer that safely terminates long-running queries and sleeping connections exceeding configurable thresholds
  • Automated security audit checking for default accounts, empty passwords, excessive privileges, and insecure configurations

Quick Start 

unzip mysql-admin-scripts.zip
cd mysql-admin-scripts/

# 1. Configure your connection
cp config.example.yaml config.yaml
# Edit config.yaml with your MySQL host, port, credentials

# 2. Run the health check
mysql -h localhost -u admin -p < src/monitoring/health_check.sql

# 3. Set up automated daily backups (add to crontab)
chmod +x src/backup/backup.sh
# crontab: 0 3 * * * /opt/mysql-admin/src/backup/backup.sh
Enter fullscreen mode Exit fullscreen mode

Run the health check directly:

-- Quick server health overview
SELECT
    VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
    'Threads_connected', 'Threads_running',
    'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',
    'Slow_queries', 'Uptime'
);

-- InnoDB buffer pool hit ratio (should be > 99%)
SELECT
    ROUND(
        (1 - (
            (SELECT VARIABLE_VALUE FROM performance_schema.global_status
             WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
            NULLIF((SELECT VARIABLE_VALUE FROM performance_schema.global_status
             WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'), 0)
        )) * 100, 2
    ) AS buffer_pool_hit_ratio_pct;
Enter fullscreen mode Exit fullscreen mode

Architecture / How It Works 

mysql-admin-scripts/
├── src/
│   ├── replication/
│   │   ├── setup_source_replica.sql   # GTID-based replication setup
│   │   ├── check_replication_status.sql
│   │   └── failover_prepare.sql       # Promote replica to source
│   ├── backup/
│   │   ├── backup.sh                  # mysqldump with compression + rotation
│   │   ├── xtrabackup.sh             # Hot backup via Percona XtraBackup
│   │   ├── restore.sh                # Restore from backup with verification
│   │   └── verify_backup.sh          # Test restore to temp instance
│   ├── users/
│   │   ├── create_app_user.sql        # Least-privilege app user
│   │   ├── create_readonly_user.sql   # Reporting/analytics user
│   │   ├── create_monitor_user.sql    # Monitoring-only user
│   │   └── audit_privileges.sql       # Find overprivileged accounts
│   ├── monitoring/
│   │   ├── health_check.sql           # Server health overview
│   │   ├── slow_query_analysis.sql    # Top slow queries from perf_schema
│   │   ├── lock_contention.sql        # Active locks and waiters
│   │   └── connection_killer.sql      # Kill long-running queries
│   └── maintenance/
│       ├── optimize_tables.sh         # Batch OPTIMIZE for fragmented tables
│       └── partition_management.sql   # Add/drop partitions by date range
├── examples/
│   ├── crontab_example.txt
│   └── monitoring_integration.md
└── config.example.yaml
Enter fullscreen mode Exit fullscreen mode

Usage Examples

GTID-based replication setup:

-- On the SOURCE server
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;

CREATE USER 'repl_user'@'10.0.0.%'
    IDENTIFIED BY 'YOUR_REPLICATION_PASSWORD_HERE';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.0.%';

-- On the REPLICA server
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '10.0.0.1',
    SOURCE_USER = 'repl_user',
    SOURCE_PASSWORD = 'YOUR_REPLICATION_PASSWORD_HERE',
    SOURCE_AUTO_POSITION = 1,
    SOURCE_SSL = 1;

START REPLICA;
SHOW REPLICA STATUS\G
-- Verify: Replica_IO_Running = Yes, Replica_SQL_Running = Yes
Enter fullscreen mode Exit fullscreen mode

Find and kill long-running queries:

-- Find queries running longer than 60 seconds
SELECT
    id AS process_id,
    user,
    host,
    db,
    command,
    time AS seconds_running,
    LEFT(info, 100) AS query_preview
FROM information_schema.processlist
WHERE command != 'Sleep'
  AND time > 60
  AND user != 'system user'
ORDER BY time DESC;

-- Kill specific long-running query (replace <PID>)
-- KILL <PID>;
Enter fullscreen mode Exit fullscreen mode

Automated backup with rotation:

#!/bin/bash
# backup.sh — daily mysqldump with 30-day retention
BACKUP_DIR="/backups/mysql/$(date +%Y%m%d_%H%M%S)"
RETENTION_DAYS=30
MYSQL_USER="backup_user"
MYSQL_PASS="YOUR_BACKUP_PASSWORD_HERE"
MYSQL_HOST="localhost"

mkdir -p "$BACKUP_DIR"

# Dump all databases with routines and triggers
mysqldump \
  --host="$MYSQL_HOST" --user="$MYSQL_USER" --password="$MYSQL_PASS" \
  --all-databases \
  --single-transaction \
  --routines --triggers --events \
  --set-gtid-purged=AUTO \
  | gzip > "$BACKUP_DIR/full_backup.sql.gz"

# Verify backup is not empty
if [ $(stat -c%s "$BACKUP_DIR/full_backup.sql.gz") -lt 1000 ]; then
  echo "ERROR: Backup file suspiciously small" >&2
  exit 1
fi

# Rotate old backups
find /backups/mysql -maxdepth 1 -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;
echo "Backup completed: $BACKUP_DIR"
Enter fullscreen mode Exit fullscreen mode

Configuration 

# config.example.yaml
mysql:
  host: localhost
  port: 3306
  admin_user: admin
  admin_password: YOUR_ADMIN_PASSWORD_HERE

backup:
  method: mysqldump           # mysqldump | xtrabackup
  schedule: "0 3 * * *"       # daily at 3 AM
  retention_days: 30
  compression: gzip
  include_routines: true
  include_triggers: true
  single_transaction: true    # consistent snapshot without locking

monitoring:
  slow_query_threshold_ms: 1000
  max_connection_age_seconds: 3600
  kill_sleeping_connections_after: 300
  buffer_pool_hit_ratio_alert: 99.0

replication:
  topology: source-replica    # source-replica | multi-source | group
  gtid_mode: true
  semi_sync: false
  lag_alert_seconds: 30
Enter fullscreen mode Exit fullscreen mode

Best Practices

  1. Always use --single-transaction with mysqldump on InnoDB tables. Without it, you get an inconsistent backup that requires a global lock.
  2. Monitor Threads_running, not Threads_connected. A server can have 500 connected threads but only 5 running — that's normal. 50 running is not.
  3. Use GTID-based replication for new setups. It simplifies failover and eliminates the need to track binary log file positions manually.
  4. Never grant ALL PRIVILEGES ON *.* to application users. Map each role to the minimum grants needed — SELECT for readers, SELECT/INSERT/UPDATE/DELETE for writers.
  5. Test your backups monthly by restoring to a staging instance. A backup you've never restored is a hope, not a plan.
  6. Set innodb_buffer_pool_size to 70-80% of available RAM on dedicated database servers. This single setting has more impact than any query tuning.

Troubleshooting

Problem Cause Fix
Replication stops with duplicate key error Data written directly to replica STOP REPLICA; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START REPLICA; then prevent direct writes
mysqldump hangs on large database Table lock wait or slow network Use --single-transaction and consider mysqlpump with --default-parallelism=4
Buffer pool hit ratio below 99% innodb_buffer_pool_size too small Increase to 70-80% of RAM; check if working set exceeds available memory
"Too many connections" error Connection leak in application Check SHOW PROCESSLIST for sleeping connections; set wait_timeout=300

This is 1 of 9 resources in the Database Admin Pro toolkit. Get the complete [MySQL Admin Scripts] with all files, templates, and documentation for $29.

Get the Full Kit →

Or grab the entire Database Admin Pro bundle (9 products) for $109 — save 30%.

Get the Complete Bundle →

Related Articles

Top comments (0)