Connect to mysql on a different server

Question

Im trying to connect to mysql database server from another server. The following are configurations on both servers.

Server 1: I installed xampp with php and apache services and it has the following ip 172.x1.x1.x1

Server 2: I installed mysql and it has the following ip 172.x1.x1.x2.

the following is the connection script I am using to connect to the database

<?php
    //Database connection constants
    define("DATABASE_HOST", "172.x1.x1.x2");
    define("DATABASE_USERNAME", "root");
    define("DATABASE_PASSWORD", "");
    define("DATABASE_NAME", "management");
?>

The above script is in a file called app_config.php and its located in server 1 The following script is in a file called connect.php and its also located in server 1

<?php
require 'app_config.php';
$connect_error = 'Sorry we\'experiencing connection problems.';
$table_error = 'Table not found';
mysql_connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD)
or die($connect_error);

mysql_select_db(DATABASE_NAME) or die($table_error); ?> now when I try to connect I get the following error

Warning: mysql_connect() [function.mysql-connect]: Host 'hr1.saqa.co.za' is not allowed to connect to this MySQL server in C:\xampp\htdocs\scripts\functions\database\connect.php on line 4

Fatal error: Call to undefined function handle_error() in C:\xampp\htdocs\scripts\functions\database\connect.php on line 5

It will be awsome if you can help me guys.

You should stop using `mysql_*` and port your code to either `mysqli` or `PDO`. mysql is obosolete, and it will most likely be completely removed in the next major release. — Vlad Preda, Jan 21 '13 at 12:26
Try this one :) http://stackoverflow.com/questions/822902/access-xampp-localhost-from-internet — Ivo Pereira, Jan 21 '13 at 12:26
You also need to set it so the user is allowed to connect to the database from remote IP addresses, or set a specific IP address. — EM-Creations, Jan 21 '13 at 12:27
@VladPreda I'l eventually move away from mysql_* but I am still new to php and the book I was reading still uses that script. — user1783675, Jan 21 '13 at 13:38

Suku · Accepted Answer · 2013-01-21T12:36:47.437

3

Since your database server is different from your php/apache server you need to specify the hostname as 172.x1.x1.x2 in mysql-php connection string.

Also make sure that mysql user root have remote connection permission. Other wise mysql-server will not allow your root user to login remotely. i.e. from your server1.

You can make sure that from mysql.user table.

mysql> select Host,User from user where User = "root";
+------------+------+
| Host       | User |
+------------+------+
| 127.0.0.1  | root |
| ::1        | root |
| localhost  | root |
| sgeorge-mn | root |
| %          | root |
+------------+------+
4 rows in set (0.01 sec)

% means any host.

To create a user with remote connection permission, use following mysql query:

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'your_password';

edited Jan 21 '13 at 12:36

answered Jan 21 '13 at 12:26

Suku

3,820
1
21
23

I updated my script with the correct ip, but I am still getting the same error – user1783675 Jan 21 '13 at 12:31
see my answer part about `mysql.user` – Suku Jan 21 '13 at 12:32
Just a further explanation, these servers both run windows server 2008 r2 – user1783675 Jan 21 '13 at 12:35
do I run this sql command in the sql database or the webserver ? – user1783675 Jan 21 '13 at 12:37
Off course, from DB server. From webserver you can't run `sql` query. ;-). Also don't for give `GRANT PRIVILEGES` for this new root user with `%` as host – Suku Jan 21 '13 at 12:39
Now the first error is resolved, only the fatal error still appears – user1783675 Jan 21 '13 at 12:43
The fatal error means you don't have a function called "handle_error" anywhere in your code. – 1615903 Jan 21 '13 at 12:44
Its the mistake in your code. nothing to do with mysql connection string. – Suku Jan 21 '13 at 12:46
Thanks Suku, let me correct my code and I will update to say if its working now – user1783675 Jan 21 '13 at 12:51
@Suku, please have a look at the update I made to the script. and I am sure that database called management exists – user1783675 Jan 21 '13 at 13:01
It not really an error, I get the message 'table not found' meaning it doesnt connect to the database called management. I updated the script, please have a look at it – user1783675 Jan 21 '13 at 13:20

score 2 · Answer 2 · answered Jan 21 '13 at 12:33

2

1)Make sure firewall Allow MySQL.

2)Make sure MySQL remote connection permission Allowed .

answered Jan 21 '13 at 12:33

mohammad mohsenipur

3,218
2
17
22

Hi I dropped the firewall and opened port 3306 on the database server – user1783675 Jan 21 '13 at 12:34
IF you are sure MySQL remote connection permission Allowed . put port address on ip (172.x1.x1.x2:3306) and check php.ini for MySQL config – mohammad mohsenipur Jan 21 '13 at 13:01
1

Yeah! I checked it twice to ensure that permission is allowed. Now it connects to the database, but doesnt find the database called management – user1783675 Jan 21 '13 at 13:08
get Result of $conn=mysql_connect and use it on mysql_select_db(DATABASE_NAME,$conn) or die($table_error); and check your MySQL user access set correctly dont test by root user because of this user access to all DB – mohammad mohsenipur Jan 21 '13 at 13:58

Connect to mysql on a different server

2 Answers2

Linked