donglan9651
donglan9651
2015-07-31 11:39

无法使用php mysqli连接到远程服务器上的MySQL数据库

已采纳

I am trying to use mysqli to insert some data into a MySQL database (let's call the schema myDatabase), but cannot successfully connect. Here's the code snippet to connect:

...
$config = parse_ini_file('../includes/config.ini');
$username = $config['username'];
$password = $config['password'];
$dbname = $config['dbname'];
$server = $config['server'];
$conn = new mysqli($server, $username, $password, $dbname);
if (!$conn || $conn->connect_error)   {
  die( 'Connection Failed: ('.$conn->connect_errno.') '.$conn->connect_error);
}
...

I get the following result:

Connection Failed: (1045) Access denied for user 'myUser'@'my.laptop.ip.address' (using password: YES)

Here's some details on the set-up, in case they are relevant:
The code is on my laptop running Windows 7 and using PHP 5.3.5 that came with xammpp. The database is hosted on a remote server with MySQL5.1.52. I created a user to which I granted all privileges on myDatabase.*. No host was specified for the user (e.g. 'myUser'@'%'), as I am still in development and don't know the ip address where the code for the live application will be hosted.

If I ssh onto the database server, I can connect to mysql using the credentials for myUser and access the tables in myDatabase. I have another schema on this same server which is accessed by a different user, and have been able to use mysqli to connect without any problems.

Just to be sure it wasn't a typo, I dropped the user, and created it again, copying and pasting the username and password from the config.ini file used in my php code (and flushed privileges, of course). I did this again, except this time the host was specified, e.g. CREATE USER 'myUser'@'my.laptop.ip.address' IDENTIFIED BY 'myPassword'. I keep getting the same error and now I'm completely stumped.

Help, please.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dongpang2483 dongpang2483 6年前

    Okay, this is strange, but it appears the problem had to do with the password I was using. The original one contained some special characters ($, & +). When I changed it so that it only contained numbers, letters and underscore, it worked.

    Is this real, or did I accidentally do something else without realizing that turned out to be the actual solution?

    点赞 评论 复制链接分享
  • duanji4449 duanji4449 6年前

    On your mysql machine hit:

    GRANT ALL PRIVILEGES ON database.* TO 'myUser'@'%' IDENTIFIED BY 'newpassword';
    FLUSH PRIVILEGES;
    

    This will allow the user to connect from any host. Once it works, you can limit it to just a specific host and database.

    点赞 评论 复制链接分享