MySQL Database Administration Workshop Plan
1. Installation & Setup
- Install MySQL 8.0/8.4 on RHEL Linux (step-by-step)
- Repository & GPG key setup
- Initial service startup & temporary root password retrieval
- Running mysql_secure_installation
2. Configuration (/etc/my.cnf)
- Basic server configuration ([mysqld], [client], [mysqldump])
- Networking & security (bind-address, firewalls)
- Logging (error log, slow query log, binary logs)
- InnoDB tuning (innodb_buffer_pool_size, innodb_log_file_size)
- Charset & collation (utf8mb4)
- Replication config (server-id, log_bin, relay_log)
3. User Management & Security
- Create users with least privileges
- Grant, revoke, and check permissions
- Enforce password policies
- Remote access user setup
- Resetting forgotten root password
4. Backup & Recovery
- Logical backups with mysqldump and mysqlpump
- Step-by-Step Procedure for MySQL Enterprise Backup
- Binary log backups for PITR (Point-in-Time Recovery)
- Restore procedures (full & incremental)
5. Replication & High Availability
- Master–Slave replication setup (MySQL 8.4 syntax)
- Common replication errors & troubleshooting (Replica_IO_Running: Connecting)
- Monitoring replication (SHOW REPLICA STATUS\G)
- Basics of Group Replication
6. Performance & Monitoring
- Important status commands (SHOW VARIABLES, SHOW PROCESSLIST)
- Slow query log analysis
- Using Explain & Explain Analyze
- Performance Schema & Sys schema usage
- Tuning checklist (buffer pool, connections, temp tables)
7. Troubleshooting
- Installation issues (repo, GPG, MariaDB conflicts)
- Configuration issues (unknown variable, memory misconfig)
- Service won’t start (permissions, SELinux, logs)
- Common runtime issues (connections, locks, slow queries)
8. MySQL Upgrade
- Pre-Requisites
- Full Backup the existing Database
- Upgrading MySQL (8.0 → 8.4)
- Restore the Database.
9. Quick Reference Commands
- User management one-liners
- Replication reset/repair commands
- Backup & restore quick commands
- Log and error inspection
10. Maintenance & Housekeeping
- Cleaning up old binary logs (PURGE BINARY LOGS)
- Monitoring disk usage (information_schema.tables)
- Automating backups with cronjobs