Digital Ocean数据库连接丢失：客户双站点数据库连接故障求助

Let’s work through fixing these database connection issues step by step—since you noted no code changes were made, this is almost certainly an infrastructure or database configuration problem:

1. First, Confirm the Database Service is Running #

Start by checking if MySQL/MariaDB is active and listening. Log into your Droplet via Putty and run:

sudo systemctl status mysql
# For MariaDB setups, use:
sudo systemctl status mariadb

If it’s stopped, restart it immediately (and enable auto-start to avoid future crashes):

sudo systemctl start mysql
sudo systemctl enable mysql

If it fails to start, skip ahead to checking error logs (step 4)—they’ll tell you exactly why it won’t boot.

2. Verify UFW Firewall Rules #

DigitalOcean Droplets use UFW by default. Ensure the database port (3306) is accessible locally (your apps connect via localhost, so public access isn’t needed):

sudo ufw status

You should see a rule allowing 3306/tcp from 127.0.0.1. If not, add it:

sudo ufw allow from 127.0.0.1 to any port 3306

3. Fix Database User Permissions & Credential Issues #

Even without code changes, user permissions can get corrupted, or the root account might lock after failed login attempts.

First, try logging into the database locally using the system socket (bypasses password checks temporarily):

sudo mysql -u root

If that works, check the permissions for your site-specific database users:

-- List all database users and their allowed hosts
SELECT user, host FROM mysql.user;
-- Replace 'site_user' and 'localhost' with your actual user/host
SHOW GRANTS FOR 'site_user'@'localhost';

Make sure the user has full privileges on their assigned database, and the host is set to localhost (not a remote IP, since your apps run on the same server).

If you can’t log in with sudo mysql, reset the root password:

# Stop the database service first
sudo systemctl stop mysql
# Start it in safe mode without password authentication
sudo mysqld_safe --skip-grant-tables &
# Log in without a password
mysql -u root
# Update the root password (replace 'new_secure_pass' with your desired password)
USE mysql;
UPDATE user SET authentication_string=PASSWORD('new_secure_pass') WHERE User='root';
FLUSH PRIVILEGES;
EXIT;
# Restart the database normally
sudo systemctl start mysql

4. Check Database Error Logs #

Logs are your best friend for diagnosing obscure issues. On DigitalOcean, MySQL/MariaDB logs are usually here:

# Tail the latest entries in real time
sudo tail -f /var/log/mysql/error.log
# For MariaDB, use this path if the above doesn't work:
sudo tail -f /var/log/mariadb/mariadb.log

Look for red flags like:

• "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'" (socket missing, service not running)
• "Access denied for user 'root'@'localhost'" (bad credentials or locked account)
• "Out of memory" (Droplet resource limits—check next step)

5. Check Droplet Resource Usage #

If your Droplet is maxed out on RAM or CPU, the database service might crash or become unresponsive. Check resource usage with:

# Interactive overview of CPU/RAM usage
htop
# Quick RAM check
free -h

If RAM is exhausted, add swap space temporarily (or upgrade your Droplet size for a permanent fix):

# Create a 2GB swap file
sudo fallocate -l 2G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile
# Make swap permanent across reboots
echo '/swapfile none swap sw 0 0' | sudo tee -a /etc/fstab

6. Test Connection From the App’s Perspective #

Finally, replicate the app’s connection attempt to get the exact error message. Create a simple test script (replace placeholders with your site’s details):

<?php
$server = "localhost";
$user = "your_site_db_user";
$pass = "your_site_db_password";
$db = "your_site_db_name";

// Attempt connection
$conn = new mysqli($server, $user, $pass, $db);

// Check for errors
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully!";
$conn->close();
?>

Upload this to your site’s root directory and access it via browser—this will show you the exact error your app is encountering, which might differ from what you see via Putty.

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