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 (18.104.22.168) and the hostname that is defined as mysql.database.
22.214.171.124Â Â Â 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.