Connecting to Remote SQl database

Need help installing Joomla!? For all Joomla! installation issues please use this forum.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
ekeller21
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Wed Oct 08, 2008 1:42 pm

Connecting to Remote SQl database

Postby ekeller21 » Wed Oct 08, 2008 1:52 pm

I am creating a joomla based site for a client of mine. For various reasons they are unable to have an SQL database on their own server. My plan was to created a database on a hosting plan I have and then link the site to that.

When I input the info (hosting name, user, password, etc) and click next, it says that the values are incorrect. I have triple check the values and I know they are correct. My thought is that Joomla is searching for the database on the server where joomla is installed.

Question: How do I go about linking to an external database??

User avatar
PhilD
Joomla! Hero
Joomla! Hero
Posts: 2727
Joined: Sat Oct 21, 2006 10:20 pm
Location: Wisconsin USA
Contact:

Re: Connecting to Remote SQl database

Postby PhilD » Thu Oct 09, 2008 5:33 am

Though a remote database connection will likely be rather slow, it should be able to be done. I do it with sqlyog all the time to sync my laptop with the live site.

You need the mysql host address of the database you want to connect to. It is usually the actual server that mysql lives on, which is probably the server your site lives on like this: athena.medialayer.net It most likely will not be your domain name.

You will also need the database user name for the database you want to connect to. I would make a specific one for this usage with the normal full privileges.

you need the password for this database user name

The above will go in Joomla setup.

Now one other thing to do, and that is to allow another site to connect remotely to your database.
In the database you want to connect to, under your domains control panel mysql management, select the desired database and scroll down to access host. Enter the ip address of the clients domain that you want to connect to the database. This will allow your clients domain (joomla) to access your database on your domain.

It is helpful if the client has a static ip address. If dynamic and it changes, the connection will break to your database and you will have to add the new ip address to your database to get it working again.
PhilD -- Unrequested PM's and/or emails may not get a response.
Security Moderator

User avatar
aclm
Joomla! Apprentice
Joomla! Apprentice
Posts: 8
Joined: Sat Oct 14, 2006 8:07 pm
Location: Austin, TX
Contact:

Re: Connecting to Remote SQl database

Postby aclm » Fri Oct 10, 2008 8:02 am

Ok so I have the same problem.

I am looking in the configuration.php

I suppose the line to edit is:

$mosConfig_db = 'site_joom';

I ran a script to show me the absolute path but I dont know how it would relate to creating a remote path on a another server.

mysql:
/usr/bin/mysql
/usr/lib/mysql
/usr/local/lib/mysql.sock
/usr/include/mysql
/usr/share/mysql
/usr/share/man/man1/mysql.1.gz

I am on a linux shared hosting.
I have already added the IP of the remote server to my access host list.

What should this path look like? What would be the syntax?

Thank you
Building sand castles since 1999.

User avatar
PhilD
Joomla! Hero
Joomla! Hero
Posts: 2727
Joined: Sat Oct 21, 2006 10:20 pm
Location: Wisconsin USA
Contact:

Re: Connecting to Remote SQl database

Postby PhilD » Sat Oct 11, 2008 4:04 am

Just to start off, Joomla does work with remote database connections.

I am going to put what I posted earlier in a better format.

To connect a Joomla site to a remote database, (not on your host account) you will need to make changes to the following in the configuration.php file in Joomla. You will also need to give the remote database the ip address of the server you will connect with.

This is what needs changing in the Joomla configuration.php file to connect to the new database:

$mosConfig_db = 'remote_database_name';
$mosConfig_host = 'remote_database_host';
$mosConfig_password = 'remote_db_usr_password';
$mosConfig_user = 'remote_db_usr_name';

The config line $mosConfig_host = 'remote_database_host'; is the db host you want to connect to (normally localhost) and in this case a remote db host. In my case 'athena.medialayer.net' is the remote db host I connect to for a remote database connection. Put the remote server in without the http:// . In a normal Joomla install this would be localhost, but your trying to connect to a remote database so you have to put in the server name the remote database lives on.

If you can't figure out the proper server name, then ask your hosts tech support for the name of the server your needing the information for. You may also need the mysql port number but try connecting without the number first. Standard mysql port number is 3306, but hosts can change it. Your host may also have a FAQ or help file explaining how to connect to a database remotely.

You will still need to set up the remote database (the one you want to connect to) properly to enable a connection from an remote ip address.

Log in to your desired remote domain (where the database is you want to connect to) and go to the mysql management area.
Set up a new user and password in the desired database. This is the one you will use along with the database name and the mysql server name in Joomla to remote connect to this database.
Scroll down the page below where the users are listed and you should find Access Hosts (or remote database connection). Enter the ip address of the server you want to connect from here. This should be a static ip address for best results and could be the domain ip of a clients server like in the first posters question.


If your looking to change the servers my.cnf file to setup remote access, then this is beyond the scope of this forum and I would suggest you look on a mysql forum for help.
PhilD -- Unrequested PM's and/or emails may not get a response.
Security Moderator

mansour_81
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Tue Aug 11, 2009 8:29 pm

Re: Connecting to Remote SQl database

Postby mansour_81 » Mon Dec 07, 2009 7:13 am

Hi PhilD,

I have a problem installing my joomla 1.5. Installation is going on two servers: web server and database server. I used to have jooma intalled with db on the same server but now i want to split them. What i did is that i took full back up using joomla pack, installed mysql database on seperate server (up and running), and when i run jooma pack installer and get to database restore. I fill up database connection parameter and i get a msg: could not connect to database.

I am running test installation on vmware enviroment before applying it on real servers. The severs can ping each other, and i am able to connect from web to db server using mysql client and work on the database normally.

Do you have an idea why i am getting that msg (could not connect to db server) ? Is it related to database connection parameter "Database server host name" as i am using ip address of db server instead of hostname ? Should the database server hostname be registered on dns server in order to proceed with installation or the probelm is somewhere else ?
I really appreciate your attention and reply.

Regards,

Mansour

User avatar
PhilD
Joomla! Hero
Joomla! Hero
Posts: 2727
Joined: Sat Oct 21, 2006 10:20 pm
Location: Wisconsin USA
Contact:

Re: Connecting to Remote SQl database

Postby PhilD » Mon Dec 07, 2009 7:30 am

If you can connect to the database server with the mysql client, or phpmyadmin then what login parameters are you using there?

Try creating a database username and database user password for the database with the client and don't forget to assign the user to the database. Then try that.

I think host may be able to use the ip, but normally would be localhost or whatever FQDN name you may have assigned to it such as sql200.byethost12.com
PhilD -- Unrequested PM's and/or emails may not get a response.
Security Moderator

mansour_81
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Tue Aug 11, 2009 8:29 pm

Re: Connecting to Remote SQl database

Postby mansour_81 » Mon Dec 07, 2009 7:40 am

Hi,

Thanks for your quick response.

Web server: 192.168.201.11
Database server: 192.168.201.12

To connect to the database server using mysql client i write:
mysql -u root -p -h 192.168.201.12 and write the password and am in.

I also grant user root full permission to all databases in mysql:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.201.12' IDENTIFIED BY 'mypassword';

For db parameter connection:
Database type :mysql
Database server host name:192.168.201.12
User name: root
Password : mypassword
Database name: joomla

So there are no other steps to be done else from what i mentioned ? I will try to create a new user name and password on the joomla database from mysql client and assign to the db n hope it will solve my problem. Else i would have to try using registered FQDN in the host name right ?

Regards...

mansour_81
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Tue Aug 11, 2009 8:29 pm

Re: Connecting to Remote SQl database

Postby mansour_81 » Mon Dec 07, 2009 8:12 am

I have tried creating a database username and database user password for the database with the mysql client and assigned the user to the database. But i got stuck to the same problem.

The only thing left is to try registered FQDN for variable hostname (database connection parameter). Can you please correct me if i am wrong ? Do you have another suggestions ?


Thanks

banik78
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Thu Dec 31, 2009 8:49 am

Re: Connecting to Remote SQl database

Postby banik78 » Wed Aug 25, 2010 7:17 am

Hi guys, I have a subdomain e.g. sub.mydomain.com on a server and I have another host server that hosts my joomla site. How I can connect that subdomain to the joomla site on the other server? It is a pain. I need istructions please. Thanks.

User avatar
PhilD
Joomla! Hero
Joomla! Hero
Posts: 2727
Joined: Sat Oct 21, 2006 10:20 pm
Location: Wisconsin USA
Contact:

Re: Connecting to Remote SQl database

Postby PhilD » Wed Jan 18, 2012 11:20 pm

The instructions are above
PhilD -- Unrequested PM's and/or emails may not get a response.
Security Moderator

a_amado
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Tue Jan 31, 2012 11:01 pm

Re: Connecting to Remote SQl database

Postby a_amado » Tue Jan 31, 2012 11:46 pm

Hi all,

I'm new to this forum. I've been reading about how to connect joomla to a remote mysql database, but I'm not beeing able to do it :(

At this time, I have 2 VMs (unix): one running mysql server and another with lamp installed. I want to connect lamp's VM to the MySQL server (the other VM), but I must be doing something wrong.

According to the instructions above, I have changed the configuration.php:
$mosConfig_db = 'database_name';
$mosConfig_host = 'remote_database_ip';
$mosConfig_password = 'remote_db_usr_password';
$mosConfig_user = 'remote_db_usr_name';

For db user, for now, I'm still using the default one (root with no password) just for testing purposes. Joomla version 1.5

Can anyone help please?

Thanks!
regards

User avatar
PhilD
Joomla! Hero
Joomla! Hero
Posts: 2727
Joined: Sat Oct 21, 2006 10:20 pm
Location: Wisconsin USA
Contact:

Re: Connecting to Remote SQl database

Postby PhilD » Wed Feb 01, 2012 12:58 am

"For db user, for now, I'm still using the default one (root with no password) just for testing purposes. Joomla version 1.5"

First, set a password. Many issues can be fixed by setting things up properly.
Second you have to add the ip address of the computer needing access to the database as a remote access (Access hosts/remote access depending upon setup)
Third the database server has to be set up properly to allow remote connections by ip address. Not all hosts or setups will allow remote connections.
Fourth Joomla needs to be told the server name of the server hosting the database. This is not your domain name or account name, but rather the server name your database resides on or server your domain is on (if same).

viewtopic.php?f=35&t=332410&p=2733284#p1444761
and the one above it

"I want to connect lamp's VM to the MySQL server (the other VM), but I must be doing something wrong."
Running a 2 boxes one with the webserver and a second as a database server is beyond the scope of this post and I would suggest you look in some Linux forums on how to properly tie the two together.
PhilD -- Unrequested PM's and/or emails may not get a response.
Security Moderator

tittbit
Joomla! Apprentice
Joomla! Apprentice
Posts: 7
Joined: Sun Mar 11, 2012 7:55 am
Contact:

Re: Connecting to Remote SQl database

Postby tittbit » Mon Mar 12, 2012 7:39 am

its not complicates, you have to tell joomla where to find databse, i mean the server.

so for that u can specify the ip address of your server where u have your db,
and put name and pass as u have,

and then the additional things u have to do is that u have to enable remote mysql access on your server, i think cpanel has the wizard for that, plz look yourself as i remember that it should be there.

then create a user on your server as normally and assign that all priveleges required.

tell me if it solves the problem otherwise i have to open my old notes treasures.
Avoid spam with one time usable email address at http://www.tittbit.in or 10 minute mail only at http://www.spamsalad.in


Return to “Installation - 1.0.x”

Who is online

Users browsing this forum: Yahoo [Bot] and 2 guests