How to install MySQL on a VPS: users, safe remote access, less RAM and backups

Installing MySQL takes one command. Most of the work comes after: an app user, a way to reach the database from your computer that doesn't put port 3306 on the internet, memory settings for a small server, and backups that run without you. This guide covers all four on Ubuntu 24.04. Every step was run on our Ubuntu 24.04 image, and the numbers below come from that server.

Run the commands as a user with sudo. The Ubuntu setup guide creates one.

1. Install

sudo apt update
sudo apt install -y mysql-server
mysql --version

The install took 51 seconds on our server and gave us MySQL 8.0.46. It starts right away, starts again at every boot, and listens only on the server itself: 127.0.0.1:3306 for normal connections and 127.0.0.1:33060 for MySQL's newer X protocol. Nothing is reachable from outside yet.

2. Create a database and a user

MySQL's root account uses socket authentication on Ubuntu, so sudo mysql logs you in without a password. Create a database and a user for your app:

sudo mysql

CREATE DATABASE app CHARACTER SET utf8mb4;
CREATE USER 'app'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT ALL PRIVILEGES ON app.* TO 'app'@'localhost';
EXIT;

Test it the way your app will connect:

mysql -u app -p app -e "SELECT CURRENT_USER();"

It should print app@localhost. A wrong password gets ERROR 1045 (28000): Access denied for user 'app'@'localhost'. The user can do anything inside app and nothing outside it.

3. Connect from your computer: SSH tunnel

To use a desktop client such as DBeaver, MySQL Workbench or the mysql command line on your own machine, don't open a port. Forward one over SSH:

ssh -L 3307:127.0.0.1:3306 deploy@YOUR.SERVER.IP

On a NAT plan, add -p and the personal SSH port from your dashboard. While that window is open, point your client at host 127.0.0.1, port 3307, user app. We used 3307 on the local side so it doesn't clash with a MySQL you might run on your own computer. In our test the server answered through the tunnel straight away, while a direct connection to its address on 3306 was refused.

4. Direct access for one other server

Sometimes the app runs on a second server and has to connect directly. That needs a plan with its own IPv4 for the database, because a NAT plan only forwards your SSH port. Three changes, all limited to the app server's address (203.0.113.20 here):

sudo sed -i 's/^bind-address.*/bind-address = 0.0.0.0/' /etc/mysql/mysql.conf.d/mysqld.cnf
sudo systemctl restart mysql
sudo ufw allow from 203.0.113.20 to any port 3306 proto tcp

CREATE USER 'app'@'203.0.113.20' IDENTIFIED BY 'a-long-random-password';
GRANT ALL PRIVILEGES ON app.* TO 'app'@'203.0.113.20';

If ufw isn't enabled yet, allow SSH first (sudo ufw allow OpenSSH) and then run sudo ufw enable. The ufw guide has the details.

We checked both locks separately. With the firewall rule in place but no matching MySQL user, the server refused with error 1130: Host '…' is not allowed to connect to this MySQL server. With the user added, the connection went through. With the firewall rule removed, the connection just timed out. Any other address on the internet gets that timeout.

5. Use less RAM

Idle, with no data of its own, MySQL 8.0 used 383 MB on our server. Most of that is the Performance Schema, a diagnostics feature that reserves memory up front. On a small VPS, turn it off and size the main cache yourself:

sudo tee /etc/mysql/mysql.conf.d/zz-small-vps.cnf >/dev/null <<'EOF'
[mysqld]
performance_schema = OFF
innodb_buffer_pool_size = 256M
max_connections = 50
EOF
sudo systemctl restart mysql
ps -o rss= -C mysqld

After the restart it used 165 MB. The buffer pool caches your data and indexes, and 256 MB suits a 2 GB server that also runs the app. Raise it if the database is the main thing on the server and has grown larger than the cache. max_connections = 50 is plenty for one app with a connection pool. You lose the performance_schema statistics. Turn it back on if you ever need to dig into slow queries in detail.

6. Nightly backups

mysqldump --single-transaction takes a consistent copy of InnoDB tables without locking the app out. Put it in a small script:

sudo tee /usr/local/bin/mysql-backup >/dev/null <<'EOF'
#!/bin/bash
set -euo pipefail
umask 077
mkdir -p /var/backups/mysql
mysqldump --single-transaction --routines --triggers app | gzip > /var/backups/mysql/app-$(date +%F).sql.gz
find /var/backups/mysql -name 'app-*.sql.gz' -mtime +14 -delete
EOF
sudo chmod 755 /usr/local/bin/mysql-backup
echo '0 3 * * * root /usr/local/bin/mysql-backup' | sudo tee /etc/cron.d/mysql-backup
sudo /usr/local/bin/mysql-backup && sudo ls -l /var/backups/mysql

Each line is there for a reason:

• umask 077 makes the dump readable by root only (-rw-------), since it holds all your data.
• pipefail makes a failed mysqldump fail the whole script. Without it, gzip would write a tiny archive with nothing inside and report success. We broke the command on purpose and the script exited with code 2, as it should.
• find … -mtime +14 -delete keeps two weeks of copies.
• The script runs as root, so mysqldump logs in through the socket and no password sits in a file.

A backup you've never restored doesn't count yet. Restore into a scratch database and look at the data:

sudo mysql -e "CREATE DATABASE app_restore"
gunzip -c /var/backups/mysql/app-2026-10-25.sql.gz | sudo mysql app_restore
sudo mysql -e "SHOW TABLES FROM app_restore"
sudo mysql -e "DROP DATABASE app_restore"

In our test the restored table had the same rows as the original. Copies on the same disk don't protect you from losing the server, so pull them to another machine now and then with scp or restic. Managed Backups add daily restore points of the whole server on top.

Which plan

Micro (2 vCPU, 2 GB, 25 GB, $5/mo) holds MySQL tuned as above plus a web app. If the database itself grows past a few gigabytes, Small with 4 GB and 35 GB for $8/mo lets you give the buffer pool 1 GB or more. Both are NAT plans, and the SSH tunnel from step 3 works on them. Step 4 needs your own IPv4: Micro-IP is $10/mo.

Prefer PostgreSQL? The PostgreSQL guide follows the same plan. All plans are in VPS plans, and payment is in USDT, USDC or ETH on Base, Ethereum or Polygon.