麻烦连接到phpMyAdmin MySQL数据库

Question

I'm pretty new at php and I'm getting an error whenever I try to load a php page that requires access to the database I set up on phpMyAdmin

Here is the error:

Database connection failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (46) (2002)

The code I'm using for the connection is this:

<?php
define("DB_SERVER", "localhost");
define("DB_USER", "xxxx");
define("DB_PASS", "xxxx");
define("DB_NAME", "tester");

$connection = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);

// Test if connection occurred.
if(mysqli_connect_errno()) {
    die("Database connection failed: " .
        mysqli_connect_error() .
        " (" . mysqli_connect_errno() . ")"
    );
}
?>

I set up phpMyAdmin on my server by following the directions from the site that hosts my website, so I'm pretty sure I did that correctly.

I've been able to get databases to work before on my computer by using WAMP, but this is the first time I've actually tried getting everything working online, so I don't know if this is a stupid error on my part, or if it's something bigger.

I don't know what a socket is either so I'm not sure how to troubleshoot this.

Thanks for your help!!

Answer 1

You have to find a way to connect to your MySQL instance. There are two ways, TCP (networking, even if that network address is 127.0.0.1) and sockets (a file such as /var/run/mysqld/mysql.sock). Generally, MySQL is configured out of the box to listen on both, and again in general there's little difference between the two. You have to be careful when creating or editing permissions that they match the connection type, user bob@localhost is different from user bob@127.0.0.1. I tend to use sockets and that's also what your script is trying to do by default. You could tell it to connect via TCP, but it's just as easy to tell it the proper path to the socket.

Anyway, for me the quickest way to figure it out is to try the command line tool. If you can do mysql -u root -p at the command line and get a MYSQL> shell prompt, type in "STATUS" and look for the Connection line, which might read Localhost via UNIX socket; and a bit further down you might see a line like "UNIX socket: /var/run/mysqld/mysql.sock" in which case you just tell your PHP script or global PHP configuration about the socket path, because right now it's looking in /tmp/mysql.sock which doesn't exist.

You can also see this when you log in to phpMyAdmin, it should display on the right hand side of the page some "Database Server" information -- look here for the "Server" line which might read something like "phpMyAdmin demo - MySQL (192.168.30.23 via TCP/IP" (a clear indication you're connecting over TCP).

Anyway, whichever method you use to find the path to the socket or deciding to use TCP networking, see the PHP manual page for information about using that to connect. You can also just set the global path in php.ini for the socket (which is what I'd suggest you do), then you don't have to set it manually in each script as it just uses the php.ini setting as a default. You may need to set each of the values for pdo_mysql.default_socket, mysql.default_socket, and mysqli.default_socket (though in your code you're only using mysqli, so technically you only need to set the last value...but why risk confusing your future self with questions like "why would mysqli work but pdo fail?" -- just set them all now and don't worry about it).

Hope this helps.