Domain 8Data InfrastructureINTERMEDIATE

Mastering MySQL and MariaDB Administration: Architecture, Operations, and Troubleshooting for SREs

A comprehensive operational guide for Linux SysAdmins and SREs covering MySQL and MariaDB architecture, configuration management, replication topology, backups, CLI administration, performance tuning, and systematic troubleshooting.

Domain: Domain 8: Data Infrastructure•Updated: 2026-11-03
#linux#sysadmin#mysql#mariadb#database#replication

1. Overview

MySQL and MariaDB form the backbone of modern relational database infrastructure across enterprise Linux environments. Both systems store relational data organized into tables and schemas, utilizing InnoDB as the default ACID-compliant transactional storage engine. Applications interact with the database over TCP (listening by default on port 3306) or through local Unix domain sockets using native database drivers or standard command-line tools such as the mysql client.

MariaDB was created as a community-driven fork of MySQL following Oracle’s acquisition of Sun Microsystems. It maintains high operational compatibility with MySQL—most command-line options, configuration parameter concepts, replication topology mechanisms, and troubleshooting workflows apply interchangeably to both systems. However, package naming and default distributions vary across Linux platforms: Debian and Ubuntu distributions typically ship mariadb-server as the default database package, whereas RHEL, Rocky Linux, and standard cloud provider base images often package Oracle MySQL or MariaDB explicitly depending on repository configurations.

Use Cases and Role in Linux/SRE Architecture

In production Linux deployments, MySQL and MariaDB serve as stateful data tiers for web applications, microservices, enterprise content management systems, and financial ledgers. Within a Site Reliability Engineering (SRE) context, these databases fit into multi-tier architectures where application servers scale horizontally while the database layer achieves scaling and high availability through read replication, connection pooling, or clustered multi-primary topologies.

Key Advantages and Limitations

  • Advantages: High performance for read-heavy and transactional OLTP workloads, mature tooling ecosystem, robust crash recovery via write-ahead logging (redo log), flexible replication paradigms (asynchronous, semi-synchronous, synchronous clustering), and wide distribution adoption.
  • Limitations: Horizontally scaling writes across non-clustered nodes is complex; schema alteration (DDL) on large tables requires careful online DDL management to avoid table locking; asynchronous replication topologies introduce risks of replication lag and data divergence if failovers are unmanaged.

Basic Practical Example

Connecting locally via socket and querying basic server information demonstrates the interaction model:

# Connect using the command-line interface as the root administrative user
sudo mysql -u root -p -e "SHOW VARIABLES LIKE 'version';"

2. Architecture and Mental Model

Understanding the internal architecture of MySQL and MariaDB is essential for diagnosing performance bottlenecks, connection exhaustion, locking conflicts, and replication failures.

The Request and Connection Lifecycle

When a client application executes an SQL query, the interaction follows a strict sequential lifecycle across the server’s internal components:

  1. TCP/Socket Connect: The client initiates a TCP handshake on port 3306 (or connects directly via Unix socket file /var/run/mysqld/mysqld.sock for local connections).
  2. Authentication: The server’s thread manager receives the connection and validates the client credentials against the internal mysql.user privilege table, matching both username and connecting host IP pattern.
  3. Session Allocation: Upon successful authentication, a dedicated worker thread is assigned to the session. Session parameters—such as SQL_MODE, default character set, and transaction isolation level—are negotiated.
  4. Parsing and Optimization: The SQL parser validates statement syntax and generates a logical syntax tree. The Query Optimizer calculates the most efficient execution path based on table statistics, deciding index usage and table join order.
  5. Storage Engine Execution: The SQL engine invokes the storage engine interface (InnoDB). InnoDB retrieves target data pages into the Buffer Pool, acquires necessary row or table locks, and modifies data within memory while recording changes to the Redo Log to ensure durability.
  6. Result Return: The generated result set is streamed back across the network buffer to the client. The connection either terminates or returns to an active state in the application’s connection pool.
graph TD
    Client[Client Application / CLI Client] -->|TCP:3306 or Socket| Listener[Connection Listener / Thread Pool]
    Listener --> Auth{Authentication against mysql.user}
    Auth -->|Failed| AccessDenied[Return Access Denied Error]
    Auth -->|Success| SessionThread[Assign Session Thread]
    SessionThread --> Parser[SQL Parser & Syntax Validator]
    Parser --> Optimizer[Query Optimizer]
    Optimizer --> StorageEngine[InnoDB Storage Engine Interface]
    StorageEngine <--> BufferPool[InnoDB Buffer Pool RAM]
    BufferPool <--> DataFiles[Disk: Data Files /var/lib/mysql]
    StorageEngine --> RedoLog[Disk: Redo Log ib_logfile]
    StorageEngine --> Binlog[Disk: Binary Log binlog.XXXXXX]
    StorageEngine --> Result[Return Result Set to Client]

Internal Subsystems and Storage Engine Components

  • Connection Pool & Thread Handler: Manages incoming client connections. To prevent overhead from thread creation, connection pooling or internal thread caching (thread_cache_size) is utilized.
  • InnoDB Buffer Pool: The main memory region where InnoDB caches table data and indexes. Reads and writes operate on memory pages inside the buffer pool; dirty pages are periodically flushed to disk.
  • Redo Log (ib_logfile*): A disk-based write-ahead log used during crash recovery. Transactions are written sequentially to the redo log before dirty pages are committed to primary data files, guaranteeing ACID durability.
  • Undo Log: Maintains historical snapshots of modified data pages. It supports multi-version concurrency control (MVCC) to provide non-blocking read operations and enables transaction rollback.
  • Binary Log (Binlog): A record of all DDL and DML operations modifying database structure or content. Binlogs are critical for point-in-time recovery (PITR) and binary log-based replication topologies.

3. Core Concepts and Terminology

User Account Specification (user@host)

In MySQL and MariaDB, access control accounts are explicitly composed of a username and a host restriction (e.g., 'app_user'@'10.0.1.%'). A user connecting from IP 10.0.1.5 matches this pattern, but a connection from 10.0.2.5 will be rejected even if the password is correct.

InnoDB ACID Transactions and Row-Level Locking

InnoDB provides full ACID (Atomicity, Consistency, Isolation, Durability) guarantees. Unlike legacy engines (e.g., MyISAM) that lock entire table files during write operations, InnoDB utilizes row-level locking combined with MVCC. Locks are acquired on index records corresponding to target rows. If a query updates rows without using an indexed lookup, InnoDB may scan and lock extended ranges or the entire table, leading to lock contention.

Read-Only Topologies (read_only vs super_read_only)

To prevent accidental data divergence on replication read replicas, databases are configured with read-only protections:

  • read_only = 1: Prevents non-administrative users (accounts without the SUPER or SYSTEM_VARIABLES_ADMIN privilege) from performing write operations.
  • super_read_only = 1: Restricts all users, including root and accounts with SUPER privileges, from modifying data (replication applier threads are exempted).

Technical Glossary

Term Definition Operational Significance
InnoDB Default transactional storage engine supporting ACID and row locking. Handles memory caching, crash recovery, and data durability.
Binlog (Binary Log) Append-only log file recording data modification events. Required for master-replica replication and point-in-time recovery.
GTID Global Transaction Identifier assigned uniquely across a replication topology. Simplifies replica tracking and failover without manual file/position offsets.
Buffer Pool Memory allocation caching disk data and indexes in RAM. Primary driver of database memory utilization and query performance.
Slow Query Log Diagnostic log capturing queries whose execution duration exceeds long_query_time. Primary tool for pinpointing unindexed or unoptimized SQL queries.

4. Installation and Version Management

Installing MariaDB and MySQL Across Linux Distributions

Debian / Ubuntu (Defaulting to MariaDB)

# Update repository index and install MariaDB server and client packages
sudo apt update
sudo apt install -y mariadb-server mariadb-client

# Alternative: Install Oracle MySQL Server (if official MySQL repos are configured)
# sudo apt install -y mysql-server

RHEL / Rocky Linux / AlmaLinux

# Install MariaDB server on Enterprise Linux distributions
sudo dnf install -y mariadb-server mariadb

# Enable and start the database daemon
sudo systemctl enable --now mariadb

Verifying Installation and Environment Layout

Verify that the binary executable is available and determine the installed version:

# Inspect installed software version
mysql --version

# Ping the database service to verify daemon responsiveness
mysqladmin -u root ping

Checking Software Installation Paths

  • Executable Binaries: /usr/bin/mysql, /usr/sbin/mysqld
  • Systemd Unit File: /lib/systemd/system/mariadb.service or /lib/systemd/system/mysql.service
  • Primary Configuration File: /etc/mysql/my.cnf or /etc/my.cnf
  • Default Base Data Directory: /var/lib/mysql/

5. Configuration Files and Directory Structure

MySQL and MariaDB rely on a structured hierarchy of configuration files that are read sequentially upon daemon startup.

Configuration Directory Reference

File / Directory Purpose Distribution / Context
/etc/my.cnf Global primary configuration file. RHEL / CentOS / Fedora standard.
/etc/mysql/my.cnf Global umbrella configuration file. Debian / Ubuntu standard.
/etc/mysql/mariadb.conf.d/ MariaDB modular configuration directory. Debian/Ubuntu MariaDB specific (50-server.cnf).
/etc/mysql/mysql.conf.d/ MySQL modular configuration directory. Debian/Ubuntu Oracle MySQL specific (mysqld.cnf).
/var/lib/mysql/ Primary data directory holding schemas, tables, redo logs. Standard across all distributions.
/var/log/mysql/ Default directory for error logs and slow query logs. Standard log path (error.log).
/var/run/mysqld/ Runtime directory storing PID file and socket. Default path for mysqld.sock.

Important Configuration Parameters (mysqld)

The following annotated configuration snippet highlights essential production parameters:

[mysqld]
# Basic Networking and Bindings
user                    = mysql
pid-file                = /var/run/mysqld/mysqld.sock.pid
socket                  = /var/run/mysqld/mysqld.sock
port                    = 3306
bind-address            = 0.0.0.0

# Resource Allocation & Connections
max_connections         = 500
thread_cache_size       = 32
max_allowed_packet      = 64M

# InnoDB Storage Engine Optimizations
default_storage_engine  = InnoDB
innodb_buffer_pool_size = 4G         # Set to ~70-80% of total system RAM on dedicated DB nodes
innodb_log_file_size    = 512M       # Size of each redo log file
innodb_flush_log_at_trx_commit = 1   # 1 = Full ACID compliance (flushes to disk on every commit)

# Logging and Diagnostics
log_error               = /var/log/mysql/error.log
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mariadb-slow.log
long_query_time         = 2.0        # Log queries executing longer than 2 seconds

# Replication & Binary Logging Configurations
server-id               = 101        # Unique integer ID across replication topology
log_bin                 = /var/lib/mysql/mysql-bin
binlog_format           = ROW        # ROW format recommended for consistent replication
gtid_mode               = ON         # Enable GTID replication (MySQL specific)
enforce_gtid_consistency = ON        # Enforce GTID safety (MySQL specific)

Validating Configuration Changes

Before restarting production instances, validate configuration changes to avoid service start failures:

# Test configuration syntax (MySQL 8.0+)
mysqld --validate-config

# Check effective variable values on a running server
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

6. Service Management and Common Operations

Management of the database daemon is executed via systemd and administrative utility binaries.

Service Management Commands

# Check service status (Debian/Ubuntu MariaDB)
systemctl status mariadb

# Check service status (Debian/Ubuntu Oracle MySQL)
systemctl status mysql

# Start, Stop, and Restart Service
sudo systemctl start mariadb
sudo systemctl stop mariadb
sudo systemctl restart mariadb

# Reload Configuration (Applies dynamic configuration parameters without terminating connections)
sudo systemctl reload mariadb

Reload vs. Restart: Executing systemctl reload triggers a SIGHUP, instructing the daemon to re-read configuration parameters that support dynamic modification (e.g., max_connections, long_query_time). Static parameters (e.g., innodb_buffer_pool_size in older versions, port, bind-address) require a full service restart, which terminates active connections and re-initializes memory structures.

Verifying Active Network Listeners and Process State

# Verify the daemon is listening on TCP port 3306
ss -tlnp | grep 3306

# Ping daemon responsiveness
mysqladmin -u root -p ping

# Inspect process resource consumption
ps aux | grep mysqld

7. Common Administrative Tasks and Workflows

Task 1: Creating a Restricted Application User

Objective: Provision a dedicated user account following the principle of least privilege.

-- Connect to the database shell as root
-- Create user restricted to specific subnet '10.0.1.%'
CREATE USER 'app_prod'@'10.0.1.%' IDENTIFIED BY 'SecurePassword123!';

-- Grant specific privileges on application database only
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_prod'@'10.0.1.%';

-- Refresh memory privilege tables
FLUSH PRIVILEGES;

-- Verification: Display granted permissions
SHOW GRANTS FOR 'app_prod'@'10.0.1.%';

Task 2: Provisioning an Asynchronous GTID Replication Replica

Objective: Provision a new read-replica using GTID-based replication.

-- Step 1: On the Replica Node, apply target configuration (server-id=102, read_only=1, super_read_only=1)
-- Step 2: Seed schema and data from Primary physical/logical backup
-- Step 3: Configure replication source settings on Replica
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.1.10',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='ReplicaPassword123!',
  SOURCE_PORT=3306,
  SOURCE_AUTO_POSITION=1;

-- Step 4: Start replication applier threads
START REPLICA;

-- Step 5: Verify status
SHOW REPLICA STATUS\G

8. CLI Command Reference and Cheatsheet

Core Command Cheatsheet

Category Command / Statement Important Flags / Parameters Practical Purpose
Client Connection mysql -h <host> -u <user> -p -h (host), -P (port), -S (socket) Open interactive SQL shell.
Non-Interactive Query mysql -u root -p -e "<SQL>" -e (execute query and exit) Run queries from shell/scripts.
Service Diagnostics mysqladmin ping -h (host), -u (user) Verify database daemon responsiveness.
Session Diagnostics SHOW FULL PROCESSLIST; None View active query sessions and states.
Process Termination KILL <thread_id>; Thread ID from PROCESSLIST Terminate runaway or blocking session.
Process Termination KILL QUERY <thread_id>; Thread ID from PROCESSLIST Abort active query while keeping session open.
Engine Deep Dive SHOW ENGINE INNODB STATUS\G \G (vertical row layout) Inspect locks, deadlocks, buffer pool metrics.
Storage Diagnostics information_schema.tables data_length, index_length Calculate table and database size on disk.
Replication Primary SHOW MASTER STATUS; None Inspect current primary binlog file & position.
Replication Primary SELECT @@gtid_executed; None Check total executed GTID sets.
Replication Replica SHOW REPLICA STATUS\G SHOW SLAVE STATUS\G (legacy syntax) Verify IO/SQL thread state and replication lag.
Replication Control START REPLICA; / STOP REPLICA; START SLAVE; (legacy syntax) Control replication worker threads.
Logical Backup mysqldump --single-transaction, --all-databases Generate non-blocking logical SQL dumps.
Physical Backup mariabackup / xtrabackup --backup, --target-dir Perform hot physical backup of data files.

Useful Diagnostic Query Examples

Check Database Disk Footprint

SELECT 
    table_schema AS 'Database', 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' 
FROM information_schema.tables 
GROUP BY table_schema 
ORDER BY SUM(data_length + index_length) DESC;

Identify Running Queries Executing Longer Than 10 Seconds

SELECT id, user, host, db, command, time, state, info 
FROM information_schema.processlist 
WHERE command != 'Sleep' AND time > 10 
ORDER BY time DESC;

9. Logs, Monitoring and Diagnostics

Effective observation requires correlating native database logs with operating system performance metrics.

Key Log Files and Locations

  • Error Log (/var/log/mysql/error.log): Records daemon startup failures, crash recovery sequences, severe InnoDB engine errors, and replication warnings.
  • Slow Query Log (/var/log/mysql/mariadb-slow.log): Captures SQL statements executing longer than long_query_time or operations failing to utilize indexes.
  • Binary Logs (/var/lib/mysql/mysql-bin.XXXXXX): Contains data modification events used for auditing, PITR, and replication.
# Tail the error log in real-time
sudo tail -f /var/log/mysql/error.log

# Inspect systemd journal logs for MariaDB
sudo journalctl -u mariadb -f --since "1 hour ago"

Analyzing Slow Queries with mysqldumpslow

The mysqldumpslow utility parses slow query log files and summarizes execution statistics:

# Sort top 10 queries by average execution time
mysqldumpslow -s at -t 10 /var/log/mysql/mariadb-slow.log

10. Troubleshooting Guide and Common Failure Scenarios

Problem 1: Connection Refused / Cannot Connect to MySQL Server

Symptoms

Clients receive ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.1.10' (111) or ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'.

Possible Causes

  1. Database daemon (mariadb / mysql) is stopped or crashing on boot.
  2. Database is bound strictly to 127.0.0.1 (bind-address), rejecting external IP connections.
  3. Local host firewall (ufw / firewalld) or Cloud Security Group is blocking TCP port 3306.
  4. Socket file path mismatch between client configuration and server runtime directory.

Investigation and Diagnosis

  1. Verify Daemon Status:
systemctl status mariadb

Interpretation: If active (running) is displayed, proceed to network testing. If failed or inactive, inspect /var/log/mysql/error.log. 2. Test Daemon Responsiveness via Admin Tool:

mysqladmin -u root -p ping

Interpretation: Output mysqld is alive confirms local engine responsiveness. 3. Verify Listening Sockets and Bind Address:

ss -tlnp | grep 3306

Interpretation: 127.0.0.1:3306 indicates remote connections will fail. 0.0.0.0:3306 or :::3306 confirms listening on all network interfaces.

Root Cause Identification

If ss -tlnp shows 127.0.0.1:3306, the issue is a binding restriction in /etc/mysql/mariadb.conf.d/50-server.cnf.

Resolution

Edit the server configuration file to adjust binding:

bind-address = 0.0.0.0

Reload or restart the service:

sudo systemctl restart mariadb

Verification

Execute remote connectivity test from client server:

mysql -h 10.0.1.10 -u app_prod -p -e "SELECT 1;"

Prevention

Enforce configuration management (e.g., Ansible) to ensure standard binding rules, and integrate mysqladmin ping checks into monitoring platforms.


Problem 2: Access Denied for User (‘user’@‘host’)

Symptoms

Client application logs indicate ERROR 1045 (28000): Access denied for user 'app_user'@'10.0.1.25' (using password: YES).

Possible Causes

  1. Host pattern matching failure (e.g., user created as 'app_user'@'10.0.1.10', but request originates from app node IP 10.0.1.25).
  2. Incorrect password configured in application connection pool.
  3. Missing schema privileges for specified user.

Investigation and Diagnosis

  1. Inspect Existing User Privilege Map:
SELECT user, host FROM mysql.user WHERE user = 'app_user';

Interpretation: Verify whether 'app_user'@'10.0.1.25' or wildcard 'app_user'@'10.0.1.%' exists. 2. Inspect Granted Privileges:

SHOW GRANTS FOR 'app_user'@'10.0.1.%';

Interpretation: Ensure targeted database and table permissions are explicitly listed.

Resolution

Grant access matching the exact connecting host IP or subnet:

CREATE USER 'app_user'@'10.0.1.25' IDENTIFIED BY 'CorrectPassword!';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'10.0.1.25';
FLUSH PRIVILEGES;

Verification

Run targeted client login attempt from client host 10.0.1.25:

mysql -h 10.0.1.10 -u app_user -p -e "SHOW TABLES FROM app_db;"

Prevention

Use subnet masks (e.g., 10.0.1.%) for dynamically scaled application instances and maintain centralized secrets management.


Problem 3: Server Exhaustion — “Too many connections”

Symptoms

Client requests fail with ERROR 1040 (00000): Too many connections.

Possible Causes

  1. Active client connection count reached the max_connections threshold.
  2. Application connection leaks due to unclosed database connections.
  3. Slow running queries holding connections open in Sleep state.

Investigation and Diagnosis

  1. Emergency Administrative Access: Log in locally via Unix domain socket (MySQL/MariaDB reserves an extra connection administrative slot for localhost socket root logins):
sudo mysql -u root
  1. Inspect Active Connection Count:
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

Interpretation: If Threads_connected equals max_connections, connection capacity is exhausted. 3. Identify Sleeping or Stalled Threads:

SELECT id, user, host, db, command, time, state 
FROM information_schema.processlist 
WHERE command = 'Sleep' ORDER BY time DESC LIMIT 10;

Resolution

  1. Immediate Relief (Kill idle/stale connections):
-- Kill specific idle connection thread
KILL 1402;
  1. Dynamic Limit Increase:
SET GLOBAL max_connections = 1000;

Verification

Verify that available thread headroom has been restored:

SHOW STATUS LIKE 'Threads_connected';

Prevention

Implement server-side connection pooling proxy solutions (e.g., ProxySQL) and set appropriate wait_timeout and interactive_timeout limits to automatically terminate abandoned idle connections.


Problem 4: Lock Waits and Deadlocks

Symptoms

Application requests hang or fail with ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction.

Possible Causes

  1. Long-running transactions modifying overlapping rows in inverse order across concurrent threads.
  2. Unindexed UPDATE or DELETE statements causing InnoDB to scan and lock wide index ranges.

Investigation and Diagnosis

  1. Check Active Locks in Processlist:
SHOW FULL PROCESSLIST;

Look for states such as: Waiting for table metadata lock or updating. 2. Inspect Detailed InnoDB Engine Diagnostics:

SHOW ENGINE INNODB STATUS\G

Interpretation: Scroll to the LATEST DETECTED DEADLOCK section. Analyze transaction statements, held locks, and requested locks to identify the conflicting SQL paths.

Resolution

  1. Terminate the blocking connection ID:
KILL <blocking_thread_id>;
  1. Optimize application transaction boundaries: ensure transactions update tables/rows in identical order across application threads.
  2. Add missing indexes for predicate columns to ensure point locks instead of gap/range locks.

Verification

Execute EXPLAIN SELECT ... on candidate queries to verify index coverage.


Problem 5: Disk Full due to Binary Logs or InnoDB Data

Symptoms

Database stops accepting writes, throwing ERROR 1022 (23000): Can't write; duplicate key in table or daemon crashes with disk I/O errors.

Possible Causes

  1. Binary log retention is unconstrained, filling /var/lib/mysql/.
  2. Unoptimized temporary tables spilling to disk during large sorting queries.

Investigation and Diagnosis

  1. Check Disk Partition Space:
df -h /var/lib/mysql
  1. Inspect Directory Breakdown:
sudo du -sh /var/lib/mysql/* | sort -rh | head -n 10
  1. Check Binlog Disk Consumption:
SHOW BINARY LOGS;

Resolution

  1. Purge Old Binary Logs safely: Caution: Ensure replicas have already applied binary log events before purging!
-- Safely purge logs older than a specific timestamp
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;
  1. Configure Automatic Binlog Expiration: Add/modify in configuration file:
binlog_expire_logs_seconds = 604800   # Retain logs for 7 days (MySQL 8.0+)
# expire_logs_days = 7                # MariaDB / older MySQL

Verification

Verify partition usage drops to acceptable thresholds:

df -h /var/lib/mysql

Problem 6: Replication Stopped — SQL Thread Error

Symptoms

SHOW REPLICA STATUS\G shows Replica_SQL_Running: No and Last_SQL_Error contains error messages (e.g., duplicate entry, missing record, table doesn’t exist).

Possible Causes

  1. Direct manual write operations executed on read replica causing key collisions.
  2. DDL statement failure or schema drift between primary and replica.

Investigation and Diagnosis

  1. Execute Status Inspection on Replica:
SHOW REPLICA STATUS\G

Interpretation: Read Last_SQL_Error to pinpoint the specific failing transaction and binary log position.

Resolution

Inject an empty transaction for the errant GTID to skip the failing event:

STOP REPLICA;
SET GTID_NEXT='3E11FA47-71BA-11ED-9F0C-0242AC110002:1045';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START REPLICA;
Option B: Legacy Non-GTID Skip Counter (Use with caution!)
STOP REPLICA;
SET GLOBAL sql_replica_skip_counter = 1;
START REPLICA;

Verification

Re-check status to verify thread resumption and lag decline:

SHOW REPLICA STATUS\G
-- Confirm: Replica_IO_Running: Yes, Replica_SQL_Running: Yes, Last_SQL_Error: (empty)

Prevention

Set read_only = 1 and super_read_only = 1 on all read replica nodes to prevent local modifications.


Problem 7: Replication Stopped — IO Thread Error

Symptoms

SHOW REPLICA STATUS\G reports Replica_IO_Running: No and Last_IO_Error indicates network timeout, authentication failure, or invalid binlog offset.

Possible Causes

  1. Network firewall blocking TCP 3306 between replica and primary host.
  2. Replication user account (repl_user) password expired or revoked on primary.
  3. Primary purged binlog file requested by replica.

Investigation and Diagnosis

  1. Check IO Error Detail:
SHOW REPLICA STATUS\G
-- Inspect Last_IO_Error field
  1. Test Network and Credentials from Replica CLI:
mysql -h primary.example.com -u repl_user -p -e "SELECT 1;"

Interpretation: If login fails, correct firewall rules or authentication credentials.

Resolution

Re-configure replication source parameters with valid credentials:

STOP REPLICA;
CHANGE REPLICATION SOURCE TO
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='ValidSecretPassword';
START REPLICA;

Problem 8: High Replication Lag (Seconds_Behind_Source)

Symptoms

Replication status displays elevated Seconds_Behind_Source numbers; replica reads return stale data.

Possible Causes

  1. Large batch DML updates (e.g., bulk DELETE or UPDATE affecting millions of rows) running on primary.
  2. Single-threaded replication SQL applier bottleneck on replica.
  3. Missing table indexes on replica.

Investigation and Diagnosis

Inspect Seconds_Behind_Source and check worker processes:

SHOW REPLICA STATUS\G

Resolution

Enable Multi-Threaded Replica (MTR) worker threads in configuration:

replica_parallel_workers = 8
replica_parallel_type    = LOGICAL_CLOCK

Apply dynamically:

STOP REPLICA;
SET GLOBAL replica_parallel_workers = 8;
START REPLICA;

Problem 9: Accidental Writes on Replica & Split-Brain Drift

Symptoms

Replication breaks due to primary key conflicts; replica contains records absent on primary.

Possible Causes

Lack of explicit super_read_only restrictions allowing administrative accounts or local scripts to write to replica tables.

Resolution & Enforcement

Enforce total read-only protections immediately on replica:

SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

Add to /etc/mysql/mariadb.conf.d/50-server.cnf:

read_only = 1
super_read_only = 1

11. Security and Best Practices

  1. Enforce Least Privilege: Never use GRANT ALL PRIVILEGES for application accounts. Bind accounts to specific database names and IP subnets.
  2. File Permissions: Ensure /var/lib/mysql directory ownership is strictly held by mysql:mysql with 0700 or 0750 permissions.
  3. Network Isolation: Never expose port 3306 to public network interfaces. Enforce network-level isolation via VPNs, private VPC subnets, or SSH tunnels.
  4. Harden Default Installation: Execute mysql_secure_installation to remove anonymous user accounts, disable remote root login, and purge test databases.
  5. Replica Hardening: Set super_read_only = 1 on all secondary replica instances.

12. Performance and Resource Optimisation

Optimizing the InnoDB Buffer Pool

The InnoDB Buffer Pool caches data and indexes in RAM. On dedicated database servers, allocate 70%–80% of available system memory to innodb_buffer_pool_size.

# /etc/mysql/mariadb.conf.d/50-server.cnf
innodb_buffer_pool_size = 12G   # On a node with 16GB total RAM

Analyzing Query Execution Plans with EXPLAIN

Analyze query performance to detect table scans:

EXPLAIN SELECT * FROM orders WHERE customer_id = 45012;

Key indicators:

  • type: ALL -> Indicates a full table scan (missing index).
  • type: ref or range -> Indicates index usage (good performance).

13. Backup, Recovery and Maintenance

A solid backup strategy balances consistency, operational overhead, and recovery time objectives (RTO).

Warning: Never copy /var/lib/mysql directly using standard filesystem utilities (cp, rsync) while mysqld is active! Hot modifications produce partial page writes, resulting in table corruption during restoration.

1. Logical Backups via mysqldump

Best suited for smaller databases or schema extraction. Uses --single-transaction to acquire an InnoDB snapshot without locking reads or writes.

# Take single-transaction backup of single database
mysqldump -u root -p --single-transaction --quick --routines --triggers app_db > /backups/app_db_$(date +%F).sql

# Restore Logical Backup
mysql -u root -p app_db < /backups/app_db_2026-11-03.sql

2. Physical Hot Backups via mariabackup / Percona XtraBackup

Best for large multi-gigabyte/terabyte databases. Copies physical InnoDB data pages directly while the server runs, capturing redo log offsets to guarantee consistency.

# Step 1: Perform physical hot backup (MariaDB)
mariabackup --backup --target-dir=/backups/full_physical --user=root --password=Secret

# Step 2: Prepare backup (apply redo log to achieve point-in-time state)
mariabackup --prepare --target-dir=/backups/full_physical

# Step 3: Restore (requires stopped server and empty data directory)
sudo systemctl stop mariadb
sudo rm -rf /var/lib/mysql/*
mariabackup --copy-back --target-dir=/backups/full_physical
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mariadb

3. Point-in-Time Recovery (PITR)

Combines a full baseline backup with binary log replay using mysqlbinlog:

# Extract transactions from binary log up to exact point of accidental DROP TABLE (e.g. 2026-11-03 14:30:00)
mysqlbinlog --stop-datetime="2026-11-03 14:30:00" /var/lib/mysql/mysql-bin.000012 > /tmp/pitr_replay.sql

# Apply restored baseline dump first, then replay binary logs
mysql -u root -p app_db < /backups/full_baseline.sql
mysql -u root -p app_db < /tmp/pitr_replay.sql

14. Automation and Integration

Integrating database management into operational infrastructure streamlines routine tasks:

Systemd Timer for Automated Daily Logical Backups

Create /etc/systemd/system/db-backup.service:

[Unit]
Description=Daily MySQL Database Backup Task
After=mariadb.service

[Service]
Type=oneshot
ExecStart=/usr/bin/mysqldump --defaults-file=/etc/mysql/debian.cnf --all-databases --single-transaction --quick --result-file=/backups/db_full_%Y%m%d.sql

Create associated systemd timer /etc/systemd/system/db-backup.timer:

[Unit]
Description=Run DB backup daily at 02:00 AM

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true

[Install]
WantedBy=timers.target

Enable timer:

sudo systemctl daemon-reload
sudo systemctl enable --now db-backup.timer

15. Practical Labs and Exercises

Lab 1: Provisioning GTID Replication and Hardening Replica (Beginner-Intermediate)

  • Objective: Establish GTID replication between two nodes and enforce read-only safety.
  • Environment: Primary (10.0.1.10), Replica (10.0.1.20).
  • Instructions:
  1. Set server-id = 101 on Primary and server-id = 102 on Replica.
  2. Enable log_bin and gtid_mode = ON on both hosts.
  3. Create replication user on Primary (GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.1.20').
  4. Seed Replica data using mysqldump --single-transaction --master-data=2.
  5. Issue CHANGE REPLICATION SOURCE TO on Replica with SOURCE_AUTO_POSITION = 1.
  6. Enable super_read_only = 1 on Replica.
  • Validation: Execute SHOW REPLICA STATUS\G on Replica and confirm Replica_IO_Running: Yes, Replica_SQL_Running: Yes. Attempt an INSERT on Replica as root to verify super_read_only blocks non-applier writes.

Lab 2: Diagnosing and Clearing Stalled Transaction Deadlocks (Intermediate)

  • Objective: Identify locked sessions and clear stalled lock contention.
  • Environment: Local test database instance.
  • Instructions:
  1. Session A: Open transaction BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; (Do not commit).
  2. Session B: Open transaction BEGIN; UPDATE accounts SET balance = balance + 100 WHERE id = 1; (Session hangs waiting for lock).
  3. Session C (SysAdmin): Inspect blocking state using SHOW FULL PROCESSLIST; and SHOW ENGINE INNODB STATUS\G.
  4. Abort blocking Session A using KILL <thread_id>.
  • Validation: Verify Session B completes immediately upon clearing Session A lock.

16. Quick Reference and Cheat Sheet

Important Configuration Files

Path Description
/etc/my.cnf Global main configuration (RHEL family)
/etc/mysql/mariadb.conf.d/50-server.cnf Server configuration file (Debian MariaDB)
/var/lib/mysql/ Default data directory holding tables & logs

Important Log Locations

Log Path / Mechanism
Error Log /var/log/mysql/error.log
Slow Query Log /var/log/mysql/mariadb-slow.log
Systemd Log journalctl -u mariadb

Common CLI Administration Commands

Task Syntax
Connect Interactive Shell mysql -u root -p
Test Service Responsiveness mysqladmin -u root -p ping
Check Active Queries mysql -e "SHOW FULL PROCESSLIST;"
Check InnoDB Locks/Deadlocks mysql -e "SHOW ENGINE INNODB STATUS\G"
Check Replica Replication Status mysql -e "SHOW REPLICA STATUS\G"
Consistent Logical Backup mysqldump --single-transaction --all-databases > backup.sql

Troubleshooting Quick Matrix

Symptom Initial Diagnostic Command Primary File/Setting to Inspect
Connection Refused `ss -tlnp grep 3306`
Access Denied mysql -u user -p Host pattern in mysql.user table
Too Many Connections SHOW STATUS LIKE 'Threads_connected'; max_connections setting
Replication Broken SHOW REPLICA STATUS\G Last_SQL_Error / Last_IO_Error

17. Further Reading and References

  1. MySQL Official Documentation
  2. MariaDB Knowledge Base
  3. MySQL Replication Guide
  4. MySQL GTID Integration
  5. Backup and Recovery Strategies