Shell Scripting for Database Administrators: Essential Scripts

Table of Contents

1. Why Shell Scripting Matters for DBAs
2. Fundamentals of Shell Scripting for DBAs
   • Shebang Line
   • Variables and User Input
   • Conditional Statements
   • Loops
   • Error Handling
   • Database Connectivity
3. Essential Script Categories with Examples
   • Automated Database Backups
   • Database Monitoring
   • User and Privilege Management
   • Log Rotation
   • Data Validation
4. Best Practices for DBA Shell Scripts
   • Security First
   • Robust Error Handling
   • Logging and Auditing
   • Idempotency
   • Testing and Version Control
5. Common Pitfalls and How to Avoid Them
6. Conclusion
7. References

Why Shell Scripting Matters for DBAs

Shell scripting (Bash, Zsh, etc.) is a lightweight, accessible way to automate database operations. Unlike complex programming languages, shell scripts leverage existing command-line tools (e.g., mysqldump, psql, sqlplus) and system utilities (e.g., cron, logrotate), making them ideal for integrating with database workflows. Benefits include:

• Efficiency: Automate daily/weekly tasks (e.g., backups) to run unattended.
• Consistency: Eliminate human error by standardizing procedures.
• Scalability: Manage multiple databases or clusters with reusable scripts.
• Rapid Response: Trigger alerts or remediation actions during outages (e.g., low disk space).

Fundamentals of Shell Scripting for DBAs

Before diving into complex scripts, let’s cover core shell scripting concepts tailored to DBA needs.

Shebang Line

The first line of a script specifies the interpreter (e.g., Bash):

#!/bin/bash

This ensures the script runs with the correct shell.

Variables and User Input

Variables store dynamic values (e.g., database credentials, paths). Avoid hardcoding sensitive data!

Example: Defining Variables

DB_NAME="mydb"
BACKUP_DIR="/var/backups/db"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)  # e.g., 20240520_143022

User Input with read
Prompt for input (e.g., a password):

read -p "Enter database password: " DB_PASS

Conditional Statements

Use if-else to handle logic (e.g., check if a backup succeeded):

if [ -d "$BACKUP_DIR" ]; then
  echo "Backup directory exists."
else
  echo "Creating backup directory: $BACKUP_DIR"
  mkdir -p "$BACKUP_DIR" || { echo "Failed to create dir"; exit 1; }
fi

Loops

Iterate over databases, tables, or servers with for/while loops:

# Backup multiple databases
DATABASES=("db1" "db2" "db3")
for DB in "${DATABASES[@]}"; do
  echo "Backing up $DB..."
  mysqldump ... "$DB" > "$BACKUP_DIR/$DB_$TIMESTAMP.sql"
done

Error Handling

Prevent silent failures with:

• set -e: Exit immediately if any command fails.
• set -u: Treat undefined variables as errors.
• set -o pipefail: Exit if any command in a pipeline fails.

Example:

#!/bin/bash
set -euo pipefail  # Strict error checking

# If mysqldump fails, the script exits here
mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" > backup.sql

Database Connectivity

Use database clients (e.g., mysql, psql) to run SQL commands in scripts:

MySQL Example:

mysql -u "$DB_USER" -p"$DB_PASS" -e "SELECT COUNT(*) FROM users;" "$DB_NAME"

PostgreSQL Example:

psql -U "$DB_USER" -d "$DB_NAME" -c "SELECT COUNT(*) FROM users;"

Security Note: Avoid plaintext passwords! Use environment variables or secure config files (e.g., ~/.my.cnf for MySQL with chmod 600).

Essential Script Categories with Examples

Below are实战scripts for common DBA tasks, with explanations and best practices.

1. Automated Database Backups

Goal: Create daily backups, compress them, and delete old backups.

MySQL Backup Script (mysql_backup.sh):

#!/bin/bash
set -euo pipefail

# Configuration (use environment variables in production!)
DB_USER="backup_user"
DB_PASS="${DB_PASS:-}"  # Fetch from env var; never hardcode!
BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=7  # Keep backups for 7 days

# Create backup dir if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Generate timestamp
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/full_backup_$TIMESTAMP.sql.gz"

# Backup all databases and compress
echo "Starting backup at $(date)..."
mysqldump -u "$DB_USER" -p"$DB_PASS" --all-databases --single-transaction | gzip > "$BACKUP_FILE"

# Check if backup succeeded
if [ $? -eq 0 ]; then
  echo "Backup saved to $BACKUP_FILE"
else
  echo "Backup failed!" >&2
  exit 1
fi

# Delete old backups
echo "Removing backups older than $RETENTION_DAYS days..."
find "$BACKUP_DIR" -name "full_backup_*.sql.gz" -mtime +"$RETENTION_DAYS" -delete

echo "Backup completed successfully."

Key Features:

• Uses --single-transaction for consistent InnoDB backups.
• Compresses backups with gzip to save space.
• Cleans up old backups with find -mtime.

Usage:

1. Store credentials in environment variables:

   export DB_PASS="secure_password"

2. Run with cron for daily backups (add to crontab -e):

   0 2 * * * /path/to/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

2. Database Monitoring

Goal: Check if the database is running and alert on failures.

PostgreSQL Health Check (pg_healthcheck.sh):

#!/bin/bash
set -euo pipefail

DB_USER="monitoring_user"
DB_NAME="app_db"
ALERT_EMAIL="[email protected]"

# Check if PostgreSQL is accepting connections
if ! pg_isready -U "$DB_USER" -d "$DB_NAME"; then
  echo "PostgreSQL is DOWN!" | mail -s "ALERT: PostgreSQL Unavailable" "$ALERT_EMAIL"
  exit 1
else
  echo "PostgreSQL is UP."
fi

Key Features:

• Uses pg_isready (more reliable than checking the service status).
• Sends email alerts via mail (install postfix or use sendmail).

Cron Job: Run every 5 minutes:

*/5 * * * * /path/to/pg_healthcheck.sh >> /var/log/pg_healthcheck.log 2>&1

3. User and Privilege Management

Goal: Automate creating users with predefined privileges.

PostgreSQL User Creation (create_db_user.sh):

#!/bin/bash
set -euo pipefail

# Inputs (pass as arguments or use variables)
NEW_USER="$1"
NEW_PASS="$2"
DB_NAME="$3"
PRIVILEGES="SELECT,INSERT,UPDATE"  # Restrict to least privilege

# Create user and grant privileges
psql -U "postgres" -c "
  CREATE USER $NEW_USER WITH PASSWORD '$NEW_PASS';
  GRANT CONNECT ON DATABASE $DB_NAME TO $NEW_USER;
  GRANT $PRIVILEGES ON ALL TABLES IN SCHEMA public TO $NEW_USER;
"

echo "User $NEW_USER created with $PRIVILEGES on $DB_NAME."

Usage:

./create_db_user.sh "app_user" "secure_pass123" "app_db"

Best Practice: Use parameterized queries to avoid SQL injection (though risk is low here with trusted inputs).

4. Log Rotation

Goal: Compress and archive large database logs to save disk space.

Logrotate Configuration (/etc/logrotate.d/mysql):
Logrotate is preferred over custom scripts for log management.

/var/log/mysql/*.log {
  daily
  missingok
  rotate 14  # Keep 14 days of logs
  compress
  delaycompress
  notifempty
  create 640 mysql mysql
}

What It Does:

• Rotates logs daily.
• Compresses old logs (.log.1.gz, .log.2.gz, etc.).
• Keeps 14 days of logs.

5. Data Validation

Goal: Check for duplicate records in a critical table.

MySQL Duplicate Check (check_duplicates.sh):

#!/bin/bash
set -euo pipefail

DB_USER="validator"
DB_NAME="orders_db"
TABLE="orders"
DUPLICATE_COL="order_id"

# Query for duplicates
DUPLICATES=$(mysql -u "$DB_USER" -p"$DB_PASS" -N -B -e "
  SELECT $DUPLICATE_COL, COUNT(*) 
  FROM $TABLE 
  GROUP BY $DUPLICATE_COL 
  HAVING COUNT(*) > 1;
" "$DB_NAME")

if [ -n "$DUPLICATES" ]; then
  echo "Duplicates found in $TABLE:$DUPLICATE_COL:"
  echo "$DUPLICATES"
  exit 1  # Trigger alert in monitoring tools (e.g., Prometheus)
else
  echo "No duplicates found."
fi

Usage: Integrate with monitoring (e.g., Nagios) to alert on non-zero exit codes.

Best Practices for DBA Shell Scripts

Security First

• Avoid plaintext credentials: Use environment variables (export DB_PASS=...) or secure config files (~/.my.cnf with chmod 600).
• Restrict permissions: Scripts should be owned by root/dba and have chmod 700 (no world access).
• Least privilege: Use database users with minimal required permissions (e.g., BACKUP_ADMIN for backups, not root).

Robust Error Handling

• Use set -euo pipefail for strict error checking.
• Add trap to clean up temporary files on exit:

  trap 'rm -f "$TMP_FILE"' EXIT  # Delete TMP_FILE if script exits

Logging and Auditing

• Log all actions to a file (e.g., >> /var/log/script.log 2>&1).
• Include timestamps and script names in logs for debugging:

  echo "[$(date +%Y-%m-%dT%H:%M:%S)] Backup started." >> "$LOG_FILE"

Idempotency

Ensure scripts can run multiple times without side effects. For example:

• Check if a user exists before creating them:

  if ! psql -tAc "SELECT 1 FROM pg_roles WHERE rolname='$NEW_USER'" | grep -q 1; then
    CREATE USER ...  # Only run if user doesn't exist
  fi

Testing and Version Control

• Test scripts in staging first (never production!).
• Store scripts in Git for versioning and collaboration.

Common Pitfalls and How to Avoid Them

Conclusion

Shell scripting is a cornerstone of efficient DBA workflows. By automating backups, monitoring, and user management, DBAs reduce manual effort and human error. The scripts in this guide provide a foundation—adapt them to your database (MySQL, PostgreSQL, etc.) and environment.

Remember: Prioritize security (no plaintext credentials!), test rigorously, and version-control your scripts. With these practices, you’ll build a robust automation toolkit to keep your databases reliable and secure.

References

• Bash Reference Manual
• MySQL mysqldump Documentation
• PostgreSQL psql Documentation
• logrotate Man Page
• The Linux Command Line (Book) (free online)

Happy scripting! 🚀<|FCResponseEnd|># Shell Scripting for Database Administrators: Essential Scripts

Introduction

Database Administrators (DBAs) are tasked with ensuring databases are secure, performant, and available. A significant portion of their work involves repetitive tasks: backups, monitoring, user management, and log rotation. Manually executing these tasks is time-consuming, error-prone, and scales poorly. Shell scripting emerges as a critical tool to automate these workflows, enabling DBAs to focus on high-impact tasks while ensuring consistency and reliability.

This blog explores shell scripting fundamentals tailored for DBAs, provides实战scripts for common tasks, and outlines best practices for security and efficiency. Whether you manage MySQL, PostgreSQL, or other databases, these concepts and examples will help you build a robust automation toolkit.

Table of Contents

1. Why Shell Scripting Matters for DBAs
2. Fundamentals of Shell Scripting for DBAs
   • Shebang Line
   • Variables and User Input
   • Conditional Statements
   • Loops
   • Error Handling
   • Database Connectivity
3. Essential Script Categories with Examples
   • Automated Database Backups
   • Database Monitoring
   • User and Privilege Management
   • Log Rotation
   • Data Validation
4. Best Practices for DBA Shell Scripts
5. Common Pitfalls and How to Avoid Them
6. Conclusion
7. References

Why Shell Scripting Matters for DBAs

Shell scripting (Bash, Zsh, etc.) is a lightweight, accessible tool for automating database operations. Unlike complex programming languages, shell scripts leverage existing command-line tools (e.g., mysqldump, psql) and system utilities (e.g., cron, logrotate), making them ideal for integrating with database workflows. Key benefits include:

• Efficiency: Automate