“拒绝用户访问"将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更改为num8er的127.0.0.1可能会起作用.

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/zh/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屋！