MySQL: How to allow remote connection to mysql

I have installed MySQL Community Edition 5.5 on my local machine and I want to allow remote connections so that I can connect from external source.

How can I do that?

That is allowed by default on MySQL.

What is disabled by default is remote root access. If you want to enable that, run this SQL command locally:

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
 FLUSH PRIVILEGES;

And then find the following line and comment it out in your my.cnf file, which usually lives on /etc/mysql/my.cnf on Unix/OSX systems. In some cases the location for the file is /etc/mysql/mysql.conf.d/mysqld.cnf).

If it's a Windows system, you can find it in the MySQL installation directory, usually something like C:\Program Files\MySQL\MySQL Server 5.5\ and the filename will be my.ini.

Change line

 bind-address = 127.0.0.1

to

 #bind-address = 127.0.0.1

And restart the MySQL server (Unix/OSX, and Windows) for the changes to take effect.

After doing all of above I still couldn't login as root remotely, but Telnetting to port 3306 confirmed that MySQL was accepting connections.

I started looking at the users in MySQL and noticed there were multiple root users with different passwords.

select user, host, password from mysql.user;

So in MySQL I set all the passwords for root again and I could finally log in remotely as root.

use mysql;
update user set password=PASSWORD('NEWPASSWORD') where User='root';
flush privileges;

Just a note from my experience, you can find configuration file under this path /etc/mysql/mysql.conf.d/mysqld.cnf.

(I struggled for some time to find this path)

In my case I was trying to connect to a remote mysql server on cent OS. After going through a lot of solutions (granting all privileges, removing ip bindings,enabling networking) problem was still not getting solved.

As it turned out, while looking into various solutions,I came across iptables, which made me realize mysql port 3306 was not accepting connections.

Here is a small note on how I checked and resolved this issue.

• Checking if port is accepting connections:

telnet (mysql server ip) [portNo]

• Adding ip table rule to allow connections on the port:

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

• Would not recommend this for production environment, but if your iptables are not configured properly, adding the rules might not still solve the issue. In that case following should be done:

service iptables stop

Hope this helps.

All process for remote login. Remote login is off by default.You need to open it manually for all ip..to give access all ip

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

Specific Ip

GRANT ALL PRIVILEGES ON *.* TO 'root'@'your_desire_ip' IDENTIFIED BY 'password';

then

flush privileges;

You can check your User Host & Password

SELECT host,user,authentication_string FROM mysql.user;

Now your duty is to change this

bind-address = 127.0.0.1

You can find this on

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

if you not find this on there then try this

sudo nano /etc/mysql/my.cnf

comment in this

#bind-address = 127.0.0.1

Then restart Mysql

sudo service mysql restart

Now enjoy remote login