MySQL/MariaDB on VPS: installation, configuration, security

MariaDB is a MySQL fork with improved performance. It's available from the official Ubuntu repositories on most ZevsHost VPS servers.

Installing MariaDB

sudo apt update
sudo apt install -y mariadb-server mariadb-client

# Start it and enable autostart
sudo systemctl start mariadb
sudo systemctl enable mariadb

# Initial hardening
sudo mysql_secure_installation

During mysql_secure_installation, it's recommended to:

• Set a root password — Yes
• Remove anonymous users — Yes
• Disallow remote root login — Yes
• Remove the test database — Yes

Creating a database and user

sudo mysql -u root -p

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

-- Create a user
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON myapp.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Configuring remote access

# Open the port only for a specific IP
sudo ufw allow from 203.0.113.10 to any port 3306

# Allow bind in MariaDB
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
# Change: bind-address = 0.0.0.0
sudo systemctl restart mariadb

Automated backups

# Create the backup script
cat > /usr/local/bin/db-backup.sh << 'EOF'
#!/bin/bash
DATE=$(date +%Y-%m-%d)
mysqldump -u root -pYOUR_PASSWORD myapp | gzip > /backups/myapp-$DATE.sql.gz
find /backups -name "*.sql.gz" -mtime +7 -delete
EOF
chmod +x /usr/local/bin/db-backup.sh
mkdir -p /backups

# Add to cron (every day at 3:00)
echo "0 3 * * * root /usr/local/bin/db-backup.sh" >> /etc/crontab

Diagnosing common errors

"ERROR 1698 (28000): Access denied for user 'root'@'localhost'" when trying to log in as root right after installation — on Ubuntu/Debian, MariaDB uses the unix_socket plugin by default, and the root password is not checked at all unless you connect through sudo.

mysql -u root -p
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

The fix is to connect as sudo mysql (no password, via unix_socket) or explicitly switch the authentication method: ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('password');.

A remote connection fails with "ERROR 2003: Can't connect to MySQL server" — even with bind-address changed and the port open in UFW, the cause is often that the MySQL user itself was created with host localhost, not a specific IP or %.

ERROR 2003 (HY000): Can't connect to MySQL server on '203.0.113.10' (111)
-- Error 111 = Connection refused: the port is closed or nothing is listening on it

Check all three things at once: bind-address in the config, the UFW rule, and the host in the user definition (SELECT host FROM mysql.user WHERE user='myuser';) — the connection fails unless all three match.

Database options compared

When local MariaDB is not enough

• Load is growing faster than a single VPS can handle — you will need a separate database server or master-slave replication.
• The application needs complex JSON queries or window functions — PostgreSQL is often a better fit for that workload than MariaDB.
• You need high availability with no manual intervention when the server fails — that calls for a cluster (Galera) or a managed solution, not a single MariaDB instance.

Verifying the result

1. Check the service is running and enabled at boot: systemctl is-enabled mariadb && systemctl is-active mariadb — both must return a positive status.
2. Connect as the new user: mysql -u myuser -p myapp — the login must succeed with no errors.
3. Check the privileges: SHOW GRANTS FOR 'myuser'@'localhost'; must show exactly the rights you granted.
4. Check that the backup is actually created: ls -lh /backups/ must contain a fresh file after manually running /usr/local/bin/db-backup.sh.

Backups specifically for large, busy databases are covered separately in the article on automating MySQL backups, and basic user privileges are covered in the guide to MySQL privileges.