php连接本地mysql连接错误的问题解决方法
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、未加载php的mysqli文件</a></li><li><a href="#_label1">二、本地数据库拒绝连接</a></li><li><a href="#_label2">三、权限问题</a></li></ul></div><p>我的服务器启用的nginx，配置了php的连接mysql的配置文件connect.php：</p><div class="jb51code"><pre class="brush:php;"><?php
$server="localhost";//主机
$db_username="root";//你的数据库用户名
$db_password="root";//你的数据库密码
$dbname = "users";
$conn = new mysqli($server,$db_username,$db_password);//链接数据库
if($conn->connect_error){
die("can't connect".$conn->connect_error);//如果链接失败输出错误
}
if (!mysqli_select_db($conn, 'users')) {
die("选择数据库失败: " . mysqli_error($conn));
}</pre></div>
<p>其中一个页面include了这个文件，按理来说可以直接连接的，当然你需要在本地mysql建立这个users库和user表。</p>
<p>但是调试过程中 include函数并不起作用： include('/var/www/html/phpTest/connect.php');</p>
<p>我们查看nginx的错误日志 cat /var/log/nginx/error.log：</p>
<p>下面这两个报错分别是两个不同的问题：</p>
<p class="maodian"><a name="_label0"></a></p><h2>一、未加载php的mysqli文件</h2>
<div class="jb51code"><pre class="brush:sql;">2024/10/16 15:58:08 370320#370320: *18 FastCGI sent in stderr: "PHP message: PHP Fatal error:Uncaught Error: Class "mysqli" not found in /var/www/html/phpTest/connect.php:7
Stack trace:
#0 /var/www/html/phpTest/signup.php(32): include()
#1 {main}
thrown in /var/www/html/phpTest/connect.php on line 7" while reading response header from upstream, client: 113.132.177.30, server: _, request: "POST /phpTest/signup.php HTTP/1.1", upstream: "fastcgi://unix:/run/php/php8.1-fpm.sock:", host: "116.205.127.142", referrer: "http://116.205.127.142/phpTest/signup.php"</pre></div>
<p>这个是因为没有加载php的mysqli文件导致的，我们需要在服务器上下载：</p>
<div class="jb51code"><pre class="brush:bash;">sudo apt-get install php-mysqli
sudo apt-get install php8.1-mysqli //根据你的php版本选择msqli版本
sudo systemctl restart php8.1-fpm//重启fpm和nginx
sudo systemctl restart nginx</pre></div>
<p>还需要确保php的配置文件php.ini中没有禁用mysqli，找到;extension=mysqli 去掉前面分号注释符，重启fpm。</p>
<p class="maodian"><a name="_label1"></a></p><h2>二、本地数据库拒绝连接</h2>
<div class="jb51code"><pre class="brush:sql;">2024/10/16 16:27:26 371624#371624: *10 FastCGI sent in stderr: "PHP message: PHP Fatal error:Uncaught mysqli_sql_exception: Access denied for user 'root'@'localhost' in /var/www/html/phpTest/connect.php:7
Stack trace:
#0 /var/www/html/phpTest/connect.php(7): mysqli->__construct()
#1 /var/www/html/phpTest/signup.php(32): include('...')
#2 {main}
thrown in /var/www/html/phpTest/connect.php on line 7" while reading response header from upstream, client: 113.132.177.30, server: _, request: "POST /phpTest/signup.php HTTP/1.1", upstream: "fastcgi://unix:/run/php/php8.1-fpm.sock:", host: "116.205.127.142", referrer: "http://116.205.127.142/phpTest/signup.php"</pre></div>
<p>需要确认 <code>root</code> 用户是否有权限从 <code>localhost</code> 访问数据库。执行以下 MySQL 命令来查看权限：</p>
<p>登录成功后，检查用户权限：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT host, user FROM mysql.user;</pre></div>
<p>如果没有 <code>root@localhost</code>，或者 <code>root</code> 用户没有适当的权限，你可以通过以下命令为 <code>root</code> 赋予权限：</p>
<div class="jb51code"><pre class="brush:sql;">GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;</pre></div>
<p>确保 MySQL 配置文件 (<code>/etc/mysql/my.cnf</code> 或 <code>/etc/mysql/mysql.conf.d/mysqld.cnf</code>) 中允许本地连接：默认是开启的</p>
<div class="jb51code"><pre class="brush:bash;">bind-address = 127.0.0.1</pre></div>
<p>修改完之后重启mysql即可</p>
<p class="maodian"><a name="_label2"></a></p><h2>三、权限问题</h2>
<p>确保root用户的权限</p>
<div class="jb51code"><pre class="brush:sql;">SELECT User, Host FROM mysql.user WHERE User = 'root';</pre></div>
<div class="jb51code"><pre class="brush:sql;">mysql> SELECT User, Host FROM mysql.user WHERE User = 'root';
+------+-----------+
| User | Host |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.00 sec)</pre></div>
<p>然后检查mysql认证插件：某些情况下，MySQL 可能使用了 <code>auth_socket</code> 插件，这会阻止使用密码登录。检查 <code>root</code> 是否使用 <code>auth_socket</code> 插件：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT user, host, plugin FROM mysql.user WHERE user = 'root';</pre></div>
<p>如果 <code>plugin</code> 显示为 <code>auth_socket</code>，修改为 <code>mysql_native_password</code>：</p>
<div class="jb51code"><pre class="brush:sql;">ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;</pre></div>
<div class="jb51code"><pre class="brush:sql;">mysql> SELECT user, host, plugin FROM mysql.user WHERE user = 'root';
+------+-----------+-----------------------+
| user | host | plugin |
+------+-----------+-----------------------+
| root | localhost | mysql_native_password |
+------+-----------+-----------------------+
1 row in set (0.00 sec)</pre></div>
<p>尤其是最后一步，如果不修改还是连不上的</p>
頁:
[1]