📋

Description

MySQL Error 2012, 'Error in server handshake', indicates a failure during the initial communication phase between a MySQL client and the MySQL server. This typically occurs when the client attempts to establish a connection but an underlying issue prevents the server from successfully acknowledging or negotiating the connection parameters.

💬

Error Message

Error in server handshake

🔍

Known Causes

4 known causes

⚠️

Network or Firewall Blockage

The client or server's network connection might be unstable, or a firewall could be actively blocking the communication port (default 3306) required for the handshake.

⚠️

Client/Server Version Mismatch

Significant version differences between the MySQL client and server can lead to incompatible handshake protocols, preventing a successful connection establishment.

⚠️

SSL/TLS Configuration Issues

If SSL/TLS is enabled, incorrect certificates, keys, or cipher configurations on either the client or server side can disrupt the secure handshake process.

⚠️

Server Unresponsiveness

The MySQL server might be overloaded, crashed, or not running, preventing it from responding to the client's initial connection request.

🛠️

Solutions

4 solutions available

1. Verify MySQL Server Status and Network Connectivity easy

Ensure the MySQL server is running and accessible from the client machine.

1

Check if the MySQL server process is running on the server machine. The command will vary based on the operating system.

sudo systemctl status mysql  # For systemd-based Linux distributions
sudo service mysql status  # For older init.d-based Linux distributions
Get-Service MySQL*  # For Windows PowerShell

2

If the server is not running, start it.

sudo systemctl start mysql  # For systemd-based Linux distributions
sudo service mysql start  # For older init.d-based Linux distributions
Start-Service MySQL*  # For Windows PowerShell

3

From the client machine, attempt to ping the MySQL server's IP address or hostname to check basic network reachability.

ping <mysql_server_ip_or_hostname>

4

Attempt to connect to the MySQL server using the `mysql` command-line client, specifying the hostname/IP and port. This helps isolate if the issue is with the client configuration or server availability.

mysql -h <mysql_server_ip_or_hostname> -P <mysql_port> -u <username> -p

2. Check MySQL Server Configuration for Bind Address medium

Ensure the MySQL server is configured to listen on the network interface the client is trying to connect to.

1

Locate the MySQL configuration file. Common locations include `/etc/mysql/my.cnf`, `/etc/my.cnf`, or `/etc/mysql/mysql.conf.d/mysqld.cnf`.

text: Find your my.cnf file. Common locations are /etc/mysql/my.cnf, /etc/my.cnf, or within /etc/mysql/mysql.conf.d/

2

Open the configuration file in a text editor with administrative privileges.

sudo nano /etc/mysql/my.cnf

3

Look for the `bind-address` directive within the `[mysqld]` section. If it's set to `127.0.0.1` or `localhost`, the server will only accept local connections. To allow remote connections, change it to `0.0.0.0` (to listen on all interfaces) or the specific IP address of the server that the client can reach.

[mysqld]
bind-address = 0.0.0.0
# Or bind-address = <server_specific_ip>

4

Save the changes to the configuration file and restart the MySQL server for the changes to take effect.

sudo systemctl restart mysql

3. Review MySQL User Permissions and Authentication Method medium

The user account might not have privileges to connect from the client's host, or an outdated authentication plugin might be in use.

1

Connect to the MySQL server as a user with sufficient privileges (e.g., `root`).

mysql -u root -p

2

Check the user's allowed hosts. Replace `<username>` with the actual username and `<client_ip_or_hostname>` with the IP address or hostname of the client machine.

SELECT user, host FROM mysql.user WHERE user = '<username>';

3

If the user does not have a record for the client's host, grant the necessary privileges. Use `ALL PRIVILEGES` or specific privileges as needed.

GRANT ALL PRIVILEGES ON *.* TO '<username>'@'<client_ip_or_hostname>' IDENTIFIED BY '<user_password>';
FLUSH PRIVILEGES;

4

Check the authentication plugin used by the user. Newer MySQL versions default to `caching_sha2_password`, which might not be supported by older clients. If you encounter issues, you can change the authentication plugin to `mysql_native_password`.

SELECT user, host, plugin FROM mysql.user WHERE user = '<username>';

5

To change the authentication plugin (if necessary):

ALTER USER '<username>'@'<client_ip_or_hostname>' IDENTIFIED WITH mysql_native_password BY '<user_password>';
FLUSH PRIVILEGES;

4. Examine Network Firewalls and Security Groups medium

Firewall rules on the server or client, or cloud security groups, might be blocking the MySQL port.

1

On the MySQL server, check the firewall rules to ensure that incoming connections on the MySQL port (default is 3306) are allowed from the client's IP address or network. The commands for this vary by OS and firewall software.

sudo ufw status  # For UFW on Ubuntu
sudo firewall-cmd --list-all  # For firewalld on CentOS/RHEL
# For iptables, you might need to inspect rules with 'sudo iptables -L'

2

If the port is not open, add a rule to allow incoming connections on port 3306 from the client's IP address or a specific range.

sudo ufw allow from <client_ip_or_hostname> to any port 3306
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="<client_ip_or_hostname>" port protocol="tcp" port="3306" accept'
sudo firewall-cmd --reload

3

If using a cloud provider (AWS, Azure, GCP), check the security group or network firewall rules associated with the MySQL server instance. Ensure that inbound traffic on port 3306 is permitted from the client's IP address or a trusted network range.

text: Refer to your cloud provider's documentation for managing security groups or network firewalls.

4

On the client machine, ensure no local firewall is blocking outbound connections to the MySQL server's IP and port.

text: Check your operating system's firewall settings (e.g., Windows Defender Firewall, macOS Firewall).

🔗

Related Errors

5 related errors

MySQL Error 2012: Server Handshake Failure