elearn
By:
elearn

how can connect mysql database in my vps?

April 9, 2015 7.5k views
MySQL

I have installed mysql database on my vps ip, and input the following command when to login mysql.

mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'%'IDENTIFIED BY 'passwd' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
service mysql restart

Now i want to login mysql database on my vps ip from remote machine.

mysql -h vpsip -P 3306 -u root -ppasswd
ERROR 2003 (HY000): Can't connect to MySQL server on 'vps
ip' (111)

Why can't login ?

4 Answers

Run this:

netstat -plant|grep ":3306"|grep "LISTEN"

If it comes back with something like this:

tcp        0      127.0.0.1:3306              0.0.0.0:*               LISTEN      1881/mysql

This means that it's bound to local only. To fix this, follow the appropriate guide below for your distro.

If it shows something like this:

tcp        0      0.0.0.0:3306              0.0.0.0:*               LISTEN      1881/mysql

This means it's a firewall issue. Follow the Firewall guide below for your distro.

For 127.0.0.1 result

Ubuntu:

edit /etc/mysql/my.cnf, and look for this:

bind-address=127.0.0.1

and change it to this:

bind-address=0.0.0.0

Restart MySQL:

service mysql restart

And you should be good to go

CentOS/Fedora:

edit /etc/my.cnf, and look for this:

bind-address=127.0.0.1

and change it to this:

bind-address=0.0.0.0

Restart MySQL:

service mysqld restart

And you should be good to go

For Firewall issue

Ubuntu:

run this:

ufw allow from any to any port 3306

And you should be good to go

CentOS 6.x:

run this:

iptables -I INPUT -p tcp -m tcp --dport 3306 -j ACCEPT; service iptables save

And you should be good to go

CentOS 7:

run this:

firewall-cmd --zone=public --add-port=3306/tcp --permanent; firewall-cmd --reload

and you should be good to go.

By default MySQL only listens on localhost. You would need to update your /etc/mysql/my.cnf to have the service listen on your public or private IP address. You can do this by directly editing the file and replacing 127.0.0.1 with your droplet's public or private IP address or you can use this command to quickly update the file and restart the MySQL service:

sed -i.bak "s/127.0.0.1/Your_Droplet's_IP/g" /etc/mysql/my.cnf;
service mysql restart;

It is ok for me .

sed -i.bak "s/127.0.0.1/YourDroplet'sIP/g" /etc/mysql/my.cnf;
service mysql restart;

It's great. I spent a long time not solving this problem. Thank you!

Have another answer? Share your knowledge.