MySQL Server Administration Training

RexGalaxy Academy's MySQL Server Administration Training is a practical, job-focused database administration program designed for students who want to work confidently with MySQL Server environments in real business and production-support scenarios.

This course covers the complete MySQL Server workflow from foundational architecture to daily DBA operations. Students learn how MySQL works as a database engine, how client tools connect to the server, how configuration files affect server behavior, how users and privileges are managed, how InnoDB stores data, and how backup, restore, monitoring, replication, performance tuning, troubleshooting, automation, migration, and cloud database operations are handled in a structured manner.

The training is built around guided labs and portfolio-ready assignments. Instead of only learning SQL commands, students practice server setup, configuration review, user creation, privilege assignment, backup scripts, restore validation, log analysis, slow query investigation, EXPLAIN plan review, storage reporting, replication design, incident documentation, migration checklists, and a final MySQL Server administration capstone.

Students should be able to:

• Administer MySQL Server setup, configuration, users, privileges, service status, logs, backups, restore testing, and monitoring reports.

• Use MySQL Workbench, command line tools, SQL scripts, server variables, process lists, EXPLAIN plans, slow query logs, backup utilities, and documentation templates.

• Explain MySQL Server architecture, InnoDB storage, security hardening, disaster recovery, replication awareness, performance symptoms, troubleshooting flow, and support responsibilities.

• Build practical portfolio evidence including setup notes, security scripts, configuration checklist, storage report, backup and restore proof, replication design, tuning report, incident report, migration checklist, and DBA capstone documentation.

• Prepare for roles such as MySQL DBA Trainee, Database Support Engineer, Backend Database Support Executive, Production Support Associate, and Cloud Database Support Assistant.

Build a strong foundation in MySQL Server architecture, client tools, admin workflow, and safe lab practice.

• Understand the purpose of MySQL Server as a database engine for business applications, reporting workloads, backend storage, and operational responsibility.

• Learn the client-server model, mysqld process, connections, sessions, memory areas, logs, storage engines, and data files.

• Work with MySQL Workbench, command line client, shell awareness, admin utilities, connectors, and documentation habits.

• Plan a safe local lab with server setup, sample database, admin user, limited user, test schema, folders, scripts, and screenshots.

• Practice connection workflow including host, port, socket awareness, username, password, SSL awareness, default schema, and connection testing.

• Review server lifecycle tasks such as start, stop, restart, service status, boot behavior, and the impact of restarting database services.

• Explore system variables, status variables, configuration file locations, dynamic settings, static settings, and safe change methods.

• Inspect metadata using information_schema, performance_schema, system tables, database lists, table lists, and object review.

• Build documentation discipline with server inventory, version notes, port details, users, databases, backup paths, maintenance notes, and change history.

• Lab outcome: connect to MySQL Server, inspect version, create schema, verify service status, review variables, and prepare an admin workbook.

Install and configure MySQL Server with service management, file paths, port settings, and environment checks.

• Understand installation options including Linux package managers, Windows installer awareness, version selection, prerequisites, and server edition considerations.

• Learn Linux basics for DBA work such as terminal usage, sudo awareness, directories, permissions, service commands, log paths, and safe command execution.

• Manage services with systemctl awareness, start, stop, restart, enable, status checks, and controlled restart planning.

• Identify important paths such as data directory, configuration file, socket file, log directory, backup folder, and script storage structure.

• Review configuration file basics including my.cnf or mysqld.cnf sections, comments, port settings, bind address, character set, collation, and memory settings.

• Understand network settings including port 3306, localhost access, remote access, firewall awareness, bind address, and connection validation.

• Follow secure installation awareness including root password, anonymous user removal, test database review, remote root restrictions, and safe defaults.

• Configure character set and collation with Unicode support, utf8mb4 awareness, table defaults, and prevention of text storage problems.

• Perform environment checks for disk space, RAM, CPU, OS version, MySQL version, time zone, and basic capacity notes.

• Lab outcome: install or verify MySQL Server, manage service status, locate configuration files, test remote access awareness, and record setup details.

Secure MySQL Server using proper users, privileges, roles, authentication, access control, and hardening habits.

• Learn security principles such as least privilege, separation of duties, strong passwords, controlled access, auditability, and production caution.

• Practice user management with CREATE USER, ALTER USER, DROP USER awareness, host patterns, account locking, and user inventory checks.

• Understand the privilege model including global privileges, database privileges, table privileges, column privileges, routine privileges, GRANT, REVOKE, SHOW GRANTS, and privilege review.

• Use role awareness for role creation, assigning roles, default roles, simplified access management, and clean permission grouping.

• Review authentication basics including password authentication, plugin awareness, password expiry, account policy, and secure login practices.

• Control remote access using allowed hosts, firewall awareness, bind address, VPN awareness, and avoidance of unsafe public exposure.

• Understand SSL/TLS connection ideas, secure transport, certificate awareness, and protection of credentials in transit.

• Build a hardening checklist for removing unused accounts, restricting root, reviewing grants, protecting backups, hiding secrets, and documenting access.

• Design application users such as read-only user, reporting user, app user, admin user, backup user, and access boundary planning.

• Lab outcome: create limited users, grant and revoke privileges, review SHOW GRANTS, test denied actions, and build an access review sheet.

Understand how MySQL stores data using storage engines, InnoDB components, tablespaces, logs, and capacity planning.

• Learn the purpose of storage engines, pluggable engine behavior, InnoDB as the default engine, MyISAM awareness, MEMORY engine awareness, and engine selection basics.

• Study InnoDB architecture including buffer pool, redo log, undo data, transaction support, crash recovery, row locks, and foreign key support.

• Identify data directory structure, database folders, table files awareness, dictionary metadata, file ownership, and why manual file edits are risky.

• Understand tablespace concepts including system tablespace awareness, file-per-table awareness, general tablespaces, and storage organization.

• Review redo and undo awareness for durable changes, rollback support, recovery behavior, and transaction safety explanation.

• Learn temporary tablespace awareness for sorting, grouping, large queries, temporary workloads, and disk usage risk.

• Practice capacity planning using disk space, growth trends, table size, index size, binary logs, backups, and free space rules.

• Review table maintenance concepts including ANALYZE TABLE, OPTIMIZE TABLE awareness, CHECK TABLE, fragmentation awareness, and when maintenance is useful.

• Follow storage safety practices including permissions, backups before file changes, avoiding direct deletion, and controlled data directory operations.

• Lab outcome: inspect storage engines, review table status, check table sizes, locate data directory, and prepare a storage report.

Build reliable backup and restore skills with logical backups, restore drills, validation, and recovery planning.

• Plan backup strategy with full backup, incremental awareness, logical versus physical backup, retention schedule, storage location, and business RPO/RTO.

• Practice logical backup using mysqldump awareness, schema backup, data backup, routines, triggers, events, compression, and repeatable scripts.

• Understand restore workflow including creating target database, restoring SQL files, verifying tables, validating row counts, testing queries, and documenting evidence.

• Learn binary log awareness for point-in-time recovery concepts, binlog role, retention, disk impact, and recovery sequence thinking.

• Review physical backup awareness including file-based backup caution, hot backup tools awareness, downtime planning, and consistency requirements.

• Protect backup security by avoiding plain secrets, restricting backup folders, encrypting sensitive backups, and reviewing access.

• Validate backups through checksum awareness, restore testing, sample query checks, object counts, file size review, and failure reporting.

• Prepare for disaster scenarios such as accidental delete, disk failure, server crash, bad deployment, corruption awareness, and recovery communication.

• Create documentation including backup command, schedule, location, retention, restore steps, responsible person, and emergency checklist.

• Lab outcome: export database, restore to test schema, validate data, document backup plan, and simulate recovery from accidental change.

Learn replication concepts, read scaling, failover thinking, monitoring, and high availability design awareness.

• Understand replication purpose for read scaling, reporting replicas, backup replicas, disaster recovery support, and data copy across servers.

• Learn replication roles including source server, replica server, binary logs, relay logs, replication user, server ID, and position awareness.

• Study replication flow from transaction written on source, binary log recording, replica reading events, relay log storage, and SQL apply process.

• Review configuration awareness including server_id, log_bin, replica connection, credentials, network access, and version compatibility checks.

• Monitor replication using replication status, lag awareness, IO thread, SQL thread, last error, and health review.

• Troubleshoot failure cases such as network issues, permission problems, duplicate keys, missing logs, disk full, schema mismatch, and delayed replicas.

• Learn failover thinking including planned switchover, unplanned failure, data loss risk, application connection update, and validation steps.

• Understand high availability concepts such as clustering, managed database HA, load balancer awareness, and business continuity goals.

• Use read/write split awareness for application routing, reporting queries, stale data risk, and safe use of replicas.

• Lab outcome: draw replication topology, review sample replication status, troubleshoot common errors, and write HA design notes.

Operate MySQL Server using daily health checks, logs, sessions, variables, status metrics, and support checklists.

• Perform daily DBA checks for service status, uptime, connections, errors, disk usage, backup status, replication status, and slow query review.

• Review server status variables including connections, threads, queries, uptime, aborted connects, table locks awareness, and operational clues.

• Understand system variables for configuration visibility, memory settings, timeout values, log settings, storage settings, and change tracking.

• Use process list investigation for active sessions, long queries, sleeping connections, locked queries, user activity, and safe session review.

• Read error logs for startup messages, shutdown messages, crashes, warnings, permission errors, and troubleshooting evidence.

• Use slow query log awareness including enablement, long query threshold, logged SQL, analysis workflow, and performance investigation.

• Understand general log awareness for debugging use, high overhead caution, temporary usage, and production safety.

• Explore Performance Schema awareness including waits, statements, events, summaries, and why monitoring data matters.

• Prepare operational reporting with daily report format, issue summary, evidence screenshots, actions taken, escalation notes, and closure comments.

• Lab outcome: prepare health checklist, inspect process list, review logs, identify slow query evidence, and write a daily DBA report.

Tune MySQL performance using indexes, EXPLAIN plans, slow query analysis, and practical InnoDB settings awareness.

• Develop a performance mindset: baseline first, measure impact, avoid guessing, test safely, document changes, and validate results.

• Understand index basics including primary index, secondary index, unique index, composite index, covering index awareness, and index maintenance cost.

• Select indexes based on WHERE columns, JOIN columns, ORDER BY columns, selectivity, cardinality, and avoiding duplicate indexes.

• Read EXPLAIN output including access type, possible keys, chosen key, rows estimate, filtered percentage, extra notes, and tuning clues.

• Follow slow query workflow: capture query, understand requirement, check plan, test index, compare timing, and document before-after result.

• Review InnoDB tuning awareness including buffer pool, redo log, flush behavior, connection limits, temp tables, and safe configuration review.

• Tune schema design through data types, normalization balance, constraints, table size review, archive strategy, and query-friendly design.

• Review server resources including CPU, RAM, disk I/O, network, concurrency, storage speed, and database workload patterns.

• Identify bad query patterns such as SELECT star misuse, missing WHERE, leading wildcard, functions on indexed columns, wrong joins, and large sorts.

• Lab outcome: optimize slow reports, add indexes, compare EXPLAIN output, review InnoDB variables, and write tuning recommendation notes.

Develop a structured troubleshooting approach for connection, permission, storage, lock, backup, and performance incidents.

• Learn the troubleshooting method: define problem, collect evidence, isolate scope, test safely, apply fix, verify result, and document closure.

• Resolve connection issues such as wrong host, blocked port, service down, firewall, bind address, socket error, DNS issue, and authentication failure.

• Handle permission issues including access denied, missing privilege, wrong host pattern, expired password, locked account, and grant verification.

• Investigate storage incidents including disk full, table size growth, log growth, temp file growth, backup folder pressure, and cleanup planning.

• Understand lock and blocking issues including long transactions, metadata lock awareness, deadlock awareness, process list clues, and safe escalation.

• Troubleshoot backup failures such as wrong path, permission issue, disk full, syntax error, inconsistent dump, failed restore, and validation mistakes.

• Review replication incidents including lag, stopped thread, duplicate key, network drop, missing binary log, credentials issue, and resync thinking.

• Diagnose performance incidents including slow queries, high CPU, many connections, lock waits, missing indexes, large reports, and resource pressure.

• Communicate incidents through impact summary, timeline, evidence, action plan, risk, rollback, stakeholder update, and final incident note.

• Lab outcome: solve staged connection, grant, lock, disk, slow query, backup, restore, and replication-style troubleshooting cases.

Create repeatable maintenance workflows with scripts, scheduled checks, table care, patching, upgrades, and change control.

• Plan daily, weekly, and monthly maintenance checks including backup review, security review, storage review, performance review, and reporting.

• Use automation basics such as reusable SQL scripts, shell script awareness, cron awareness, scheduled backups, health checks, and log review.

• Understand event scheduler awareness for database events, scheduled routines, cleanup jobs, hidden automation risks, and monitoring.

• Perform table maintenance awareness including ANALYZE TABLE, OPTIMIZE awareness, statistics, fragmentation awareness, and careful maintenance windows.

• Manage log rotation awareness including error log, slow log, binary log retention, disk usage, purge planning, and compliance needs.

• Plan patches using release notes, security fixes, maintenance window, backup first, test environment, rollback plan, and validation.

• Prepare upgrade plans with compatibility review, application testing, version jump awareness, plugin checks, configuration review, downtime, and post-upgrade checks.

• Follow change management with request, approval, command plan, rollback steps, validation queries, communication, and closure notes.

• Use automation safety through dry runs, limited permissions, test server, logging, alerts, and avoiding destructive scheduled commands.

• Lab outcome: create backup script outline, health check script, log review checklist, patch checklist, and change implementation template.

Connect MySQL Server administration with cloud databases, migration, import-export workflows, DevOps habits, and managed services.

• Understand cloud MySQL concepts including self-managed VM, managed database service, shared responsibility, backups, monitoring, security, and cost awareness.

• Review managed service awareness including automated backup, high availability options, parameter groups, maintenance windows, and restricted admin access.

• Learn cloud connectivity including private endpoint awareness, public endpoint risk, security groups, firewall rules, SSL, and application connection strings.

• Plan migration using source inventory, database size, users, routines, triggers, events, downtime, compatibility review, and validation plan.

• Practice import-export workflow including mysqldump, CSV import, schema-only export, data-only export, character set checks, and row count validation.

• Validate migration using object counts, row counts, sample queries, application testing, performance review, and rollback decision points.

• Build DevOps awareness around Git for SQL scripts, migration files, environment variables, secret handling, CI/CD database caution, and review process.

• Understand container awareness including Docker MySQL basics, volumes, ports, environment variables, development database use, and persistence caution.

• Use observability concepts including metrics, alerts, logs, dashboards, backup notifications, replication lag, and response runbooks.

• Lab outcome: create migration checklist, import sample data, validate row counts, design cloud MySQL architecture, and document DevOps handoff.

Complete a MySQL Server capstone and prepare portfolio evidence, interview answers, and career direction.

• Plan capstone requirements including business requirement, server setup, users, databases, backup plan, monitoring checklist, security rules, and validation goals.

• Build practical tasks such as configure server notes, create schemas, create admin and app users, assign privileges, prepare sample data, and document setup.

• Complete backup tasks including logical backup, restore test, validation evidence, retention note, recovery steps, and emergency checklist.

• Perform performance tasks including slow query capture, EXPLAIN review, index recommendation, before-after note, and tuning summary.

• Complete operations tasks such as daily health check, process list review, log review, storage report, replication design note, and issue tracker.

• Solve troubleshooting cases including connection failure, access denied, disk pressure, slow query, failed restore, blocking query, and replication lag awareness.

• Build portfolio documentation with architecture diagram, command sheet, SQL scripts, grants summary, backup proof, screenshots, and final README.

• Prepare interview topics including architecture, installation, users, privileges, InnoDB, backup, restore, replication, logs, indexes, EXPLAIN, and incidents.

• Discuss career direction for MySQL DBA trainee, database support engineer, backend database support, production support, and cloud database support roles.

• Final outcome: deliver a portfolio-ready MySQL Server admin project and confidently explain DBA operations and support workflow.