Question

Using my digitalOcean mysql (phpmyadmin) server external

Hey! I have a phpmyadmin mysql database hosted on my digitalOcean vps, but i can’t connect externaly, i have al the right settings and even followed a few tutorials about how to set it up. But nothing is working, does anyone know how to make it work?


Submit an answer


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

alexdo
Site Moderator
Site Moderator badge
August 18, 2023

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!

Bobby Iliev
Site Moderator
Site Moderator badge
August 17, 2023

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

KFSys
Site Moderator
Site Moderator badge
August 17, 2023

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?

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

card icon
Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Sign up
card icon
Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We’d like to help.

Learn more
card icon
Become a contributor

You get paid; we donate to tech nonprofits.

Learn more
Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow – whether you’re running one virtual machine or ten thousand.

Learn more ->
DigitalOcean Cloud Control Panel
Get started for free

Enter your email to get $200 in credit for your first 60 days with DigitalOcean.

New accounts only. By submitting your email you agree to our Privacy Policy.

© 2023 DigitalOcean, LLC.