Ubuntu环境Symfony 5数据库导入报错SQLSTATE[HY000][2002]求助

Hey there, let’s work through this connection issue together. Since your first Symfony project runs perfectly, we know your system’s core MySQL/Symfony setup is solid—this is almost certainly a subtle project-specific or configuration mismatch. Here are targeted steps to fix it:

1. Verify MySQL/MariaDB Service Status #

First, double-check if the database service is actually running (even if your first project works, it might have stopped unexpectedly):

sudo systemctl status mysql

If it shows "inactive", start it and enable auto-start on boot:

sudo systemctl start mysql
sudo systemctl enable mysql

2. Check MySQL’s Bind Address #

When switching to 127.0.0.1 gives a "Connection refused" error, it often means MySQL isn’t listening on the TCP/IP stack. Edit the MySQL config file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Find the bind-address line and set it to:

bind-address = 127.0.0.1

Save the file, then restart MySQL to apply changes:

sudo systemctl restart mysql

3. Validate Database User Permissions #

Even if your DATABASE_URL is identical, your database user might not have permission to connect via 127.0.0.1 (MySQL differentiates between localhost (socket) and 127.0.0.1 (TCP) connections).

Log into MySQL as root:

mysql -u root -p

Run these commands to check existing user hosts and grant the right permissions (replace placeholders with your actual credentials):

-- Check current user hosts
SELECT user, host FROM mysql.user;

-- If your user only exists for @localhost, create a TCP-specific entry
CREATE USER 'your_db_user'@'127.0.0.1' IDENTIFIED BY 'your_db_password';

-- Grant full access to your project database
GRANT ALL PRIVILEGES ON your_db_name.* TO 'your_db_user'@'127.0.0.1';

-- Refresh permissions
FLUSH PRIVILEGES;

Exit MySQL with exit; after running these.

4. Fix MySQL Socket Path Configuration #

The "No such file or directory" error usually means Symfony can’t find the MySQL socket file. Let’s nail down the correct path and update configurations:

Step 4.1: Find the actual socket path #

Run this command to locate where your MySQL socket is stored:

find / -name mysql.sock 2>/dev/null

On Ubuntu, this is typically /var/run/mysqld/mysqld.sock.

Step 4.2: Update Symfony’s DATABASE_URL #

Modify your .env file to explicitly specify the socket path when using localhost:

DATABASE_URL="mysql://db_user:db_password@localhost/db_name?unix_socket=/var/run/mysqld/mysqld.sock"

Step 4.3: Sync PHP’s socket settings #

Edit your PHP ini file (location varies by version—e.g., /etc/php/7.4/fpm/php.ini or /etc/php/8.0/cli/php.ini) and set these three values to match the socket path:

mysql.default_socket = /var/run/mysqld/mysqld.sock
mysqli.default_socket = /var/run/mysqld/mysqld.sock
pdo_mysql.default_socket = /var/run/mysqld/mysqld.sock

Restart PHP-FPM to apply changes:

sudo systemctl restart php7.4-fpm  # Replace with your PHP version

5. Clear Symfony’s Cache #

Cached configuration can sometimes ignore your .env changes. Clear the cache for your environment:

# For development environment
php bin/console cache:clear --env=dev

# For production environment
php bin/console cache:clear --env=prod

You can also manually delete the var/cache directory if needed.

6. Test Basic Connections (Bypass Symfony) #

To rule out Symfony-specific issues, create a simple PHP script to test both socket and TCP connections:

<?php
// Replace these with your actual credentials
$socketPath = '/var/run/mysqld/mysqld.sock';
$dbUser = 'your_db_user';
$dbPass = 'your_db_password';
$dbName = 'your_db_name';

// Test socket connection
$socketConn = new mysqli(null, $dbUser, $dbPass, $dbName, null, $socketPath);
if ($socketConn->connect_error) {
    echo "Socket Connection Failed: " . $socketConn->connect_error . "\n";
} else {
    echo "Socket Connection Successful!\n";
    $socketConn->close();
}

// Test TCP connection
$tcpConn = new mysqli('127.0.0.1', $dbUser, $dbPass, $dbName);
if ($tcpConn->connect_error) {
    echo "TCP Connection Failed: " . $tcpConn->connect_error . "\n";
} else {
    echo "TCP Connection Successful!\n";
    $tcpConn->close();
}

Save this as test.php and run it with:

php test.php

This will tell you if the issue is with the database itself or Symfony’s configuration.

内容的提问来源于stack exchange，提问作者Marcel Puy