By default, Ubuntu MySQL Server blocks all remote connections. This prevents us from accessing the database server from the outside.
To enable remote connection of MySQL, you will need to edit the MySQL main configuration file.
So if you have installed MySQL Database server configuration file is: /etc/mysql/mysql.conf.d/mysqld.cnf. However, if you are using MariaDB Database server, configuration file going to be /etc/mysql/mariadb.conf.d/50-server.cnf.
1. Edit the configuration file
Firstly, you need to open /etc/mysql/mysql.conf.d/mysqld.cnf file (if you use MariaDB, installed of the provided file path, use earlier provided path for MariaDB):
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
Under the [mysqld] section, locate the line:
bind-address = 127.0.0.1
Then change this line to:
bind-address = 0.0.0.0
By changing value to 0.0.0.0, we instruct MySQL to bind to all available interfaces and by doing that we allow remote connections to the MySQL Server.
2. Restart the service
After that, save the change in the file and restart MySQL or MariaDB:
For MySQL:
sudo systemctl restart mysql
For MariaDB:
sudo systemctl restart mariadb
3. Allow remote connections for a user
Even if the server listens on all interfaces, you must grant remote access to a user.
Login to MySQL/MariaDB:
mysql -u root -p
Then run these commands (replace yourdb.*, yourpassword and youruser)
CREATE USER 'youruser'@'%' IDENTIFIED BY 'yourpassword';
GRANT ALL PRIVILEGES ON yourdb.* TO 'youruser'@'%';
FLUSH PRIVILEGES;
After that, exit.
4. Confirm the server is listening on all interfaces
To make sure that MySQL server listens on socket 0 0.0.0.0:3306, you should run this command:
For MySQL:
sudo ss -tulwn | grep 3306
For MariaDB:
sudo ss -tulwn | grep 3306
You should see the output showing 0.0.0.0:3306 if it’s listening on all interfaces.
4. Adjust Your Firewall
If on your VPS is enabled UFW firewall, then it will block the MySQL remote access, so you need to add a firewall rule on your firewall to open the port 3306.
sudo ufw allow 3306/tcp
This rule allows any IP from anywhere in the world to connect to port 3306. If you want to allow only specific IP, run this command:
sudo ufw allow from <your_IP> to any port 3306
If you are using IPtables, run this command to allow any IP:
iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
Or this, to allow specific IP:
sudo iptables -A INPUT -p tcp --dport 3306 -s <your_IP> -j ACCEPT
Then save changes:
sudo apt install -y iptables-persistent
sudo netfilter-persistent save
Please keep in mind that enabling remote connections to MySQL server is not a good practice from a security side. So don't expose your database server to outside unless you must, especially in a production environment.