Install and Secure MariaDB on VPS Ubuntu

June 2026 · 9 min read

MariaDB is a fork of MySQL created by the original MySQL developers and serves as a drop-in replacement that works with WordPress, Laravel, and most applications without code changes. It offers better performance than MySQL on many workloads and receives consistent security updates.

Install MariaDB on Ubuntu 22.04

# Install from Ubuntu repository (MariaDB 10.6)
apt update
apt install mariadb-server mariadb-client -y

# Or install the latest version from MariaDB Foundation repository
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash
apt install mariadb-server -y

# Start the service
systemctl enable --now mariadb

# Check version
mariadb --version

Hardening with mysql_secure_installation

mysql_secure_installation

Answer the prompts as follows:

• Enter current password for root: Press Enter (no password yet)
• Switch to unix_socket authentication: Y
• Change the root password: Y and enter a strong password
• Remove anonymous users: Y
• Disallow root login remotely: Y
• Remove test database: Y
• Reload privilege tables: Y

Create Database and User

# Login as root
mariadb -u root -p

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

-- Create a user that can only connect from localhost
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

-- Grant privileges only on the relevant database
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp_user'@'localhost';

-- Example for WordPress
CREATE DATABASE wordpress CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'wp_user'@'localhost' IDENTIFIED BY 'WpPassword456!';
GRANT ALL PRIVILEGES ON wordpress.* TO 'wp_user'@'localhost';

FLUSH PRIVILEGES;
EXIT;

Configure Performance in my.cnf

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

[mysqld]
# InnoDB Buffer Pool — set to 50-70% of RAM
# 2GB VPS → 1G, 4GB VPS → 2G
innodb_buffer_pool_size = 512M
innodb_buffer_pool_instances = 1

# Log file size
innodb_log_file_size = 128M

# Query Cache (enable for read-heavy applications)
query_cache_type = 1
query_cache_size = 32M
query_cache_limit = 2M

# Connection
max_connections = 150
connect_timeout = 10
wait_timeout = 300

# Slow Query Log (for performance debugging)
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2

# Character Set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

systemctl restart mariadb

Lock Down Network Access

MariaDB should bind only to localhost or a private network:

# In /etc/mysql/mariadb.conf.d/50-server.cnf
bind-address = 127.0.0.1

# If multiple servers need remote access, use private IP
# bind-address = 10.0.0.1

# Firewall: allow only necessary IPs
ufw allow from 10.0.0.2 to any port 3306
ufw deny 3306

Automated Backup

nano /usr/local/bin/mariadb-backup.sh

#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/mariadb"
mkdir -p "$BACKUP_DIR"

# Dump all databases
mysqldump -u root --all-databases \
  --single-transaction --routines --triggers \
  | gzip > "${BACKUP_DIR}/all_${DATE}.sql.gz"

# Keep only 14 days of backups
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +14 -delete

echo "Backup done: all_${DATE}.sql.gz"

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

# Run as cron job daily at 3 AM
echo "0 3 * * * root /usr/local/bin/mariadb-backup.sh >> /var/log/mariadb-backup.log 2>&1" \
  > /etc/cron.d/mariadb-backup

Check Database Health

# View overall status
mariadb -u root -p -e "SHOW STATUS LIKE 'Threads%';"
mariadb -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool%';"

# View slow queries
tail -50 /var/log/mysql/mariadb-slow.log

# Check database size
mariadb -u root -p -e "
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;"

After completing these steps, your MariaDB installation on VPS will be production-ready from both security and performance perspectives. Monitor your slow query log regularly and adjust innodb_buffer_pool_size based on your actual server workload. If you don't yet have a VPS that supports MariaDB, lease a Singapore VPS from asiagb.com with SSD storage and low latency from Thailand — ideal for database workloads and fast recovery in emergencies. Before going live, use dnsxray.com to verify your domain's DNS and SSL configuration before connecting to your production database.

Verify MariaDB is Working Correctly

# Check version and service status
mariadb --version
systemctl status mariadb

# Test login and basic query
mariadb -u root -p -e "SHOW DATABASES; SELECT VERSION();"

# Check application user connection
mariadb -u myapp_user -p myapp -e "SHOW TABLES;"

# Check InnoDB buffer pool health
mariadb -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool%';"

Common Issues & Troubleshooting

Error: "Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock'"

MariaDB is not running or the socket file is in a different location:

# Check and start the service
systemctl status mariadb
systemctl start mariadb

# Find the actual socket path
mariadb --print-defaults | grep socket
# Or check the configuration file
grep socket /etc/mysql/mariadb.conf.d/50-server.cnf

Error: "Access denied for user 'root'@'localhost'" after mysql_secure_installation

Ubuntu/Debian use unix_socket authentication for root by default. You must use sudo or su:

# Method 1: Use sudo
sudo mariadb

# Method 2: Switch to password authentication
sudo mariadb -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';"
sudo mariadb -e "FLUSH PRIVILEGES;"

Error: MariaDB service fails to start — "[ERROR] InnoDB: Cannot read the first block in file"

This often occurs after changing innodb_log_file_size without removing old log files:

# View detailed error log
journalctl -u mariadb -n 50
tail -50 /var/log/mysql/error.log

# Fix: Stop service, remove old logs, restart
systemctl stop mariadb
rm /var/lib/mysql/ib_logfile*
systemctl start mariadb

Error: "Incorrect string value" when inserting Thai text

The database or table was created with a charset that doesn't support Thai. Use utf8mb4:

-- Check the database charset
SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'mydb';

-- Convert database to utf8mb4
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Convert problem tables
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Error: Slow queries persist even after setting innodb_buffer_pool_size

The issue is often missing indexes. Check your slow query log and use EXPLAIN:

# View recent slow queries
tail -50 /var/log/mysql/mariadb-slow.log

# Analyze a slow query
mariadb -u root -p mydb -e "EXPLAIN SELECT * FROM users WHERE email = '[email protected]';"
# If type = ALL, it's a full table scan — you need to add an index

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

Frequently Asked Questions

How do MariaDB and MySQL differ? Can I use them interchangeably?

MariaDB is a fork of MySQL created by the original MySQL team and maintains compatibility at the syntax and API level, allowing WordPress, Laravel, and most applications to work without code changes. MariaDB's advantages include more frequent updates, more diverse storage engines, and a more open license.

How can I allow an application on another server to connect to my database?

Change the bind-address to a private network IP and create a user authorized from that IP: CREATE USER 'user'@'10.0.0.2' IDENTIFIED BY 'pass'; Then configure UFW to open port 3306 only from that IP. Never expose 3306 directly to the internet.

Can I migrate a database from MySQL to MariaDB?

Yes. The process is: mysqldump on MySQL → create new MariaDB → import dump into MariaDB. SQL syntax is identical throughout. However, if you're using MySQL 8.0+ specific features (like certain window functions), verify compatibility before migrating.