PHP connection to remote MySQL server [duplicate]

Question

When I try to connect through the shell of the local machine at the remote MySQL server I can successfully connect:

> mysql -h remotehost -u myuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

But when I try to connect through a PHP script using

$serverName = "remotehost"; // here I put the actual IP address
$userName = "myuser";
$passCode = "actualpassword";
mysql_connect($serverName,$userName,$passCode);

I get the following error

Warning: mysql_connect(): Access denied for user 'myuser'@'localhost' (using password: YES)

The remote MySQL server version is 5.1.52 and the PHP version in the local machine is 5.3.10-1ubuntu3.4

I found a similar question but the answer does not solve my problem: problem connecting to remote mysql database using php

I'd really appreciate some help!

EDIT:

The output of php -i | grep "mysql"

/etc/php5/cli/conf.d/mysql.ini,

/etc/php5/cli/conf.d/mysqli.ini,

/etc/php5/cli/conf.d/pdo_mysql.ini

mysql

MYSQL_SOCKET => /var/run/mysqld/mysqld.sock

MYSQL_INCLUDE => -I/usr/include/mysql

MYSQL_LIBS => -L/usr/lib/i386-linux-gnu -lmysqlclient_r

mysql.allow_local_infile => On => On

mysql.allow_persistent => On => On

mysql.connect_timeout => 60 => 60

mysql.default_host => no value => no value

mysql.default_password => no value => no value

mysql.default_port => no value => no value

mysql.default_socket => /var/run/mysqld/mysqld.sock => /var/run/mysqld/mysqld.sock

mysql.default_user => no value => no value

mysql.max_links => Unlimited => Unlimited

mysql.max_persistent => Unlimited => Unlimited

mysql.trace_mode => Off => Off

mysqli

MYSQLI_SOCKET => /var/run/mysqld/mysqld.sock

mysqli.allow_local_infile => On => On

mysqli.allow_persistent => On => On

mysqli.default_host => no value => no value

mysqli.default_port => 3306 => 3306

mysqli.default_pw => no value => no value

mysqli.default_socket => /var/run/mysqld/mysqld.sock => /var/run/mysqld/mysqld.sock

mysqli.default_user => no value => no value

mysqli.max_links => Unlimited => Unlimited

mysqli.max_persistent => Unlimited => Unlimited

mysqli.reconnect => Off => Off

PDO drivers => mysql

pdo_mysql

pdo_mysql.default_socket => /var/run/mysqld/mysqld.sock => /var/run/mysqld/mysqld.sock

You say its a remote server but the error says localhost. Which is it? — Galen
– Galen, Commented Mar 17, 2013 at 18:33
this is the error I get in the local machine when I try to connect to the remote server — Vasilis
– Vasilis, Commented Mar 17, 2013 at 18:34
myuser is only allowed to login from localhost. You need to change/add a user that allows your host. — Bart
– Bart, Commented Mar 17, 2013 at 18:48
Why are you using the long-deprecated mysql_ code library? It was discontinued many years ago and removed entirely in PHP7. No new code should be written using this library. It leaves you vulnerable to SQL injection attacks (due to the lack of parameterised query support) and potentially other unpatched vulnerabilities. Switch to using mysqli or PDO as soon as possible, and then learn how to write parameterised queries to protect your data from malicious input. See bobby-tables.com for a simple explanation of the risks and some sample PHP code to write queries safely. — ADyson
– ADyson, Commented Nov 10, 2017 at 12:22

Anonymous · Accepted Answer · 2013-03-17 18:35:26Z

0

Before proceeding, confirm the $serverName variable really is a remote address, either a hostname or an IP address. Then...

Be sure you have enabled remote access on the SQL server. See this link for details: http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html
Ensure that you have told the remote MySQL server to allow the local IP address' access.
Ensure the username and password are correct.

answered Mar 17, 2013 at 18:35

Anonymous

5536 gold badges17 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vasilis Over a year ago

I try to ensure the above by trying to connect through the shell which succeeds

printfmyname · Accepted Answer · 2013-03-17 18:42:42Z

0

You need to allow remote access to the mySQL(on the server where you have mySQL db). If you are using CPanel or Plesk, you can do it in the control panel. I had this problem, apparently mySQL default config is to only allow localhost.

answered Mar 17, 2013 at 18:42

printfmyname

98115 silver badges32 bronze badges

Comments

gmartellino · Accepted Answer · 2013-03-17 18:43:07Z

0

You need to grant access for that user to be accessible by your server's IP address. Often times the MySQL user is locked down to the localhost.

A quick way to do this would be to look at the table mysql.users and see if that user is locked down to the localhost.

There are many nice GUIs that can help with adding hosts, but the most manual way possible would be to add another row to the DB. You could change the host to '%' to test but that opens things up and is less secure.

answered Mar 17, 2013 at 18:43

gmartellino

7075 silver badges16 bronze badges

5 Comments

Vasilis Over a year ago

how is it possible to do that?

gmartellino Over a year ago

I just edited my response. Are you using a particular GUI, phpMyAdmin, or just manually using MySQL CLI?

Vasilis Over a year ago

I'm using the phpmyadmin gui, I specify the user host as % but it doesn't work. I can connect remotely from the mysql shell command, but not from php

gmartellino Over a year ago

can you post your MySQL runtime config for php, either via phpinfo or from the command line php -i | grep "mysql"

Vasilis Over a year ago

I added the output to my question!

Anthony Rutledge · Accepted Answer · 2014-05-30 14:12:26Z

Use SHOW GRANTS to see if the user you are trying to login with has permission to connect from the web server's IP or domain name.

mysql> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6381FFD48B21F7ED17AE12AF4F8BE4458F4B0AC7' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Use the GRANT command to give remote access to your web user, so to speak. Something like this.

mysql> GRANT SELECT ON database.table TO 'myuser'@'web_server_IP_or_domain_name' IDENTIFIED BY 'actualPassword';

Reference the MySQL 5.1 manual for exact syntax. MySQL 5.1 Manual: GRANT

matt · Accepted Answer · 2013-03-17 18:36:33Z

-1

Did you post actual code? If so, you forgot to enclose the strings in quotes:

$serverName = "remotehost"; // here I put the actual IP address
$userName   = "myuser";
$passCode   = "actualpassword";
mysql_connect($serverName,$userName,$passCode);

answered Mar 17, 2013 at 18:36

matt

3671 silver badge7 bronze badges

Collectives™ on Stack Overflow

PHP connection to remote MySQL server [duplicate]

5 Answers 5

1 Comment

Comments

5 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

5 Comments

Comments

Comments

Linked

Related