Database Administration

DBA Fundamentals

• User Management: CREATE USER 'app'@'localhost' IDENTIFIED BY 'strong_password'; — create database users with specific host restrictions. Never use root for applications
• Permissions: GRANT SELECT, INSERT ON database.* TO 'app'@'localhost'; — principle of least privilege. Grant only needed permissions. REVOKE to remove. SHOW GRANTS to audit
• Database Monitoring: SHOW PROCESSLIST — view active queries. SHOW STATUS — connection count, query cache hits, slow queries. pg_stat_activity (PostgreSQL) for session monitoring
• Slow Query Log: SET GLOBAL slow_query_log = 'ON'; long_query_time = 2; — capture queries taking > 2 seconds. Analyze with mysqldumpslow or pt-query-digest for patterns
• Connection Pooling: Database connections are expensive to create — connection pools maintain reusable connections. PgBouncer (PostgreSQL), ProxySQL (MySQL). Configure pool size based on workload
• Capacity Planning: Monitor disk usage growth, query volume trends, connection count over time — plan hardware upgrades before hitting limits. Alert at 75% capacity