MySQL

Quick Usage

Since MySQL is part of the PubMatic SRE role, here’s a quick MySQL crash course focusing on what SREs need to know for debugging, performance, and troubleshooting.

---

🔹 1. MySQL Basics (CRUD Operations)

Create a Database and Table

CREATE DATABASE mydb;
USE mydb;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert Data

INSERT INTO users (name, email) VALUES ('Pavan', '[email protected]');

Retrieve Data

SELECT * FROM users;
SELECT name FROM users WHERE id = 1;

Update Data

UPDATE users SET email = '[email protected]' WHERE id = 1;

Delete Data

DELETE FROM users WHERE id = 1;

---

🔹 2. Debugging MySQL Issues

Check Running Queries (Find Slow Queries)

SHOW PROCESSLIST;

• If you see long-running queries, find the slow ones:

SHOW FULL PROCESSLIST;

Check Database Size

SELECT table_schema AS DB_NAME, 
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS DB_Size_MB 
FROM information_schema.tables 
GROUP BY table_schema;

Find Large Tables

SELECT table_name, 
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS Table_Size_MB
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY Table_Size_MB DESC;

---

🔹 3. Indexing for Performance

Check if Index Exists

SHOW INDEX FROM users;

Create an Index to Speed Up Queries

CREATE INDEX idx_email ON users(email);

Use EXPLAIN to Analyze Query Performance

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

---

🔹 4. MySQL Logs & Monitoring (For SREs)

Enable Slow Query Logging

1️⃣ Edit MySQL Config File (/etc/mysql/my.cnf or /etc/my.cnf):

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 2   # Log queries taking longer than 2 sec

2️⃣ Restart MySQL

systemctl restart mysql

3️⃣ Check Slow Queries

cat /var/log/mysql-slow.log

---

🔹 5. Backup & Restore (Disaster Recovery)

Backup a Database

mysqldump -u root -p mydb > mydb_backup.sql

Restore a Database

mysql -u root -p mydb < mydb_backup.sql

---

🔹 6. MySQL High Availability (HA) Concepts

As an SRE, you should be familiar with: ✅ Replication (Primary-Replica setup) ✅ Clustering (Galera Cluster for HA) ✅ Load Balancing (Using ProxySQL or HAProxy) ✅ Failover Strategies (Using Orchestrator)

---

🔥 What to Focus on for the Interview

✅ Debugging slow queries ✅ Checking MySQL performance (Processlist, Indexing, Logs) ✅ Basic CRUD operations & Backups ✅ HA & Replication concepts

---

Would you like practice questions or hands-on exercises to test your MySQL skills? 🚀

Sure, Pavan. Here's a quick SQL basics refresher tailored for DevOps/SRE engineers like you, covering concepts and commonly used queries you may be asked in a senior DevOps role, especially during a Terraform + infrastructure interview (e.g., for database provisioning, inspection, etc.).

---

✅ SQL Basics for DevOps/SRE (MySQL/PostgreSQL Focus)

---

🧠 Key SQL Concepts

Concept	Description
Database	Collection of organized data
Table	Collection of rows and columns to store structured data
Row	A single record in a table
Column	A field/attribute in the table
Primary Key	Uniquely identifies each row
Foreign Key	Links records between two tables
Index	Improves query performance
View	Virtual table based on result of a query
Stored Procedure	Predefined SQL code that can be reused

---

🔎 Basic SQL Queries

1. Create Database/Table

CREATE DATABASE devops_db;

CREATE TABLE servers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  ip_address VARCHAR(15),
  environment VARCHAR(10)
);

2. Insert Data

INSERT INTO servers (id, name, ip_address, environment)
VALUES (1, 'nginx', '192.168.1.10', 'prod');

3. Select (Read)

SELECT * FROM servers;
SELECT name, ip_address FROM servers WHERE environment = 'prod';

4. Update

UPDATE servers SET environment = 'stage' WHERE name = 'nginx';

5. Delete

DELETE FROM servers WHERE id = 1;

---

⚙️ Useful DevOps Queries

🔄 Check for Configuration Drift (e.g., mismatched states)

SELECT * FROM servers WHERE environment != 'prod';

🔍 Find Duplicate Records

SELECT ip_address, COUNT(*) FROM servers
GROUP BY ip_address HAVING COUNT(*) > 1;

📊 Count by Environment

SELECT environment, COUNT(*) FROM servers GROUP BY environment;

---

🧪 Join Example

-- Two tables: servers and monitoring
SELECT s.name, m.status 
FROM servers s
JOIN monitoring m ON s.id = m.server_id;

---

🔐 User Management (PostgreSQL Example)

CREATE USER dev_user WITH PASSWORD 'StrongP@ss';
GRANT ALL PRIVILEGES ON DATABASE devops_db TO dev_user;

---

📁 Backup Command (PostgreSQL)

pg_dump -U user -d devops_db > devops_db_backup.sql

---

📌 Real-Life Scenarios You May Be Asked

Scenario	Query or Approach
Fetch latest deployed container info	SELECT * FROM deployments ORDER BY timestamp DESC LIMIT 1;
Identify failed jobs in CI/CD	SELECT * FROM jobs WHERE status = 'failed';
Compare expected state with actual state (drift detection)	Use JOINs or mismatched WHERE conditions
Audit logs / changes in infra	Query audit table with filters on action type/date

---

If you want, I can create a cheat sheet PDF for your interview prep or a SQL practice set specifically for DevOps engineers. Just let me know.