{"skill":{"slug":"mysqladm","displayName":"MySQL Administration","summary":"Manage MySQL databases via mysql CLI or Python mysql-connector, supporting queries, schema changes, backups, performance analysis, and user permissions.","description":"---\nname: mysqladm\ndescription: MySQL database management via mysql CLI or Python mysql-connector. Use when: (1) executing queries and displaying results, (2) managing schemas (create/alter tables, indexes), (3) database backup/restore, (4) performance analysis (slow queries, index usage), (5) user and permission management. NOT for: complex ETL workflows (use specialized tools), real-time streaming (use CDC tools), or when mysql CLI is not installed/accessible.\nhomepage: https://dev.mysql.com/doc/\nmetadata:\n  {\n    \"openclaw\":\n      {\n        \"emoji\": \"🐬\",\n        \"requires\":\n          {\n            \"anyBins\": [\"mysql\", \"mysqlcheck\", \"mysqldump\"],\n            \"optionalBins\": [\"python3\"],\n          },\n        \"install\":\n          [\n            {\n              \"id\": \"apt\",\n              \"kind\": \"apt\",\n              \"package\": \"mysql-client\",\n              \"bins\": [\"mysql\", \"mysqlcheck\", \"mysqldump\"],\n              \"label\": \"Install MySQL client (apt)\",\n            },\n            {\n              \"id\": \"brew\",\n              \"kind\": \"brew\",\n              \"formula\": \"mysql-client\",\n              \"bins\": [\"mysql\", \"mysqlcheck\", \"mysqldump\"],\n              \"label\": \"Install MySQL client (brew)\",\n            },\n          ],\n      },\n  }\n---\n\n# MySQL Administration\n\nManage MySQL databases using the mysql CLI or Python mysql-connector.\n\n## When to Use\n\n✅ **USE this skill when:**\n\n- \"Query users who registered today\"\n- \"Add an index to the orders table\"\n- \"Backup the production database\"\n- \"Check slow query logs\"\n- \"Grant permissions to a new user\"\n- \"Analyze table size and storage usage\"\n\n## When NOT to Use\n\n❌ **DON'T use this skill when:**\n\n- Complex ETL workflows → use specialized ETL tools (Airflow, dbt)\n- Real-time data streaming → use CDC tools (Debezium, Maxwell)\n- Graph database queries → use Neo4j or graph-specific tools\n- Time-series analytics → use TimescaleDB or InfluxDB\n- Large-scale data processing → use Spark or distributed systems\n\n## Setup\n\n### Connection Configuration\n\nSet environment variables or use command-line flags:\n\n```bash\n# Environment variables (recommended)\nexport MYSQL_HOST=\"localhost\"\nexport MYSQL_PORT=\"3306\"\nexport MYSQL_USER=\"root\"\nexport MYSQL_PASSWORD=\"password\"\nexport MYSQL_DATABASE=\"mydb\"\n\n# Or use flags directly\nmysql -h localhost -P 3306 -u root -p mydb\n```\n\n### Test Connection\n\n```bash\nmysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e \"SELECT VERSION();\"\n```\n\n## Quick Start\n\n### Execute Query\n\n```bash\n# Using environment variables\nmysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e \"SELECT * FROM users LIMIT 10\"\n\n# Direct connection\nmysql -h localhost -u root -p -e \"SHOW DATABASES;\"\n```\n\n### Schema Information\n\n```bash\n# List all databases\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e \"SHOW DATABASES;\"\n\n# Show tables\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e \"SHOW TABLES;\"\n\n# Describe table structure\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e \"DESCRIBE users;\"\n```\n\n## Common Operations\n\n### Query Execution\n\n```bash\n# Simple query\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e \"SELECT id, name FROM users WHERE created_at > '2026-01-01';\"\n\n# Query with formatting (table output)\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -t -e \"SELECT * FROM orders LIMIT 5;\"\n\n# Query with JSON output\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE --json -e \"SELECT * FROM products;\"\n```\n\n### Schema Management\n\n```bash\n# Create table\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE << 'SQL'\nCREATE TABLE IF NOT EXISTS events (\n  id INT AUTO_INCREMENT PRIMARY KEY,\n  event_type VARCHAR(50),\n  payload JSON,\n  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\nSQL\n\n# Add index\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e \"CREATE INDEX idx_user_email ON users(email);\"\n\n# Alter table\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e \"ALTER TABLE orders ADD COLUMN status VARCHAR(20);\"\n```\n\n### Database Backup\n\n```bash\n# Using bundled script\n{baseDir}/scripts/mysql_backup.sh --host $MYSQL_HOST --user $MYSQL_USER --password $MYSQL_PASSWORD --database $MYSQL_DATABASE --output /tmp/backup.sql\n\n# Or using mysqldump directly\nmysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE > /tmp/backup.sql\n\n# Backup with specific tables\nmysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE users orders > /tmp/partial_backup.sql\n```\n\n### Database Restore\n\n```bash\n# Using bundled script\n{baseDir}/scripts/mysql_restore.sh --host $MYSQL_HOST --user $MYSQL_USER --password $MYSQL_PASSWORD --database $MYSQL_DATABASE --input /tmp/backup.sql\n\n# Or using mysql directly\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE < /tmp/backup.sql\n```\n\n### Performance Analysis\n\n```bash\n# Check table sizes\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD information_schema -e \"\nSELECT\n  table_schema,\n  table_name,\n  ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb\nFROM tables\nWHERE table_schema = '$MYSQL_DATABASE'\nORDER BY size_mb DESC;\n\"\n\n# Show indexes\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e \"SHOW INDEX FROM users;\"\n\n# Analyze table\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e \"ANALYZE TABLE users;\"\n\n# Check slow queries (requires slow query log enabled)\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e \"SHOW VARIABLES LIKE 'slow_query_log';\"\n```\n\n### User Management\n\n```bash\n# Create user\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD << 'SQL'\nCREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';\nSQL\n\n# Grant permissions\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD << 'SQL'\nGRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';\nFLUSH PRIVILEGES;\nSQL\n\n# Show users\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e \"SELECT user, host FROM mysql.user;\"\n```\n\n## Script Usage\n\n### mysql_query.sh\n\nExecute queries with formatted output:\n\n```bash\n{baseDir}/scripts/mysql_query.sh \\\n  --host $MYSQL_HOST \\\n  --user $MYSQL_USER \\\n  --password $MYSQL_PASSWORD \\\n  --database $MYSQL_DATABASE \\\n  --query \"SELECT COUNT(*) FROM users\"\n```\n\n### mysql_backup.sh\n\nBackup database with timestamp:\n\n```bash\n{baseDir}/scripts/mysql_backup.sh \\\n  --host $MYSQL_HOST \\\n  --user $MYSQL_USER \\\n  --password $MYSQL_PASSWORD \\\n  --database $MYSQL_DATABASE \\\n  --output /backups/$(date +%Y%m%d)_backup.sql\n```\n\n### mysql_restore.sh\n\nRestore from backup:\n\n```bash\n{baseDir}/scripts/mysql_restore.sh \\\n  --host $MYSQL_HOST \\\n  --user $MYSQL_USER \\\n  --password $MYSQL_PASSWORD \\\n  --database $MYSQL_DATABASE \\\n  --input /backups/20260115_backup.sql\n```\n\n## Advanced Queries\n\n### Aggregate Functions\n\n```bash\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE << 'SQL'\nSELECT\n  DATE(created_at) as date,\n  COUNT(*) as daily_users,\n  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_users\nFROM users\nWHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)\nGROUP BY DATE(created_at)\nORDER BY date DESC;\nSQL\n```\n\n### Join Queries\n\n```bash\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE << 'SQL'\nSELECT\n  u.name,\n  u.email,\n  COUNT(o.id) as order_count,\n  SUM(o.total) as total_spent\nFROM users u\nLEFT JOIN orders o ON u.id = o.user_id\nGROUP BY u.id, u.name, u.email\nHAVING total_spent > 1000\nORDER BY total_spent DESC;\nSQL\n```\n\n## Security Notes\n\n- **Never hardcode passwords** in scripts or queries. Use environment variables.\n- **Use least-privilege principle**: Grant only necessary permissions to application users.\n- **Encrypt connections**: Use SSL/TLS for remote connections (`--ssl-mode=REQUIRED`).\n- **Validate inputs**: When constructing queries dynamically, always sanitize inputs to prevent SQL injection.\n- **Backup before modifications**: Always create a backup before schema changes or bulk updates.\n\n## Troubleshooting\n\n### Connection Issues\n\n```bash\n# Test connectivity\ntelnet $MYSQL_HOST $MYSQL_PORT\n\n# Check MySQL service\nsystemctl status mysql  # or: service mysql status\n\n# Check firewall\nsudo ufw status\n```\n\n### Permission Denied\n\n```bash\n# Check current user\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e \"SELECT CURRENT_USER();\"\n\n# Check grants\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e \"SHOW GRANTS FOR CURRENT_USER();\"\n```\n\n### Slow Queries\n\n```bash\n# Enable slow query log\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD << 'SQL'\nSET GLOBAL slow_query_log = 'ON';\nSET GLOBAL long_query_time = 2;\nSQL\n\n# View slow queries\nmysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e \"SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;\"\n```\n\n## References\n\nFor detailed schema analysis and performance tuning, see:\n\n- [Schema Analysis](references/schema-analysis.md) - Table structure analysis and optimization\n- [Performance Tuning](references/performance-tuning.md) - Query optimization and indexing strategies\n\n## Notes\n\n- Always use transactions for multi-step operations: `START TRANSACTION; ... COMMIT;` or `ROLLBACK;`\n- Use `EXPLAIN` to analyze query execution plans before running complex queries\n- Monitor database size and growth regularly\n- Keep backups in multiple locations for disaster recovery\n- Test backup/restore procedures regularly to ensure they work when needed\n","tags":{"administration":"0.1.0","backup":"0.1.0","database":"0.1.0","latest":"0.1.0","mysql":"0.1.0","performance":"0.1.0","restore":"0.1.0"},"stats":{"comments":0,"downloads":840,"installsAllTime":0,"installsCurrent":0,"stars":1,"versions":1},"createdAt":1773642438134,"updatedAt":1779078318244},"latestVersion":{"version":"0.1.0","createdAt":1773642438134,"changelog":"Initial release: MySQL database management with query execution, backup/restore, performance analysis, and user management capabilities. Includes 3 utility scripts and comprehensive documentation.","license":"MIT-0"},"metadata":null,"owner":{"handle":"jasydongo","userId":"s170kasrbbtnxqrbp1031pq78s8854j6","displayName":"jasydongo","image":"https://avatars.githubusercontent.com/u/182348694?v=4"},"moderation":{"isSuspicious":false,"isMalwareBlocked":false,"verdict":"clean","reasonCodes":["review.llm_review"],"summary":"Review: review.llm_review","engineVersion":"v2.4.24","updatedAt":1780089912824}}