Syed Jahanzaib Personal Blog to Share Knowledge !

November 13, 2016

mysql unable to connect with remote mysql host/database

Filed under: Linux Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 8:30 PM

If you are trying to connect with remote mysql database to perform remote management or connectivity with some web app to add data into the db , and sees the following error

Connection failed: Access denied for user ‘zaib’@’x.x.x.x’ (using password: YES)

OR

Host ‘xxx.xx.xxx.xxx’ is not allowed to connect to this MySQL server

Then you need to grant access to the user from any hostname. This is how you add new privilege from mysql command line.

NOTE: Please beware that this is for just example purpose only , and you MUST take good security measures like Strong Password, and allow only specific IP address to access etc.


When you setup the MySQL server on Linux machine, it sets MySQL Server to accept connection from local IP only (i.e 127.0.0.1).  We need to make modification on this.
If wewant to allow connections from any IP, we need to set the bind-address IP to 0.0.0.0,

[for Ubuntu]

Edit /etc/mysql/my.cnf 

nano /etc/mysql/my.cnf 

Search ‘bind-address’

 bind-address=127.0.0.1

& change it to

 bind-address=0.0.0.0

 

Open mySQL , and issue following commands

mysql -uroot -pMYSQLPASSWORD
CREATE USER 'zaib'@'localhost' IDENTIFIED BY 'zaib1234';
GRANT ALL PRIVILEGES ON *.* TO 'zaib'@'localhost' WITH GRANT OPTION;
CREATE USER 'zaib'@'%' IDENTIFIED BY 'zaib1234';
GRANT ALL PRIVILEGES ON *.* TO 'zaib'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit

 

Restart mySQL service ..

service mysql restart

 


Sample PHP page to test Remote Mysql connection

Now you can test it with sample php page. change the host name of remote mysql server, and the id , password we just created in above steps.

<?php
$servername = "remote.mysql.host.ip.or.name";
$username = "zaib";
$password = "PASSWORD";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully. Hurraahhhhh/Alhamdolillah";
?>

Regard’s
Syed Jahanzaib

 

Advertisements

1 Comment »

  1. MashAllah g
    Issue is resolved,
    Good Work 🙂

    Like

    Comment by WayLink — November 19, 2016 @ 6:53 AM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: