MYSQL for Oracle DBA

Excellent! Since you’re already a seasoned Oracle DBA on Linux, transitioning to MySQL will be straightforward — but you’ll need to shift focus to MySQL’s different architecture, tooling, and storage engine model.

---

🧠 Training Plan: MySQL DBA for Oracle DBAs (Linux-Based)

We’ll cover the following areas step-by-step. Each topic will include theory, practical commands, and best practices.

---

✅ Phase 1: MySQL Basics & Architecture

1. Installation on Linux (Ubuntu/CentOS)
   • apt install mysql-server or yum install mysql-server
   • Key config: /etc/my.cnf or /etc/mysql/my.cnf
2. Server startup, shutdown
   • systemctl start|stop|status mysqld
   • mysqld_safe and mysqladmin
3. Directory Structure
   • Data directory (@@datadir)
   • Log files: error.log, slow.log, binlog
4. Architecture Overview
   • Storage Engines (InnoDB, MyISAM, MEMORY)
   • Single-process, multi-threaded
   • Buffer pool, redo logs, doublewrite buffer

---

✅ Phase 2: Core DBA Tasks

1. User Management
   • CREATE USER, GRANT, REVOKE
   • mysql.user table (like dba_users)
2. Backup and Restore
   • Logical: mysqldump, mysqlpump
   • Physical: xtrabackup (hot backup for InnoDB)
3. Security Hardening
   • mysql_secure_installation
   • Removing test DB, anonymous users
   • Using caching_sha2_password
4. Configuration Parameters
   • SHOW VARIABLES
   • Modify in my.cnf: innodb_buffer_pool_size, max_connections, etc.

---

✅ Phase 3: Monitoring and Troubleshooting

1. Monitoring
   • SHOW FULL PROCESSLIST
   • performance_schema & information_schema
   • Tools: mysqladmin, Percona Toolkit, pmacct
2. Locking & Concurrency
   • MVCC in InnoDB
   • Check locks: performance_schema.metadata_locks
3. Slow Query Logging
   • Enable slow query log
   • Use mysqldumpslow, pt-query-digest

---

✅ Phase 4: Performance Tuning

1. Query Optimization
   • Use EXPLAIN, ANALYZE FORMAT=JSON
   • Index design: SHOW INDEX, information_schema.statistics
2. Buffer Pool & Caching
   • Key parameters: innodb_buffer_pool_size, query_cache_size (deprecated)
3. MySQL Tuner
   • Use mysqltuner.pl to suggest tuning
4. Table Statistics
   • ANALYZE TABLE, OPTIMIZE TABLE

---

✅ Phase 5: High Availability & Replication

1. Replication
   • Async replication: master → replica
   • Semi-sync & GTID-based replication
   • SHOW SLAVE STATUS
2. InnoDB Cluster
   • MySQL Shell, Group Replication
   • Requires MySQL 8+
3. Failover
   • Tools: Orchestrator, MHA
   • Monitor replication lag

---

✅ Phase 6: Advanced Topics

1. Stored Procedures / Triggers
   • Limited compared to PL/SQL
   • Use DELIMITER, BEGIN...END
2. Events (Jobs)
   • MySQL Event Scheduler (information_schema.events)
3. Partitioning
   • RANGE, LIST, HASH (less flexible than Oracle)
4. Tablespaces
   • InnoDB general vs file-per-table
   • CREATE TABLESPACE in 8.0+

---

✅ Phase 7: Migration & Tools

1. Migration Tools
   • Oracle → MySQL: MySQL Workbench, ora2pg, AWS SCT
2. Data Types Mapping
   • NUMBER → DECIMAL, VARCHAR2 → VARCHAR, DATE/TIMESTAMP match closely
3. SQL Differences
   • No packages, limited error handling
   • Use DELIMITER, SIGNAL for error

---