MySql Communication link failure while connecting MySql server remotely
I’m getting the following error when I tried to connect MySql server remotely:
ERROR:
——————————————
Cannot connect to database server
Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server:
Why this error occurs?
——————————————
Generally we configures “mysql.cnf” configuration file for localhost (127.0.0.1) so we can connect to the databases locally only. If we’ll try to connect MySql remotely with correct username and password then it will show communications link failure error.
Configure the MySql server for local access:
——————————————
Using commands below I created a new MySQL user with name ‘webners’ who can access MySQL running on ‘127.0.0.1’ locally and granted the privileges to user on database objects:
mysql> CREATE USER 'webners'@'127.0.0.1' IDENTIFIED BY 'password'; mysql> GRANT ALL PRIVILEGES ON * . * TO 'webners'@'127.0.0.1';
Configure the MySql server for remote access:
——————————————
There is need to perform two different steps in order to access your MySql server remotely:
1.First of all, you have to change the host from ‘127.0.0.1’ to ‘%’ by the following command:
mysql> CREATE USER 'webners'@'%' IDENTIFIED BY 'password';
The ‘%’ symbol represents any random host, including remote and local connections, where localhost allows only local connections.
mysql> GRANT ALL PRIVILEGES ON * . * TO 'webners'@'%';
The above command provides all the access to ‘webners’ user on databases object. For more details please check screenshots below:
2. Second:
We need to comment the line no. 42 under “ /etc/mysql/mysql.conf.d/mysql.cnf ” configuration file, where bind-address is 127.0.0.1 by default, it means server binds to 127.0.0.1 where only local computer will able to connect.
Some more parameters examples:
. MySql server can accept the connection from IPv4 hosts only if it is configured like
[Bind-address = 0.0.0.0 ]
. MySql server can accept the connection from mentioned specific IP address only if it is configured like [ Bind-address = 192.168.1.84 ]
Under “mysql.cnf” configuration file, I have commented this line. It means the MySql server can accept the connection from any random host.
After the modification please restart the MySql service by the following command:
$ Sudo /etc/init.d/mysql restart
You can access the MySql server remotely now.