9. 🛢️ MySQL Basics for DevOps Engineers (Production & Interview Guide)

🎯 Why DevOps Engineers Must Know MySQL

As a DevOps engineer, you may not design schemas, but you must:

• Monitor database health

• Troubleshoot performance issues

• Handle backups & recovery

• Support application teams

---

🔹 What is MySQL?

MySQL is an open-source relational database that stores data in tables (rows & columns).

Used in:

• Web applications

• Microservices

• Cloud-native apps

---

🔹 Basic MySQL Concepts

---

🔹 Connect to MySQL

mysql -u root -p

---

🔹 List Databases & Tables

SHOW DATABASES;
USE mydb;
SHOW TABLES;

---

🔹 Fetch All Records

SELECT * FROM employees;

---

🔹 WHERE Clause

SELECT * FROM employees WHERE dept = 'IT';

---

🔹 GROUP BY (Interview Favorite)

SELECT dept, COUNT(*) FROM employees GROUP BY dept;

Used for aggregation.

---

🔹 WHERE vs HAVING

SELECT dept, COUNT(*) 
FROM employees 
GROUP BY dept 
HAVING COUNT(*) > 5;

---

🔹 Indexes (Performance Basics)

Indexes improve read performance.

CREATE INDEX idx_email ON users(email);

⚠️ Too many indexes slow writes.

---

🔹 MySQL Users & Permissions

CREATE USER 'appuser'@'%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON mydb.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

---

🔹 Backup MySQL Database

mysqldump -u root -p mydb > mydb.sql

---

🔹 Restore Database

mysql -u root -p mydb < mydb.sql

---

🔹 Check Running Queries

SHOW PROCESSLIST;

Used when:

• DB is slow

• Queries are stuck

---

🔹 Monitor MySQL (DevOps View)

• CPU & memory usage

• Disk I/O

• Slow queries

• Connection count

---

🔹 MySQL in Docker (Common Setup)

docker run -d \
  -e MYSQL_ROOT_PASSWORD=root \
  -e MYSQL_DATABASE=mydb_password \
  -p 3306:3306 mysql

---

🔹 MySQL on Kubernetes (Basic Idea)

• StatefulSet

• Persistent Volumes

• Secrets for credentials

---

🔹 Common Production Issues

Database Slow

• Missing indexes

• Long-running queries

• High connections

Database Down

• Disk full

• Memory exhaustion

• Corrupt tables

---

🔹 Real Production Scenario

Issue: Application latency high
Cause: Full table scan
Fix: Added index on search column
Result: Query time reduced drastically

---

🎯 Interview Takeaways

• DevOps handles DB operations

• GROUP BY & HAVING are common

• Backup & restore are critical

• Indexes improve performance