How to access MySQL from a remote computer (not localhost)?
Solution 1
Ok, be aware this gives the world and his dog access to your mysql server.
GRANT ALL ON *.* to '%'@'%' WITH GRANT OPTION;
But say you are on your home network of 192.168.1.2/16 then you can at least limit it like this.
GRANT ALL ON *.* to '%'@'192.168.%' WITH GRANT OPTION;
Another option is that you have a user and password but want to connect from anywhere
GRANT ALL ON *.* to 'mysecretuser'@'%' IDENTIFIED BY 'mysecretpassword' WITH GRANT OPTION;
Solution 2
first check the ip assigned to vmware using from cmd
ipconfig/all
suppose ipassigned to vmware is 192.168.11.1 now in vmware in ubuntu check the ipadress
ifconfig
suppose ip adress of ubuntu is 192.168.11.137 now open mysql configurations
sudo nano /etc/mysql/my.cnf change the bind address to ubuntu ip address
bind-address = 192.168.11.137
restart mysql now connect to mysql
mysql -u root -p
and create a user with all privileges and host ip of vmware i.e 192.168.11.1
GRANT ALL ON db.* TO user@'192.168.11.1' IDENTIFIED BY 'PASSWORD';
now try to connect from windows. also open port of mysql
/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
restart mysql and try to connect on ubuntu host ip address 192.168.11.137
Admin
Updated on June 17, 2022Comments
-
Admin almost 2 years
I have my dev environment set up as a Ubuntu Server (with LAMP installation) inside a vmware. The vmware is running on my local windows 7 machine. When I try to access my mysql server via HeidiSQL program the connection fails. I get a:
Server Error 2003, can't connect to mysql server on <IP ADRESS HERE>
I can however access the db server via PhpMyAdmin. MySQL is running and my connection credentials and port are all correct.
I read that you should enter the IPs of the computer you are trying to connect from as the "bind address" in the my.cnf file. Which I did. I tried both the internal network IP as well as the online IP. Still no luck, same message.
Since this isn't a production environment I would ideally like to allow anyone to access that server, not limit it by IP. Especially since my ISP assigns dynamic IPS. So I would have to change it all the time, assuming that even works.
So does anyone know how I can connect to my MySQL server from a remote computer?
P.S. I assume this is something developers have to deal with that's why I posted it here and not Super User. If it must be migrated please send it to Server Fault not Super User.