MariaDB

Install and configure MariaDB on Linux VPS, secure setup, user management, performance tuning and backup

MariaDB is an open-source relational database forked from MySQL, providing drop-in compatibility with MySQL syntax and tools. It's the default database server on Debian/Ubuntu and offers excellent performance for web applications, CMS platforms, and enterprise systems.

Installation

Ubuntu/Debian (Latest LTS - 10.11)

Use the official MariaDB Foundation repository for the latest stable version:

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
apt update
apt install mariadb-server -y

Start and enable:

systemctl start mariadb
systemctl enable mariadb

CentOS/AlmaLinux/Rocky

dnf install mariadb-server -y
systemctl start mariadb
systemctl enable mariadb

Verify Installation

mariadb --version
mysql -u root -e "SELECT VERSION();"

Secure Installation

Run the security wizard to harden your instance:

mysql_secure_installation

This removes:

Anonymous user accounts
Root remote login capability
Test database
Optionally disables password less authentication

User Management

Create Database and User

mysql -u root -p

Inside mysql prompt:

-- Create database
CREATE DATABASE wordpress;

-- Create local user (localhost only, most secure)
CREATE USER 'wordpress'@'localhost' IDENTIFIED BY 'secure_password_123';
GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpress'@'localhost';

-- Create remote user (from specific IP)
CREATE USER 'wordpress'@'192.168.1.100' IDENTIFIED BY 'secure_password_456';
GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpress'@'192.168.1.100';

-- Apply changes
FLUSH PRIVILEGES;

-- Verify
SHOW GRANTS FOR 'wordpress'@'localhost';
EXIT;

Always use localhost connections when possible. Only allow remote connections from specific IPs that require them.

Grant Specific Permissions

-- Read-only user
CREATE USER 'reader'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON wordpress.* TO 'reader'@'localhost';
FLUSH PRIVILEGES;

-- User with specific tables
GRANT SELECT, INSERT, UPDATE ON wordpress.users TO 'wordpress'@'localhost';
FLUSH PRIVILEGES;

Delete User

DROP USER 'olduser'@'localhost';
FLUSH PRIVILEGES;

Configuration

Main Configuration File

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

nano /etc/mysql/mariadb.conf.d/50-server.cnf

Key parameters:

# Network
bind-address = 127.0.0.1          # localhost only (secure)
bind-address = 0.0.0.0            # listen on all interfaces

# Connections
max_connections = 200              # adjust based on workload

# Memory (critical for performance)
# Set to 50-70% of system RAM
innodb_buffer_pool_size = 2G       # main memory cache (8GB RAM = 6GB here)
innodb_log_file_size = 512M

# Query cache (disable on MariaDB 10.5+)
# query_cache_type = OFF

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2                # log queries slower than 2 seconds

# InnoDB
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2 # balance between safety and performance

Restart to apply changes:

systemctl restart mariadb

Verify Configuration

mysql -u root -p

-- Check current settings
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'slow_query_log';

-- Monitor live connections
SHOW PROCESSLIST;
SHOW STATUS LIKE 'Threads%';

Essential Commands

Database Operations

-- List databases
SHOW DATABASES;

-- Select database
USE wordpress;

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

-- List tables
SHOW TABLES;

-- Describe table structure
DESCRIBE users;

-- Drop database
DROP DATABASE myapp;

User Operations

-- List all users
SELECT user, host FROM mysql.user;

-- Show user grants
SHOW GRANTS FOR 'wordpress'@'localhost';

-- Change password
ALTER USER 'wordpress'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

Server Status

-- Check server uptime and connections
SHOW STATUS LIKE 'Uptime';
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Questions';

-- Show active queries
SHOW PROCESSLIST;

Backup

Backup Single Database

mysqldump -u wordpress -p wordpress > wordpress_$(date +%Y%m%d).sql

# Compressed backup
mysqldump -u wordpress -p wordpress | gzip > wordpress_$(date +%Y%m%d).sql.gz

Backup All Databases

mysqldump -u root -p --all-databases | gzip > all_databases_$(date +%Y%m%d).sql.gz

Backup with Locks (Minimal Downtime)

mysqldump -u root -p --single-transaction --lock-tables=false wordpress > backup.sql

Restore Database

# From SQL file
mysql -u root -p wordpress < wordpress_20250329.sql

# From compressed file
gunzip < wordpress_20250329.sql.gz | mysql -u root -p wordpress

Automated Backup Script

#!/bin/bash
BACKUP_DIR="/backups/mysql"
DATE=$(date +%Y%m%d)
MYSQL_USER="root"
MYSQL_PASS="your_password"

mkdir -p $BACKUP_DIR

# Backup all databases
mysqldump -u $MYSQL_USER -p$MYSQL_PASS \
  --single-transaction \
  --lock-tables=false \
  --all-databases | \
  gzip > $BACKUP_DIR/all_databases_$DATE.sql.gz

# Keep only last 30 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete

echo "Backup completed: $BACKUP_DIR/all_databases_$DATE.sql.gz"

Add to crontab for daily backups at 2 AM:

0 2 * * * /usr/local/bin/mysql-backup.sh

Hot Backup with Mariabackup

For large databases with minimal downtime:

apt install mariadb-backup -y

# Create backup
mariabackup --backup --target-dir=/backups/mysql-full --user=root --password=

# Prepare backup
mariabackup --prepare --target-dir=/backups/mysql-full

# Restore (stop MariaDB first)
systemctl stop mariadb
mv /var/lib/mysql /var/lib/mysql.old
mariabackup --copy-back --target-dir=/backups/mysql-full
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb

Performance Tuning

Check Slow Queries

tail -f /var/log/mysql/slow.log

Analyze Query Performance

EXPLAIN SELECT * FROM users WHERE email='user@example.com';

Look for table scans without index usage.

Add Index to Improve Performance

-- Check if index exists
SHOW INDEX FROM users;

-- Add index
CREATE INDEX idx_email ON users(email);
ALTER TABLE users ADD INDEX idx_email (email);

Monitor Memory Usage

SHOW STATUS WHERE variable_name IN (
  'Innodb_buffer_pool_bytes_data',
  'Innodb_buffer_pool_bytes_dirty'
);

Remote Connection Security

Enable Remote Access (Carefully)

Do NOT expose port 3306 to the internet. Always use SSH tunnel or VPN for remote access.

For specific IP only:

nano /etc/mysql/mariadb.conf.d/50-server.cnf
# bind-address = 192.168.1.50  # specific IP

Restart:

systemctl restart mariadb

SSH Tunnel for Remote Access

Connect from remote machine:

ssh -L 3306:localhost:3306 user@vps.example.com -N

Then connect locally:

mysql -h 127.0.0.1 -u wordpress -p wordpress

SSL/TLS for Encrypted Connections

-- Create user with SSL required
CREATE USER 'secure_user'@'192.168.1.100' IDENTIFIED BY 'password'
  REQUIRE SSL;
FLUSH PRIVILEGES;

Generate certificates:

mysql_ssl_rsa_setup --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql/
systemctl restart mariadb

Monitoring

Useful Monitoring Queries

-- Database sizes
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;

-- Top 10 largest tables
SELECT
  table_schema,
  table_name,
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.tables
ORDER BY (data_length + index_length) DESC
LIMIT 10;

-- Current connections by user
SELECT user, COUNT(*) FROM information_schema.processlist GROUP BY user;

Monitor with mytop (optional)

apt install mytop
mytop -u root -p

Maintenance

Repair Tables (if corrupted)

mysqlcheck -u root -p --auto-repair --optimize wordpress

Optimize All Databases

mysqlcheck -u root -p --optimize --all-databases

Check Table Status

CHECK TABLE users;
REPAIR TABLE users;
OPTIMIZE TABLE users;

On this page