← Back to Frameworks

MySQL

World's most popular open source database. Features extensive web development track record, high performance, and simple management. Rich replication capabilities.

Database ServerRDBMSSQLOpen SourceHigh PerformanceScalableClusteringReplication

Database Server

MySQL

Overview

MySQL is the world's most popular open source relational database management system (RDBMS). Known for its high performance, reliability, and ease of use, MySQL is widely utilized across web applications and large-scale enterprise systems. With over 25 years of development history, MySQL has established itself as the de facto standard for web development and is trusted by millions of developers worldwide.

Details

MySQL 2025 edition continues to maintain its position as the leading open source database solution. The system features a pluggable storage engine architecture that allows developers to choose the optimal storage engine for specific use cases. The default InnoDB storage engine provides ACID compliance, Multi-Version Concurrency Control (MVCC), and advanced transaction management. MySQL 8.0 introduces significant performance improvements including enhanced optimizer statistics, improved JSON support, and Common Table Expressions (CTEs). The system supports various replication topologies including master-slave replication, Group Replication for automatic failover, and advanced security features like transparent data encryption (TDE) and row-level security.

Key Features

  • Pluggable Storage Engine Architecture: Multiple storage engines optimized for different use cases
  • ACID Compliance: Full transactional support with InnoDB storage engine
  • High Performance: Optimized query execution and advanced indexing strategies
  • Scalability: Support for clustering, partitioning, and horizontal scaling
  • Replication: Master-slave replication, Group Replication, and binary logging
  • Security: Transparent data encryption, SSL/TLS, and comprehensive access control

Pros and Cons

Pros

  • Overwhelming market share and mature ecosystem with extensive community support
  • Excellent performance for read-heavy workloads and web applications
  • Rich documentation, learning resources, and third-party tools availability
  • Strong integration with popular web frameworks and programming languages
  • Proven scalability in high-traffic environments (Facebook, Twitter, YouTube)
  • Cost-effective solution with dual licensing options (GPL and commercial)

Cons

  • Complex configuration for optimal performance tuning in enterprise environments
  • Limited support for advanced SQL features compared to PostgreSQL
  • Version 8.0 support ending in April 2025 requiring urgent migration planning
  • Oracle's commercial control may impact future open source development direction
  • Replication lag can occur under heavy write loads without proper configuration
  • Some storage engines lack full ACID compliance (MyISAM, Memory)

Reference Pages

Code Examples

Installation and Initial Setup

# Ubuntu/Debian installation
sudo apt update
sudo apt install mysql-server mysql-client

# Start MySQL service
sudo systemctl start mysql
sudo systemctl enable mysql

# Secure installation
sudo mysql_secure_installation

# CentOS/RHEL installation
sudo dnf install mysql-server mysql
sudo systemctl start mysqld
sudo systemctl enable mysqld

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

# Docker installation
docker run --name mysql-server \
  -e MYSQL_ROOT_PASSWORD=my-secret-pw \
  -e MYSQL_DATABASE=myapp \
  -e MYSQL_USER=appuser \
  -e MYSQL_PASSWORD=apppass \
  -p 3306:3306 \
  -d mysql:8.0

# Connect to MySQL
mysql -u root -p

Database and User Management

-- Create database with UTF-8 support
CREATE DATABASE myapp 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- Create user and grant privileges
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'secure_password';
CREATE USER 'appuser'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

-- Show databases and users
SHOW DATABASES;
SELECT User, Host FROM mysql.user;

-- Change user password
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'new_password';

-- Remove user
DROP USER 'appuser'@'localhost';

-- Database backup
mysqldump -u root -p --single-transaction --routines --triggers myapp > backup.sql

-- Database restore
mysql -u root -p myapp < backup.sql

Table Creation and Management

-- Create table with various data types
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    profile JSON,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_email (email),
    INDEX idx_username (username),
    INDEX idx_created_at (created_at),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create table with foreign key
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    slug VARCHAR(255) UNIQUE,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_published_at (published_at),
    FULLTEXT KEY ft_title_content (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add column to existing table
ALTER TABLE users ADD COLUMN last_login TIMESTAMP NULL;

-- Create index
CREATE INDEX idx_users_last_login ON users(last_login);

-- Show table structure
DESCRIBE users;
SHOW CREATE TABLE users;

Advanced Queries and JSON Operations

-- Insert data with JSON
INSERT INTO users (username, email, first_name, last_name, profile) VALUES
('john_doe', '[email protected]', 'John', 'Doe', 
 '{"age": 30, "city": "New York", "skills": ["MySQL", "PHP", "JavaScript"], "preferences": {"theme": "dark", "notifications": true}}'),
('jane_smith', '[email protected]', 'Jane', 'Smith',
 '{"age": 28, "city": "Los Angeles", "skills": ["Python", "MySQL", "React"], "preferences": {"theme": "light", "notifications": false}}');

-- JSON queries
SELECT username, profile->>'$.age' as age, profile->>'$.city' as city
FROM users 
WHERE JSON_EXTRACT(profile, '$.age') > 25;

-- JSON array operations
SELECT username, 
       JSON_EXTRACT(profile, '$.skills') as skills,
       JSON_CONTAINS(profile, '"MySQL"', '$.skills') as knows_mysql
FROM users
WHERE JSON_CONTAINS(profile, '"MySQL"', '$.skills');

-- Update JSON data
UPDATE users 
SET profile = JSON_SET(profile, '$.last_login', NOW(), '$.login_count', 
                      COALESCE(JSON_EXTRACT(profile, '$.login_count'), 0) + 1)
WHERE username = 'john_doe';

-- Complex JSON aggregation
SELECT 
    profile->>'$.city' as city,
    COUNT(*) as user_count,
    AVG(CAST(profile->>'$.age' AS UNSIGNED)) as avg_age,
    JSON_ARRAYAGG(username) as usernames
FROM users 
WHERE profile->>'$.city' IS NOT NULL
GROUP BY profile->>'$.city';

-- Full-text search
SELECT *, MATCH(title, content) AGAINST('MySQL database performance' IN NATURAL LANGUAGE MODE) as score
FROM posts
WHERE MATCH(title, content) AGAINST('MySQL database performance' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

-- Boolean full-text search
SELECT * FROM posts
WHERE MATCH(title, content) AGAINST('+MySQL +performance -slow' IN BOOLEAN MODE);

Performance Optimization and Indexing

-- Analyze query performance
EXPLAIN FORMAT=JSON 
SELECT u.username, p.title, p.created_at
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.status = 'active' 
AND p.published_at > '2024-01-01'
ORDER BY p.created_at DESC;

-- Create composite index
CREATE INDEX idx_posts_user_published ON posts(user_id, published_at);

-- Create partial index (MySQL 8.0.13+)
CREATE INDEX idx_active_users ON users(username) WHERE status = 'active';

-- Analyze table statistics
ANALYZE TABLE users, posts;

-- Show index usage
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    INDEX_TYPE
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'myapp'
ORDER BY TABLE_NAME, INDEX_NAME;

-- Query performance monitoring
SELECT 
    query_sample_text,
    exec_count,
    total_latency,
    avg_latency,
    rows_examined_avg
FROM sys.statements_with_full_table_scans 
ORDER BY total_latency DESC 
LIMIT 10;

-- Connection and process monitoring
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    LEFT(INFO, 100) as QUERY
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;

Replication Setup and Management

-- Master server configuration (my.cnf)
/*
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
binlog_do_db = myapp
expire_logs_days = 7
*/

-- Create replication user on master
CREATE USER 'replicator'@'%' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

-- Check master status
SHOW MASTER STATUS;

-- Slave server configuration (my.cnf)
/*
[mysqld]
server-id = 2
relay-log = relay-bin
read_only = 1
*/

-- Configure slave
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.10',
    MASTER_USER = 'replicator',
    MASTER_PASSWORD = 'replica_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

-- Start replication
START SLAVE;

-- Check replication status
SHOW SLAVE STATUS\G

-- Group Replication setup
SET GLOBAL group_replication_group_name = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
SET GLOBAL group_replication_start_on_boot = OFF;
SET GLOBAL group_replication_local_address = '192.168.1.10:33061';
SET GLOBAL group_replication_group_seeds = '192.168.1.10:33061,192.168.1.11:33061,192.168.1.12:33061';

-- Start Group Replication
START GROUP_REPLICATION;

-- Check Group Replication status
SELECT * FROM performance_schema.replication_group_members;

Backup and Recovery Strategies

# Logical backup with mysqldump
mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --all-databases > full_backup.sql

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

# Compressed backup
mysqldump -u root -p --single-transaction myapp | gzip > myapp_backup.sql.gz

# Backup with binlog position
mysqldump -u root -p \
  --single-transaction \
  --master-data=2 \
  --flush-logs \
  myapp > myapp_with_binlog.sql

# Physical backup with MySQL Enterprise Backup
mysqlbackup --user=root --password=password \
  --backup-dir=/backup/full \
  backup-and-apply-log

# Point-in-time recovery
mysqlbinlog --start-datetime="2024-01-15 09:00:00" \
           --stop-datetime="2024-01-15 10:00:00" \
           mysql-bin.000001 | mysql -u root -p myapp

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

# Incremental backup using binlog
mysqlbinlog --start-position=154 mysql-bin.000001 > incremental.sql
mysql -u root -p myapp < incremental.sql