MySQL DBA
End-to-End Project

The Goal

I wanted to build a MySQL project that covers the full scope of database administration — not just queries. Schema design, bulk data loading, performance baselining, index optimization, backup and recovery, and security aren't isolated skills. They form a complete discipline, and this project treats them that way.

The Dataset

I used eight publicly available datasets from the EPA, CDC, and USDA to explore the relationship between cancer rates across the United States and contributing environmental, lifestyle, food, and water quality factors. The database covers cancer incidence and mortality, air quality, drinking water violations, food environment, chronic disease indicators, agricultural operations, and CAFO facility data — 22.7 million rows across 14 tables in a star schema design.

Screenshot — Database Size Summary

cancer_environment_db — 14 tables, 22.7M rows, 4.57 GB total

Design Approach

I chose a star schema because the research question — correlating cancer rates with environmental and lifestyle factors — is fundamentally analytical. Dimension tables hold stable reference data (states, counties, years, water systems) while fact tables hold measurements and events. This separation keeps joins clean and query patterns predictable.

The most significant design decision was building dim_county from the USDA Census of Agriculture rather than a generic county list. This means only counties with agricultural activity are included — approximately 1,388 EPA AQI counties and 27,400 food environment records were excluded as a documented known limitation. The tradeoff was worth it: it keeps the county dimension grounded in the agricultural data rather than forcing artificial joins.

Screenshot — Table Inventory

14 tables — 4 dimension, 10 fact — across the full dataset

Screenshot — Schema Overview in DataGrip

cancer_environment_db in DataGrip — full table inventory visible in the database explorer

I ran six analytical queries before and after adding indexes and rewriting query logic. The results below are measured execution times from DataGrip — real numbers from real runs, not estimates.

Screenshot — Q4 Baseline EXPLAIN

Q4 baseline EXPLAIN — full nested-loop scan through 15.3M violation rows, is_health_based_ind filter applied after the join

Screenshot — Q4 Optimized Result

Q4 after optimization — 9 min 42 sec down to 2,619 ms using CTE pre-filter and composite index

Screenshot — Q5 Optimized Result

Q5 after optimization — 4,658 ms down to 165 ms using subquery pre-aggregation and corrected composite index

Screenshot — Before / After Comparison

Full before/after comparison across all six queries after Phase 4 optimization

Backup Strategy

I implemented a logical backup strategy using mysqldump with --single-transaction for consistent InnoDB snapshots without table locks, and --source-data=2 to record the binary log position in the dump file for point-in-time recovery capability. Binary logging was confirmed active on the instance before backup execution.

Screenshot — Recovery Verification

Post-recovery verification — all 22,774,105 rows confirmed across all 14 tables after full restore from backup

Role-Based Access Control

I designed a three-tier access model using MySQL 8.0 roles. db_readonly grants SELECT only — for Tableau dashboards and reporting. db_analyst adds CREATE TEMPORARY TABLES for intermediate query results. db_etl grants SELECT, INSERT, UPDATE, and DELETE for data loading pipelines. No role holds CREATE, DROP, ALTER, or any global privilege.

Screenshot — Role Assignments

Role assignments confirmed via mysql.role_edges — one role per user, least-privilege throughout

Screenshot — User Inventory

Three application users — all active, localhost only, no global privileges