MySQL / MariaDB

MariaDB is an open-source fork of MySQL and serves as the default MySQL-compatible database in RHEL-based distributions. This article covers MariaDB installation, security initialization, basic SQL operations, user and database management, backup and recovery, and remote access configuration.

Versions Across Distributions

Live version data by pkgseek.com

Install MariaDB

Install from System Default Repository

sudo dnf install mariadb-server mariadb -y

Use the MariaDB Official Repository (for Newer Versions)

The MariaDB version in the system repositories varies by distribution (EL 10 ships 10.11, and some EL 9 repositories also provide 10.11 / 11.8 module streams). If you want a consistent MariaDB 12.3 LTS (the current LTS; 11.8 LTS is still supported), add the official repository:

sudo tee /etc/yum.repos.d/MariaDB.repo << 'EOF'
[mariadb]
name = MariaDB
baseurl = https://mirror.mariadb.org/yum/12.3/rhel/$releasever/$basearch
gpgkey = https://mirror.mariadb.org/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1
EOF

sudo dnf install MariaDB-server MariaDB-client -y

Start the Service

sudo systemctl start mariadb
sudo systemctl enable mariadb
sudo systemctl status mariadb

Security Initialization

After installation, be sure to run the security initialization script to set the root password and remove insecure defaults:

sudo mysql_secure_installation

The wizard will ask the following questions in order. The recommended responses are:

Enter current root password — Press Enter on first install (empty password)
Switch to unix_socket authentication — Choose based on your needs, Y recommended
Set root password — Y, then enter a strong password
Remove anonymous users — Y
Disallow root remote login — Y
Delete test database — Y
Reload privilege tables — Y

After initialization, test the login:

sudo mysql -u root -p

Basic SQL Operations

Here are the most common SQL statements for daily use.

Database Operations

-- View all databases
SHOW DATABASES;

-- Create a database (with character set)
CREATE DATABASE myapp DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Switch database
USE myapp;

-- Drop a database
DROP DATABASE myapp;

Table Operations

-- Create a table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- View table structure
DESCRIBE users;

-- View all tables
SHOW TABLES;

CRUD Operations

-- Insert data
INSERT INTO users (username, email) VALUES ('zhangsan', '[email protected]');
INSERT INTO users (username, email) VALUES ('lisi', '[email protected]');

-- Query data
SELECT * FROM users;
SELECT username, email FROM users WHERE id = 1;

-- Update data
UPDATE users SET email = '[email protected]' WHERE username = 'zhangsan';

-- Delete data
DELETE FROM users WHERE username = 'lisi';

User and Database Management

In production environments, you should not use the root account for application connections. Instead, create a dedicated user for each application.

Create a User and Database

-- Create a database
CREATE DATABASE webapp DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create a user (local connections only)
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

-- Grant all privileges on the specified database
GRANT ALL PRIVILEGES ON webapp.* TO 'webapp_user'@'localhost';

-- Flush privileges
FLUSH PRIVILEGES;

View and Manage Users

-- View all users
SELECT User, Host FROM mysql.user;

-- View user privileges
SHOW GRANTS FOR 'webapp_user'@'localhost';

-- Change user password
ALTER USER 'webapp_user'@'localhost' IDENTIFIED BY 'NewPassword456!';

-- Revoke privileges
REVOKE ALL PRIVILEGES ON webapp.* FROM 'webapp_user'@'localhost';

-- Drop a user
DROP USER 'webapp_user'@'localhost';

Read-Only User Example

CREATE USER 'reader'@'localhost' IDENTIFIED BY 'ReadOnly789!';
GRANT SELECT ON webapp.* TO 'reader'@'localhost';
FLUSH PRIVILEGES;

mysqldump Backup and Recovery

mysqldump is the built-in logical backup tool for MariaDB/MySQL, suitable for small to medium-sized databases.

Back Up a Single Database

mysqldump -u root -p webapp > /backup/webapp_$(date +%Y%m%d_%H%M%S).sql

Back Up All Databases

mysqldump -u root -p --all-databases > /backup/all_databases_$(date +%Y%m%d_%H%M%S).sql

Back Up Specific Tables

mysqldump -u root -p webapp users > /backup/webapp_users.sql

Compressed Backup

mysqldump -u root -p webapp | gzip > /backup/webapp_$(date +%Y%m%d).sql.gz

Restore a Database

# Create the target database first (if it doesn't exist)
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS webapp;"

# Restore data
mysql -u root -p webapp < /backup/webapp_20260324.sql

gunzip < /backup/webapp_20260324.sql.gz | mysql -u root -p webapp

Automated Backup Script

sudo tee /usr/local/bin/mariadb-backup.sh << 'SCRIPT'
#!/bin/bash
BACKUP_DIR="/backup/mariadb"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"

# Back up all databases
mysqldump -u root --all-databases | gzip > "${BACKUP_DIR}/all_db_${DATE}.sql.gz"

# Delete backups older than the retention period
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete

echo "Backup completed: ${BACKUP_DIR}/all_db_${DATE}.sql.gz"
SCRIPT

sudo chmod +x /usr/local/bin/mariadb-backup.sh

Add a cron job to run the backup daily at 2:00 AM:

echo "0 2 * * * root /usr/local/bin/mariadb-backup.sh" | sudo tee /etc/cron.d/mariadb-backup

Remote Access Configuration

By default, MariaDB only listens on localhost. To allow remote access, the following configuration changes are needed.

Modify the Bind Address

sudo tee /etc/my.cnf.d/bind-address.conf << 'EOF'
[mysqld]
bind-address = 0.0.0.0
EOF

If you only need to listen on a specific IP, replace 0.0.0.0 with that IP address.

Restart the Service

sudo systemctl restart mariadb

Create a User Allowed to Connect Remotely

-- Allow connections from any IP (not recommended for production)
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'RemotePass123!';
GRANT ALL PRIVILEGES ON webapp.* TO 'remote_user'@'%';

-- Allow connections from a specific IP only (recommended)
CREATE USER 'remote_user'@'192.168.1.100' IDENTIFIED BY 'RemotePass123!';
GRANT ALL PRIVILEGES ON webapp.* TO 'remote_user'@'192.168.1.100';

FLUSH PRIVILEGES;

Open the Firewall Port

sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

Test the Remote Connection

On the remote client, run:

mysql -u remote_user -p -h server_IP_address

Security Recommendations

Remote access introduces additional security risks. The following measures are recommended:

Restrict user connection source IPs; avoid using the '%' wildcard
Use firewall rules to restrict access to port 3306
Consider using an SSH tunnel instead of directly exposing the port
Enable SSL encrypted connections

# Create an SSH tunnel locally
ssh -L 3306:127.0.0.1:3306 user@server_IP_address

# Then connect locally
mysql -u webapp_user -p -h 127.0.0.1

Common Configuration Optimizations

sudo tee /etc/my.cnf.d/custom.conf << 'EOF'
[mysqld]
# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# InnoDB buffer pool (recommended: 50%-70% of available memory)
innodb_buffer_pool_size = 256M

# Separate tablespace per table
innodb_file_per_table = 1

# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow-query.log
long_query_time = 2

# Maximum connections
max_connections = 150
EOF

sudo systemctl restart mariadb

Common Operations Quick Reference

# Start / Stop / Restart
sudo systemctl start mariadb
sudo systemctl stop mariadb
sudo systemctl restart mariadb

# View running status
sudo systemctl status mariadb

# Log into the database
mysql -u root -p

# View database version
mysql -V

# View runtime status variables
mysqladmin -u root -p status

# View process list
mysqladmin -u root -p processlist

# Check and repair tables
mysqlcheck -u root -p --all-databases --auto-repair