使用sha256_password时如何使用PHP的mysqli连接到MySQL(访问被拒绝) [英] How do you connect to MySQL using PHP's mysqli when using sha256_password (access denied)
问题描述
我正在使用MySQL 5.7.24和PHP 7.2.10. MySQL已配置SSL.我有一个密码为abc123
的用户my_user
.我尝试使用两个身份验证插件进行配置:
I'm using MySQL 5.7.24 and PHP 7.2.10. MySQL is configured with SSL. I have a user my_user
with password abc123
. I have tried configuring with both authentication plugins:
ALTER USER 'my_user'@'192.168.192.150' IDENTIFIED WITH sha256_password BY 'abc123';
ALTER USER 'my_user'@'192.168.192.150' IDENTIFIED WITH mysql_native_password BY 'abc123';
在我的PHP服务器上,我可以使用mysql
命令行客户端成功连接,而无需指定主机,用户和密码以外的任何内容.但是,从PHP,我只能在使用mysql_native_password
时进行连接.使用sha256_password
时不可以.
From my PHP server, I can successfully connect using the mysql
command line client without specifying anything other than the host, user, and password. However, from PHP, I can connect only when using mysql_native_password
. Not when using sha256_password
.
以下PHP代码在mysql_native_password
上可以正常使用:
The following PHP code works fine with mysql_native_password
:
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);
$con = $mysqli->real_connect('192.168.192.100', 'my_user', 'abc123', 'my_db',
3306, null, MYSQLI_CLIENT_SSL | MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT);
var_dump($mysqli->query('SELECT 1;'));
但是,当我使用sha256_password
时,出现错误用户'my_user'@'192.168.192.150'被拒绝访问(使用密码:是)."我尝试交替添加以下两个语句(在real_connect
之前)均未成功(但是,我会添加,我不需要这两个语句,因为我可以在不指定这些选项的情况下与命令行客户端连接* ).
However, when I use sha256_password
, I get the error "Access denied for user 'my_user'@'192.168.192.150' (using password: YES)." I've tried alternately adding both of the following statements (before real_connect
) with no success (but, I would add, I shouldn't need either of these, since I can connect with the command line client without specifying these options*).
$mysqli->ssl_set(null, null, 'ca.pem', null, null);
$mysqli->ssl_set('client-key.pem', 'client-cert.pem', 'ca.pem', null, null);
那么在PHP中应该如何使用mysqli
中的sha256_password
?
So how is one supposed to use sha256_password
from mysqli
in PHP?
*注意:我的PHP服务器与我的MySQL服务器不同.在PHP上,该服务器上没有my.cnf
包含ssl-ca
,ssl-cert
和ssl-key
选项,因此我真的可以尝试使用mysql
在没有它们的情况下进行连接.
*NOTE: My PHP server is a different machine than my MySQL server. There is no my.cnf
on the PHP that server that contains the ssl-ca
, ssl-cert
, and ssl-key
options, so I really, try can connect without them using mysql
.
更新1
如果有帮助,请参见phpinfo
的一些诊断信息:
In case it's helpful, here's some diagnostic info from phpinfo
:
mysqli
MysqlI Support => enabled
Client API library version => mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $
Active Persistent Links => 0
Inactive Persistent Links => 0
Active Links => 0
Directive => Local Value => Master Value
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 => no value => no value
mysqli.default_user => no value => no value
mysqli.max_links => Unlimited => Unlimited
mysqli.max_persistent => Unlimited => Unlimited
mysqli.reconnect => Off => Off
mysqli.rollback_on_cached_plink => Off => Off
mysqlnd
mysqlnd => enabled
Version => mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $
Compression => supported
core SSL => supported
extended SSL => supported
Command buffer size => 4096
Read buffer size => 32768
Read timeout => 86400
Collecting statistics => Yes
Collecting memory statistics => No
Tracing => n/a
Loaded plugins => mysqlnd,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password,auth_plugin_caching_sha2_password,auth_plugin_sha256_password
API Extensions => mysqli,pdo_mysql
openssl
OpenSSL support => enabled
OpenSSL Library Version => OpenSSL 1.1.0g 2 Nov 2017
OpenSSL Header Version => OpenSSL 1.1.0g 2 Nov 2017
Openssl default config => /usr/lib/ssl/openssl.cnf
Directive => Local Value => Master Value
openssl.cafile => no value => no value
openssl.capath => no value => no value
推荐答案
据我从文档中了解,一次只能激活一个auth_option.
For what I understand from documentation, only one auth_option can be active at a time.
所以当你在做
ALTER USER 'my_user'@'192.168.192.150' IDENTIFIED WITH sha256_password BY 'abc123';
ALTER USER 'my_user'@'192.168.192.150' IDENTIFIED WITH mysql_native_password BY 'abc123';
您的第二个请求实际上将sha256_password身份验证重新定义为mysql_native_password
your second resquest actually redefines sha256_password auth to mysql_native_password
这篇关于使用sha256_password时如何使用PHP的mysqli连接到MySQL(访问被拒绝)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!