0

I am planning to setup a portal where end users can create mysql database in there dev environment. I am using XAMPP to setup the portal.

Now I need to create a remote DB (hosted on UNIX server) from windows server using the portal. I am using PHP to create DB form.

Following is the code I use-

<?php
    //Attempt MySQL server connection on remote host server.
    $link = mysqli_connect("test.server.com", "mysqladmin", "pass");
    // Check connection
    if($link === false){die("ERROR: Could not connect. " . mysqli_connect_error());}
    // Attempt create database query execution
    $sql = "CREATE DATABASE vikas ";
    if(mysqli_query($link, $sql)){echo "Database created successfully";}
    else{echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);}
    // Close connection
    mysqli_close($link);
?>

Here test.server.com is a remote server hosted on UNIX. I am trying to create from windows server where XAMPP is installed. When I run the query I see that instead of going to remote server it is searching on localhost only.

Warning: mysqli_connect(): (HY000/1045): Access denied for user 'mysqladmin'@'dev-windows.server.com' (using password: YES) in C:\xampp\htdocs\login\create_db.php on line 4 ERROR: Could not connect. Access denied for user 'mysqladmin'@'dev-windows.server.com' (using password: YES)

dev-windows.server.com is windows server where XAMPP is installed and all websites will be created here only.
test.server.com is remote db server hosted in UNIX

DarkBee
  • 16,592
  • 6
  • 46
  • 58
Rahul Mukerjee
  • 1
  • 1
  • 4
  • 2
    you would need to allow first mysql remotely then you can do, as well make sure you have also allowed mysql default port in firewall inbound rules. And finally, mysql username should have appropriate permission to create database. – Girish Nov 28 '17 at 05:49
  • 1
    You kind of need to uncomment the code for it to work :-o – M. Eriksson Nov 28 '17 at 06:06
  • @MagnusEriksson I did not understand what do you mean by uncomment code to work? Do you mean comments are wrong.I have removed the comments but still not working – Rahul Mukerjee Nov 28 '17 at 06:10
  • @RahulMukerjee - Please go through this link https://stackoverflow.com/questions/14779104/how-to-allow-remote-connection-to-mysql. Hope it might helps you! – Elumalai Kaliyaperumal Nov 28 '17 at 06:14

2 Answers2

0

As you are getting response from the remote server of "Access Denied", so I assume that mysql port 3306 on remote server (test.server.com) is open for accepting connections from local server "dev-windows.server.com".

I am little confused when you are saying... "When i run the query i see that instead of going to remote server it is searching on localhost only.". This is how mysql throws errors. If you are connecting to "test.server.com", it will surely connect to it and will tell you why you are unable to connect. So, it clearly says that "mysqladmin" user @ "dev-windows.server.com" server is not allowed to connect to database on "test.server.com". Responses are given by remote server itself to the local server.

So, basically you need to allow connections on your remote server "test.server.com" from your local server "dev-windows.server.com". Please go on your remote server (test.server.com) via command line mysql client OR Adminer OR PHPMYADMIN and run a query something like this:

GRANT ALL ON *.* to mysqladmin@'dev-windows.server.com' IDENTIFIED BY 'pass';

And, it should work, I think. Also, you should ping test.server.com, if its really showing your remote server's IP.

EDIT: And, just for more security, please use IP addresses to connect like XXX.XXX.XXX.XXX in your mysql_connect function and do not use domain. Because, anyone can fake domain and try to connect combinations of passwords like an hacker. Use IP addresses instead.

GRANT ALL ON *.* to mysqladmin@'XXX.XXX.XXX.XXX' IDENTIFIED BY 'pass';
Rehmat
  • 2,121
  • 2
  • 24
  • 28
  • when i run it to make connection to remote server it is trying to make connection on my local server instead of making connection to remote server. – Rahul Mukerjee Nov 28 '17 at 07:46
  • @RahulMukerjee: How do you know that its making connection to local server instead of remote server? Did you try to ping "test,arbeteserver.com" from your local server. Do you see "test.arbeteserver.com = remote server's IP" when you ping? – Rehmat Nov 28 '17 at 10:00
  • If remote server's domain name is not a registered domain OR subdomain name, then you can try to edit /etc/hosts file on your local server and put "XXX.XXX.XXX.XXX test.arbeteserver.com". On Windows the file is "C:\Windows\System32\drivers\etc\hosts". Edit it as Administrator. – Rehmat Nov 28 '17 at 10:03
  • Thanks all of you.It finally worked out.Thanks to @rsharpy .I followed your steps and it worked fine. – Rahul Mukerjee Nov 29 '17 at 06:01
  • @RahulMukerjee I am glad that it worked for you. Please accept the answer by upvoting it. :D – Rehmat Nov 29 '17 at 09:48
0

You need to do few steps via SQL Commands

  • Create Database (as you did)
  • Assign user to the database (with grants)
  • Select database

Then your query will be executing fine.

Naveed Ramzan
  • 3,565
  • 3
  • 25
  • 30