MySQL Introduction

MySQL is the world's most widely deployed open-source relational database management system (RDBMS). Originally developed in 1995 by Michael Widenius and David Axmark at MySQL AB, it was acquired by Sun Microsystems in 2008 and then by Oracle Corporation in 2010. Today MySQL powers billions of applications worldwide — from personal blogs to the largest internet platforms on the planet.

At its core, MySQL stores data in tables made up of rows and columns, and exposes it through Structured Query Language (SQL) — the universal language of relational databases. If you already know a bit of SQL, you can query any relational database with only minor syntax differences.

Who Uses MySQL

MySQL's success stems from a combination of factors that no single competitor has matched all at once:

Open source with a GPL licence — free to use, study, and modify.
Rock-solid reliability — deployed at Facebook, Twitter, YouTube, GitHub, Airbnb, and Shopify.
Fortune 500 companies like Walmart, Verizon, and Booking.com run MySQL at massive scale.
Cross-platform — runs on Linux, macOS, Windows, and cloud-managed services (AWS RDS, Cloud SQL, PlanetScale, TiDB).
Massive ecosystem — drivers and ORMs for every major programming language.
Powers the majority of open-source CMS platforms: WordPress, Drupal, Joomla, Magento.
MySQL 8.0 narrowed the feature gap with PostgreSQL significantly (window functions, CTEs, JSON, roles).

The LAMP Stack Context

MySQL became the database of choice for web development as part of the LAMP stack:

Linux — the operating system
Apache — the web server
MySQL — the database
PHP (later Python, Perl) — the scripting language

This combination was the dominant web architecture for over a decade and is still widely deployed. Modern equivalents extend the concept: LEMP (Nginx instead of Apache), and fully cloud-managed stacks on AWS, Google Cloud, and Azure all center MySQL-compatible databases.

Key Features of MySQL

Feature	Description
ACID transactions	Atomicity, Consistency, Isolation, Durability — data is never left in a partial state
Foreign keys	Enforce referential integrity between tables (InnoDB engine)
Full-text search	Built-in inverted indexes for natural-language and boolean text search
JSON support	Native JSON column type with path-based operators (MySQL 5.7.8+)
Window functions	ROW_NUMBER, RANK, LAG, LEAD, SUM OVER, etc. (MySQL 8.0+)
CTEs	WITH clauses including recursive CTEs for hierarchical data (MySQL 8.0+)
Replication	Built-in async and semi-sync replication for scaling reads and HA
Partitioning	Split large tables across physical partitions for manageability
InnoDB storage engine	Default engine — row-level locking, crash recovery, clustered PK index

Quick-Start: Install and Connect in 5 Minutes

Bash

# Option 1: Docker (fastest for local dev — no install required)
docker run --name mysql-dev   -e MYSQL_ROOT_PASSWORD=secret   -e MYSQL_DATABASE=myapp   -p 3306:3306   -d mysql:8.0

# Connect to the Docker container
docker exec -it mysql-dev mysql -u root -psecret

# Option 2: macOS with Homebrew
brew install mysql
brew services start mysql
mysql_secure_installation
mysql -u root -p

# Option 3: Ubuntu / Debian
sudo apt update && sudo apt install mysql-server
sudo systemctl start mysql
sudo mysql_secure_installation
sudo mysql

First Steps After Connecting

SQL

-- Check MySQL version
SELECT VERSION();

-- List all databases
SHOW DATABASES;

-- Create your first database
CREATE DATABASE myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Switch to it
USE myapp;

-- Create a simple table
CREATE TABLE users (
  id         INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  email      VARCHAR(255)  NOT NULL,
  name       VARCHAR(100)  NOT NULL,
  created_at DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert a row
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');

-- Query it back
SELECT id, name, email, created_at FROM users;

Tip

Always specify ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 when creating tables. These are the defaults in MySQL 8.0 but being explicit protects against inherited configurations from older servers.

Understanding the MySQL Ecosystem

MySQL is more than just the server process. The broader ecosystem includes several tools:

Tool	Purpose
MySQL Server (mysqld)	The database server process — stores and manages data
MySQL Shell (mysqlsh)	Advanced CLI with JavaScript/Python scripting, AdminAPI, InnoDB Cluster management
MySQL Workbench	GUI for schema design, query editing, performance monitoring, and administration
MySQL Router	Connection routing for InnoDB Cluster and ReplicaSet — transparent load balancing
MySQL InnoDB Cluster	High-availability solution: Group Replication + MySQL Router + MySQL Shell
mysqldump	Logical backup tool — dumps SQL statements to recreate the database
mysqlbinlog	Reads binary log files for point-in-time recovery and replication debugging
INFORMATION_SCHEMA	Virtual database with metadata about all objects in the server
performance_schema	Runtime statistics and instrumentation for query profiling and monitoring
sys schema	Human-readable views over performance_schema — top queries, unused indexes, etc.

MySQL 8.0 Highlights

Window functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE).
Common Table Expressions (CTEs) including recursive CTEs — enables hierarchical and graph queries.
Invisible indexes — hide an index from the optimizer without dropping it.
Descending indexes — indexes can now be sorted in descending order natively.
Roles — group privileges into named roles and assign them to users.
UTF-8 done right — utf8mb4 is now the default character set.
Improved JSON with JSON_TABLE() — turn JSON arrays into relational rows.
Atomic DDL — DROP TABLE and other DDL statements are now crash-safe.
INSTANT ADD COLUMN — add columns to large tables in milliseconds without rebuilding.

Storage Engines

Engine	Transactions	Foreign Keys	Best For
InnoDB	Yes	Yes	Almost everything — the default and recommended engine
MyISAM	No	No	Legacy; read-heavy tables (mostly replaced by InnoDB)
MEMORY	No	No	Temporary tables, session caches — data lost on restart
ARCHIVE	No	No	Write-once, compressed archival storage
CSV	No	No	Storing table data as plain CSV files for data exchange

Note

Always use InnoDB. It is the default since MySQL 5.5 and the only engine that supports transactions, foreign keys, and crash recovery. MyISAM should only appear in legacy migrations.

Common Misconceptions About MySQL

"utf8 means full UTF-8 support" — FALSE. MySQL's utf8 is a broken 3-byte variant. Use utf8mb4 always.
"MySQL is slow" — FALSE for OLTP workloads. MySQL is extremely fast for web application read/write patterns. Facebook serves billions of queries per second on MySQL.
"MySQL and MariaDB are the same" — NOT quite. MariaDB forked from MySQL 5.5 and diverged significantly. Some features (JSON, roles, window functions) work differently or have different syntax.
"NoSQL databases replaced MySQL" — FALSE. Relational databases remain dominant for structured data requiring ACID guarantees and complex queries.
"AUTO_INCREMENT IDs are always sequential" — FALSE. Gaps appear from rollbacks, deletes, and concurrent inserts. Never rely on sequence continuity.

MySQL vs PostgreSQL — Quick Comparison

Feature	MySQL 8.0	PostgreSQL 16
Licence	GPL / commercial dual-licence	PostgreSQL Licence (MIT-like)
JSON	JSON column type, JSON_TABLE()	JSONB (binary, indexable), richer operators
Arrays	No native array type	Native array columns
Window functions	Full support (8.0+)	Full support
CTEs	Recursive CTEs (8.0+)	Recursive + writable CTEs
Full-text search	Built-in (limited language support)	tsvector, better language support
Speed (OLTP reads)	Slightly faster in many benchmarks	Comparable, better for complex queries
Managed cloud	AWS RDS, Cloud SQL, PlanetScale	Aurora PG, Supabase, Neon

Note

Neither MySQL nor PostgreSQL is universally better. MySQL is the safe, widely-supported choice for web applications. PostgreSQL excels at complex queries, GIS, and workloads that benefit from its richer type system.

MySQL Configuration Essentials

Bash

# MySQL configuration file locations:
# Linux:  /etc/mysql/my.cnf  or  /etc/mysql/mysql.conf.d/mysqld.cnf
# macOS:  /usr/local/etc/my.cnf
# Docker: pass --defaults-file or environment variables

# Key settings in [mysqld] section:
# innodb_buffer_pool_size  = 70-80% of RAM for dedicated DB servers
# max_connections          = max concurrent client connections (tune for your app)
# slow_query_log           = ON  (log queries slower than long_query_time)
# long_query_time          = 1   (threshold in seconds)
# character_set_server     = utf8mb4
# collation_server         = utf8mb4_unicode_ci

Where to Get Help

Official documentation: dev.mysql.com/doc — comprehensive, accurate, and version-specific.
MySQL built-in help: type HELP 'SELECT' or HELP 'CREATE TABLE' in the MySQL CLI.
Stack Overflow: the mysql tag has over 600,000 answered questions.
MySQL bug tracker: bugs.mysql.com — search before reporting a suspected bug.
Percona Blog and documentation — excellent deep dives on InnoDB internals and performance.
DBA Stack Exchange: dba.stackexchange.com — more focused on DBA topics than Stack Overflow.

Getting Help from MySQL Itself

SQL

-- Built-in help system
HELP 'SELECT';
HELP 'CREATE TABLE';
HELP 'data types';

-- Show all databases, tables, columns
SHOW DATABASES;
SHOW TABLES FROM myapp;
DESCRIBE users;
SHOW CREATE TABLE users;

-- Show running queries
SHOW PROCESSLIST;

-- Show current session status
SHOW STATUS LIKE 'Threads_connected';
SELECT @@version, @@datadir, @@character_set_server;

What This Tutorial Series Covers

Getting started — installation, connecting, databases, and your first table.
Data definition — CREATE TABLE, data types, constraints, ALTER TABLE, DROP, TRUNCATE.
Data manipulation — INSERT, SELECT, WHERE, UPDATE, DELETE with deep dives into each.
Filtering and sorting — WHERE operators, LIKE, BETWEEN, IN, ORDER BY, LIMIT.
Aggregation — GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAX, DISTINCT.
Joins — INNER, LEFT, RIGHT, FULL OUTER, CROSS, self-joins, multi-table joins.
Indexes — B-tree indexes, composite indexes, covering indexes, EXPLAIN, performance tuning.
Transactions — START TRANSACTION, COMMIT, ROLLBACK, isolation levels, deadlocks.
Advanced SQL — Subqueries, CTEs, window functions, JSON, full-text search.
Administration — Users, privileges, roles, backup, replication, query optimisation.