MySQL is an open-source relational database management system that stores data in tables, is queried with SQL and runs as a client-server database, by default on TCP port 3306. First released in 1995 by the Swedish company MySQL AB, it passed to Sun Microsystems in 2008 and to Oracle in 2010, which develops it today under a dual license: the free Community Edition under the GPL version 2 and commercial editions with additional tools and support.
Among database systems, MySQL is known for straightforward operation, fast primary-key access and simple replication, which made it the database layer of the classic LAMP stack (Linux, Apache, MySQL and PHP) and a common choice for web applications, content management systems and online shops. Its default storage engine, InnoDB, provides ACID transactions, row-level locking and crash recovery.
How MySQL is built
MySQL has a layered architecture that separates SQL processing from data storage. The upper layer handles connections, authentication, parsing and query optimization, while a pluggable storage engine below it decides how rows are physically stored, indexed and locked.
This design lets different tables use different engines, although in modern deployments InnoDB is used for almost everything. The table compares the engines that still matter in practice.
| Engine | Transactions | Locking | Typical use |
|---|---|---|---|
| InnoDB | Yes, ACID | Row-level with MVCC | Default since MySQL 5.5; all general workloads |
| MyISAM | No | Table-level | Legacy read-mostly tables; not recommended for new designs |
| MEMORY | No | Table-level | Temporary lookup data that may be lost on restart |
| NDB | Yes | Row-level | MySQL NDB Cluster for distributed, in-memory high availability |
| ARCHIVE | No | Row-level inserts | Compressed, append-only logging data |
InnoDB stores each table as a clustered index on the primary key, so rows are physically ordered by that key and secondary indexes point to the primary key value. This makes primary-key lookups very fast but means that a long or random primary key, such as an unordered UUID stored as text, bloats every secondary index and fragments inserts.
Key InnoDB mechanisms and settings
InnoDB achieves durability and concurrency through a set of cooperating structures: a buffer pool that caches data pages in memory, a redo log that records changes before data pages are written, undo logs that keep older row versions for multiversion concurrency control (MVCC), and a doublewrite buffer that protects against torn page writes.
The following table lists the settings that most influence a production server; the right values depend on memory, workload and durability requirements.
| Setting | Default | Common production guidance |
|---|---|---|
innodb_buffer_pool_size | 128 MB | About 50 to 75 percent of RAM on a dedicated server |
innodb_flush_log_at_trx_commit | 1 | Keep 1 for full durability; 2 risks about one second of commits on power loss |
max_connections | 151 | Size to real demand; use connection pooling instead of very high values |
transaction_isolation | REPEATABLE-READ | Keep, or use READ-COMMITTED to reduce gap locking |
character_set_server | utf8mb4 since 8.0 | Use utf8mb4; the legacy utf8 alias stores at most 3 bytes per character |
innodb_page_size | 16 KB | Rarely changed; fixed when the data directory is initialized |
The REPEATABLE READ default differs from most other relational systems, which default to READ COMMITTED. Under REPEATABLE READ, InnoDB uses gap and next-key locks for locking reads and writes, which prevents phantom rows but can cause unexpected lock waits and deadlocks under heavy concurrent inserts.
MySQL compared with other database systems
MySQL competes most directly with PostgreSQL and MariaDB for server workloads and is often contrasted with SQLite for embedded use. Each system makes different trade-offs between simplicity, feature depth and licensing.
The table summarizes the main differences.
| Aspect | MySQL | MariaDB | PostgreSQL | SQLite |
|---|---|---|---|---|
| Governance | Oracle | MariaDB Foundation and MariaDB plc | Community project | Small core team, public domain |
| License | GPLv2 or commercial | GPLv2 | PostgreSQL License, permissive | Public domain |
| Architecture | Client-server | Client-server | Client-server | Embedded library, single file |
| Default isolation | Repeatable read | Repeatable read | Read committed | Serializable |
| Strengths | Simple operation, replication tooling | MySQL compatibility, extra engines | Advanced SQL, extensibility, strict typing | Zero administration, portability |
MariaDB began in 2009 as a fork of MySQL led by its original creator, Michael Widenius. The two remain largely compatible at the SQL level, but features such as JSON storage, replication internals and the optimizer have diverged enough that a migration between them must be tested rather than assumed to work.
Replication and high availability
MySQL replication copies changes from a source server to one or more replicas by shipping the binary log, a record of committed changes in row-based or statement-based format. Global transaction identifiers (GTIDs) label every transaction, which simplifies failover because each replica knows exactly which transactions it has applied.
- Asynchronous replication: the default; fast, but a failover can lose the last transactions that had not yet reached a replica.
- Semisynchronous replication: the source waits until at least one replica acknowledges receipt, reducing the risk of data loss at the cost of commit latency.
- Group Replication: a consensus-based group of servers that agree on each transaction; the basis of InnoDB Cluster with automatic failover.
- Read replicas: a common way to scale read traffic, with the caveat that replicas can lag behind the source.
Replication is not a substitute for backups, because a mistaken DELETE is replicated within seconds. Production systems combine replicas with regular physical or logical backups and binary log archiving for point-in-time recovery.
Versions, limitations and common pitfalls
Version choice matters because MySQL changed substantially over time. Version 8.0 added window functions and common table expressions, a transactional data dictionary and utf8mb4 as the default character set, and since 2023 Oracle publishes short-lived Innovation releases alongside long-term support (LTS) releases, the first of which was 8.4.
The table lists frequent problems in MySQL deployments, their cause and the remedy.
| Problem | Cause | Remedy |
|---|---|---|
| Emoji or some characters fail to save | Columns use the 3-byte utf8 alias | Convert to utf8mb4 |
| Slow queries after data growth | Missing or unused indexes | Analyze with EXPLAIN and the slow query log |
| Lock wait timeouts | Long transactions and gap locks | Keep transactions short; review isolation level |
| Replica lag | Large transactions or single-threaded apply | Split large writes; enable parallel replication |
| Silent data truncation | Strict SQL mode disabled | Keep STRICT_TRANS_TABLES enabled |
MySQL is less suited to workloads that need advanced SQL features, custom data types or complex analytical queries, where PostgreSQL or a columnar analytical database is usually a better fit. For typical transactional web workloads, a well-configured MySQL server remains a mature and predictable choice.
In simple terms
MySQL is a program that keeps an application’s data in organized tables and answers questions about it. A website uses it to remember customers, products and orders. When someone places an order, the website asks MySQL to save it. When the customer checks the order status later, MySQL finds it again.
It runs as a separate service, a bit like a warehouse with a counter where many clerks can drop off and pick up items at the same time. The main reason for its popularity is that it is free to use, well understood and dependable for everyday web applications.
A practical example
A mid-sized online shop runs its catalog and orders on a single MySQL 8.4 server with 32 GB of RAM. During seasonal sales, product pages slow down to several seconds, and the monitoring shows the server reading from disk constantly because the buffer pool was left at its 128 MB default.
The administrator raises innodb_buffer_pool_size to 20 GB, which holds the roughly 14 GB of active data in memory, and adds a read replica that serves catalog browsing while the source handles checkout transactions. The slow query log reveals two queries filtering on unindexed columns, and composite indexes fix both.
During the next sale, median page response time falls from about 2.5 seconds to under 200 milliseconds, and the source server’s CPU load stays below half. Nightly physical backups plus archived binary logs now allow recovery to any point in the previous week.