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:
- TCP/Socket Connect: The client initiates a TCP handshake on port 3306 (or connects directly via Unix socket file
/var/run/mysqld/mysqld.sockfor local connections). - Authentication: The server’s thread manager receives the connection and validates the client credentials against the internal
mysql.userprivilege table, matching both username and connecting host IP pattern. - 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. - 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.
- 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.
- 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 theSUPERorSYSTEM_VARIABLES_ADMINprivilege) from performing write operations.super_read_only = 1: Restricts all users, includingrootand accounts withSUPERprivileges, 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.serviceor/lib/systemd/system/mysql.service - Primary Configuration File:
/etc/mysql/my.cnfor/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 reloadtriggers 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_sizein older versions,port,bind-address) require a full servicerestart, 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 thanlong_query_timeor 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
- Database daemon (
mariadb/mysql) is stopped or crashing on boot. - Database is bound strictly to
127.0.0.1(bind-address), rejecting external IP connections. - Local host firewall (
ufw/firewalld) or Cloud Security Group is blocking TCP port 3306. - Socket file path mismatch between client configuration and server runtime directory.
Investigation and Diagnosis
- 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
- Host pattern matching failure (e.g., user created as
'app_user'@'10.0.1.10', but request originates from app node IP10.0.1.25). - Incorrect password configured in application connection pool.
- Missing schema privileges for specified user.
Investigation and Diagnosis
- 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
- Active client connection count reached the
max_connectionsthreshold. - Application connection leaks due to unclosed database connections.
- Slow running queries holding connections open in
Sleepstate.
Investigation and Diagnosis
- 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
- 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
- Immediate Relief (Kill idle/stale connections):
-- Kill specific idle connection thread
KILL 1402;
- 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
- Long-running transactions modifying overlapping rows in inverse order across concurrent threads.
- Unindexed
UPDATEorDELETEstatements causing InnoDB to scan and lock wide index ranges.
Investigation and Diagnosis
- 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
- Terminate the blocking connection ID:
KILL <blocking_thread_id>;
- Optimize application transaction boundaries: ensure transactions update tables/rows in identical order across application threads.
- 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
- Binary log retention is unconstrained, filling
/var/lib/mysql/. - Unoptimized temporary tables spilling to disk during large sorting queries.
Investigation and Diagnosis
- Check Disk Partition Space:
df -h /var/lib/mysql
- Inspect Directory Breakdown:
sudo du -sh /var/lib/mysql/* | sort -rh | head -n 10
- Check Binlog Disk Consumption:
SHOW BINARY LOGS;
Resolution
- 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;
- 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
- Direct manual write operations executed on read replica causing key collisions.
- DDL statement failure or schema drift between primary and replica.
Investigation and Diagnosis
- 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
Option A: GTID Topology Skip (Recommended)
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
- Network firewall blocking TCP 3306 between replica and primary host.
- Replication user account (
repl_user) password expired or revoked on primary. - Primary purged binlog file requested by replica.
Investigation and Diagnosis
- Check IO Error Detail:
SHOW REPLICA STATUS\G
-- Inspect Last_IO_Error field
- 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
- Large batch DML updates (e.g., bulk
DELETEorUPDATEaffecting millions of rows) running on primary. - Single-threaded replication SQL applier bottleneck on replica.
- 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
- Enforce Least Privilege: Never use
GRANT ALL PRIVILEGESfor application accounts. Bind accounts to specific database names and IP subnets. - File Permissions: Ensure
/var/lib/mysqldirectory ownership is strictly held bymysql:mysqlwith0700or0750permissions. - Network Isolation: Never expose port 3306 to public network interfaces. Enforce network-level isolation via VPNs, private VPC subnets, or SSH tunnels.
- Harden Default Installation: Execute
mysql_secure_installationto remove anonymous user accounts, disable remote root login, and purge test databases. - Replica Hardening: Set
super_read_only = 1on 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: reforrange-> 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/mysqldirectly using standard filesystem utilities (cp,rsync) whilemysqldis 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:
- Set
server-id = 101on Primary andserver-id = 102on Replica. - Enable
log_binandgtid_mode = ONon both hosts. - Create replication user on Primary (
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.1.20'). - Seed Replica data using
mysqldump --single-transaction --master-data=2. - Issue
CHANGE REPLICATION SOURCE TOon Replica withSOURCE_AUTO_POSITION = 1. - Enable
super_read_only = 1on Replica.
- Validation: Execute
SHOW REPLICA STATUS\Gon Replica and confirmReplica_IO_Running: Yes,Replica_SQL_Running: Yes. Attempt anINSERTon Replica as root to verifysuper_read_onlyblocks 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:
- Session A: Open transaction
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1;(Do not commit). - Session B: Open transaction
BEGIN; UPDATE accounts SET balance = balance + 100 WHERE id = 1;(Session hangs waiting for lock). - Session C (SysAdmin): Inspect blocking state using
SHOW FULL PROCESSLIST;andSHOW ENGINE INNODB STATUS\G. - 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 |