How to Install MySQL/MariaDB on a VPS

Relational database management systems (RDBMS) like MySQL and MariaDB form the persistent data backbone for web applications, CMS platforms like WordPress, e-commerce stores, and backend APIs.

In this tutorial, you will learn how to install MariaDB / MySQL on your RivinCloud Linux VPS, harden the database engine against security threats, create custom databases, and set up granular user permissions.

1. Choosing Between MySQL and MariaDB

• MySQL: The original open-source relational database created in 1995, now maintained by Oracle Corporation.
• MariaDB: An open-source, binary drop-in replacement created by the original founders of MySQL. MariaDB offers enhanced query optimization algorithms, faster replication, and active community-driven development.

(Commands and SQL syntax are virtually identical for both engines).

2. Step 1: Installing MariaDB Server

On Ubuntu or Debian:

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

(To install Oracle MySQL instead, replace mariadb-server with mysql-server).

Verify the database service is running:

sudo systemctl status mariadb

3. Step 2: Running the Security Wizard

Fresh database installations include default settings (such as sample test databases and anonymous users) that should be removed immediately in production:

sudo mysql_secure_installation

Answer the interactive prompts as follows:

• Switch to unix_socket authentication?: Y (or n if using password authentication).
• Change root password?: Y (Set a strong, random password).
• Remove anonymous users?: Y
• Disallow root login remotely?: Y
• Remove test database and access to it?: Y
• Reload privilege tables now?: Y

4. Step 3: Creating a Database and User

Never configure web applications or bots to connect using the root database account. Always create dedicated users with access limited strictly to their own database.

Log into Database Console as Root:

sudo mariadb -u root
# (or sudo mysql -u root)

Run SQL Commands:

-- 1. Create a new database
CREATE DATABASE rivin_webapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 2. Create a dedicated user with a strong password
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongRandomPassword123!';

-- 3. Grant full permissions on this database only
GRANT ALL PRIVILEGES ON rivin_webapp.* TO 'appuser'@'localhost';

-- 4. Flush privilege tables
FLUSH PRIVILEGES;

-- 5. Exit console
EXIT;

Test Connection with New User:

mariadb -u appuser -p rivin_webapp

5. Configuring Database Memory & Localhost Binding

By default, databases listen on 127.0.0.1 (localhost), which prevents unauthorized connections from the public Internet.

Verify this setting in /etc/mysql/mariadb.conf.d/50-server.cnf:

[mysqld]
bind-address = 127.0.0.1

[!WARNING] Never change bind-address to 0.0.0.0 unless you strictly restrict port 3306 in UFW to specific trusted whitelisted client IPs!

Common Mistakes

• Using Root for web applications: If a web app is compromised via SQL injection, an attacker with root database privileges can dump all databases on the system.
• Forgetting FLUSH PRIVILEGES;: In some legacy versions, permission changes don't take effect immediately without flushing privileges.

Troubleshooting

• Access denied for user 'root'@'localhost': On modern Ubuntu, root uses auth_socket plugin. Log in with sudo mariadb or sudo mysql directly instead of typing -p.

Best Practices

• Use utf8mb4 character encoding to fully support international characters and emojis.
• Schedule automated daily database backups using mysqldump:

  mysqldump -u root rivin_webapp | gzip > backup_$(date +%F).sql.gz
  

Frequently Asked Questions

Can I manage my database with a graphical tool like DBeaver or HeidiSQL?

Yes! Use SSH Tunneling in DBeaver/HeidiSQL to securely connect to 127.0.0.1:3306 over encrypted SSH without exposing port 3306 publicly.

Conclusion

Installing and securing MariaDB/MySQL is straightforward and provides a rock-solid foundation for all your web and application databases on RivinCloud VPS!