Set Up a MySQL Database on Your Ubuntu VPS

Most apps need somewhere to store data. MySQL is one of the most common choices, and it runs well on a small VPS. This guide installs MySQL on Ubuntu 24.04, secures it, and creates a database and user your app can actually connect with.

Before you start

Connect to your VPS over SSH as root or a user with sudo access. If you have not connected yet, see Connect to Your VPS with SSH.

Install MySQL Server

Update the package list first, then install MySQL:

sudo apt update
sudo apt install -y mysql-server

apt update refreshes Ubuntu's list of available packages. apt install -y mysql-server installs the MySQL database server and starts it automatically.

Check that it is running:

sudo systemctl status mysql

You should see active (running). Press q to leave the status screen.

Secure the installation

MySQL ships with a script that removes unsafe defaults: an anonymous user, a test database anyone can access, and remote root login.

sudo mysql_secure_installation

Answer its questions:

Set up the password validation plugin if you want, or skip it.
Set a strong root password if asked.
Remove anonymous users: yes.
Disallow root login remotely: yes.
Remove the test database: yes.
Reload privilege tables now: yes.

Create a database and a user for your app

Do not use the MySQL root account in your application. Create a separate database and user with only the access that app needs.

Open the MySQL shell:

sudo mysql

Then run these commands, changing the names and password:

CREATE DATABASE myapp;
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'a-strong-password-here';
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

CREATE DATABASE myapp; makes an empty database called myapp.
CREATE USER ... IDENTIFIED BY ... makes a login that can only connect from the same server (localhost), with its own password.
GRANT ALL PRIVILEGES ON myapp.* gives that user full access to tables inside myapp only, not to other databases.
FLUSH PRIVILEGES; makes the new permissions take effect immediately.

Use a long, random password, and keep it out of your source code. An environment variable or a .env file that is not committed to git is the usual place for it.

Test the connection

Confirm the new user can log in and see the database:

mysql -u myapp_user -p myapp

Enter the password when asked. If you land on a mysql> prompt with no error, the user and database both work. Type EXIT; to leave.

Connect from Node.js

Most Node.js apps use a package such as mysql2 to connect:

npm install mysql2

Then point it at localhost, the database name, and the user you made:

const mysql = require('mysql2/promise');

const db = await mysql.createConnection({
  host: 'localhost',
  user: 'myapp_user',
  password: 'a-strong-password-here',
  database: 'myapp',
});

Because the database only accepts connections from localhost, it is not reachable from the internet at all, only from your own VPS. That is the safest setup for a single-server app, and it needs no extra firewall rules. See Host a Node.js App on a VPS with PM2 and Nginx for running the app itself as a service.

Back up your data

MySQL includes a tool to export a whole database to a single file:

mysqldump -u myapp_user -p myapp > myapp-backup.sql

Run this regularly, for example from a nightly cron job, and copy the file somewhere off the server. A database with no backup is one disk failure away from being gone for good.

Summary

Install mysql-server, run mysql_secure_installation, then create a dedicated database and user for each app rather than using the root account. Keep the database listening on localhost only unless you have a specific reason to expose it, and back it up on a schedule. From here, a Cloud VPS on AtoZNode with full root access is enough to run MySQL alongside your app on the same server.