无法通过编程连接MySQL（MariaDB）问题求助

Hey there, let’s work through this connection problem step by step— I’ve run into similar snags before, so here are the key checks and fixes to try:

1. Double-Check Your Connection Parameters #

• Host Matching: MariaDB treats localhost (socket-based connection) and 127.0.0.1 (TCP/IP connection) as distinct identities. If your code uses 127.0.0.1 as the host, your newuser@localhost credentials won’t work. Either update your code to use localhost as the host, or create a separate user for TCP connections:

  CREATE USER 'newuser'@'127.0.0.1' IDENTIFIED BY 'password';
  GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'127.0.0.1';
  FLUSH PRIVILEGES;
  

• Password Accuracy: Ensure the password in your code exactly matches 'password'— watch out for accidental typos, case sensitivity, or special character escaping (e.g., if your password has quotes, make sure they’re properly handled in your programming language).

2. Verify Socket Path Consistency (For Localhost Connections) #

Many programming clients use a default socket path that doesn’t match MariaDB’s actual socket location.

• First, confirm you can connect via the terminal:

  mysql -u newuser -p -h localhost
  

• If the terminal works but your code doesn’t, find MariaDB’s socket path with this SQL query:

  SHOW VARIABLES LIKE 'socket';
  

• Add this socket path to your code’s connection parameters (e.g., in Python’s mysql-connector, use the unix_socket parameter pointing to the path like /var/run/mysqld/mysqld.sock).

3. Ensure Permissions Are Applied #

Even if you ran the GRANT command, sometimes permissions don’t take effect immediately. Run this to refresh them:

FLUSH PRIVILEGES;

Double-check the user’s grants with:

SHOW GRANTS FOR 'newuser'@'localhost';

You should see a line like GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost'.

4. Check MariaDB Bind Address & Firewall #

If you’re using TCP/IP connections:

• Open your MariaDB config file (usually /etc/mariadb/mariadb.conf.d/50-server.cnf or /etc/mysql/my.cnf) and verify the bind-address is set to 127.0.0.1 (to allow local TCP connections). Restart the service after changes:

  sudo systemctl restart mariadb
  

• Ensure your local firewall (like ufw) isn’t blocking port 3306. You can allow local access with:

  sudo ufw allow in from 127.0.0.1 to any port 3306
  

5. Dig Into Error Logs #

MariaDB’s error logs will give you precise details about why connections are failing. Check the latest entries with:

sudo tail -n 20 /var/log/mariadb/mariadb.log

Look for messages like "Access denied for user 'newuser'@'localhost'" or "Can't connect to local MySQL server through socket"— these will point you directly to the root cause.

6. Test Connection From Terminal First #

If your code fails, always test the same credentials via the terminal first. If the terminal can’t connect either, the issue is with the user setup or MariaDB configuration, not your code. If the terminal works, the problem is definitely in your code’s connection logic.

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