无法使用 php 连接到 mysql

can't connect to mysql with php

提问by Chris Westbrook

I can't seem to connect to mysql with a php script, even though I can connect fine with phpmyadmin. I created a user with a password, and gave it the proper priveleges for the db, but everytime it connects it dies saying access denied. I am using xampp on a windows xp box. Firewalls are all disabled, and I've checked the username nad password are correct. Here's the code:

我似乎无法使用 php 脚本连接到 mysql，即使我可以与 phpmyadmin 连接正常。我用密码创建了一个用户，并为它提供了适当的数据库权限，但每次连接时它都会死说拒绝访问。我在 windows xp 盒子上使用 xampp。防火墙都被禁用了，我已经检查过用户名和密码是否正确。这是代码：

$conn=mysql_connect('localhost','westbrookc16','megadots') || die (mysql_error());

Do usernames have to be in a specific format or something?

用户名是否必须采用特定格式或其他格式？

回答by Emil H

I have a hunch that the problem here is the host you granted it to, though it's really not more than an educated guess. If you grant access myuser@'127.0.0.1' or the servers actual ip address, you won't be allowed to connect using localhost as host. This is due to the fact that when "localhost" is specified as host, php will assume that you want to use a unix socket instead of network sockets, and in that context 127.0.0.1 isn't the same as localhost.

我有一种预感，这里的问题是您授予它的主机，尽管这实际上只是一个有根据的猜测。如果您授予访问权限 myuser@'127.0.0.1' 或服务器的实际 IP 地址，则将不允许您使用 localhost 作为主机进行连接。这是因为当“localhost”被指定为主机时，php 会假设您要使用 unix 套接字而不是网络套接字，并且在这种情况下 127.0.0.1 与 localhost 不同。

From the manual entry for mysql_connect():

从mysql_connect()的手册条目：

Note: Whenever you specify "localhost" or "localhost:port" as server, the MySQL client library will override this and try to connect to a local socket (named pipe on Windows). If you want to use TCP/IP, use "127.0.0.1" instead of "localhost". If the MySQL client library tries to connect to the wrong local socket, you should set the correct path as Runtime Configuration in your PHP configuration and leave the server field blank.

注意：每当您指定“localhost”或“localhost:port”作为服务器时，MySQL 客户端库将覆盖它并尝试连接到本地套接字（Windows 上的命名管道）。如果要使用 TCP/IP，请使用“127.0.0.1”而不是“localhost”。如果 MySQL 客户端库尝试连接到错误的本地套接字，您应该在 PHP 配置中将正确的路径设置为运行时配置，并将服务器字段留空。

Hope this isn't totally redundant. :)

希望这不是完全多余的。:)

回答by Fire Crow

I've seen this before, where one mysql user logs in via php and another does not. Sometimes the user even works from the commandline but not from php.

我以前见过这个，其中一个 mysql 用户通过 php 登录，而另一个没有。有时用户甚至从命令行工作，而不是从 php 工作。

It's always been what Emil is reffering two. A mysql user is really user/host pair. so the user megadots@localhost and the user megadots@mycoolhost are listed in the mysql.user table as two seperate records.

这一直是埃米尔提到的两个。mysql 用户实际上是用户/主机对。所以用户 megadots@localhost 和用户 megadots@mycoolhost 作为两个单独的记录列在 mysql.user 表中。

If you can login from the command line run this query.

如果您可以从命令行登录，请运行此查询。

SELECT user, host FROM mysql.user

you should see the full list of users and thier hosts.

您应该会看到用户及其主机的完整列表。

if you recognize the phpMyAdmin user that is working, look at the host column that's probably the host you want to use.

如果您识别出正在工作的 phpMyAdmin 用户，请查看可能是您要使用的主机的主机列。

to reset the host run these queries (!be careful doing this your working with the privilages table for the entire mysql installation)

重置主机运行这些查询（！小心这样做，你使用整个 mysql 安装的权限表）

update mysql.user set host = 'hostname' 
where user = 'username' and host = 'oldhostname';

flush privileges;

If you see a record with the username and % as the host that will have priority over everything else, if there are multiple records for the user and % as the host for one of them, it's possible that the username with % as the host has the wrong password and no matter how manytimes you reset username@localhost's password it's invalid because it'll be compared to username@% on login.

如果您看到用户名和 % 作为主机的记录优先于其他所有内容，如果用户有多条记录，其中一个的主机为 %，则用户名可能是 % 作为主机错误的密码，无论您重置 username@localhost 的密码多少次，它都是无效的，因为它会在登录时与 username@% 进行比较。

回答by hector

If you are using Linux use the synaptic package manager to find and download all the libraries and mods that PHP and MySQL need so they can both connect. My problem was that PHP scripts running alone worked on the server side but when I tried using PHP scripts to connect to MySQL it would not connect I would only see a plain white page. Then I noticed I did not have the MySQL client libraries that PHP uses to conect to MySQL. I only had the MySQL server libraries. Once I installed the client side libraries and restarted the apache server my PHP scripts had no problem connecting. By default PHP 5 does not come installed with the MySQL client side libraries.

如果您使用的是 Linux，请使用突触包管理器来查找和下载 PHP 和 MySQL 需要的所有库和模块，以便它们都可以连接。我的问题是单独运行的 PHP 脚本在服务器端工作，但是当我尝试使用 PHP 脚本连接到 MySQL 时，它无法连接，我只会看到一个纯白色页面。然后我注意到我没有 PHP 用来连接 MySQL 的 MySQL 客户端库。我只有 MySQL 服务器库。一旦我安装了客户端库并重新启动了 apache 服务器，我的 PHP 脚本连接就没有问题了。默认情况下，PHP 5 未随 MySQL 客户端库一起安装。

回答by Chris Wininger

In my case, it turned I had deleted the user that was used to create the database I was using. Normally this should give the error 'user specified definer does not exist', but for some reason it was simply returning the more general access denied to my PHP code. I'm not sure why I could still login through the command line and other interfaces. To correct my problem I recreated the user that was deleted.

就我而言，结果我删除了用于创建我正在使用的数据库的用户。通常这应该会给出错误“用户指定的定义器不存在”，但由于某种原因，它只是返回更一般的访问被拒绝给我的 PHP 代码。我不知道为什么我仍然可以通过命令行和其他界面登录。为了纠正我的问题，我重新创建了被删除的用户。

回答by JMS

After having a similar problem I found that replacing '127.0.0.1' with 'localhost' did the trick (despite the fact that I was able to successfully connect via the terminal using 'localhost').

在遇到类似的问题后，我发现用“localhost”替换“127.0.0.1”可以解决问题（尽管我能够使用“localhost”通过终端成功连接）。

回答by Erik Hosszú

I had the same problem, but then I figured it out that if you leave in the default accounts with '%' (any host), NO PASSWORD then you just cannot connect with a password. I am not sure what the problem is exactly but removing them solved it.

我遇到了同样的问题，但后来我发现如果您使用“％”（任何主机）保留默认帐户，则没有密码，那么您就无法使用密码进行连接。我不确定问题到底是什么，但删除它们解决了它。

回答by vy32

1. Be sure that you have configured PHP with the MySQL extensions. On MacPorts, you need to install it separately:

1. 确保您已使用 MySQL 扩展配置 PHP。在 MacPorts 上，您需要单独安装它：

me@lastdance Sites % sudo port search php | grep mysql
php-mysql-xdevapi @8.0.17 (php, devel)
php52-mysql @5.2.17 (php, databases)
a PHP interface to MySQL databases, including the mysql, mysqli and pdo_mysql extensions
php53-mysql @5.3.29 (php, databases)
a PHP interface to MySQL databases, including the mysql, mysqli and pdo_mysql extensions
php54-mysql @5.4.45 (php, databases)
a PHP interface to MySQL databases, including the mysql, mysqli and pdo_mysql extensions
php55-mysql @5.5.38 (php, databases)
a PHP interface to MySQL databases, including the mysql, mysqli and pdo_mysql extensions
php56-mysql @5.6.40 (php, databases)
a PHP interface to MySQL databases, including the mysql, mysqli and pdo_mysql extensions
php70-mysql @7.0.33 (php, databases)
a PHP interface to MySQL databases, including the mysqli and pdo_mysql extensions
php71-mysql @7.1.32 (php, databases)
a PHP interface to MySQL databases, including the mysqli and pdo_mysql extensions
php71-mysql-xdevapi @8.0.17 (php, devel)
php72-mysql @7.2.23 (php, databases)
a PHP interface to MySQL databases, including the mysqli and pdo_mysql extensions
php72-mysql-xdevapi @8.0.17 (php, devel)
php73-mysql @7.3.10 (php, databases)
a PHP interface to MySQL databases, including the mysqli and pdo_mysql extensions
php73-mysql-xdevapi @8.0.17 (php, devel)
me@lastdance Sites % sudo port install php73-mysql
--->  Computing dependencies for php73-mysql
--->  Cleaning php73-mysql
--->  Scanning binaries for linking errors
--->  No broken files found.
--->  No broken ports found.
me@lastdance Sites %

1. Be sure that your localhost mysql socket is correctly specified in your php.ini file. See My database user exists, but I still get an (HY000/2002): No such file or directory

2. 确保在 php.ini 文件中正确指定了 localhost mysql 套接字。请参阅我的数据库用户存在，但我仍然得到一个 (HY000/2002): No such file or directory

On my system, I had to edit the file /opt/local/etc/php73/php.iniand add this:

在我的系统上，我必须编辑文件/opt/local/etc/php73/php.ini并添加以下内容：

; Default socket name for local MySQL connects.  If empty, uses the built-in
; MySQL defaults.
; http://php.net/mysqli.default-socket
mysqli.default_socket = /opt/local/var/run/mysql8/mysqld.sock

回答by Fredrik

and gave it the proper priveleges for the db

并赋予它适当的数据库权限

How? What host did you use for the user when granting it?

如何？授予用户时，您为用户使用了什么主机？

This is how it is normally done:

这是通常的做法：

GRANT ALL ON mydb.* TO 'someuser'@'somehost' identified by 'password';
FLUSH privileges;

in your case 'somehost' should probably be 'localhost' or '127.0.0.1' (see other post)

在你的情况下'somehost'应该是'localhost'或'127.0.0.1'（见其他帖子）

Syntax reference: http://dev.mysql.com/doc/refman/5.1/en/grant.html

语法参考：http: //dev.mysql.com/doc/refman/5.1/en/grant.html

回答by Cruachan

This will either be a typo or you have not granted privileges. Sometimes it can be alarmingly difficult to spot these.

这要么是打字错误，要么是您没有授予权限。有时，发现这些可能非常困难。

I suggest don't use phpmyadmin but download a copy of SQLYOG(the free community edition is great) and try to login with your user via that. Once you have the issue diagnosed there copy/paste username/password back to your script.

我建议不要使用 phpmyadmin，而是下载SQLYOG的副本（免费社区版很棒）并尝试通过它与您的用户登录。诊断出问题后，将用户名/密码复制/粘贴回您的脚本。

Incidentally phpmyadmin is fine, but a program such as sqlyog is generally more convenient, so worth checking out anyway - I'm sure you'll be converted. If sqlyog isn't quite to your taste there are several other free and commercial alternatives.

顺便说一句，phpmyadmin 很好，但是像 sqlyog 这样的程序通常更方便，所以无论如何都值得一试——我相信你会被转换。如果sqlyog 不太符合您的口味，还有其他几种免费和商业替代品。