Dedicated Server Connect to MySQL database from a remote connection

Dedicated Server Connect to MySQL database from a remote connection.

If you have a MySQL database with your hosting account and need to connect to it from your home computer, or another web server, you’ll need to add a remote “Access Host” to your database to allow the connection in.

In DA in your Dedicated Server, Go to:

User Level -> MySQL Management -> databasename -> Add Access Host

You can either add the IP of the remote connecting box, or just use:

%

Note that the correct login/password is still required, the Access Host is just another layer of security.

Also make sure that port 3306 is open in your Dedicated Server firewall on the DirectAdmin box, so the remote box can connect.

Setting up DA to use a remote MySQL server

If you host a large database that can’t be split up, and your server doesn’t have the resources to handle it, you can set up MySQL to be run on an external server.

This shouldn’t be needed too often, as usually, you can just move entire user accounts to another server to ease the load. But in the case of one large database using up the whole server, then you don’t have much choice.

Note that this guide does not transfer any databases over.

It’s generally a good idea to do this before you add users.

Also, MySQL will continue to run on your local DA, so existing databases and scripts should continue to function, however users will not be able to control them through DA.

1. The first step is to install MySQL onto the remote dedicated server. DA is not require on this server, as it is just used for MySQL and nothing else. I won’t go into the detail on how to do this (use Google).
2. The next step is to set up a user that DA can connect to on the remote MySQL server. The default DA user is da_admin, so staying with that name is a good idea, but not required.

You can use this guide to set up the da_admin user on your server.

The username and password you set up have to be set in the /usr/local/directadmin/conf/mysql.conf file for DA to use.

On a related note, if the remote server is on a LAN IP and the connecting IP to that remote server will not be your server IP (but rather some other IP such as a 192.168.x.x type of IP), then you can use this guideopen in new window to specify a default access host instead of the dedicated server IP.

Note that the above guide will only grants da_admin on the “localhost”, meaning you have to already be on that remote mysql server to use the account. We’ll need to add another access host (ip) to allow the DA server to connect to it.

Basically, you just run the “GRANT ALL PRIVILEGES ON . TO da_admin@localhost” command again, but you change localhost to the IP of your DA dedicated server:

GRANT ALL PRIVILEGES ON *.* TO da_admin@1.2.3.4 WITH GRANT OPTION;
FLUSH PRIVILEGES;

3. At this point, the remote MySQL database should be set up and ready to accept DA to use it. Test it out by logging into your DA machine via SSH, and type:

mysql -uda_admin -p --host=4.3.2.1

4. Now, the easier part is to tell DA to use the remote server. Edit the /usr/local/directadmin/conf/mysql.conf file and add the line:

host=4.3.2.1

5. That should be it. Log in to DA and check the MySQL section of your user level to see if you get any errors. If not, try adding a database to make sure it works.
6. The only cleanup task would then be to fix up phpMyAdmin to also connect to the remote host.

Edit the /var/www/html/phpMyAdmin/config.inc.php file , find this line:

$cfg['Servers'][$i]['host']          = 'localhost'; // MySQL hostname or IP address

$cfg['Servers'][$i]['host']          = '4.3.2.1'; // MySQL hostname or IP address

7. For new MySQL User databases, you’ll want to tell DirectAdmin about the new IP, so they have that IP assigned into their access hosts. Set the “host” value in the mysql.conf for remote database controlopen in new window or add multiple access_host values upon DB creationopen in new window.

Multiple access_hosts could be specified in mysql.conf file:

access_host=1.2.3.4
access_host1=2.3.4.5
access_host2=3.4.5.6

• the host= value must be set to some value other than localhost. (usually the IP of a remote box).
• the first access_host= entry must already exist. If you don’t have the first one, then the numbered values won’t be loaded.
• the number you use after the access_hostX= value doesn’t matter.. it can be any number, any order, out of order, eg: access_host76345834=1.2.3.4 is allowed.

Any database created with these values set will have the listed access_hosts added to their database (eg, 1.2.3.4, 2.3.4.5, 3.4.5.6). This also applies to the restores of the databases, even if the backed up tar.gz DBs do not not have any access_hosts listed in the backup.

NOTE:

As of DirectAdmin 1.57.0, you can install DirectAdmin without MySQL, allowing you to specify a remote box ahead of time: https://www.directadmin.com/features.php?id=2351open in new window

Don’t have dedicated server? get your own here