Diagnose common connection errors such as access denied or server not found, with step-by-step solutions for network and privilege issues.
The guide helps diagnose and resolve common issues encountered when connecting to a MariaDB server. Identify causes for errors like 'Can't connect to local server' or access denied messages, and learn steps to effectively troubleshoot these connection problems.
If you are completely new to MariaDB and relational databases, you may want to start with A MariaDB Primer. Also, ensure you understand the connection parameters discussed in the Connection Parameters Guide.
Symptoms:
You receive errors similar to:
or
Causes & Solutions:
Server Not Running: The MariaDB server process may not be running.
Incorrect Parameters: The server is running, but not on the specified host, port, socket, pipe, or protocol. Verify your connection parameters.
Socket File Mismatch (Unix): The socket file path might be non-standard or inconsistent between server and client configurations.
Symptoms:
You can connect locally, but not from a remote machine, possibly seeing errors like:
You can use telnet (if available) to test basic network connectivity to the port:
A "Connection refused" message from telnet indicates a network or firewall issue, or that MariaDB is not listening for TCP/IP connections or on that specific interface/port.
The perror utility can interpret OS error codes:
Example output:
Causes & Solutions:
By default, MariaDB often does not accept remote TCP/IP connections or is bound only to localhost (127.0.0.1).
Solution: See for detailed instructions on how to enable remote connections by adjusting the bind-address server variable and ensuring user accounts are configured correctly for remote hosts.
Symptoms:
Connection is established, but authentication fails (e.g., "Access denied for user...").
Causes & Solutions:
Unix Socket Authentication (MariaDB 10.4.3+): On Unix-like systems, the unix_socket authentication plugin is enabled by default for local connections via the Unix socket file. This plugin uses operating system user credentials.
See the for connection instructions and how to switch to password-based authentication if needed.
For an overview of authentication changes in MariaDB 10.4, see .
Symptoms:
You can run regular queries but get authentication or permission errors when using SELECT ... INTO OUTFILE, SELECT ... INTO DUMPFILE, or LOAD DATA INFILE.
Causes & Solutions:
These operations require the FILE privilege on the server.
Solution: Grant the necessary FILE privilege to the user. See the article.
Symptoms:
You can connect to the MariaDB server, but attempting to USE or query a specific database results in an error:
Or, connecting with mariadb -u user -p db1 works, but mariadb -u user -p db2 fails for db2.
Causes & Solutions:
The user account has not been granted sufficient privileges for that particular database.
Solution: Grant the required privileges (e.g., SELECT, INSERT, etc.) on the specific database to the user. See the GRANT article.
Symptoms:
Unexpected connection behavior or parameter usage that you didn't explicitly provide on the command line.
Causes & Solutions:
Option files (e.g., my.cnf, .my.cnf) or environment variables (e.g., MYSQL_HOST) might be supplying incorrect or overriding connection parameters.
Troubleshooting:
Check the values in any option files read by your client. See and the documentation for the specific client you are using (listed under ).
Symptoms:
You cannot connect to a running server, often because the root (or other administrative) password is lost or unknown.
Causes & Solutions:
Solution: You can start the MariaDB server with the --skip-grant-tables option. This bypasses the privilege system, granting full access. Use this with extreme caution and only temporarily.
Stop the MariaDB server.
Restart the server manually from the command line, adding the --skip-grant-tables option.
localhost vs. % Wildcard Host IssuesSymptoms:
You've created a user like 'melisa'@'%' but cannot log in as melisa when connecting from localhost.
Example output showing the problem:
Causes & Solutions:
MariaDB's user authentication prioritizes more specific host matches. If an anonymous user (''@'localhost') exists, it can take precedence over 'melisa'@'%' when connecting from localhost.
Solutions:
**Create a specific user for localhost:**SQL
This page is licensed: CC BY-SA / Gnu FDL
ERROR 2002 (HY000): Can't connect to local MySQL server through
socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")my.cnf (or my.ini) configuration file. Ensure the socket option has the identical value in both the [mysqld] (server) section and the [client] (or [mysql]) section.To find the running Unix socket file, you can try commands like:
Example output:
See also: Troubleshooting Installation Issues.
Incorrect Username/Host Combination: Authentication is specific to a username@host combination. For example, 'user1'@'localhost' is distinct from 'user1'@'166.78.144.191'. Ensure the user account exists for the host from which you are connecting.
See the GRANT article for details on granting permissions.
Password Hashing: When setting or changing passwords using SET PASSWORD, ensure the PASSWORD() function is used if the server expects hashed passwords.
Example: SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
Rather than: SET PASSWORD FOR 'bob'@'%.loc.gov' = 'newpass'; (which might store the password as plain text, potentially leading to issues depending on the authentication plugin).
You can often suppress the reading of default option files by using a --no-defaults option (if supported by the client):Bash
Connect to the server (no password will be required for root@localhost).
Execute FLUSH PRIVILEGES; to reload the grant tables (they are now active again).
Change the password for the necessary account, e.g.:
Stop the server and restart it normally (without --skip-grant-tables).
**Remove the anonymous user for localhost (use with caution):**SQL
Ensure this doesn't break other intended anonymous access if any.
netstat -ln | grep mysqldunix 2 [ ACC ] STREAM LISTENING 33209505 /var/run/mysqld/mysqld.sockmariadb-import --no-defaults ...SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_new_strong_password');DROP USER ''@'localhost';
FLUSH PRIVILEGES;mariadb -u someuser -p --port=3307 --protocol=tcp
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost'
(111 "Connection refused")./client/mysql --host=myhost --protocol=tcp --port=3306 test
ERROR 2002 (HY000): Can't connect to MySQL server on 'myhost' (115)telnet myhost 3306perror 115OS error code 115: Operation now in progressUSE test;
ERROR 1044 (42000): Access denied for user 'youruser'@'yourhost' to database 'test'-- User created with '%' host
CREATE USER 'melisa'@'%' IDENTIFIED BY 'password';
-- Checking users in mysql.user table
SELECT user, host FROM mysql.user WHERE user='melisa' OR user='';+--------+-----------+
| user | host |
+--------+-----------+
| melisa | % |
| | localhost | -- An anonymous user for localhost
+--------+-----------+CREATE USER 'melisa'@'localhost' IDENTIFIED BY 'password_for_melisa_localhost';
GRANT ALL PRIVILEGES ON yourdatabase.* TO 'melisa'@'localhost'; -- Grant necessary privileges
FLUSH PRIVILEGES;