MariaDB (the default on RHEL 7+) and MySQL are the most widely used relational database servers in the Linux ecosystem. As an RHCA-level admin, you need to know how to install, secure, configure, manage users, perform backups, tune performance, and troubleshoot database issues.
Relational Database Concepts
Before administration, understand the fundamentals:
- Database: A collection of related tables
- Table: Rows and columns of structured data
- Schema: The structure definition of tables (field names, types, indexes)
- Index: A data structure that speeds up queries (like a book index)
- Primary key: A unique identifier for each row (usually an auto-increment integer)
- Foreign key: A reference from one table to another (enforces referential integrity)
- Transaction: A group of operations that all succeed or all fail together (ACID properties)
ACID Properties
- Atomicity: All operations in a transaction complete, or none do
- Consistency: Database moves from one valid state to another
- Isolation: Concurrent transactions don't interfere with each other
- Durability: Committed transactions survive system failures
Installation and Initial Security
# RHEL 7 (MariaDB):
# yum install mariadb-server mariadb -y
# systemctl start mariadb
# systemctl enable mariadb
# RHEL 6 (MySQL):
# yum install mysql-server mysql -y
# service mysqld start
# chkconfig mysqld on
# Data directory: /var/lib/mysql/
# Config: /etc/my.cnf
# Log: /var/log/mariadb/mariadb.log or /var/log/mysqld.log
# Secure the installation (CRITICAL — run immediately after install):
# mysql_secure_installation
# What mysql_secure_installation does:
# 1. Sets root password (was empty by default)
# 2. Removes anonymous users (default: any user can connect without credentials)
# 3. Disallows remote root login (root should only connect locally)
# 4. Removes test database (world-accessible test DB is a security risk)
# 5. Reloads privilege tables (applies changes immediately)MySQL Client — Connection and Navigation
# Connect as root:
# mysql -u root -p
# Connect to specific host and database:
# mysql -h 192.168.1.100 -u raju -p mydb
# Execute single query without interactive mode:
# mysql -u root -p -e "SHOW DATABASES;"
# mysql -u root -p mydb < /backup/restore.sql
# Inside mysql client:
# MySQL/MariaDB statements end with semicolon (;)
# Keywords are case-insensitive (SELECT = select)
# Object names are case-sensitive on case-sensitive filesystems
# Navigation:
MariaDB [(none)]> SHOW DATABASES;
MariaDB [(none)]> USE mydb;
MariaDB [mydb]> SHOW TABLES;
MariaDB [mydb]> DESCRIBE tablename; # show table structure
MariaDB [mydb]> SHOW CREATE TABLE users; # full table definition
MariaDB [mydb]> SHOW FULL PROCESSLIST; # active connections/queries
MariaDB [mydb]> SHOW GLOBAL STATUS; # server statistics
MariaDB [mydb]> SHOW VARIABLES; # all configuration variables
MariaDB [mydb]> QUIT; # exitDatabase and Table Operations
# Create database with character set:
MariaDB> CREATE DATABASE webdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# utf8mb4 supports all Unicode including emoji (utf8 in MySQL only supports 3-byte)
# Drop database (DESTRUCTIVE — no undo):
MariaDB> DROP DATABASE webdb;
# Create table with common field types:
MariaDB> CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100),
password VARCHAR(255),
role ENUM('admin','user','guest') DEFAULT 'user',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_email (email) # index for faster email lookups
);
# Add column to existing table:
MariaDB> ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
# Modify column:
MariaDB> ALTER TABLE users MODIFY COLUMN email VARCHAR(200);
# Delete column:
MariaDB> ALTER TABLE users DROP COLUMN phone;
# Rename table:
MariaDB> RENAME TABLE users TO system_users;User Management and Privileges
# Create user (localhost only):
MariaDB> CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'SecurePass123!';
# Create user (from any host):
MariaDB> CREATE USER 'appuser'@'%' IDENTIFIED BY 'SecurePass123!';
# Create user from specific network:
MariaDB> CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'pass';
# Privilege levels:
# Global: ON *.* (all databases, all tables)
# Database: ON mydb.* (specific database)
# Table: ON mydb.users (specific table)
# Column: specific column permissions
# Grant privileges:
MariaDB> GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'localhost';
MariaDB> GRANT SELECT, INSERT, UPDATE ON webdb.* TO 'readonly'@'%';
MariaDB> GRANT SELECT ON webdb.users TO 'viewer'@'192.168.1.10';
# Grant with ability to grant to others:
MariaDB> GRANT ALL ON mydb.* TO 'admin'@'localhost' WITH GRANT OPTION;
# Revoke privileges:
MariaDB> REVOKE INSERT, UPDATE ON mydb.* FROM 'appuser'@'localhost';
MariaDB> REVOKE ALL ON mydb.* FROM 'appuser'@'localhost';
# Drop user:
MariaDB> DROP USER 'appuser'@'localhost';
# CRITICAL: Always run after privilege changes:
MariaDB> FLUSH PRIVILEGES;
# View user privileges:
MariaDB> SHOW GRANTS FOR 'appuser'@'localhost';
# View all users:
MariaDB> SELECT user, host, password FROM mysql.user;Backup Strategies
Logical Backup with mysqldump
# Single database:
# mysqldump -u root -p mydb > /backup/mydb_$(date +%Y%m%d_%H%M).sql
# All databases:
# mysqldump -u root -p --all-databases > /backup/all_$(date +%Y%m%d).sql
# Specific tables:
# mysqldump -u root -p mydb users orders > /backup/selected.sql
# With stored procedures, triggers, and events:
# mysqldump -u root -p --routines --triggers --events mydb > backup.sql
# Compressed:
# mysqldump -u root -p --all-databases | gzip > /backup/all_$(date +%Y%m%d).sql.gz
# Exclude specific tables (e.g., large log tables):
# mysqldump -u root -p mydb --ignore-table=mydb.access_log > backup.sql
# Restore:
# mysql -u root -p mydb < /backup/mydb_20260607.sql
# gunzip < backup.sql.gz | mysql -u root -p mydbPhysical Backup with Percona XtraBackup (Hot Backup)
# mysqldump requires a brief table lock for consistency
# XtraBackup does a hot backup with no locking (for InnoDB tables):
# yum install percona-xtrabackup -y
# Full backup:
# xtrabackup --backup --target-dir=/backup/full
# Restore:
# systemctl stop mariadb
# xtrabackup --prepare --target-dir=/backup/full
# xtrabackup --copy-back --target-dir=/backup/full
# chown -R mysql:mysql /var/lib/mysql
# systemctl start mariadbPerformance Tuning
# vim /etc/my.cnf
[mysqld]
# InnoDB Buffer Pool — most important setting:
# Set to 70-80% of available RAM for a dedicated DB server
innodb_buffer_pool_size = 4G # adjust based on RAM
# InnoDB settings:
innodb_log_file_size = 256M # larger = better performance for writes
innodb_flush_log_at_trx_commit = 1 # 1=safest, 2=faster (risk on crash)
innodb_file_per_table = 1 # each table in its own file (easier management)
# Query cache (MySQL 5.x only, removed in MySQL 8):
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M
# Connection settings:
max_connections = 200 # max simultaneous connections
thread_cache_size = 50 # keep idle threads (reduces connect overhead)
table_open_cache = 2000 # cached open table file descriptors
# Slow query log:
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow.log
long_query_time = 1 # log queries taking > 1 second
log_queries_not_using_indexes = 1 # log queries with no index use
# Apply:
# systemctl restart mariadbMonitoring and Diagnostics
# Check database size:
MariaDB> SELECT table_schema AS database_name,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;
# Current connections:
MariaDB> SHOW FULL PROCESSLIST;
MariaDB> SHOW STATUS LIKE 'Threads_connected';
# Check for slow queries in real time:
MariaDB> SHOW FULL PROCESSLIST; # queries running > 30 sec
MariaDB> KILL QUERY thread_id; # kill slow query without killing connection
MariaDB> KILL thread_id; # kill entire connection
# Buffer pool usage:
MariaDB> SHOW STATUS LIKE 'Innodb_buffer_pool%';
# Key buffer (MyISAM):
MariaDB> SHOW STATUS LIKE 'Key_reads';
# Query cache hit rate:
MariaDB> SHOW STATUS LIKE 'Qcache%';Common Troubleshooting
# Cannot connect:
# check service: systemctl status mariadb
# check port: ss -tulnp | grep 3306
# check firewall: firewall-cmd --list-all
# Access denied:
# Verify user@host combination:
MariaDB> SELECT user, host FROM mysql.user WHERE user='raju';
# MariaDB matches user@host exactly — 'raju'@'192.168.1.10' != 'raju'@'%'
# Password expired:
MariaDB> ALTER USER 'raju'@'localhost' IDENTIFIED BY 'newpassword';
# Table is locked:
MariaDB> SHOW OPEN TABLES WHERE In_use > 0;
MariaDB> KILL thread_id_holding_lock;
# Disk full:
# df -h /var/lib/mysql/ # check data directory
# mysql> PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY); # clean old binary logs
# Corrupted table:
MariaDB> CHECK TABLE mydb.users; # check integrity
MariaDB> REPAIR TABLE mydb.users; # repair (MyISAM only)
# For InnoDB: restore from backup or use innodb_force_recovery