MariaDB Database Server

What is MariaDB?

MariaDB is an open-source relational database forked from MySQL. It's the default database in most Linux distributions and offers better performance and security than MySQL.

Installation

sudo apt update
sudo apt install mariadb-server mariadb-client -y
 
# Start and enable
sudo systemctl enable mariadb
sudo systemctl start mariadb
 
# Verify
sudo systemctl status mariadb
mariadb --version

Securing MariaDB

Run the security script immediately after installation:

sudo mysql_secure_installation

Answer the prompts:

Set root password → Yes (choose a strong password)
Remove anonymous users → Yes
Disallow root login remotely → Yes
Remove test database → Yes
Reload privilege tables → Yes

Connecting to MariaDB

# Connect as root
sudo mariadb -u root -p
 
# Or
sudo mysql -u root -p

Database Administration

Create a Database

CREATE DATABASE webapp;
SHOW DATABASES;
USE webapp;

Create a User

-- Create user with password
CREATE USER 'deploy'@'localhost' IDENTIFIED BY 'StrongPassword123!';
 
-- Grant privileges on specific database
GRANT ALL PRIVILEGES ON webapp.* TO 'deploy'@'localhost';
 
-- Grant limited privileges
GRANT SELECT, INSERT, UPDATE ON webapp.* TO 'readonly'@'localhost' IDENTIFIED BY 'ReadPass123!';
 
-- Apply changes
FLUSH PRIVILEGES;
 
-- View users
SELECT User, Host FROM mysql.user;
 
-- Exit
EXIT;

Connect as New User

mariadb -u deploy -p webapp

Working with Tables

USE webapp;
 
-- Create a table
CREATE TABLE servers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hostname VARCHAR(100) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    role VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Insert data
INSERT INTO servers (hostname, ip_address, role) VALUES
    ('web-01', '10.0.1.10', 'webserver'),
    ('web-02', '10.0.1.11', 'webserver'),
    ('db-01', '10.0.2.10', 'database');
 
-- Query data
SELECT * FROM servers;
SELECT hostname, ip_address FROM servers WHERE role = 'webserver';
 
-- Update data
UPDATE servers SET status = 'maintenance' WHERE hostname = 'web-02';
 
-- Delete data
DELETE FROM servers WHERE hostname = 'web-02';
 
-- Describe table structure
DESCRIBE servers;

Backup and Restore

Backup a Database

# Single database
mysqldump -u root -p webapp > webapp_backup.sql
 
# All databases
mysqldump -u root -p --all-databases > all_databases.sql
 
# Specific table
mysqldump -u root -p webapp servers > servers_table.sql

Restore a Database

# Restore (database must exist)
mysql -u root -p webapp < webapp_backup.sql
 
# Create database first if needed
mysql -u root -p -e "CREATE DATABASE webapp;"
mysql -u root -p webapp < webapp_backup.sql

Automated Backup Script

#!/bin/bash
# /home/ubuntu/db_backup.sh
 
BACKUP_DIR="/var/backups/mariadb"
DATE=$(date +%Y%m%d_%H%M%S)
 
mkdir -p $BACKUP_DIR
 
# Backup all databases
mysqldump -u root --all-databases | gzip > "$BACKUP_DIR/all_db_$DATE.sql.gz"
 
# Keep only last 7 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
 
echo "Backup completed: $DATE"

Schedule with cron:

crontab -e
# Add: 0 2 * * * /home/ubuntu/db_backup.sh

Performance Tuning

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

[mysqld]
# InnoDB buffer pool (70% of available RAM for dedicated DB servers)
innodb_buffer_pool_size = 1G
 
# Max connections
max_connections = 200
 
# Disable DNS lookups (faster connections)
skip-name-resolve
 
# Query cache
query_cache_size = 64M
 
# Close idle connections after 60 seconds
wait_timeout = 60
 
# Slow query log (find problematic queries)
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Apply changes:

sudo systemctl restart mariadb

Find Optimal Buffer Pool Size

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) AS Recommended_GB
FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
      FROM information_schema.tables WHERE engine='InnoDB') A;

Useful Commands

Command	Purpose
`SHOW DATABASES;`	List all databases
`SHOW TABLES;`	List tables in current DB
`DESCRIBE table;`	Show table structure
`SHOW PROCESSLIST;`	Active connections
`SHOW VARIABLES LIKE '%max%';`	View config variables

Summary

Install with apt install mariadb-server, secure with mysql_secure_installation
Create dedicated users per application — never use root in production
mysqldump creates SQL backups; restore with mysql < file.sql
Automate backups with cron scripts
Tune innodb_buffer_pool_size, max_connections, and enable slow query log
Always FLUSH PRIVILEGES after user/permission changes

Next Steps

Next, we'll cover backup strategies and restoration — protecting your infrastructure data with rsync, tar, and automated schedules.

Learning Objectives