What to do when encountering a MySQL remote login error 1045? The error message "Error 1045: Access denied" may seem brief, but the underlying causes can be varied. Whether you've just deployed a MySQL service or suddenly find that previously working remote connections are no longer available, this issue shouldn't be ignored.

The essence of error 1045 is that the MySQL server has refused your connection request. The reason for the refusal is usually authentication failure, but to pinpoint the problem, you need to troubleshoot step by step. Many people's first reaction upon seeing this error is that they entered the wrong password, but in reality, the problem may involve multiple aspects such as user permission configuration, server settings, and network environment. Systematically checking these possibilities is necessary to find the true solution.

The first step is to check if the remote user exists and its permission settings. In MySQL, a user account is defined by both the username and hostname. In many cases, the problem lies in the user being configured to only connect from the local host. You need to log in to the MySQL server and check the relevant user's permission information. First, log in to MySQL via the command line:

mysql -u root -p

After logging in, execute the following SQL statement to view users and their privileges:

SELECT user, host FROM mysql.user;

This command will display all users and their allowed hosts. If you see your user's host is `localhost`, it means that the user can only connect from the MySQL server itself. To allow remote connections, you need to modify the user's host settings. You can change a user's hostname to a specific IP address, or use the `%` wildcard to allow connections from all hosts:

UPDATE mysql.user SET host='%' WHERE user='your username';
FLUSH PRIVILEGES;

Alternatively, a more recommended approach is to create a dedicated user for remote connections without modifying existing local users:

CREATE USER 'remote username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'remote username'@'%';
FLUSH PRIVILEGES;

This creates a dedicated account for remote connections without compromising the security of local connections. Note that the `%` wildcard allows connections from any host; you can also specify a specific IP address or network segment, such as `'192.168.1.%'`, for enhanced security.

After adjusting user permissions, the next step is to check the MySQL bind address settings. By default, the MySQL server only listens on the local loopback address (127.0.0.1), meaning it won't accept connection requests from the external network. To change this setting, you need to locate the MySQL configuration file. The location of the configuration file varies depending on the operating system and installation method; common locations include `/etc/mysql/my.cnf`, `/etc/my.cnf`, or `/usr/local/etc/my.cnf`.

Open the configuration file and find the `[mysqld]` section. Check the `bind-address` option. If it's set to `127.0.0.1`, you need to change it to the server's actual IP address, or use `0.0.0.0` to have MySQL listen on all network interfaces:

bind-address = 0.0.0.0

After making the changes, you need to restart the MySQL service for the configuration to take effect. On Ubuntu or Debian systems, use:

sudo systemctl restart mysql

On CentOS or RHEL systems, use:

sudo systemctl restart mysqld

In some cases, the problem persists even after changing the binding address. In this case, you need to check the MySQL server's authentication plugin settings. MySQL 8.0 and later versions use the new authentication plugin `caching_sha2_password` by default, while some older MySQL clients or applications may not yet support this plugin. To check the authentication plugin a user is using, you can execute:

SELECT user, host, plugin FROM mysql.user;

If the remote user is using the `caching_sha2_password` plugin, and your client doesn't support it, consider changing the plugin to the older `mysql_native_password`:

ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

Alternatively, you can specify the older plugin directly when creating the user:

CREATE USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

Network and firewall settings are another common cause. Even if MySQL is configured correctly, you will still encounter a 1045 error if a firewall in the network path is blocking the connection. First, confirm that you can access the MySQL server over the network. You can use the ping command to test basic connectivity:

ping server IP address

If the ping is successful, then test if port 3306 of the MySQL service is open. You can use the telnet or nc commands:

telnet server IP address 3306

If the connection is refused or times out, it's likely that the firewall is blocking port 3306. On the MySQL server, you need to check the firewall rules to ensure that port 3306 is open to remote clients. On Ubuntu systems, if using UFW firewall, you can run:

sudo ufw allow 3306/tcp

On CentOS/RHEL systems, if using firewalld, you can run:

sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

If using iptables, the corresponding command is:

sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT

In some cloud server environments, in addition to the operating system firewall, you also need to check the cloud platform's security group settings. These platforms usually have their own network security policies, requiring you to manually add rules to allow inbound traffic on port 3306.

If the above steps are correct but the problem persists, consider more detailed troubleshooting methods. First, try connecting from the MySQL server locally using the same username and password:

mysql -u username -p -h server IP address

If this connects successfully, the problem is likely with network or firewall settings. If even a local connection fails, the problem may be with user permissions or the password.

Another useful technique is to enable verbose MySQL logging to view detailed information about connection attempts. Add the following lines to the MySQL configuration file:

[mysqld]
log-error = /var/log/mysql/error.log
general_log = 1
general_log_file = /var/log/mysql/general.log

After modifying the configuration, restart the MySQL service and then try connecting remotely. Check the log files for relevant records. Logs usually provide more detailed error information, helping you pinpoint the problem.

When all basic troubleshooting steps fail, consider special cases. For example, the MySQL server might be configured to skip network connections, which can occur in some default installations. Check if your MySQL configuration file contains the `skip-networking` option. If so, comment it out or remove it.

Additionally, if your MySQL is running via a Docker container, ensure the container correctly maps port 3306 and that its network configuration allows external access. The Docker run command should include the port mapping parameter:

docker run -p 3306:3306 --name mysql-container -e MYSQL_ROOT_PASSWORD=password -d mysql:tag

Troubleshooting MySQL remote connection error 1045 is like a detective solving a case; it requires systematically investigating every possibility. From user permissions to server configuration, from firewall settings to the network environment, no step can be overlooked. The most important thing is to check each step logically, rather than randomly trying various methods. This will not only solve the current problem but also help you gain a deeper understanding of MySQL's security mechanisms and network connection principles.