What is MySQL? Introduction and Overview

What is MySQL?

MySQL is the world's most popular open-source Relational Database Management System (RDBMS). It stores data in structured tables with rows and columns, and uses SQL (Structured Query Language) to query and manipulate that data. MySQL is fast, reliable, scalable, and easy to use - making it the go-to database for millions of web applications worldwide.

MySQL is developed, distributed, and supported by Oracle Corporation. It runs on virtually every platform including Linux, Windows, and macOS, and powers some of the largest websites on the internet including Facebook, Twitter, YouTube, and Wikipedia.

A Brief History

MySQL was created by Michael "Monty" Widenius and David Axmark at the Swedish company MySQL AB. The name "MySQL" combines the name of Monty's daughter My with SQL. The first internal version was released in 1994, with the first public release following in 1995.

In 2008, Sun Microsystems acquired MySQL AB for approximately $1 billion. Oracle Corporation then acquired Sun Microsystems in 2010, bringing MySQL under Oracle's ownership. The community edition remains free and open-source under the GPL license. After the Oracle acquisition, Michael Widenius forked MySQL to create MariaDB as a fully open-source alternative.

-- Select all rows from a table
SELECT * FROM customers;

-- Select specific columns with a condition
SELECT first_name, last_name, email
FROM customers
WHERE active = 1;

-- Insert a new row
INSERT INTO customers (first_name, last_name, email, active)
VALUES ('Alice', 'Johnson', 'alice@example.com', 1);

-- Update a row
UPDATE customers
SET email = 'alice.j@example.com'
WHERE customer_id = 1;

-- Delete a row
DELETE FROM customers
WHERE customer_id = 1;

MySQL vs Other RDBMS

MySQL is one of several popular relational database systems. Here's how it compares to the most common alternatives:

Key Features of MySQL

MySQL ships with a rich set of features that make it suitable for everything from small hobby projects to large-scale enterprise applications:

• ACID Compliance - The InnoDB storage engine provides full ACID (Atomicity, Consistency, Isolation, Durability) transactions, ensuring data integrity even during failures.
• Replication - MySQL supports master-slave and master-master replication, enabling high availability and read scaling across multiple servers.
• Partitioning - Large tables can be split into smaller partitions (RANGE, LIST, HASH, KEY) to improve query performance and manageability.
• Full-Text Search - Built-in FULLTEXT indexes allow efficient natural-language and boolean-mode searches across text columns.
• JSON Support - Since MySQL 5.7, a native JSON data type with a rich set of JSON functions enables document-style storage alongside relational data.
• Stored Procedures & Triggers - Business logic can be encapsulated in the database layer using stored procedures, functions, and triggers.
• Views - Virtual tables built from SELECT queries simplify complex queries and add a security layer over raw tables.
• Multiple Storage Engines - Pluggable storage engines (InnoDB, MyISAM, MEMORY, CSV, ARCHIVE) let you choose the right engine per table.

-- Check MySQL version
SELECT VERSION();
-- Output: 8.0.35

-- Check available storage engines
SHOW ENGINES;

-- Check current user and database
SELECT USER(), DATABASE();

-- List all databases on the server
SHOW DATABASES;

-- Check global variables (e.g. max connections)
SHOW VARIABLES LIKE 'max_connections';

Common Use Cases

MySQL is the backbone of countless applications across industries:

• Web Applications (LAMP Stack) - Linux + Apache + MySQL + PHP is one of the most widely deployed web stacks in the world. Frameworks like Laravel, CodeIgniter, and WordPress all use MySQL by default.
• E-Commerce - Platforms like Magento, WooCommerce, and OpenCart rely on MySQL to manage products, orders, customers, and inventory.
• Content Management Systems - WordPress, Drupal, and Joomla all use MySQL to store posts, pages, users, and settings.
• Analytics & Reporting - MySQL's aggregation functions and partitioning make it suitable for business intelligence dashboards and reporting systems.
• SaaS Applications - Multi-tenant SaaS products use MySQL for its reliability, connection pooling, and replication capabilities.

MySQL Architecture

Understanding MySQL's architecture helps you write better queries and tune performance. MySQL is organized into three main layers:

• Client Layer - Applications connect to MySQL using connectors (PHP PDO, JDBC, Python mysql-connector, etc.) or the mysql CLI. The client sends SQL statements and receives result sets.
• Server Layer - The MySQL server handles connection management, query parsing, query optimization (the query optimizer rewrites your SQL for efficiency), caching, and execution. The server layer is storage-engine agnostic.
• Storage Engine Layer - The storage engine handles the actual reading and writing of data to disk. MySQL supports pluggable storage engines per table:
  • InnoDB - The default engine since MySQL 5.5. Supports ACID transactions, row-level locking, foreign keys, and crash recovery. Best for most use cases.
  • MyISAM - The older default engine. Faster for read-heavy workloads but lacks transactions and foreign key support. Uses table-level locking.
  • MEMORY - Stores data in RAM for ultra-fast access. Data is lost on restart. Useful for temporary tables and caching.
  • CSV - Stores data as comma-separated values files. Useful for data exchange.
  • ARCHIVE - Optimized for storing large amounts of infrequently accessed historical data with high compression.

-- Create a table using InnoDB (default)
CREATE TABLE orders (
    order_id   INT          NOT NULL AUTO_INCREMENT,
    customer_id INT         NOT NULL,
    total      DECIMAL(10,2) NOT NULL,
    created_at DATETIME     DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create a read-optimized table using MyISAM
CREATE TABLE page_views (
    view_id    BIGINT NOT NULL AUTO_INCREMENT,
    page_url   VARCHAR(500) NOT NULL,
    viewed_at  DATETIME NOT NULL,
    PRIMARY KEY (view_id)
) ENGINE=MyISAM;

-- Check which engine a table uses
SHOW TABLE STATUS LIKE 'orders';

-- Convert a table from MyISAM to InnoDB
ALTER TABLE page_views ENGINE=InnoDB;