Fix MySQL Errors while Connecting to Remote Server

Fix MySQL Errors while Connecting to Remote Server

When you try connecting to your remote server you might encounter errors which we will help you to resolve. Following the below guidelines, you can fix MySQL errors.

Suppose you are unable to connect to the local MySQL server via socket ‘/var/lib/mysql/mysql.sock’ (2) 0 error, the check if the mysqld daemon is functional or not. If it’s functional, verify the Unix socket by running the command netstat -ax | grep mysql.

Error Messages:

You may experience the following errors, which are described below along with recommended solutions.

Remote Server Connection Fails

DNS Error: the server cannot resolve the hostname – Check the DNS configuration settings.

Error – Invalid IP address: the IP address doesn’t exist

  • Ensure the network setup for the local server’s IP address.
  • Confirm the IP address of the remote MySQL server.
  • Verify the responsiveness of the remote MySQL server.
  • Check the firewall configuration settings on both the local and remote servers.

Incorrect Port – SSH

  • Confirm the remote MySQL server’s SSH port.
# ss -pln | grep ssh

LISTEN    0      128                      :::22                      :::*      users:(("sshd",1163,4))

LISTEN    0      128                       *:22                       *:*      users:(("sshd",1163,3))
  • The remote MySQL server’s SSH port must be set correctly. The correct port in the above example is 22.

Invalid Credentials – SSH

Invalid Credentials – SSH

To access the remote MySQL server, ensure you have a valid username and password.

An error occurred when trying to connect to the remote MySQL host IP

The host ‘hostname’ is not permitted to connect to this MySQL server.

If MySQL permissions are insufficient then provide superuser access to the user.

# mysqlmysql
-snip-

mysql> CREATE USER 'user'@'hostname' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'hostname' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Connection lost to MySQL server at ‘handshake: waiting for initial communication packet’, system error: 110

Error in the firewall. The remote MySQL server’s specified port cannot be accessed by the server. Check the firewall’s settings.

Note: The configuration options for the firewall tool, remote server, and local server will determine the specific solution.

Unable to connect with MySQL server on ‘IP’ (107)

Let’s restart MySQL because MySQL is down on the remote server.

Incorrect Post – MySQL

  • Confirm the remote MySQL server’s SSH port.
# ss -pln | grep mysql
LISTEN    0      50                        *:3306                     *:*      users:(("mysqld",3308,12))
  • The remote MySQL server’s MySQL port must be set correctly. The correct port in the above example is 3306.

Reverse DNS entry warning

Make sure that the remote server can resolve the hostname of your local server to its IP address before you set it up. Log in to the remote server using SSH to verify this and run the following command, Where 1.2.3.4 stands for IP address:

host 1.2.3.4

If a reverse DNS entry does not configure, add one to the /etc/hosts file on the remote server.

Remote MySQL root password

You can change the root password in the WHM interface by WHM > Home > SQL Services > MySQL Root Password. If you choose to use a remote MySQL server then it is necessary to change the local or the remote server’s root password. A remote MySQL server is a default selection by the system.

Warning:

When you install MySQL 8.0 from the community repository via a remote server, the server will automatically enable its own levels of password validation. It may conflict with the password validation in cPanel and WHM.

  • If you use the SSH command to automatically create a MySQL superuser. The system will automatically adapt to these various validation procedures when the remote MySQL profile setting is used.
  • The following changes must be made via the MySQL command line while manually entering an existing MySQL superuser’s credentials option is selected while creating the remote MySQL profile:
  • Open the /etc/my.cnf file
  • Add the below lines to the file:
validate-password.policy=0
validate-password.length=1
validate_password.special_char_count=0
validate_password.mixed_case_count=0
validate_password.number_count=0

Run MySQL 8.0 on a cPanel Server

If the remote MySQL server is also a cPanel & WHM server then you must follow the extra procedures:

  • Update the remote MySQL server’s /root/.my.cnf configuration file if the hosting server’s MySQL root password is changed.
  • If the remote MySQL server’s root password is changed, then the MySQL profile on the hosting server must be updated and reactivated.

Run MySQL 8.0 on any other server

You must disable password caching when the remote MySQL server is running on any other server. To prevent an authentication error, follow the below steps:

  1. Open the /etc/my.cnf
  2. Change the settings default-authentication-plugin to mysql_native_password.default-authentication-plugin=mysql_native_password
  3. Use the command line to log into MySQL. After that, execute the following command:
mysql> ALTER USER 'root'@'cpanel.server.host' IDENTIFIED WITH mysql_native_password BY '12345luggage';

Hope this could have helped you to fix MySQL Errors, if you need any assistance click here. We have fixed.

To get more updates you can follow us on Facebook, Twitter, LinkedIn

Subscribe to get free blog content to your Inbox
Loading

Written by actsupp-r0cks