Mysql port forward and access from any pc outside network

networking router mysql port-forwarding

12,779

The MySQL server likely needs to be told to listen to connections from the outside. Depending on which distribution you use, find or create the right .cnf file (typically /etc/mysql/conf.d/local.cnf or worse /etc/my.cnf) with a [mysqld] block and add the line:

bind-address = 0.0.0.0

Afterwards, restart the MySQL service (typically service mysql restart) and check that lsof -n -i TCP:3306 says the same (as opposed to 127.0.0.1), and obviously check if you can connect to it from the outside.

12,779

herobrinebrother zoro

Updated on September 18, 2022

Comments

herobrinebrother zoro over 1 year

I created a application that use mysql and it will be public. I port forwarded mysql default port on my router and i want that anyone would be able to connect to mysql that is on my computer outside network. (anyone who gets my application ANYONE anywhere). But i dont know how to setup connection for that how do i do that soo anyone can connect to mysql that is on my computer?. This is how the connection string should look like:

"server=my ip;userid=user;password=something;database=databasename"

with default port that is port forwarded. I hope you understand what i want if no i will try to format it and explain better.
- Frank Thomas almost 9 years
  
  where are you having trouble? you forwarded the port, right? it shouldn't be hard to connect once that is done. Tell us what you have tried.
- herobrinebrother zoro almost 9 years
  
  I did port forward but i dont know what else i need to do. I think i need to create a new connection that people would be able to connect right now there is only localhost 3306 connection. Do i need to? last time i tried it sayed it cannot connect.
- Frank Thomas almost 9 years
  
  well, unless you have a high end router that supports NAT hairpinning, you have to test port forward rules from outside your network. go to canyouseeme.org and test that the port is open from the Internets perspective. then if the port fowarding is working, try connecting to mysql from outside your network
- herobrinebrother zoro almost 9 years
  
  i tried it in online mysql connection tester it did not connect. I dont have remote connections enabled maybe because of that? if it is how do i enable it? no my router is not high end i need to enter port name and the port and then my ip done. I know how to port forward :).
- Frank Thomas almost 9 years
  
  if you haven't enabled remote connections in my.cnf or run a GRANT ALL line granting a user@IPAddress, then you will have to do that as well. use '%' as the users IP address (eg 'user@%'), and make sure your Mysqld instance is bound to your servers localIP, not 127.0.0.1 or localhost. stackoverflow.com/questions/8348506/…
- herobrinebrother zoro almost 9 years
  
  Where is the my.cnf? i am using windows. I see a lot of folders in mysql folder. Like mysql server i was able to find my-defaults.ini file in mysql server folder. Is it the correct one? it says in the file that its the default file if i upgrade mysql it will copy it soo i guess its the wrong one. I did find etc/mysql folder in mysql fabric folder but i dont think its correct. What is the location? appdata? i been searching on the internet everyone says its etc.... BUT WHERE IS THE ETC FOLDER!?!?!
- Frank Thomas almost 9 years
  
  stackoverflow.com/questions/1712646/…
- herobrinebrother zoro almost 9 years
  
  Soo i found the file what do i need to do now add the bind-address = 0.0.0.0 ? and where on the top or bottom? like this? # server_type=3 bind-address = 0.0.0.0 [mysqld]
- herobrinebrother zoro almost 9 years
  
  OK i added bind-address = mylocalip. Soo what do i do now? that i added that bind-address do i need to enable something in the mysql or make a connection?
- herobrinebrother zoro almost 9 years
  
  Hello. Please what do i do next? i dont understand. Can you tell me where to go and what to do please? i been developing this application my hole life and i cant just cancel it right now when its FINISHED
- Frank Thomas almost 9 years
  
  I usually use the local IP unless I'm using multiple nics, but 0.0.0.0 should mean All nics. use netstat -abno to determine if the binding is working, after editing the config and restarting the service.
- Frank Thomas almost 9 years
  
  did you restart the windows service after editing the port? also how are you testing this? remember, without hairpin NAT, you will not be able to test the port forwarding rules from inside your network. are you able to connect directly to the mysql instance from within your network, by going to the servers local LAN IP?
- herobrinebrother zoro almost 9 years
  
  Yes i am able to connect to the mysql now that is on my local ip bind but like i sayed on the answer i have problems. I CANT LOGIN. Access denied!. HALP!
- Frank Thomas almost 9 years
  
  did you run the GRANT ALL 'user'@'%' ... command in MySQL described here: stackoverflow.com/questions/8348506/… ?
- herobrinebrother zoro almost 9 years
  
  That is the problem i cant run that sql command. I dont have access and the localhost is no connection.
- Frank Thomas almost 9 years
  
  you'll have to ssh in, or get direct access to the server console.
- herobrinebrother zoro almost 9 years
  
  Can you tell me how to ssh? i have no idea it give me more options and i dont know what to type in.
- Frank Thomas almost 9 years
  
  how do you normally access this server to for instance, install mysqld? ssh is a remote shell protocol, so if I use ssh to login to a server, it shows me a command line shell (BASH in my case) with which I can administer the server. on linux systems ssh is built it, so I would enter the command ssh user@serverNameOrIP and it would open a shell to the server. on Windows PuTTY is the most widely used ssh client.
- herobrinebrother zoro almost 9 years
  
  I am on windows using mysql workbench. I do have puTTy installed but i dont know how to use it. Soo what do i do? there is a ssh option in the connection manager in mysql workbench
- herobrinebrother zoro almost 9 years
  
  mediafire.com/download/vylx64trsyaca7x/newproblem.avi video about the access denied problem.
- Frank Thomas almost 9 years
  
  so can't you run the grant command via Workbench?
- herobrinebrother zoro almost 9 years
  
  I can but no at the same time. Because i cannot connect to mysql. I get access denied.
- herobrinebrother zoro almost 9 years
  
  I think i need to get my localhost back to life that is right now no connection.
- Frank Thomas almost 9 years
  
  I recommend you start another thread on that, now that you have the networking down.
- herobrinebrother zoro almost 9 years
  
  Sry for no reply. Been in a hospital. Soo this is what i get: Access denied for user 'root'@'192.168.0.11' (using password: NO) OR Access denied for user 'root'@'192.168.0.11' (using password: YES). I think i need to grant permission on this 192.168.0.11 ip to access but i cannot. Because its access denied. I tried doing the sql command still access denied. I have localhost connection too if i try to grant permission there i am gonna get this: Error Code: 1133. Can't find any matching row in the user table using this command: GRANT ALL PRIVILEGES ON . TO 'root'@'192.168.0.11'. Comment part 1
- herobrinebrother zoro almost 9 years
  
  Comment part 2. But i dont think i need to grant the permission on localhost because its on my local ip connection that is 192.168.0.11 that i cannot access. I get access denied on it soo how do i grant permission? or remove password if its the problem. I tried storing a new password in the vault of the local ip connection its still the same problem. HELP!. I tried posting this problem on other question but no one answer. 2 days no answer.
- herobrinebrother zoro almost 9 years
  
  Hey guys help me solve almost the same problem here : superuser.com/questions/931075/…
herobrinebrother zoro almost 9 years

Where is the my.cnf? i am using windows. I see a lot of folders in mysql folder. Like mysql server i was able to find my-defaults.ini file in mysql server folder. Is it the correct one? it says in the file that its the default file if i upgrade mysql it will copy it soo i guess its the wrong one. I did find etc/mysql folder in mysql fabric folder but i dont think its correct. What is the location? appdata? i been searching on the internet everyone says its etc.... BUT WHERE IS THE ETC FOLDER!?!?!
Josip Rodin almost 9 years

@herobrinebrotherzoro a quick google gives me stackoverflow.com/questions/4292769/…
herobrinebrother zoro almost 9 years

OK i added bind-address = mylocalip. Soo what do i do now? that i added that bind-address do i need to enable something in the mysql or make a connection? i still cannot connect from outside network. Here is how it looks in the file: [mysqld] bind-address = 192.168.0.11
herobrinebrother zoro almost 9 years

I restarted my computer when i added and saved that bind-address i added in the file.
herobrinebrother zoro almost 9 years

Ok i created the connection what now? the bind-address dont think it works. I still cannot connect.
Josip Rodin almost 9 years

When you connect locally to that port, do you get MySQL? For example, open PuTTY to 192.168.0.11 port 3306 type Raw, does it open a screen and sit there waiting for input, or does it close with an error?
herobrinebrother zoro almost 9 years

Ok i connect with putty to my local ip 192.168.0.11 and 3306 ip using raw type i get this text for 5 seconds: N 5.6.23-log(s/-4]Y%▒▒▒J)+<.og6sd|bmysql_native_password Something about mysql and random crap and password. Then it just close the connection after 5 sec or if i type anything. I am gonna record a video RIGHT NOW and post it in mediafire and give the download link soo you guys can see my connections i have right now the me.ini file and stuff soo you can see the problem better.
Josip Rodin almost 9 years

That is sufficient proof that it's listening. Now try the same kind of test from outside your network to verify that port forwarding is working.
herobrinebrother zoro almost 9 years

Ok soo here is the stuff. here is the video: mediafire.com/download/pusxr9d44ta3reg/my+problem.avi . Now in the video i sayed that after restart the connection will work i restarted and i still cannot connect to localhost no connection soo i made a new connection with my local ip it DID work! it asked for a password i typed my password that stored in the vault and i use it everywhere on my database it says access denied failed to connect. Ok soo how do i fix the password any does it suppose to be that localhost does not work? soo i tested my database outside connection here: NEXT c
herobrinebrother zoro almost 9 years

Comment part 2. I test it here: rainbowspuppiessunshine.com/tools/dbtest/index.php outside the connection using my public ip i get this error: Lost connection to MySQL server at 'reading initial communication packet', system error: 111 . Soo it does not work outside connection i guess. Soo what is the problems? sry for delaying you people i rly want this solved.
herobrinebrother zoro almost 9 years

If i try to connect outside connection to mysql using my local ip i get this error: Can't connect to MySQL server on '192.168.0.11' (13)
Josip Rodin almost 9 years

That code 13 in parenthesis should mean Permission denied, which means you have basic connectivity, you just need to set up the appropriate GRANTs for 'someusername'@'somehost', or possibly 'someusername'@'%'.
herobrinebrother zoro almost 9 years

how do i do that ? my connection name is test soo what do i do?
herobrinebrother zoro almost 9 years

I cannot do that grant permissions sql command line anyway i dont have access how do i get it ?
herobrinebrother zoro almost 9 years

mediafire.com/download/vylx64trsyaca7x/newproblem.avi video about the access denied problem.
Josip Rodin almost 9 years

This is probably beyond the scope of this discussion, just google MySQL Windows GRANT or something like that and see how it's done, it should be documented
herobrinebrother zoro almost 9 years

Thanks. I fixed it. Changed to 0.0.0.0 in the bind address and deleted old one. Now everything works. THANKS! OMG THANKS!
herobrinebrother zoro almost 9 years

Hey when i try to do grant all command i noticed that it does not do anything because there is Error Code: 1133. Can't find any matching row in the user table. How do i fix it soo i can get access and login to it.
Josip Rodin almost 9 years

Can you please edit your question and copy and paste the exact command and message you received?
herobrinebrother zoro almost 9 years

Sry for no reply. Been in a hospital. Soo this is what i get: Access denied for user 'root'@'192.168.0.11' (using password: NO) OR Access denied for user 'root'@'192.168.0.11' (using password: YES). I think i need to grant permission on this 192.168.0.11 ip to access but i cannot. Because its access denied. I tried doing the sql command still access denied. I have localhost connection too if i try to grant permission there i am gonna get this: Error Code: 1133. Can't find any matching row in the user table using this command: GRANT ALL PRIVILEGES ON . TO 'root'@'192.168.0.11'. Comment part 1
herobrinebrother zoro almost 9 years

Comment part 2. But i dont think i need to grant the permission on localhost because its on my local ip connection that is 192.168.0.11 that i cannot access. I get access denied on it soo how do i grant permission? or remove password if its the problem. I tried storing a new password in the vault of the local ip connection its still the same problem. HELP!. I tried posting this problem on other question but no one answer. 2 days no answer.
herobrinebrother zoro almost 9 years

Hey guys help me solve almost the same problem here : superuser.com/questions/931075/…