MySQL – World’s Most Popular Open Source Relational Database

What is MySQL?

MySQL is the world’s most widely used open-source relational database management system (RDBMS), powering everything from small websites to massive web applications and enterprise systems. Originally developed by MySQL AB, now owned by Oracle Corporation, MySQL has earned its reputation through reliability, performance, and ease of use over more than two decades of active development.

As a relational database, MySQL organizes data into structured tables with defined relationships, using SQL (Structured Query Language) for data manipulation and queries. This structured approach ensures data integrity, supports complex queries, and enables ACID-compliant transactions that guarantee reliable data processing.

MySQL serves as the database layer for countless popular platforms including WordPress, Drupal, Facebook, Twitter, YouTube, and Netflix. Its combination of open-source accessibility, commercial support options, and proven scalability makes it suitable for projects ranging from personal blogs to high-traffic global applications.

Key Features and Capabilities

ACID Compliance

MySQL’s InnoDB storage engine provides full ACID (Atomicity, Consistency, Isolation, Durability) compliance, ensuring transaction reliability. This guarantees that database operations either complete entirely or not at all, maintaining data integrity even in the event of system failures.

Multiple Storage Engines

MySQL supports pluggable storage engines, each optimized for different use cases. InnoDB provides ACID compliance and row-level locking for transactional workloads, while MyISAM offers fast read performance for data warehousing. Other engines include Memory, Archive, and NDB for clustering.

Replication

Built-in replication capabilities enable high availability and read scaling. Master-slave replication distributes read load across multiple servers, while Group Replication provides multi-master clustering with automatic failover and conflict resolution.

Partitioning

Table partitioning improves query performance and maintenance for large tables by dividing data into smaller, more manageable pieces based on range, list, hash, or key values. Partitioning enables parallel query execution and simplified data archiving.

Security Features

Comprehensive security features include authentication plugins, SSL/TLS encryption for connections, role-based access control, data masking, and audit logging. Enterprise Edition adds additional security certifications and advanced authentication options.

Full-Text Search

Built-in full-text search capabilities enable efficient text searching with natural language mode, boolean mode, and query expansion. Full-text indexes support relevance ranking and stopword customization.

System Requirements

Hardware Requirements

MySQL runs efficiently on modest hardware for development, requiring minimum 512 MB RAM and 1 GB disk space. Production deployments typically need 4-8 GB RAM minimum, with SSD storage strongly recommended for optimal performance.

Operating System Support

MySQL supports Windows 10/Server 2016+, Ubuntu 18.04+, Debian 10+, RHEL/CentOS 7+, SUSE Linux Enterprise 12+, and macOS 10.15+. FreeBSD and Solaris are also supported.

Installation Guide

Installing on Ubuntu/Debian

# Update package index
sudo apt update

# Install MySQL server
sudo apt install mysql-server

# Start MySQL service
sudo systemctl start mysql

# Enable on boot
sudo systemctl enable mysql

# Run security script
sudo mysql_secure_installation

# Connect to MySQL
sudo mysql

# Create user with password authentication
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;

Installing on RHEL/CentOS

# Add MySQL repository
sudo dnf install https://dev.mysql.com/get/mysql80-community-release-el8-4.noarch.rpm

# Install MySQL server
sudo dnf install mysql-community-server

# Start MySQL
sudo systemctl start mysqld
sudo systemctl enable mysqld

# Get temporary root password
sudo grep 'temporary password' /var/log/mysqld.log

# Secure installation
sudo mysql_secure_installation

Installing on macOS

# Install using Homebrew
brew install mysql

# Start MySQL service
brew services start mysql

# Secure installation
mysql_secure_installation

# Or download DMG from dev.mysql.com

Installing on Windows

# Download MySQL Installer from dev.mysql.com
# Run installer and select components

# Or use Chocolatey
choco install mysql

# Or use Winget
winget install Oracle.MySQL

Docker Installation

# Pull MySQL image
docker pull mysql:8.0

# Run MySQL container
docker run -d \
  --name mysql \
  -p 3306:3306 \
  -v mysql_data:/var/lib/mysql \
  -e MYSQL_ROOT_PASSWORD=rootpassword \
  -e MYSQL_DATABASE=myapp \
  -e MYSQL_USER=appuser \
  -e MYSQL_PASSWORD=apppassword \
  mysql:8.0

# Connect to MySQL
docker exec -it mysql mysql -u root -p

Essential MySQL Commands

Connection and Basic Operations

# Connect to MySQL
mysql -u username -p

# Connect to specific database
mysql -u username -p database_name

# Connect to remote server
mysql -h hostname -u username -p

# Show databases
SHOW DATABASES;

# Create database
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Use database
USE myapp;

# Drop database
DROP DATABASE myapp;

# Show tables
SHOW TABLES;

# Describe table structure
DESCRIBE users;
SHOW CREATE TABLE users;

Table Operations

# Create table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    INDEX idx_email (email),
    INDEX idx_username (username)
) ENGINE=InnoDB;

# Create table with foreign key
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

# Alter table
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN phone VARCHAR(25);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ADD INDEX idx_created (created_at);

# Rename table
RENAME TABLE users TO customers;

# Drop table
DROP TABLE IF EXISTS users;

CRUD Operations

# Insert single row
INSERT INTO users (username, email, password_hash, first_name, last_name)
VALUES ('johndoe', 'john@example.com', 'hashed_password', 'John', 'Doe');

# Insert multiple rows
INSERT INTO users (username, email, password_hash) VALUES
    ('user1', 'user1@example.com', 'hash1'),
    ('user2', 'user2@example.com', 'hash2'),
    ('user3', 'user3@example.com', 'hash3');

# Select all columns
SELECT * FROM users;

# Select specific columns
SELECT id, username, email FROM users;

# Select with conditions
SELECT * FROM users WHERE is_active = TRUE AND created_at > '2024-01-01';

# Select with pattern matching
SELECT * FROM users WHERE email LIKE '%@example.com';

# Select with sorting
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

# Select with aggregation
SELECT COUNT(*) as total_users FROM users;
SELECT status, COUNT(*) as count FROM orders GROUP BY status;

# Update rows
UPDATE users SET is_active = FALSE WHERE id = 5;
UPDATE users SET first_name = 'Jane', last_name = 'Smith' WHERE email = 'jane@example.com';

# Delete rows
DELETE FROM users WHERE id = 10;
DELETE FROM users WHERE is_active = FALSE AND created_at < '2023-01-01';

Joins and Subqueries

# Inner join
SELECT u.username, o.id as order_id, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

# Left join
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

# Multiple joins
SELECT u.username, o.id, p.name as product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

# Subquery in WHERE
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 100);

# Subquery in FROM
SELECT avg_order.user_id, avg_order.avg_amount
FROM (
    SELECT user_id, AVG(total_amount) as avg_amount
    FROM orders
    GROUP BY user_id
) as avg_order
WHERE avg_order.avg_amount > 50;

# EXISTS subquery
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Indexing Strategies

Index Types and Usage

# Create index
CREATE INDEX idx_email ON users(email);

# Create unique index
CREATE UNIQUE INDEX idx_unique_username ON users(username);

# Create composite index
CREATE INDEX idx_name ON users(last_name, first_name);

# Create full-text index
CREATE FULLTEXT INDEX idx_content ON articles(title, content);

# Show indexes
SHOW INDEX FROM users;

# Analyze query performance
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

# Drop index
DROP INDEX idx_email ON users;

# Force index usage
SELECT * FROM users FORCE INDEX (idx_email) WHERE email = 'test@example.com';

User and Permission Management

User Administration

# Create user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'password';

# Grant privileges
GRANT ALL PRIVILEGES ON myapp.* TO 'newuser'@'localhost';
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'readwrite_user'@'localhost';
GRANT SELECT ON myapp.* TO 'readonly_user'@'localhost';

# Grant specific table permissions
GRANT SELECT, INSERT ON myapp.users TO 'limited_user'@'localhost';

# View user privileges
SHOW GRANTS FOR 'newuser'@'localhost';

# Revoke privileges
REVOKE INSERT, UPDATE ON myapp.* FROM 'newuser'@'localhost';

# Change password
ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'new_password';

# Delete user
DROP USER 'newuser'@'localhost';

# Flush privileges
FLUSH PRIVILEGES;

Transactions

Transaction Management

# Start transaction
START TRANSACTION;

# Perform operations
INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

# Commit transaction
COMMIT;

# Or rollback on error
ROLLBACK;

# Set autocommit
SET autocommit = 0;  -- Disable
SET autocommit = 1;  -- Enable

# Savepoints
START TRANSACTION;
INSERT INTO orders (user_id, total_amount) VALUES (1, 100);
SAVEPOINT order_created;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 1, 2);
-- If error occurs:
ROLLBACK TO order_created;
-- Continue or:
COMMIT;

Backup and Restore

Backup Commands

# Backup single database
mysqldump -u root -p myapp > myapp_backup.sql

# Backup with compression
mysqldump -u root -p myapp | gzip > myapp_backup.sql.gz

# Backup all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Backup specific tables
mysqldump -u root -p myapp users orders > tables_backup.sql

# Backup structure only
mysqldump -u root -p --no-data myapp > myapp_structure.sql

# Backup data only
mysqldump -u root -p --no-create-info myapp > myapp_data.sql

# Backup with routines and triggers
mysqldump -u root -p --routines --triggers myapp > myapp_full.sql

# Restore database
mysql -u root -p myapp < myapp_backup.sql

# Restore compressed backup
gunzip < myapp_backup.sql.gz | mysql -u root -p myapp

Performance Optimization

Configuration Tuning

# Key my.cnf settings
[mysqld]
# InnoDB buffer pool (50-80% of RAM)
innodb_buffer_pool_size = 4G

# Query cache (consider disabling in MySQL 8.0)
query_cache_type = 0

# Connection limits
max_connections = 200
wait_timeout = 600

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# InnoDB settings
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1

# Thread settings
thread_cache_size = 16

Query Optimization

# Analyze table statistics
ANALYZE TABLE users;

# Optimize table
OPTIMIZE TABLE users;

# Check table integrity
CHECK TABLE users;

# Profile query
SET profiling = 1;
SELECT * FROM users WHERE email = 'test@example.com';
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

Replication Setup

Master-Slave Configuration

# On Master - my.cnf
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW

# Create replication user
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

# Get master status
SHOW MASTER STATUS;

# On Slave - my.cnf
[mysqld]
server-id = 2
relay_log = relay-bin
read_only = 1

# Configure slave
CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

# Start replication
START SLAVE;

# Check slave status
SHOW SLAVE STATUS\G

MySQL Tools

MySQL Workbench

MySQL Workbench provides a visual interface for database design, development, and administration. Features include schema design with EER diagrams, SQL development with query editor, server administration, data migration, and performance monitoring.

Command-Line Utilities

mysql          - Command-line client
mysqldump      - Backup utility
mysqlimport    - Data import tool
mysqladmin     - Administration utility
mysqlcheck     - Table maintenance
mysqlshow      - Display database information
mysqlslap      - Load testing tool

Conclusion

MySQL remains the go-to choice for relational database needs, combining proven reliability with modern features. Its extensive ecosystem of tools, widespread community support, and compatibility with virtually every programming language and platform ensure its continued relevance.

Whether building a simple web application or architecting a distributed database system, MySQL provides the foundation for reliable, performant data storage and retrieval.