Cyprich.com

Occasionally Relevant Insights from a Software Builder

By

Using MySQL on Another Server

Many web servers have a database server on the same machine. A problem with this type of configuration is that it puts too much of a load on the server and overall performance can be poor with a high traffic web site. Database servers work best when they are on their own computer apart from the web server. The database can then use all of the computer’s resources to run more efficiently.

Finding the hosts File

Accessing a database such as MySQL on another server requires the use of the hosts file. This is used to connect to the database server by redirecting a hostname to the server’s IP. On Windows, you can find hosts in the operating system directory in the system32\drivers\etc\ folder. If your not sure where it is, use the file search function in your operating system.

In the hosts file, move to the bottom and enter the IP of the database server and hostname that you will use in your code to use the database. The following example shows the IP of the database server (74.208.28.228) and the hostname that is defined as mysql.database.

74.208.28.228    mysql.database

Connecting with PHP

An example of connecting with PHP:

$link = mysql_connect ('mysql.database', 'mysql_user', 'mysql_password');

A user account in MySQL to access the database needs to be created. You can’t use the root account when connecting to another server.

Once connected, you can perform functions on the database just like as if it was a local server on your computer. This procedure can also be used to connect to other database servers such as Microsoft SQL Server and PostgreSQL.

2 Responses to Using MySQL on Another Server

  1. whitesites says:

    I honestly think hardware is fast enough these days where you could run it all on the same box. Just a matter of stepping up your OS to 64 bit, and loading up the RAM so MySQL / SQL server is happy. Granted its not cheap ( RAM, SSD drives in RAID 0 ), but I would rather spend big bucks on hardware, than to pay my hosting company for extra rack space.

  2. Johan says:

    I find that when you host both a web and database server on the same machine, and it gets high traffic, the performance will be sluggish. A database server works best when it can use all of the resources in a computer.

    This is referring to microcomputers, however, a mainframe wouldn’t have those types of limitation.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.