Setting up a Remote MySQL Database Connection

Selecting a reputable web hosting company is only the first step towards building and maintaining a succesful website. There may be times when you have to connect to the database from outside your work network and if that’s the case, you need to connect to your database using third party software that does not run on the server (i.e. a MySQL client on your desktop). This is referred to as making a remote MySQL connection. To do this, however, you will first have to allow the connection from within cPanel for the IP address you are connecting from. Read on to learn how.

What program do I use to connect remotely?

There are a few programs you can use to connect remotely to your database. You can utilize HTML editors like Adobe Dreamweaver for a fee, or there are free Open Source programs you can use like MySQL Workbench or Heidi SQL. Here are some options to check out.

Adding an IP address to allow a remote MySQL connection

First, you will need to make sure you have the IP address that will be accessing the database. These rules protect the database by only accepting external connections that you have set up. If you are connecting to a database from your current connection, get your IP address by clicking here, then follow these steps:

  1. Log into cPanel.
  2. Click the Remote MySQL button in the Databases section.
  3. Enter the remote IP address in the Add Access Host section.
  4. Click the Add Host button.

    You will then see a message stating the host IP address was added to the access list.

Now, since you have added the IP address to remote MySQL connections you will be able to connect to the databases. You can add as many IP addresses as you need.

Using a Wildcard to allow all IP’s

You may find you need to use a wildcard if your IP address changes regularly. You can add a wildcard by using the % symbol. This will add all IP addresses in that range.

For example, if you want to list all IP addresses in the range 174.77.92.1 to 174.77.92.255 you’d add the IP address 174.77.92.%.

Troubleshooting Remote connection issues

If you are using your preferred remote connection tool and suddenly it will not connect, it’s known as a 1045 error and usually reads similar to ‘1045 – access denied for user‘. If you are getting this error, here are a couple of things to check:

Check your cPanel password

Many people connect remotely using their cPanel username and password as opposed to creating a single administrative user to add to the databases. This is not the preferred method, however if you do use the cPanel username and you are now getting a connection error, chances are you recently changed the cPanel password and have not changed it in the Remote connection software settings. Change the password there and you should be able to connect once again.

Check to see if connecting IP has changed

From time to time your ISP (Internet Service Provider) may change your public IP address. If that is the case, you may find yourself unable to connect remotely via your preferred software. Check your IP address by clicking here.

Once you know your current IP address, go back and make sure that it is in the list of allowed IP addresses for your Remote MySQL settings. If you find it was not in the list, add the new one and then you should then be able to connect remotely.

If you need further assistance with connecting to a database remotely please contact our support department

Thoughts on “Setting up a Remote MySQL Database Connection

  • Helloo

    i have trouble on my POS system (php project) i want to connect remote mysql database to my personal computer via localhost please can you tell me how it is configure me and if you can please send me step by step..

    my website is https://kidscentremalabe.com

    i already activated the remote mysql IP address

    this is a php project
    ————————-

    $host = ‘192.168.1.1’;
    $user = ‘username’;
    $pass = ‘password’;
    $db = ‘database’;

    $conn = mysqli_connect($host,$user,$pass,$db);

    if (mysqli_connect_errno())
    {
    echo “Failed to connect to MySQL: ” . mysqli_connect_error();
    }

    if you know some method please give me the answer i couldn’t connect to the mysql server..

  • I’ve had success with remote connections over a ipv4 connection, but now I have a ipv6 address. What is a valid hostname for a ipv6 address? When I go to add the access host I get the following message.

    • “The Access Host field must be a valid hostname, for example, example.com or %.example.com.”
    • A valid hostname is usually a web address, such as the provided “example.com”. As long as the hostname is pointed correctly, there should not be an issue.

      Thank you,
      John-Paul

  • The best way to connect is use a recent version of HeidiSQL and select “SSH tunnel”.  That way, you don’t open a MySQL port to the internet, and you can use your ssh credentials to connect.  You must, however, have your SQL credentials as well because once throught the ssh tunnel, you must authenticate to MySQL.

  • Hi everyone

    Bro i want to connect to my server database directly from my localhost. 

    i’m using that code:

     

    $db_host = 'example.com';
    $db_username = 'abc123';
    $db_password = 'abc123';
    $db_name = 'service';

    but its not working. I also add my domain name in the remote mySQL. but it shows an error message:

    Access denied for user  ‘root’@’39.48.68.141’

    please help!

    Thanks!

    • Typically, you would add your IP address to the firewall instead of the domain. Ensure you are using a database user that has access to the database. You will then use this username to connect to the database instead of the “root” user.

      Thank you,
      John-Paul

  • Hi,

    You guys have been really helpful and responsive!  Thanks for many years of happy service πŸ™‚

    I am having using MySQLWorkbench and the remote connection.  I can get connected fine and do a command or two but then the connection goes away and I have reconnect again.  Is there a way to adjust the timeout on the remote connection?

    Kind Regards,

    Carol H.

    • That is certainly strange. I’ve never encountered that kind of timeout with MySQLWorkbench. If you can monitor the system logs at the moment of disconnect you might find a cause, or at least an error code. Of course, if you are in a shared server, you would need to have Live Support check those logs.

  • Hello,

    Can I use no-ip address when adding hosts? I doesn’t seem to work.

    for example: “user.no-ip.org” as a host

     

    Thanks

    • Hello Maor Levy,

      Access to the databases are done per IP address, so you need to have an IP address (or range of addresses) in order to make the remote connection. This is done due to security constraints.

      If you have any further questions or comments, please let us know.

      Regards,
      Arnel C.

  • Hi,

    I am trying to set up Mysql database on my system. When i try to connect the database from another system, it gives an error ” remote server not allows you to connect. Ask administrator to add your computer or use the % wildcard on the server host”

    How do we use the wild card or add another system ? 

     

    Thanks & Regards,

     

    Sinoj

    • Hi Sinoj Cyraic,

      It sounds like you didn’t add the IP to the firewall, as outlined in the steps. I’d suggest trying that. If you still have problems, reaching out to support would be your best option. We can help you add it accordingly.

  • I am trying to connect using Dreamweaver CC 2015 to mysql database.  I am getting the following error message:

    Your php server doesn’t have MySQL module loaded or you can’t use the mysql_(p)connect function.

    Any help would be greatly appreciated.

    • Sorry to hear you were having trouble making the mySQL connection. Were there any steps in the above article you had trouble with? Did you add your IP to cPanel to allow the connection?

  • Thanks, In my case I have dynamic IP so I use no-ip to get a domain name, so the question is: Can I put that domain name and access to mysql in that way?

  • I mean a dynamic DNS service like no-ip.org that points a specific address myname.no-ip.org to my real IP that is a dynamic one. It is a solution to dynamic addresses.

    I want to put it in the “add host” field in remote mysql, can this be done?

     

    Thks

    • Hello Mauricio,

      If the domain name is resolving so that it can be used on the internet, then it should connect. You would have to use the domain name in the Access Host area.

      I hope this helps to answer your question, please let us know if you require any further assistance.

      Regards,
      Arnel C.

    • Hello Mauricio,

      Thanks for the question. I’m not sure what you mean by “no-ip”, unless you’re saying to make the connection with a domain name – which can be done. You can’t create a rule in the Remote MySQL connection to prevent no-IP connections. You can create ranges of IP addresses, but in general, you’ll want to specify a specific IP address to access the MySQL database. Making it dynamic and allowing a large range of addresses may compromise the security of your database.

      I hope this helps to answer your question, please let us know if you require any further assistance.

      Regards,
      Arnel C.

    • Hello Arun,

      Thanks for the question about MySQL server and JAVA. This will require coding and we do not provide coding support as it is beyond our scope of support. However, we do try to point you in a direction that will help. Check out the official documentation for MYSQL connection using Java here.

      If you have any further questions or comments, please let us know.

      Regards,
      Arnel C.

    • Hello hamid,

      Thank you for contacting us. If you are connecting to a remote server, just use the domain as the hostname.

      Such as: example.com

      If you have any further questions, feel free to post them below.

      Thank you,
      John-Paul

  • already solved sir :). Thank you. My hosting provider has blocked the port for mysql, so i use simple web services in php to acces the DB.

    Thank you…

  • Hello sir, i’ve make a remote database in cpanel on my hosting, but i cant acces thaint database both in heidi sql, navicat or from my program (written in C#). my program gimme error “Unable to connect to any of specified my sql host”. Btw, i used mysql connector for .NET

    • Hello Agriirga,

      Have you made you sure you added your IP address in cPanel as it instructed? If so can you provide us your domain so we may take a look into your account and try to replicate the error.

      Kindest Regards,
      TJ Edens

  • String host= “jdbc:mysql://%.%.%.%/a21_senkwaepos”;

    String uname = “username”;

    String upass = “password”;

            try {

                Class.forName(“com.mysql.jdbc.Driver”);

                con = DriverManager.getConnection(host, uname, upass);

    am having error messages with the connection to the database..

    • Hello Fish,

      What is the error that you’re getting about problems connecting to the database? It’s hard to troubleshoot without seeing your full code or the specific error. I would make sure to reference the connecting to MySQL using JDBC guide from MySQL.

      Also you want to make sure that your host is a valid entry that your Java application can connect remotely to. So I’m not sure if you were just using jdbc:mysql://%.%.%.%/a21_senkwaepos as an example. But that needs to be a valid IP address or hostname:

      String url = “jdbc:mysql://localhost/test”;
      Class.forName (“com.mysql.jdbc.Driver”).newInstance ();
      Connection conn = DriverManager.getConnection (url, “username”, “password”);

      – Jacob

  • Thanks for the quick reply Scott.

    I can connect fine through a dummy php page I uploaded (using an example on IMH site), but still can’t connect using the JAVA app. Could you tell me what format the domain should be:-

    Eg. “jdbc:mysql://www.mydomain.org.uk:3306″

    Steve

    • Hello Steve,

      Within the snippet you provided, have you tried using the IP address instead of the domain name? Also, are you adding /dbname at the end before you provide the username and password? It should appear something like this:
      jdbc:mysql://123.123.123.123/db_name, db_user, password

      If not, please let me know any error text you are receiving as well.

      Kindest Regards,
      Scott M

  • Hello Jacob (or colleague)

    I’m trying to connect to my remote MySQL database using a JAVA program (that I used to process data from a CSV file). It all works fine when I connect to my local database, but when I change the settings to access the remote database, I can’t connect.  I discovered this morning that I don’t have SSH access to the remote database; does this mean that I will not be able to connect remotely using my JAVA app? Will I have to use something like WorkBench or Heidi? (I have got PHPMyAdmin access through cPanel, but it would be much quicker to load the database directly from the JAVA app.)

    Any help appreciated.

    • Hello Steve,

      As long as your IP is added to the Remote MySQL Connection tool and the settings are correct, there should be no issue with you connecting to the database remotely. You may opt to use Workbench, NaviCat, or Heidi as well, but they require the same setup as your Java app would. It may be good to test with one of those other applications as well as part of the troubleshooting process.

      Kindest Regards,
      Scott M

  • The error I’m getting is this:

    Lost connection to MySQL server at ‘reading initial communication packet’, system error: 110

    and yea the local connection works. What’s this about a different password for remote connection? How do I find that?

    • Hello again Ron,

      I’d assume either the server address you’re using, or the username and password are incorrect based off that error. Either that or possibly you have a local firewall running that is preventing the outbound connection on port 3306.

      Yes you do need a different username / password typically for a remote connection. This would be the database username and password that you setup on the remote server. For instance you need to create and attach a database user to a database in order to remotely connect to it.

      – Jacob

    • Hello Ron,

      What code are you trying to currently use, and is it resulting in any errors? Also are you allowing the remote IP address of the server that you’re running your PHP script from? Because that’s the one that will need to be added, and not your local IP if the PHP server is running from another server.

      There is an explanation as well as examples of mysql_connect from the PHP.net site.

      If your script works locally but not remotely, I’d try this basic example:

      <?php
      $link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
      if (!$link) {
          die('Could not connect: ' . mysql_error());
      }
      echo 'Connected successfully';
      mysql_close($link);
      ?>

      After ensuring that the localhost variable is set to the server that is hosting your MySQL database, and also that the mysql_user, and mysql_password settings are correct what error is it giving back after the Could not connect: text?

      – Jacob

  • I’m trying to do a remote mysql access – and I can’t get it to work. There’s obviously more to it than is explained here.

  • This doesn’t explain how to connect – i.e. what should the mysql_connect look like. I can’t get it to work and all I can find are platitudes.

    • Hello Ron,

      This guide just covers how to allow a remote MySQL connection into the server. If you’re looking for how to connect to MySQL from PHP, we do have a guide on connecting to MySQL with PHP that does go over mysql_connect with this bit of code in the second step of the guide:

      $con = mysql_connect("localhost","inmoti6_myuser","mypassword");

      Please let us know if you had any other questions at all.

      – Jacob

  • I’m trying to synchronize data tables  local install (mamp)  phpmysqladmin and it failes. I’m unable to troblehoot with telnet connection.

    Upon telneting to my site, it ask only for password and not a username.  I already assigned username and password for specific database.  Which password is it waiting for? cpanel, my domain login to inmotion, or something else? 

    After several failed attempts, i’m not allowed connet back to my ip address and get a packet out of order.

     

    The error message attempting to connect:

    5.5.36-cll?r”L=*W\?*?<P]cZwg”n(mysql_native_password (mypasswordhere)
    !#08S01Got packets out of orderConnection closed by foreign host.

    ?Host ‘107-223-112-160.lightspeed.sndgca.sbcglobal.net’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’Connection closed by foreign host.

    Do i have the ‘mysqladmin flush-hosts ability? It doesn’t work in sql screen and looks like a command prompt from the unix shell.  I don’t have access directly to that unix shell that i’m aware of.

     

    Example Edited log below.

    Joeys-MacAir:cgi-bin boun619$ telnet 70.39.999.999 3306
    Trying 70.39.999.999…
    Connected to ecbiz999999.inmotionhosting.com.
    Escape character is ‘^]’.
    N
    5.5.36-cllf?=Z/z:Rm?(u6KVOh=9E-Rmysql_native_password

    Connection closed by foreign host.

     

    Any Ideas?

    -Joey

    • First, you will not be able to telnet into your account. You will have to use a remote MySQL connection instead. When logging in remotely, you will be using the username and password to the MySQL user that is accessing the database that you want to connect to. This is something that you would have created within cPanel. As for the flush-hosts issue, this is caused by too many bad connections to the server and would need to be resolved by contacting technical support.

Leave a Reply to Maor Levy Cancel reply