Using my digitalOcean mysql (phpmyadmin) server external

Heya @olewelling

Firstly, ensure your MySQL server is configured to listen on its public IP address. Additionally, check that your firewall (like UFW) allows incoming connections on port 3306.

I had this case several times and it always seems to be the Firewall configuration that needs a tweak.

Hope that this helps!

Hi there,

Indeed, there are a few things that you need to do in order to allow remote MySQL connections. By default, MySQL listens on 127.0.0.1:3306 which means that only connections from the server itself would be accepted.

Here is a step-by-step guide on how to configure your MySQL service to accept remote connections:

Step 1: Backup your server

Before you start make sure to back up your Droplet so that you could always revert back in case that anything goes wrong.

Step 2: Configure MySQL to Listen for Remote Connections

As mentioned above, by default, MySQL listens only on the local interface. You’ll need to modify the MySQL configuration file to allow connections from other hosts.

1. Open the MySQL configuration file. This might be under /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf depending on the MySQL or MariaDB version:

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

2. Look for the bind-address directive and change its value to 0.0.0.0:

   bind-address = 0.0.0.0
   

3. Save the file and exit the text editor (Ctrl + X, Y, then Enter).

Step 3: Restart MySQL Server

Restart the MySQL service to apply the changes:

sudo systemctl restart mysql

Step 4: Configure User Permissions

You’ll need to grant the necessary permissions for your remote user to connect.

1. Log in to MySQL:

   mysql -u root -p
   

2. Grant permissions for the remote user, replacing ‘username’ and ‘password’ with your preferred credentials, and ‘hostname’ with the remote host’s IP address or hostname (use ‘%’ to allow any host):

   GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname' IDENTIFIED BY 'password';
   

3. Flush privileges to apply the changes:

   FLUSH PRIVILEGES;
   

4. Exit the MySQL shell:

   EXIT;
   

Step 5: Configure Firewall Rules (If Applicable)

If you have UFW or another firewall active, make sure to allow connections to the MySQL port (usually 3306):

sudo ufw allow 3306/tcp

Step 6: Testing the Remote Connection

From the remote machine, you can now connect to your MySQL server using:

mysql -h server_ip -u username -p

Replace server_ip with the IP address of your Ubuntu machine running MySQL.

That’s it! You have now set up your Ubuntu MySQL server to accept remote connections. Make sure to follow security best practices, such as using strong passwords, setting up a firewall, and allowing connections only from trusted IP addresses.

Let me know how it goes!

Best,

Bobby

Hey @olewelling,

Have you allowed the ports that are used for external use or more securely, have you allowed your IP on your firewall?

Additionally, how are you connecting internally to the database?