MySQL Administration#

Manage MySQL databases using the mysql CLI or Python mysql-connector.

When to Use#

✅ USE this skill when:

• "Query users who registered today"
• "Add an index to the orders table"
• "Backup the production database"
• "Check slow query logs"
• "Grant permissions to a new user"
• "Analyze table size and storage usage"

When NOT to Use#

❌ DON'T use this skill when:

• Complex ETL workflows → use specialized ETL tools (Airflow, dbt)
• Real-time data streaming → use CDC tools (Debezium, Maxwell)
• Graph database queries → use Neo4j or graph-specific tools
• Time-series analytics → use TimescaleDB or InfluxDB
• Large-scale data processing → use Spark or distributed systems

Setup#

Connection Configuration#

Set environment variables or use command-line flags:

bash

# Environment variables (recommended)
export MYSQL_HOST="localhost"
export MYSQL_PORT="3306"
export MYSQL_USER="root"
export MYSQL_PASSWORD="password"
export MYSQL_DATABASE="mydb"

# Or use flags directly
mysql -h localhost -P 3306 -u root -p mydb

Test Connection#

bash

mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT VERSION();"

Quick Start#

Execute Query#

bash

# Using environment variables
mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e "SELECT * FROM users LIMIT 10"

# Direct connection
mysql -h localhost -u root -p -e "SHOW DATABASES;"

Schema Information#

bash

# List all databases
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;"

# Show tables
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e "SHOW TABLES;"

# Describe table structure
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e "DESCRIBE users;"

Common Operations#

Query Execution#

bash

# Simple query
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e "SELECT id, name FROM users WHERE created_at > '2026-01-01';"

# Query with formatting (table output)
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -t -e "SELECT * FROM orders LIMIT 5;"

# Query with JSON output
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE --json -e "SELECT * FROM products;"

Schema Management#

bash

# Create table
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE << 'SQL'
CREATE TABLE IF NOT EXISTS events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_type VARCHAR(50),
  payload JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
SQL

# Add index
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e "CREATE INDEX idx_user_email ON users(email);"

# Alter table
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e "ALTER TABLE orders ADD COLUMN status VARCHAR(20);"

Database Backup#

bash

# Using bundled script
{baseDir}/scripts/mysql_backup.sh --host $MYSQL_HOST --user $MYSQL_USER --password $MYSQL_PASSWORD --database $MYSQL_DATABASE --output /tmp/backup.sql

# Or using mysqldump directly
mysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE > /tmp/backup.sql

# Backup with specific tables
mysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE users orders > /tmp/partial_backup.sql

Database Restore#

bash

# Using bundled script
{baseDir}/scripts/mysql_restore.sh --host $MYSQL_HOST --user $MYSQL_USER --password $MYSQL_PASSWORD --database $MYSQL_DATABASE --input /tmp/backup.sql

# Or using mysql directly
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE < /tmp/backup.sql

Performance Analysis#

bash

# Check table sizes
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD information_schema -e "
SELECT
  table_schema,
  table_name,
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM tables
WHERE table_schema = '$MYSQL_DATABASE'
ORDER BY size_mb DESC;
"

# Show indexes
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e "SHOW INDEX FROM users;"

# Analyze table
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e "ANALYZE TABLE users;"

# Check slow queries (requires slow query log enabled)
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW VARIABLES LIKE 'slow_query_log';"

User Management#

bash

# Create user
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD << 'SQL'
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';
SQL

# Grant permissions
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD << 'SQL'
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';
FLUSH PRIVILEGES;
SQL

# Show users
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT user, host FROM mysql.user;"

Script Usage#

mysql_query.sh#

Execute queries with formatted output:

bash

{baseDir}/scripts/mysql_query.sh \
  --host $MYSQL_HOST \
  --user $MYSQL_USER \
  --password $MYSQL_PASSWORD \
  --database $MYSQL_DATABASE \
  --query "SELECT COUNT(*) FROM users"

mysql_backup.sh#

Backup database with timestamp:

bash

{baseDir}/scripts/mysql_backup.sh \
  --host $MYSQL_HOST \
  --user $MYSQL_USER \
  --password $MYSQL_PASSWORD \
  --database $MYSQL_DATABASE \
  --output /backups/$(date +%Y%m%d)_backup.sql

mysql_restore.sh#

Restore from backup:

bash

{baseDir}/scripts/mysql_restore.sh \
  --host $MYSQL_HOST \
  --user $MYSQL_USER \
  --password $MYSQL_PASSWORD \
  --database $MYSQL_DATABASE \
  --input /backups/20260115_backup.sql

Advanced Queries#

Aggregate Functions#

bash

mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE << 'SQL'
SELECT
  DATE(created_at) as date,
  COUNT(*) as daily_users,
  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_users
FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY date DESC;
SQL

Join Queries#

bash

mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE << 'SQL'
SELECT
  u.name,
  u.email,
  COUNT(o.id) as order_count,
  SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
HAVING total_spent > 1000
ORDER BY total_spent DESC;
SQL

Security Notes#

• Never hardcode passwords in scripts or queries. Use environment variables.
• Use least-privilege principle: Grant only necessary permissions to application users.
• Encrypt connections: Use SSL/TLS for remote connections ( --ssl-mode=REQUIRED).
• Validate inputs: When constructing queries dynamically, always sanitize inputs to prevent SQL injection.
• Backup before modifications: Always create a backup before schema changes or bulk updates.

Troubleshooting#

Connection Issues#

bash

# Test connectivity
telnet $MYSQL_HOST $MYSQL_PORT

# Check MySQL service
systemctl status mysql  # or: service mysql status

# Check firewall
sudo ufw status

Permission Denied#

bash

# Check current user
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT CURRENT_USER();"

# Check grants
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW GRANTS FOR CURRENT_USER();"

Slow Queries#

bash

# Enable slow query log
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD << 'SQL'
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SQL

# View slow queries
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;"

References#

For detailed schema analysis and performance tuning, see:

• Schema Analysis - Table structure analysis and optimization
• Performance Tuning - Query optimization and indexing strategies

Notes#

• Always use transactions for multi-step operations: START TRANSACTION; ... COMMIT; or ROLLBACK;
• Use EXPLAIN to analyze query execution plans before running complex queries
• Monitor database size and growth regularly
• Keep backups in multiple locations for disaster recovery
• Test backup/restore procedures regularly to ensure they work when needed