Mysql_mariadb

Chapter 71: MySQL/MariaDB Administration - Deep Dive

Mastering Database Administration for Production Systems

71.1 Understanding MySQL/MariaDB

What is MySQL/MariaDB?

MySQL is the world’s most popular open-source relational database, now owned by Oracle. MariaDB is a drop-in replacement for MySQL, created by the original MySQL developers after Oracle’s acquisition, ensuring open-source continuity.

                     MySQL vs MariaDB
+------------------------------------------------------------------+
|                                                                   |
|    MySQL                                                            |
|    +----------------------------------------------------------+   |
|    |  - Owned by Oracle                                        |   |
|    |  - More enterprise features                               |   |
|    |  - Larger ecosystem                                      |   |
|    |  - Strict development process                            |   |
|    |  - Version: 8.0.x                                      |   |
|    +----------------------------------------------------------+   |
|                                                                   |
|    MariaDB                                                         |
|    +----------------------------------------------------------+   |
|    |  - Community-driven (MariaDB Foundation)                  |   |
|    |  - Drop-in replacement for MySQL                         |   |
|    |  - Faster development                                   |   |
|    |  - More storage engines                                  |   |
|    |  - Version: 10.11.x (LTS), 11.x                        |   |
|    +----------------------------------------------------------+   |
|                                                                   |
|    Compatibility:                                                  |
|    +----------------------------------------------------------+   |
|    |  MariaDB 10.x is binary compatible with MySQL 5.7-8.x   |   |
|    |  Most applications work without changes                  |   |
|    |  MySQL clients connect to MariaDB servers              |   |
|    +----------------------------------------------------------+   |
|                                                                   |
+------------------------------------------------------------------+

Database Architecture

                     MySQL Architecture
+------------------------------------------------------------------+
|                                                                   |
|                        Client Layer                                  |
|    +-------------------------------------------------------------+ |
|    |  - MySQL CLI (mysql client)                                | |
|    |  - MySQL Workbench                                        | |
|    |  - Connector/Drivers (JDBC, ODBC, Python, etc.)          | |
|    +-------------------------------------------------------------+ |
|                              |                                      |
|                              v                                      |
|                        Server Layer                                 |
|    +-------------------------------------------------------------+ |
|    |  +--------------------------------------------------+      | |
|    |  |            Connection Manager                     |      | |
|    |  +--------------------------------------------------+      | |
|    |                              |                            |      |
|    |                              v                            |      |
|    |  +--------------------------------------------------+      | |
|    |  |            Query Optimizer                        |      | |
|    |  |  - Parse tree                                     |      | |
|    |  |  - Query rewrite                                  |      | |
|    |  |  - Cost-based optimization                       |      | |
|    |  |  - Execution plan generation                     |      | |
|    |  +--------------------------------------------------+      | |
|    |                              |                            |      |
|    |                              v                            |      |
|    |  +--------------------------------------------------+      | |
|    |  |               Storage Engine Layer                |      | |
|    |  |  +----------+ +----------+ +----------+         |      | |
|    |  |  | InnoDB   | | MyISAM   | | Memory   |         |      | |
|    |  |  +----------+ +----------+ +----------+         |      | |
|    |  |  +----------+ +----------+ +----------+         |      | |
|    |  |  | Archive  | |  CSV     | |  Federated|         |      | |
|    |  |  +----------+ +----------+ +----------+         |      | |
|    |  +--------------------------------------------------+      | |
|    +-------------------------------------------------------------+ |
|                              |                                      |
|                              v                                      |
|                        Storage Layer                               |
|    +-------------------------------------------------------------+ |
|    |  - Data files (.ibd, .MYD)                                 | |
|    |  - Index files (.MYI)                                     | |
|    |  - Transaction logs (ib_logfile)                           | |
|    |  - Binary logs (binlog)                                   | |
|    +-------------------------------------------------------------+ |
|                                                                   |
+------------------------------------------------------------------+

71.2 Installation and Configuration

Installing MySQL/MariaDB

# =============================================================================
# ARCH LINUX
# =============================================================================

# Install MariaDB
sudo pacman -S mariadb

# Initialize database
sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql

# Start service
sudo systemctl enable --now mariadb

# Secure installation
sudo mysql_secure_installation

# =============================================================================
# UBUNTU/DEBIAN
# =============================================================================

# Install MySQL
sudo apt update
sudo apt install mysql-server

# Start service
sudo systemctl enable --now mysql
sudo systemctl status mysql

# Secure installation
sudo mysql_secure_installation

# Or install MariaDB
sudo apt install mariadb-server

# =============================================================================
# RHEL/CENTOS
# =============================================================================

# Install MariaDB
sudo dnf install mariadb-server

# Start service
sudo systemctl enable --now mariadb

# Secure installation
sudo mysql_secure_installation

Initial Configuration

# Connect to MySQL
sudo mysql -u root

# Create administrative user
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'SecurePassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

# Create application database
CREATE DATABASE myapp_production;
CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'AppPassword123!';
GRANT ALL PRIVILEGES ON myapp_production.* TO 'myapp'@'localhost';
FLUSH PRIVILEGES;

# Show grants
SHOW GRANTS FOR 'myapp'@'localhost';

# Exit
EXIT;

Configuration Files

# /etc/mysql/my.cnf (Debian/Ubuntu)
# /etc/my.cnf.d/server.cnf (RHEL/CentOS)

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
# Basic settings
port = 3306
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# InnoDB settings
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# Connection settings
max_connections = 200
wait_timeout = 600
interactive_timeout = 600

# Query cache (MariaDB 10.1 and older)
query_cache_type = 1
query_cache_size = 64M

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

# Binary logging (for replication)
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M

[mysql]
default-character-set = utf8mb4

71.3 Database Operations

Basic SQL Operations

-- ============================================================================
-- DATABASE MANAGEMENT
-- ============================================================================

-- Create database
CREATE DATABASE myapp_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Show databases
SHOW DATABASES;

-- Use database
USE myapp_production;

-- Drop database
DROP DATABASE myapp_production;

-- ============================================================================
-- TABLE MANAGEMENT
-- ============================================================================

-- 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,
    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_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Show tables
SHOW TABLES;

-- Describe table
DESCRIBE users;
SHOW CREATE TABLE users;

-- Alter table
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users MODIFY COLUMN username VARCHAR(100);

-- Add index
ALTER TABLE users ADD INDEX idx_created_at (created_at);

-- Drop table
DROP TABLE users;

-- ============================================================================
-- CRUD OPERATIONS
-- ============================================================================

-- INSERT
INSERT INTO users (username, email, password_hash)
VALUES ('john', 'john@example.com', '$2b$12$...');

INSERT INTO users (username, email, password_hash) VALUES
('alice', 'alice@example.com', 'hash1'),
('bob', 'bob@example.com', 'hash2');

-- SELECT
SELECT * FROM users;
SELECT username, email FROM users WHERE status = 'active';
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- JOIN
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.username
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC;

-- UPDATE
UPDATE users SET status = 'suspended' WHERE id = 1;
UPDATE users SET email = 'new@example.com' WHERE username = 'john';

-- DELETE
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE status = 'inactive' AND created_at < '2023-01-01';

71.4 User Management and Security

User Management

-- ============================================================================
-- USER CREATION
-- ============================================================================

-- Create user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'SecurePass123!';

-- Create user with specific privileges
CREATE USER 'app_readonly'@'%' IDENTIFIED BY 'ReadPass123!';
GRANT SELECT ON myapp_production.* TO 'app_readonly'@'%';

-- Create user for remote access
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'RemotePass123!';
GRANT ALL PRIVILEGES ON myapp_production.* TO 'app_user'@'192.168.1.%';

-- ============================================================================
-- PRIVILEGE MANAGEMENT
-- ============================================================================

-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_production.* TO 'app_user'@'localhost';
GRANT ALL PRIVILEGES ON myapp_production.* TO 'admin'@'localhost';
GRANT GRANT OPTION ON myapp_production.* TO 'admin'@'localhost';

-- Grant specific column privileges
GRANT SELECT (id, username, email), UPDATE (email) ON myapp_production.users TO 'app_user'@'localhost';

-- Grant routine privileges
GRANT EXECUTE ON PROCEDURE myapp_production.get_user_count TO 'app_user'@'localhost';

-- Show grants
SHOW GRANTS FOR 'app_user'@'localhost';

-- Revoke privileges
REVOKE INSERT, UPDATE, DELETE ON myapp_production.* FROM 'app_user'@'localhost';

-- ============================================================================
-- ROLE MANAGEMENT (MySQL 8.0+)
-- ============================================================================

-- Create role
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- Assign privileges to role
GRANT SELECT ON myapp_production.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_production.* TO 'app_write';
GRANT ALL PRIVILEGES ON myapp_production.* TO 'app_admin';

-- Assign role to user
GRANT 'app_read' TO 'developer'@'localhost';
SET DEFAULT ROLE 'app_read' FOR 'developer'@'localhost';

-- View roles
SHOW GRANTS FOR 'developer'@'localhost' USING 'app_read';

-- ============================================================================
-- PASSWORD MANAGEMENT
-- ============================================================================

-- Change password
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewPass123!';

-- Require password expiration
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE;

-- Lock/unlock user
ALTER USER 'app_user'@'localhost' ACCOUNT LOCK;
ALTER USER 'app_user'@'localhost' ACCOUNT UNLOCK;

-- ============================================================================
-- FLUSH PRIVILEGES
-- ============================================================================

FLUSH PRIVILEGES;

71.5 Backup and Restore

Backup Strategies

                     Backup Types
+------------------------------------------------------------------+
|                                                                   |
|    Logical Backup (mysqldump)                                      |
|    +----------------------------------------------------------+   |
|    |  Pros:                                                   |   |
|    |    - Human-readable SQL                                   |   |
|    |    - Portable across versions                            |   |
|    |    - Can be selective (specific tables/databases)        |   |
|    |                                                          |   |
|    |  Cons:                                                   |   |
|    |    - Slower for large databases                         |   |
|    |    - Requires more CPU and memory                       |   |
|    +----------------------------------------------------------+   |
|                                                                   |
|    Physical Backup (xtrabackup, mysqlpump)                        |
|    +----------------------------------------------------------+   |
|    |  Pros:                                                   |   |
|    |    - Faster for large databases                         |   |
|    |    - Complete backup including indexes                  |   |
|    |    - Can be used for incremental backups               |   |
|    |                                                          |   |
|    |  Cons:                                                   |   |
|    |    - Not human-readable                                 |   |
|    |    - Version-specific                                  |   |
|    +----------------------------------------------------------+   |
|                                                                   |
|    Snapshot Backup                                                |
|    +----------------------------------------------------------+   |
|    |  - Uses LVM or ZFS snapshots                           |   |
|    |  - Requires consistent filesystem                       |   |
|    |  - Very fast                                           |   |
|    |  - Best combined with xtrabackup                       |   |
|    +----------------------------------------------------------+   |
|                                                                   |
+------------------------------------------------------------------+

Backup Commands

# =============================================================================
# LOGICAL BACKUP (mysqldump)
# =============================================================================

# Backup single database
mysqldump -u root -p myapp_production > backup.sql

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

# Backup with drop and create statements
mysqldump -u root -p --add-drop-database --add-drop-table myapp_production > backup.sql

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

# Backup with transactions
mysqldump -u root -p --single-transaction --quick myapp_production > backup.sql

# Backup with compression
mysqldump -u root -p myapp_production | gzip > backup.sql.gz

# Backup with insert delays (for busy servers)
mysqldump -u root -p --lock-tables=false --single-transaction myapp_production > backup.sql

# =============================================================================
# MYSQLPUMP (Parallel backup - MySQL 5.7+)
# =============================================================================

# Parallel backup
mysqlpump -u root -p --parallel=4 --databases myapp_production > backup.sql

# Backup with exclusions
mysqlpump -u root -p --exclude-databases=mysql --databases myapp_production > backup.sql

# =============================================================================
# PHYSICAL BACKUP (xtrabackup)
# =============================================================================

# Install xtrabackup
sudo apt install xtrabackup

# Full backup
xtrabackup --backup --target-dir=/backup/full --user=root --password=secret

# Prepare backup (for restore)
xtrabackup --prepare --target-dir=/backup/full

# Incremental backup
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full --user=root

# Restore backup
xtrabackup --copy-back --target-dir=/backup/full --datadir=/var/lib/mysql

# =============================================================================
# RESTORE
# =============================================================================

# Restore logical backup
mysql -u root -p myapp_production < backup.sql

# Restore with compression
gunzip < backup.sql.gz | mysql -u root -p

# Restore specific database from full backup
mysql -u root -p myapp_production < full_backup.sql

# Restore to point in time (using binlog)
mysqlbinlog --stop-datetime="2024-01-01 12:00:00" mysql-bin.000001 | mysql -u root -p

71.6 Replication

Replication Types

                     MySQL Replication
+------------------------------------------------------------------+
|                                                                   |
|    Master-Slave Replication                                        |
|    +----------------------------------------------------------+   |
|    |                                                           |   |
|    |   Master                   Slave                         |   |
|    |   +--------+             +--------+                      |   |
|    |   | Write  |------------>| Read   |                      |   |
|    |   | Read   |             |        |                      |   |
|    |   +--------+             +--------+                      |   |
|    |       |                     |                            |   |
|    |       v                     v                            |   |
|    |   binlog                 relay-log                       |   |
|    |                                                           |   |
|    |  Use Cases:                                                |   |
|    |    - Read scaling                                         |   |
|    |    - Backup offloading                                    |   |
|    |    - Geographic distribution                               |   |
|    +----------------------------------------------------------+   |
|                                                                   |
|    Master-Master Replication                                        |
|    +----------------------------------------------------------+   |
|    |                                                           |   |
|    |   Master1 <-----------------> Master2                  |   |
|    |                                                           |   |
|    |  Use Cases:                                                |   |
|    |    - High availability                                    |   |
|    |    - Write offloading                                    |   |
|    |    - Auto-failover setup                                  |   |
|    +----------------------------------------------------------+   |
|                                                                   |
|    Galera Cluster (MariaDB)                                        |
|    +----------------------------------------------------------+   |
|    |                                                           |   |
|    |   Node1 <-----> Node2 <-----> Node3                    |   |
|    |                                                           |   |
|    |  Synchronous replication                                   |   |
|    |  Auto-node provisioning                                   |   |
|    |  Active-active multi-master                                |   |
|    +----------------------------------------------------------+   |
|                                                                   |
+------------------------------------------------------------------+

Replication Configuration

# Master configuration (my.cnf)
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_do_db = myapp_production
sync_binlog = 1

# Slave configuration (my.cnf)
[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin
log_bin = /var/log/mysql/mysql-bin.log
read_only = 1

-- On Master: Create replication user
CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- On Master: Get binlog position
SHOW MASTER STATUS;
-- Note: File = mysql-bin.000001, Position = 12345

-- On Slave: Configure master
CHANGE MASTER TO
    MASTER_HOST='master.example.com',
    MASTER_USER='repl',
    MASTER_PASSWORD='ReplPass123!',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=12345;

-- Start slave
START SLAVE;

-- Check slave status
SHOW SLAVE STATUS\G

-- Verify replication
SHOW PROCESSLIST\G

71.7 Performance Tuning

Query Optimization

-- ============================================================================
-- EXPLAIN - Analyze Query Execution
-- ============================================================================

EXPLAIN SELECT * FROM users WHERE username = 'john';
EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'active';

-- ============================================================================
-- INDEX MANAGEMENT
-- ============================================================================

-- Create index
CREATE INDEX idx_status_created ON users(status, created_at);
CREATE UNIQUE INDEX idx_username ON users(username);

-- Drop index
DROP INDEX idx_username ON users;

-- Show indexes
SHOW INDEX FROM users;

-- ============================================================================
-- QUERY OPTIMIZATION TIPS
-- ============================================================================

-- Use EXPLAIN to analyze slow queries
EXPLAIN SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id;

-- Avoid SELECT *
SELECT id, username, email FROM users;

-- Use LIMIT for pagination
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 100;

-- Use covering indexes
CREATE INDEX idx_user_status_covering ON users(status) INCLUDE (username, email);

-- Avoid functions on indexed columns (prevents index usage)
-- BAD: WHERE YEAR(created_at) = 2024
-- GOOD: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

Server Tuning

# /etc/mysql/my.cnf - Performance settings

[mysqld]
# InnoDB settings
innodb_buffer_pool_size = 2G  # 70-80% of RAM
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1  # 1 = safe, 2 = fast
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

# Connection settings
max_connections = 300
thread_cache_size = 50
table_open_cache = 4000

# Query cache (MariaDB)
query_cache_type = 1
query_cache_size = 128M

# Temp tables
tmp_table_size = 256M
max_heap_table_size = 256M

# Sort buffer
sort_buffer_size = 4M
join_buffer_size = 4M

# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

# Max allowed packet
max_allowed_packet = 64M

71.8 Monitoring and Maintenance

Monitoring Queries

-- ============================================================================
# CONNECTION STATUS
-- ============================================================================

-- Show status
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- Current connections
SELECT * FROM information_schema.PROCESSLIST;

-- Slow queries
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- ============================================================================
# TABLE STATISTICS
-- ============================================================================

-- Check table size
SELECT
    table_name AS 'Table',
    ROUND(data_length / 1024 / 1024, 2) AS 'Size (MB)',
    table_rows AS 'Rows'
FROM information_schema.tables
WHERE table_schema = 'myapp_production'
ORDER BY data_length DESC;

-- Check index usage
SELECT
    t.TABLE_NAME,
    i.INDEX_NAME,
    i.CARDINALITY
FROM information_schema.STATISTICS t
JOIN information_schema.STATISTICS i
    ON t.TABLE_NAME = i.TABLE_NAME
WHERE t.table_schema = 'myapp_production';

-- ============================================================================
# INNODB STATUS
-- ============================================================================

SHOW ENGINE INNODB STATUS;

-- Buffer pool
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Transaction status
SELECT * FROM information_schema.INNODB_TRX;

Maintenance Commands

-- Analyze tables (update statistics)
ANALYZE TABLE users;

-- Optimize tables (defragment)
OPTIMIZE TABLE users;

-- Check tables for errors
CHECK TABLE users;

-- Repair tables
REPAIR TABLE users;

-- Flush tables
FLUSH TABLES;

-- Reset slave
RESET SLAVE ALL;

71.9 Exam Tips

Storage Engines: InnoDB is default, supports transactions
Character Set: Use utf8mb4 for full Unicode support
Backup: mysqldump for logical, xtrabackup for physical
Replication: Master-Slave for read scaling
Security: Create users with least privilege
Performance: Use EXPLAIN for query analysis
Indexing: Create indexes for WHERE and JOIN columns
Configuration: innodb_buffer_pool_size = 70% RAM
Logging: Enable slow_query_log for debugging
Maintenance: Regular ANALYZE and OPTIMIZE tables

Summary

In this chapter, you learned:

✅ MySQL/MariaDB architecture
✅ Installation and configuration
✅ Database and table operations
✅ User management and security
✅ Backup and restore strategies
✅ Replication configuration
✅ Performance tuning
✅ Monitoring and maintenance

Next Chapter

Chapter 72: PostgreSQL Administration

Last Updated: February 2026