MySQL Administration

Manage MySQL databases via mysql CLI or Python mysql-connector, supporting queries, schema changes, backups, performance analysis, and user permissions.

Security Scan

The skill mostly matches its MySQL administration purpose, but there are coherence and safety issues (undeclared required credentials, use of eval/unsanitized shell arguments, and instructions that touch system services) that you should understand before installing or running it.

Purpose & Capability

The skill's name, description, included scripts, and references align with MySQL administration (queries, backup/restore, performance). The embedded SKILL.md metadata correctly lists required binaries (mysql, mysqldump, mysqlcheck) and optional python3. However, the registry metadata reported 'no required env vars' while the runtime instructions and scripts expect MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE, etc. — a mismatch between declared requirements and actual needs.

Instruction Scope

The SKILL.md instructs the agent to read and use database credentials from environment variables and to run local system commands (e.g., sudo systemctl start mysql in QUICKSTART and a hard-coded path /home/clawbot/openclaw/test_mysqladm_skill.sh). The shipped scripts build shell commands from user-provided inputs and use eval in places (backup/restore/query scripts), which increases the risk of command injection if inputs are not strictly validated. The instructions also reference {baseDir} for bundled scripts but do not document how it is set; the skill may attempt to run those scripts with whatever credentials are provided.

Install Mechanism

This is instruction-plus-scripts (no network install/spec that downloads code at runtime). The SKILL.md includes optional apt/brew install guidance for the mysql client, which is appropriate and from normal package managers. No external URLs, shorteners, or archive extraction are used.

Credentials

Although the registry metadata lists no required environment variables or primary credential, the skill requires DB credentials and host/port settings at runtime (MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE). That omission is a meaningful coherence issue: the skill will need sensitive credentials to operate but does not declare them. The scripts accept passwords on the command line and embed them into commands (mysqldump/mysql -pPASSWORD), which could expose secrets in process listings. Multiple env/config references (and a hard-coded local test path) are present without being declared.

Persistence & Privilege

The skill does not request always:true and is user-invocable. It does not modify other skills or system-wide settings in its code. However, QUICKSTART suggests using sudo to start the mysql service — running with elevated privileges is a runtime consideration for the user, not an installation-time privilege request.

What to consider before installing

This skill appears to implement MySQL admin tasks, but there are gaps and risky patterns you should consider before installing or running it: - Credentials: The skill expects MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE, etc., but the registry metadata does not declare these. Treat this as requiring sensitive DB credentials. - Inspect before running: The bundled scripts call mysqldump/mysql and use eval and unquoted variables; this can enable command injection if any argument is attacker-controlled. Review the scripts and avoid passing untrusted input. - Secret exposure: Passwords are passed on the command line (e.g., -p$PASSWORD), which can appear in process lists. Prefer using option files, protected environment variables handled by the platform, or mysql client config files with restricted permissions. - Environment impact: QUICKSTART suggests sudo systemctl start mysql and references a hard-coded test path (/home/clawbot/...), so run tests in an isolated environment (container or VM), not against production systems. - Ask the author or maintainer to: (1) declare required env vars/credentials in the skill metadata, (2) avoid eval and quote arguments properly, (3) avoid printing secrets and using passwords on the command line, and (4) explain the {baseDir} substitution and any test scripts referenced. If you must use it now, run in a sandbox with non-production credentials and back up targets first. If you are not comfortable reviewing shell scripts yourself, do not run this skill with privileged credentials or on production systems.

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:

# 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

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

Quick Start

Execute Query

# 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

# 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

# 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

# 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

# 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

# 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

# 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

# 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:

{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:

{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:

{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

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

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

# Test connectivity
telnet $MYSQL_HOST $MYSQL_PORT

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

# Check firewall
sudo ufw status

Permission Denied

# 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

# 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