This has been tested on AWS, but should work the same pretty much anywhere.
A simple guide for quickly setting up MySQL remote access on any VPS. This involves creating a new user (other than root) and changing the mysqld config to make it listen on all network interfaces.
Run these commands to install MySQL on Debian based operating systems:
sudo apt update
sudo apt install mysql-server
Run this command to initiate the mysql_secure_installation script:
sudo mysql_secure_installation
Go through the script's steps, I generally turn off remote root login and remove the test database. But the most important step here is to set a root MySQL user password.
Note this down because you'll need it later.
To get into the MySQL CLI, run this command:
sudo mysql -u root -p
Once prompted, enter the root user's password you set in Step 2.
When your prompt says mysql>, enter the following commands one by one, replacing username and password with the ones you'd like.
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';
SHOW GRANTS FOR 'username'@'%';The last command's output should show a user with the username you set and it should have a long list of privileges.
At this step, you might want to make a database from the MySQL CLI using the following command, replacing db_name with your preferred database name:
CREATE DATABASE db_name;Edit the mysqld.cnf with vim with the command below. You can switch out vim for whichever editor you prefer, like nano.
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
Once in this file, find the bind-address line and replace 127.0.0.1 with 0.0.0.0, to make mysqld listen on all network interfaces.
All that's left to do is to run this command to restart the MySQL daemon:
sudo service mysql restart
After this, you should be able to remotely connect using a MySQL client like Adminer. You can find a hosted version of Adminer here.