MySQL is the most widely deployed open source relational database management system in the world. Used by platforms such as Facebook, Uber, Booking.com or Airbnb, it powers high-traffic web applications as well as embedded systems. This section takes you from installation to replication, through security and monitoring, everything a system administrator or DevOps engineer must master to run MySQL under real conditions.
What this section covers
This course covers 8 progressive guides, from discovery to replication:
| Module | Topic | Level |
|---|---|---|
| 1 | Architecture, InnoDB and components | Beginner |
| 2 | Installation on Linux | Beginner |
| 3 | Getting started with the mysql client | Beginner |
| 4 | Configuration: InnoDB, memory, connections | Intermediate |
| 5 | Security: users, TLS, roles | Intermediate |
| 6 | Backup and restore (PITR) | Intermediate |
| 7 | Monitoring and maintenance | Intermediate |
| 8 | GTID replication and InnoDB Cluster | Advanced |
Why MySQL
MySQL remains the dominant choice for web and cloud applications for several concrete reasons:
- Read performance: the architecture of InnoDB (the default storage engine since MySQL 5.5) is optimized for read-heavy workloads, the most common scenario for web applications.
- Massive ecosystem: every web framework (Django, Rails, Laravel, Spring) offers native MySQL support. Administration, monitoring and backup tools are numerous and mature.
- MySQL 8.4 LTS: the LTS (Long Term Support) release policy guarantees security updates for 8 years, which simplifies upgrade planning in the enterprise.
- Flexible replication: asynchronous replication based on GTID (Global Transaction Identifiers), Group Replication for automatic high availability, and InnoDB Cluster as a fully integrated solution with MySQL Shell and MySQL Router.
- Performance Schema: a built-in instrumentation system that exposes detailed metrics on locks, I/O, threads and queries, without any external tool.
- Cloud-ready: available as a managed service at all cloud providers (Amazon RDS, Google Cloud SQL, Azure Database, OVHcloud) while remaining installable on your own servers.
Who this course is for
These guides are designed for:
- System administrators who manage Linux servers and must install, maintain and troubleshoot MySQL day to day.
- DevOps engineers and SREs who automate deployments, configure high availability and monitor performance in production.
- Backend developers who want to understand how InnoDB works in order to write efficient queries and diagnose locking problems.
Prerequisites: being comfortable with the Linux command line and the basics of system administration (managing systemd services, editing configuration files). No prior knowledge of MySQL is needed, the first guide starts from zero.
Recommended path
Path A, Beginner → Autonomous: follow modules 1 to 3 in order. You will understand the InnoDB architecture, know how to install MySQL and master the everyday operations with the mysql client. Allow about 2 hours.
Path B, Going to production: after path A, continue with modules 4 (configuration), 5 (security) and 6 (backup). It is the minimum for a reliable MySQL in production. Allow 3 to 4 more hours.
Path C, High availability: add modules 7 (monitoring) and 8 (replication) to prepare a deployment with GTID, Group Replication and InnoDB Cluster. This path assumes a good understanding of the previous modules.
The key concepts of MySQL
MySQL introduces several notions that are useful to know before diving into the guides:
| Concept | Role |
|---|---|
| InnoDB | Default storage engine: transactional (ACID), supports foreign keys, row-level locking and MVCC. All tables should use InnoDB. |
| Buffer Pool | Memory area where InnoDB caches data and index pages. It is the most important performance parameter: the bigger it is, the less MySQL reads from disk. |
| Binary Log (binlog) | Journal of all data changes. Used for replication (the replica reads the source's binlog) and PITR (replaying transactions after a backup). |
| GTID (Global Transaction Identifier) | Unique identifier of each transaction, in the source_uuid:sequence format. Greatly simplifies replication management compared with the old file/position pair. |
| Performance Schema | Internal database (performance_schema) that collects real-time metrics on queries, locks, I/O, threads and memory. |
| Data Dictionary | Since MySQL 8.0, metadata (tables, columns, indexes) is stored in transactional InnoDB tables, replacing the old .frm files. |
Common MySQL pitfalls
| Pitfall | Impact | Fix |
|---|---|---|
innodb_buffer_pool_size at the default value (128 MB) | MySQL does not use the available RAM, excessive disk reads | Adjust to 50-70% of the dedicated RAM (module 4) |
Using the root account for the application | No privilege separation, risk of an accidental DROP DATABASE | Create dedicated accounts with minimal GRANT (module 5) |
| Forgetting to enable the binary log | Impossible to replicate or restore to a precise point in time | Make sure log_bin is active (module 6) |
| Not monitoring slow queries | Gradual performance degradation without diagnosis | Enable the slow_query_log and analyze regularly (module 7) |
| Replication without GTID | Fragile file/position management, complex failover | Enable gtid_mode=ON from the start (module 8) |
max_connections too high without connection pooling | Each connection consumes memory, risk of OOM | Use ProxySQL or MySQL Router in front of MySQL (module 4) |
Which of these guides involve practice
Five of the eight guides above carry a validated lab from the
bdd-dsoxlab-training catalog, played on a Debian 13 machine and graded on the real
state of the server: the installation,
the client guide,
the configuration,
the security guide and the
backup guide.
Each announces its lab in a "Put it into practice" section, with what the
validation really reads, and it is rarely what you would expect: the listening
state reported by ss rather than a configuration file, a restart rather than
a variable, a called stored procedure rather than a dump that "succeeded".
The other three have no lab, and it is better to know that before starting. Discover MySQL is a concepts guide: it places InnoDB, the buffer pool and the binary log, with no gesture to prove. Monitoring and replication are awaiting theirs, which will open the next levels of the path.
Testing the level
Once the five labs are played, an exam measures what remains when the step-by-step instructions disappear: an instance inherited as is, listening on all interfaces, with an account that can do everything, no tuning and no backup. Ten graded tasks, 100 points, 70 to pass, and everything is graded on the real state of the server, after a restart.
Nothing is re-explained there, that is the principle: the exam does not relearn the role that must be activated nor the option that carries the stored procedures along, it checks that you think of them on your own. Its two hints cost 20 and 30 points, so that taking both puts a pass out of reach for that attempt. It can be retaken.
Key takeaways
- MySQL is the most widely deployed open source RDBMS, driven by the transactional InnoDB engine and a massive ecosystem.
- The architecture rests on the buffer pool (memory cache), the binary log (journal of changes) and the transactional data dictionary.
- Security relies on dedicated accounts with minimal GRANT, TLS for encryption and the caching_sha2_password authentication.
- Backups combine logical dumps (mysqldump) and physical backups (MySQL Shell / Percona XtraBackup) with binlog archiving for PITR.
- Monitoring relies on Performance Schema, the slow query log and tracking the InnoDB buffer pool.
- GTID-based replication simplifies high availability, with Group Replication and InnoDB Cluster as integrated solutions.
Going further
- If the architecture is still unclear, Discover MySQL places InnoDB, the buffer pool and the binary log before any installation.
- If the choice of engine is not settled, PostgreSQL, MySQL or SQLite sets them apart on an operator's criteria: license, LTS series, replication and backup tooling.
- If you administer both engines, PostgreSQL administration describes the other architecture, that of the WAL, and its path carries nine validated labs.