Development

Database Connection Mis-information

Whilst on my usual rounds on StackOverflow to help spread useful knowledge of computing and web development I came across the following comments on a thread:

Um... mysql doesn't connect over http... nor to a directory
Neither should it be an url. Usually it's localhost or the IP address of the remote mysql server

The first of these is, to my knowledge, correct.  However the second is absolutely wrong.  A connection to a database, or at least MySQL, does not have to be via IP address specifically.  Let's break down the claim a little further:

Usually it's localhost

This is often the case for development purposes as developers tend to run a small development database locally, allowing them to work whilst on the go and without an internet connection.

Some hosts will also tell you to use localhost and have a database server running on the platform you are deploying to.  Similarly, some websites run their databases on their webserver (I'll go into why that's not always a good idea another time).

I guess this part of the claim is true.

or the IP adress of the remote mysql server

I'll start by saying this part is also true, as it's perfectly possible to replace 'localhost' with, say, '192.168.0.7' for things on a local network, or '81.81.81.81' if that's your MySQL server somewhere.

Neither should it be an url

This is where the remainder of the comment loses all credibility, and unfortunately it's at the beginning of the comment.  It's perfectly possible to establish a database connection to something like 'database01.myhost.com' or, a more real example, 'mysql-master.garybell.co.uk' (though that doesn't work, so save your time).  That leads me on to why you might want to use a URL over an IP address:

Company X has a SaaS application which stores data in a MySQL database.  They have multiple servers and their database is on a large distributed cluster and/or has master-slave replication, so if one of their servers needs maintenance or dies, they can switch over easily.

Their connection configuration files are set up on each application as follows:

// database connection information
$database['host'] = '10.17.83.123'; // master server
$database['user'] = 'web_app_user';
$database['password'] = 'w3B4pPU53R!';
$database['schema'] = 'client1';

If their master server blew up, the data centre suffered a catastrophic failure or any other reason why a connection into that IP was stopped, then they would need to correct the configuration files of all of their clients.  If that is only 2 or 3, then no problem.  When it runs into tens, hundreds or thousands it's just not practical.

They decide the sensible thing form the get go is to use a similar config file:

// database connection information
$database['host'] = 'mysql-master.companyx.com'; // master server on 10.17.83.123
$database['user'] = 'web_app_user';
$database['password'] = 'w3B4pPU53R!';
$database['schema'] = 'client1';

If the same disaster is thrust upon them they can open up their DNS manager, point the domain 'mysql-master.companyx.com' to one of their slave servers and, with a quick change of settings on the slave server have their hundreds of customer systems running with minimal interruption, all from using a URL.

A URL is a Uniform Resource Locator, and in this case the resource is MySQL, so it's perfectly possible to use a URL as a database host name and, in some cases, actually preferential.

Read next