“拒绝用户访问"将 MySQL 数据库移动到远程服务器后 [英] "access denied for user" after moving MySQL database to remote server

问题描述

我在访问我的数据库时遇到了一些问题.

I have some problems with accessing my database.

该脚本以前在我的本地主机上运行过.我在另一台服务器中导入了它，另一台服务器给了我一条拒绝访问的消息.

The script worked before on my localhost. I imported it in another server and the other server is giving me an access denied message.

给出的消息是:Access denied for user 'root'@'10.4.1.163' (using password: YES)

我使用的脚本是:

<?php
    // Connect to database server
    mysql_connect("localhost", "root", "password") or die (mysql_error ());

    // Select database
    mysql_select_db("database") or die(mysql_error());

    // SQL query
    $strSQL = "SELECT * FROM users WHERE user_id='". $_SESSION['USER_ID'] ."'";

    // Execute the query (the recordset $rs contains the result)
    $rs = mysql_query($strSQL);

    // Loop the recordset $rs
    // Each row will be made into an array ($row) using mysql_fetch_array
    while($row = mysql_fetch_array($rs)) {

       // Write the value of the column FirstName (which is now in the array $row)
      echo $row['Name'] . " ";
      }

    // Close the database connection
    mysql_close();
?>

我也尝试将 localhost 更改为 IP 地址 10.4.1.163.

I also tried to change localhost to the IP address 10.4.1.163.

我的脚本有什么问题.我确定我使用的密码是正确的.

What is wrong with my script. I am sure that the password that I am using is right.

有人知道我该如何解决这个问题吗?

Does anybody know how I can fix this?

推荐答案

MySQL 权限基于它们所连接的地址以及用户.所以 root@localhost 和 root@10.4.1.163 将有两组单独的权限.如果您的代码和数据库在同一台服务器上，则将 localhost 更改为 127.0.0.1 作为 num8er 提到的可能会起作用.

MySQL permissions are based on the address which they are connecting to as well as the user. So root@localhost and root@10.4.1.163 will have two separate set of permissions. Changing localhost to 127.0.0.1 as num8er mentioned will probably work if your code and the database are on the same server.

如果您可以通过终端访问您的 php 所在的框，您可以尝试使用以下命令直接连接，排除与 php 相关的任何事情:

If you have terminal access to the box where your php is you could try connecting directly ruling out anything to do with php using this:

mysql -h 10.4.1.163 -u root -p[pass] database -e "SHOW TABLES"

注意 -p 和密码之间没有空格.如果成功，这将为您提供 database 中的表列表.

Note there is no space between -p and the password. If successful this will get you a list of tables in database.

要授予其他用户或其他主机名/IP 的访问权限，您需要按照以下方式运行:(尽管您确实应该根据您的要求创建一个具有更多受限权限的单独用户).

To grant access to other users or for another hostname/IP you will want to run something along the lines of this: (though you should really create a separate user with more restricted permissions based on your requirements).

GRANT ALL PRIVILEGES ON `database`.* TO 'root'@'10.4.1.163';

在此处查看 MySQL GRANT 上的文档 - http://dev.mysql.com/doc/refman/5.7/en/grant.html

Check the docs on MySQL's GRANT here - http://dev.mysql.com/doc/refman/5.7/en/grant.html

附带说明 - 请不要在不至少使用 mysql_real_escape_string (http://php.net/manual/en/function.mysql-real-escape-string.php)在它之前.您还可以查看 PDO(http://php.net/manual/en/book.pdo.php)，它通常比现在已弃用的 mysql_ 函数

On a side note - please, please, please don't just pump any old data into a query without at least using mysql_real_escape_string (http://php.net/manual/en/function.mysql-real-escape-string.php) on it before hand. You could also look into PDO (http://php.net/manual/en/book.pdo.php) which is generally preferred over the now deprecated mysql_ functions

这篇关于“拒绝用户访问"将 MySQL 数据库移动到远程服务器后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！