MySQL

Learn more

MySQL is a relational database management system, commonly abbreviated as an RDBMS. It allows applications to create databases, store information in tables, and retrieve or modify that information using SQL.

MySQL is widely used as the data layer for websites, online stores, business applications, booking platforms, administration panels, and online services. It can store user accounts, products, orders, settings, content, payments, inventory, and histories of completed operations.

MySQL is developed, distributed, and supported by Oracle. MySQL Community Edition is available as open-source software under the GPL. Commercial editions, services, and licensing options are also available.

Is MySQL a database?

MySQL is primarily a database management system rather than a single database. One MySQL server instance can manage multiple databases, users, and applications.

In everyday use, the phrase “MySQL database” may refer to:

a database created inside a MySQL server;
an entire server instance and its databases;
the MySQL technology used by an application;
a group of tables belonging to a particular system.

In MySQL, CREATE DATABASE and CREATE SCHEMA are synonyms. Other database systems may use the word schema for a different organizational level, so the terms should not automatically be treated as interchangeable outside MySQL.

MySQL vs. SQL

SQL, or Structured Query Language, is a language used to communicate with relational databases. MySQL is a specific database management system that supports SQL.

SQL can be understood as a set of commands and rules, while MySQL is software that interprets and executes those commands. Other database systems also use SQL, but individual products may provide their own functions, data types, and syntax extensions.

Core SQL statements include:

SELECT for retrieving data;
INSERT for adding records;
UPDATE for modifying records;
DELETE for removing records;
CREATE for creating databases, tables, and other objects;
ALTER for changing structures;
DROP for removing objects;
GRANT and REVOKE for managing privileges.

How does MySQL work?

MySQL normally operates through a client-server architecture. The MySQL server handles data storage, query execution, access control, transactions, locks, indexes, and change logging. A client may be an application, script, administration tool, or command-line program.

A typical request may involve the following steps:

an application connects to the MySQL server;
the server authenticates the account and checks its privileges;
the application sends an SQL statement;
MySQL parses its syntax and meaning;
the optimizer selects an execution plan;
the storage engine reads or modifies the relevant records;
the server returns a result or an error;
the application uses the data to prepare a response for the user.

A website visitor does not normally connect directly to MySQL. Queries are executed by the application’s backend, which verifies permissions, applies business rules, and prepares a secure response.

How does MySQL organize data?

A relational database stores information in tables. Each table represents a particular type of entity, such as a customer, product, or order.

The main structural elements include:

a database that groups tables and other objects;
a table that stores a particular type of data;
a column that defines a property of an entity;
a row that represents one record;
a primary key that uniquely identifies a record;
a foreign key that creates a relationship between tables;
an index that accelerates particular lookup patterns;
a constraint that helps enforce valid data;
a view that exposes a saved query result as a virtual table.

Instead of keeping every piece of information in one large table, data can be separated according to its meaning and connected through relationships.

Example of a MySQL table

A customer table could be created as follows:

CREATE TABLE customers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_customers_email (email)
) ENGINE=InnoDB
  DEFAULT CHARACTER SET utf8mb4;

Each customer receives a unique identifier. The email address is required, and the UNIQUE index prevents two customers from being stored with the same address.

A separate order table can reference the customer through a foreign key:

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id BIGINT UNSIGNED NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(30) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_orders_customer_id (customer_id),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
) ENGINE=InnoDB
  DEFAULT CHARACTER SET utf8mb4;

This relationship connects multiple orders to one customer without duplicating the customer’s complete information in every order record.

How is data retrieved from MySQL?

A query can retrieve selected orders together with customer information:

SELECT
    orders.id,
    orders.total_amount,
    orders.status,
    customers.email
FROM orders
INNER JOIN customers
    ON customers.id = orders.customer_id
WHERE orders.status = 'paid'
ORDER BY orders.created_at DESC
LIMIT 20;

MySQL can filter, sort, group, and join information from multiple tables. It also supports calculations, aggregations, subqueries, common table expressions, window functions, and operations involving text, numbers, dates, spatial information, and JSON documents.

MySQL data types

A column’s data type determines which values it can store and how MySQL interprets them. Appropriate types improve data integrity, performance, and storage efficiency.

Common types include:

INT and BIGINT for integers;
DECIMAL for exact decimal values such as monetary amounts;
FLOAT and DOUBLE for floating-point values;
CHAR and VARCHAR for shorter strings;
TEXT for longer text;
DATE, TIME, DATETIME, and TIMESTAMP for dates and times;
BINARY, VARBINARY, and BLOB for binary data;
BOOLEAN, which MySQL handles as a synonym for an appropriate numeric type;
JSON for validated JSON documents;
spatial types for geographic points, lines, and areas.

Monetary amounts should not automatically be stored in floating-point columns because representation differences can produce unexpected results. DECIMAL, or an integer representing the smallest currency unit, is usually more appropriate.

Keys and relationships

A PRIMARY KEY should uniquely identify each record. It may use an automatically generated number, a UUID, or another value that meets the application’s requirements.

A FOREIGN KEY helps enforce relationships between tables. It can, for example, prevent an order from referencing a customer that does not exist.

A relationship can define what happens when a referenced record is deleted or updated:

RESTRICT or NO ACTION can prevent the operation;
CASCADE can propagate the change to related records;
SET NULL can remove the reference if the column permits NULL.

The selected rule should match the meaning of the data. Cascading deletion can be useful, but an inappropriate cascade may remove a large number of records unintentionally.

What are MySQL indexes?

An index is an additional structure that helps MySQL find records without scanning the entire table. Indexes can support WHERE conditions, JOIN operations, sorting, and grouping.

An index is not necessarily useful for every query. Its effectiveness depends on factors including:

the order of columns in a composite index;
the comparison being performed;
the number of distinct values in a column;
data types;
sorting requirements;
functions applied to indexed columns;
the number of records returned;
the optimizer’s current statistics.

Indexes consume storage and must be maintained when data is inserted, updated, or deleted. Adding an index to every column can therefore reduce write performance and increase database size.

How can you inspect a query execution plan?

The EXPLAIN statement displays the plan selected by the MySQL optimizer:

EXPLAIN
SELECT id, total_amount
FROM orders
WHERE customer_id = 125
ORDER BY created_at DESC;

The execution plan can reveal the order in which tables are accessed, the indexes being considered or used, join methods, and estimated record counts.

Optimization is not limited to adding indexes. Problems may also result from retrieving unnecessary columns, running many similar queries, returning unbounded result sets, using an unsuitable data model, or calculating values separately for every record.

What is InnoDB?

InnoDB is MySQL’s default storage engine. It manages the physical storage of tables and indexes and provides features including:

transactions;
foreign keys;
row-level locking;
multiversion concurrency control, or MVCC;
automatic crash recovery;
caching of frequently used data and indexes;
change logging mechanisms;
consistent reads during concurrent operations.

MySQL supports additional storage engines, but they may provide different capabilities and limitations. The storage engine affects transaction support, locking, indexes, backups, and referential integrity.

Transactions and ACID

A transaction combines multiple operations into one logical unit. If all operations succeed, the application can confirm them with COMMIT. If an error occurs, an unfinished transaction can be reversed with ROLLBACK.

START TRANSACTION;

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 52
  AND stock_quantity > 0;

INSERT INTO order_items (
    order_id,
    product_id,
    quantity
) VALUES (
    9102,
    52,
    1
);

COMMIT;

The application should confirm that the inventory update actually affected a record. If the product was unavailable or a later statement failed, it should roll back the transaction.

Transaction properties are commonly described by the acronym ACID:

Atomicity means that a group of operations is completed as a unit or reversed;
Consistency means that defined rules and constraints remain valid;
Isolation describes how concurrent transactions affect one another;
Durability means that committed changes survive a failure.

Actual durability guarantees can depend on server configuration, the operating system, storage hardware, and infrastructure. Transaction support does not replace a well-designed data model or reliable backups.

Autocommit and isolation levels

MySQL normally has autocommit enabled by default. A single data-modification statement is therefore treated as its own transaction unless the application explicitly starts a larger transaction.

InnoDB supports four standard isolation levels:

READ UNCOMMITTED;
READ COMMITTED;
REPEATABLE READ;
SERIALIZABLE.

The default InnoDB isolation level is REPEATABLE READ. The selected level affects read consistency, record locking, conflict behavior, and performance. A stricter level is not automatically better and should be selected according to the application’s concurrency model.

Locks and deadlocks

When multiple transactions modify the same data concurrently, InnoDB uses locks. One operation may need to wait for another to complete.

A deadlock occurs when transactions wait for resources held by one another. InnoDB detects this condition and rolls back one transaction so that the others can continue.

An application should be prepared to retry a safe transaction after a deadlock error. The risk can be reduced by:

updating records in a consistent order;
keeping transactions short;
using appropriate indexes;
avoiding user interaction while a transaction remains open;
limiting the number of affected records;
monitoring long-running queries and lock waits.

Character sets and collations

A character set defines which characters can be stored. A collation defines how text is compared and sorted, including case handling and language-specific differences.

New applications commonly use utf8mb4, which supports the complete range of Unicode characters. It should not be confused with the older MySQL character set called utf8 or utf8mb3, which cannot represent every four-byte Unicode character.

Character settings should be configured consistently for the database, tables, columns, and application connection. Conflicting settings can cause failed writes, incorrect sorting, or corrupted text.

How does an application connect to MySQL?

Applications use drivers or libraries commonly called connectors. MySQL connectors and third-party drivers are available for languages including PHP, JavaScript, Python, Java, C#, and C++.

Connection settings usually include:

the server address;
the port, which is 3306 by default;
the database name;
a user name;
a password or another authentication method;
TLS settings;
the connection character set;
timeouts;
connection pool parameters.

Credentials should not appear in public browser code or be committed to a repository. The database server should not be exposed to the public Internet unless there is a genuine requirement and appropriate protection.

MySQL and SQL injection

SQL injection is an attack in which supplied data changes the meaning of an SQL statement. It is commonly caused by building queries through direct string concatenation with untrusted input.

An unsafe implementation may look like this:

$sql = "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'";

The application should use prepared statements and parameter binding:

SELECT id, email
FROM users
WHERE email = ?;

The parameter value is then supplied separately from the SQL structure. A placeholder cannot generally replace a table name, column name, or sorting direction. Structural elements should be selected from an explicit allowlist.

MySQL security

Securing a database requires protection of the entire infrastructure rather than a password alone. Core measures include:

installing supported security updates;
granting each account only the privileges it requires;
using separate accounts for applications, administrators, and backups;
preventing normal applications from using administrative accounts;
restricting the hosts from which an account can connect;
protecting port 3306 with private networking and firewall rules;
encrypting remote connections with TLS;
storing secrets securely;
using prepared statements;
logging errors and suspicious authentication attempts;
protecting backups;
removing unused accounts and privileges;
reviewing SHOW GRANTS output regularly;
separating production and testing environments.

Application user passwords should not be stored as plain text or with reversible encryption. The application should transform them with a modern password-hashing algorithm designed for that purpose.

Optimizing MySQL performance

Performance depends on both server configuration and application design. Important areas include:

table and relationship design;
data type selection;
primary keys and indexes;
query execution plans;
the number of queries performed per request;
result set sizes;
transaction duration;
locks and deadlocks;
connection pooling;
memory allocated to the InnoDB buffer pool;
storage performance;
application caching;
replication and read distribution;
continuous observation of real workloads.

A common problem is the N+1 query pattern, in which one query retrieves a list and a separate query is then executed for each result. Depending on the situation, it may be reduced by joining tables, retrieving related data in batches, or changing the application’s data-access strategy.

Applications should also avoid SELECT * when they need only a few columns. Large data sets require pagination, but very high OFFSET values can be expensive and may need to be replaced with keyset pagination based on the last retrieved value.

Monitoring MySQL

Database problems often develop gradually. Monitoring may cover:

server availability;
query response times;
active connection counts;
CPU, memory, and storage usage;
database size and growth rate;
slow queries;
lock waits;
deadlocks;
connection and authentication errors;
replication status and replica lag;
backup results;
the time required for a test restoration.

MySQL provides tools such as the slow query log, Performance Schema, the sys schema, server status information, and InnoDB diagnostics. Metrics do not solve a problem by themselves, but they can reveal changing behavior before a serious failure occurs.

MySQL replication

Replication transfers changes from a source server to one or more replica servers. It may support:

higher availability;
distribution of selected read queries;
a copy of data in another environment;
reporting outside the primary server;
migrations and upgrades;
a failover procedure.

A replica may lag behind its source. A read performed immediately after a write may therefore fail to see the latest change. Applications need to account for this behavior, particularly in payment, inventory, and authorization workflows.

Replication is not a backup. An accidental deletion, defective migration, or unwanted update can also be replicated.

High availability and scaling

A single MySQL server can become a single point of failure. Environments requiring high availability may use replicas, automatic failover, MySQL Group Replication, InnoDB Cluster, load balancers, and additional monitoring mechanisms.

Scaling options include:

increasing server resources;
optimizing queries and indexes;
caching frequently retrieved data;
moving selected reads to replicas;
archiving old data;
partitioning large tables;
dividing data across independent instances;
separating analytical and transactional workloads.

Replication, partitioning, and sharding solve different problems. Each adds complexity and should not replace earlier work on query and data-model optimization.

MySQL backups

A backup should make it possible to recover from infrastructure failure, human error, data corruption, or an attack. Creating an export file does not by itself guarantee that restoration will succeed.

Backup categories include:

logical backups, which record structures and data as SQL statements or text files;
physical backups, which copy files used by the database;
full backups, which include the entire selected data set;
incremental backups, which include changes since an earlier point;
online backups, which are performed while the server is running;
offline backups, which are performed while it is stopped.

The binary log can support recovery of changes made after the most recent full backup and restoration to a selected point in time.

A backup strategy should define:

backup frequency;
the maximum acceptable amount of data loss;
the maximum acceptable recovery time;
retention periods;
encryption;
storage outside the primary server;
monitoring of backup jobs;
regular restoration tests;
an incident recovery procedure.

MySQL and WordPress

WordPress uses MySQL or a supported, compatible MariaDB version to store website data. The database may contain:

posts and pages;
website and plugin settings;
users and user metadata;
comments;
categories and tags;
navigation menus;
drafts and revisions;
data created by plugins;
attachment records and metadata.

Original image and document files are normally stored in the file system, while the database contains related records, metadata, and URLs.

Backing up WordPress files without the database is not sufficient to restore the complete website. The database alone does not contain the theme, plugin code, or every uploaded file. A complete backup should include both areas.

MySQL and WooCommerce

WooCommerce uses the WordPress database to store e-commerce information. Depending on the version, configuration, and installed extensions, this may include:

products and variations;
prices;
orders;
customers;
coupons;
taxes;
shipping methods;
inventory;
payment statuses;
order notes and metadata;
cart sessions;
data belonging to payment and integration extensions.

A large store may generate substantial write activity and query volumes. Performance depends not only on hosting resources but also on extension quality, indexes, scheduled tasks, caching, record counts, and query behavior.

MySQL vs. MariaDB

MariaDB originated as a fork of MySQL but is now a separate database system with independent development. Both systems use SQL and can support many of the same applications, but they are not unconditionally interchangeable.

Differences may involve:

available features;
storage engines;
query optimization;
data types and syntax;
authentication;
replication;
administration tools;
upgrade procedures and file formats;
support schedules;
driver and extension compatibility.

A migration should be preceded by compatibility testing for the specific application, versions, configuration, and features in use. Stated support for MySQL does not necessarily imply support for every MariaDB version, or vice versa.

MySQL vs. PostgreSQL and SQLite

PostgreSQL is another advanced relational database system. It differs from MySQL in areas such as selected SQL syntax, data types, extensions, administration, and query optimization. The choice should reflect application requirements and team expertise rather than popularity alone.

SQLite normally operates as an embedded library without a separate server process. A complete database can be stored in one file, which is convenient for smaller applications, local tools, and mobile devices. It is not a direct substitute for a server-based MySQL environment supporting many users and complex infrastructure.

MySQL in the cloud

MySQL can run on an organization’s own server, a virtual machine, a container, or a managed cloud service. In a managed model, the provider may handle some updates, backups, monitoring, replicas, and automatic failover.

This does not transfer every responsibility to the provider. The application team still needs to manage:

the data model;
queries and indexes;
accounts and privileges;
network access;
backup retention;
recovery tests;
resource, storage, and network costs;
legal and regulatory requirements;
a migration strategy for moving to another environment.

LTS and Innovation releases

MySQL uses two main release tracks:

LTS targets environments that require a stable feature set and a longer support period;
Innovation provides faster access to new features and changes but requires more frequent upgrades and stronger compatibility testing.

A production version should be selected after checking its support period, application requirements, drivers, extensions, and available upgrade path. The highest available version number is not the only relevant criterion.

Upgrading MySQL

A database server upgrade should first be tested against a copy of the environment. A version change may affect:

available SQL syntax;
reserved words;
default settings;
authentication plugins;
query execution plans;
performance;
text comparison and sorting behavior;
driver compatibility;
replication;
the ability to return to an earlier version.

Before upgrading, the team should create a verified backup, review the official upgrade path, and confirm the recovery process. A downgrade is not always a simple reversal of an upgrade.

Benefits of MySQL

Commonly cited benefits include:

a mature relational data model;
transaction support through InnoDB;
broad availability across hosting and cloud platforms;
a large selection of drivers, tools, and learning resources;
compatibility with popular programming languages;
replication and high-availability options;
support for both small and complex applications;
a freely available Community Edition;
integration with platforms including WordPress and WooCommerce;
support for relational data, JSON, full-text search, and spatial information.

MySQL limitations and costs

MySQL does not automatically solve data-modeling, performance, or availability problems. An implementation may require:

administration and monitoring;
table and relationship design;
query optimization;
upgrade management;
backup creation and testing;
capacity planning;
replication and failure handling;
personal data protection;
infrastructure cost control;
expertise in diagnosing locks and performance issues.

A relational model may be inconvenient for some data structures or highly specialized workloads. MySQL may then operate alongside a search engine, cache, object store, data warehouse, or another specialized technology.

Common MySQL mistakes

Frequent problems include:

tables without primary keys;
inappropriate data types;
storing monetary values in floating-point columns without assessing the consequences;
missing indexes for frequently executed queries;
too many unnecessary indexes;
incorrect column order in composite indexes;
using SELECT * without a reason;
executing N+1 queries;
returning large result sets without limits;
placing all data in one table or one JSON column;
omitting constraints that protect data integrity;
building SQL by concatenating untrusted input;
connecting an application through an administrative account;
exposing the database port publicly;
keeping transactions open longer than necessary;
failing to handle deadlocks and transaction retries;
treating a replica as a backup;
creating backups without testing restoration;
upgrading production without compatibility tests;
failing to monitor data growth and slow queries;
assuming complete compatibility between MySQL and MariaDB;
manually modifying application data without understanding its business rules.

When should you use MySQL?

MySQL may be a suitable choice when:

the data has a clear structure and relationships;
the application requires transactions;
data integrity is important;
flexible queries and reporting are required;
the project uses WordPress or WooCommerce;
the team understands SQL and relational data modeling;
the selected environment provides strong MySQL support;
the project needs a widely supported technology with many available tools;
replication or high availability may be required later.

When might MySQL be the wrong choice?

Another solution may be more appropriate when:

the application is small, local, and does not need a separate database server;
the data has no stable structure or meaningful relationships;
the project depends on features specific to another database system;
the primary workload involves specialized full-text search;
the system is designed mainly for analyzing extremely large data sets;
the application requires a specialized time-series, graph, or object store;
the team cannot maintain a database server and a managed service is outside the budget.

This does not mean that MySQL cannot participate in such a system. It often remains the primary transactional database while other technologies handle search, analytics, files, queues, or caching.

Is MySQL only used for websites?

MySQL is strongly associated with websites because it has long been available across popular hosting environments and supports platforms such as WordPress. It is not limited to web publishing.

MySQL can support mobile applications, internal systems, commerce platforms, booking services, logistics software, APIs, reporting tools, and many other applications that process structured data.

Its suitability depends on data-modeling, consistency, performance, availability, scaling, and maintenance requirements. Popularity alone is not a substitute for evaluating the project.