Скачать или смотреть Resolving Remote Connection Issues: Can't Connect to MariaDB Server

Discover effective solutions for the "Can't connect to MySQL server" error when connecting to `MariaDB` remotely. Learn key troubleshooting steps and configuration checks.
---
This video is based on the question https://stackoverflow.com/q/74808107/ asked by the user 'Abhinav' ( https://stackoverflow.com/u/1659298/ ) and on the answer https://stackoverflow.com/a/74808678/ provided by the user 'Ergest Basha' ( https://stackoverflow.com/u/16461952/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Remote user is not able to connect to mariadb

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Troubleshooting Remote Connection Issues with MariaDB

Connecting to a database server from a remote location can often be daunting, especially when faced with error messages. One common issue many users encounter is the ERROR 2003 (HY000): Can't connect to MySQL server on '1.2.3.4' (111 "Connection refused"). In this guide, we’ll explore the problem and break down a systematic solution for resolving the connection issues to your MariaDB server.

The Problem at Hand

You have a MariaDB server installed with the IP address 1.2.3.4, and you've created a MySQL user to access it from a remote IP address a.b.c.d. However, attempts to connect to the server result in the persistent error message. Furthermore, attempts to access the MariaDB server locally using the server's IP also yield an error, while local connections using localhost work just fine.

Key Observations

Attempts to connect from a.b.c.d fail.

Telnet tests to port 3306 on 1.2.3.4 also do not work, indicating possible service restrictions.

The log file entries show binding errors related to TCP/IP ports.

Step-by-Step Solution

Let’s walk through several crucial checks and configurations to tackle this problem effectively.

1. Check the Firewall Settings

Ensure that the firewall is configured to allow traffic on port 3306, which is the default port for MariaDB.

Command to check firewall rules:

[[See Video to Reveal this Text or Code Snippet]]

If necessary, you can allow traffic using:

[[See Video to Reveal this Text or Code Snippet]]

2. Verify User Permissions

Confirm that the user has been created with the appropriate privileges and that the username is formatted correctly.

For example, check that the user is formatted as: username@'a.b.c.d'. This specifies the access granted to the user from the IP address.

3. Check Bind Address Settings

Find the MariaDB configuration file. In your case, it seems to be located at /etc/my.cnf, as /etc/mysql/my.cnf doesn't exist.

You may want to check for a line that resembles:

[[See Video to Reveal this Text or Code Snippet]]

If this line is present, it restricts access to local connections only. Change it to:

[[See Video to Reveal this Text or Code Snippet]]

This allows connections from any IP address. After making changes, restart the MariaDB service:

[[See Video to Reveal this Text or Code Snippet]]

4. Examine Log Files for Errors

Investigate log files which might provide further clues regarding connection issues. Usually found at:

/var/log/mysql/error.log

Look for errors indicating binding or configuration issues, as you’ve already noted in the log output showing bind errors.

5. Check for Existing MySQL/MariaDB Instances

Your log entries might suggest another instance of MariaDB is running on port 3306. Ensure that no conflicting services are occupying that port. Use:

[[See Video to Reveal this Text or Code Snippet]]

If another service is running on that port, consider stopping it or reconfigure your MariaDB to listen on a different port.

Conclusion

With these steps outlined, you should be equipped to troubleshoot and resolve the connection issues to your MariaDB server from a remote IP address. Ensuring proper user permissions, configuration settings, and firewall rules are in place is critical for seamless database access. If you've followed these suggestions and continue to encounter issues, consider seeking assistance from the MariaDB community or consulting additional resources for deeper technical insights.

By addressing these potential pitfalls, you can prevent the frustrating experience of being unable to connect to your MariaDB instance.