Hosting a MySQL/MariaDB Server On a VPS With Remote Access

Database for your project

Most projects you create often need a database. If you prefer working with MySQL (or MariaDB), this is a guide on how to set it up on a ubuntu VPS and allow remote access.

I have been using Hetzner Cloud when hosting my VPS, and it has been working great. But any VPS will do.

So set up your VPS with Ubuntu and log in to it.

1. Update

Always make sure it is up to date with

sudo apt update

2. Install MySQL or MariaDB

Install the necessary server depending on which language.

# MySQL
sudo apt install mysql-server mysql-client

# MariaDB
sudo apt install mariadb-server mariadb-client

3. Remote bind-address

To enable remote connections, we need to remove the bind-address. Edit this file and remove/comment out bind-address

# MySQL
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

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

4. Restart

Restart the service with the following command.

# MySQL
sudo systemctl restart mysql

# MariaDB
sudo systemctl restart mariadb

# Without password
sudo mysql -u root

# With password
sudo mysql -u root -p

6. Create a user

Create a user that will be used to access the server remotely.

CREATE USER 'your_username'@'%' IDENTIFIED BY 'your_password';

7. Grant privileges

You need to grant your user all privileges.

GRANT ALL PRIVILEGES ON *.* TO 'your_username'@'%';

To apply the changes, we need to do a flush.

FLUSH PRIVILEGES;

8. Quit and find IP

Use the quit command to exit MySQL/MariaDB. Type in ip a to get your IP that you will use to access your server.