To access MySQL Server over a local Windows network you will need to open up the port that MySQL is using and also grant privileges for the user that will access the database server.
Opening Your Server
To allow network access you need to open up the port that MySQL is using, normally 3306. This is achieved by modifying the Windows Firewall settings on the computer running MySQL:
- Open Windows Firewall.
- Select the Advanced tab.
- Under the network connection settings select Local Area Connection and then Settings.
- Select Add a new service.
- Name it something sensible like MySQL Server.
- Enter the IP address or computer name of the computer running MySQL.
- Specify the port number 3306 for both external and internal port numbers and select TCP.
- Confirm your settings.
- The new service should now be listed under services and selected. Now choose OK to exit each window that appears to save the changes.
With the service created you should now be able to connect to the database from another machine on the network.
Grant User Privileges
Before you can gain access to the database user privileges will need to be granted for the user attempting to connect. Privileges are granted in the normal way but you need to specify the host the user is connecting from or use a wildcard. Using Command Prompt log on to your MySQL server and then execute the following command to specify a host:
grant all privileges on *.* to 'user'@'host' identified by 'password' with grant option;
or the following to use a wildcard:
grant all privileges on *.* to 'user'@'host' % by 'password' with grant option;
With these changes you should now be ready to go and be able to access the database server across a Windows local network.
24 Comments
I was confused last 3 months for MySql connection from lan pc, finally I saw your post and I made my connection successfully. First I follow “Opening Your Server”
second, I open my Mysql Workbench then goto Users and Privileges > select root user > Login Tab > Limit Connectivity to Hosts Maching > %.
then Done from Another network desktop computer via Lan, and I can now use my Server as a Storage Data server.
Thanks a lot for your very useful post.
You’re welcome Mahadi. I’m pleased it helped!
For more than a year, I have looking a well explained steps of using mysql on local network. I hope that this will work for me too. on windows 7 I can. only see connection security rule. how do I set this in lieu of service settings you described. Thanks a lot
Thank you so much. I got it right. Thank you and Gob bless you
Glad you got there! I think the settings are quite similar under Windows 7.
Thanks Admin,
For the Article it’s save lot of time for me.Thanks again
I have windows 8 and when I open firework don’t find local are network and can’t follow your guide can you helpme please?
thanks in advance
Hi Roy.
I don’t have Windows 8 anymore but I think you can use the Windows 10 approach which is to go to ‘Windows Firewall’, ‘Advanced Settings’ and then create an ‘Inbound Rule’ for port 3306.
Hope that works.
Dear Friend
I have the same probelm with Mysql , i am using Unicenta opos software with Win7 .
I can not access from the other PC?
I follow up all the above but cold not make this thing : grant all privileges on *.* to ‘user’@’host’ identified by ‘password’ with grant option;
Can anyone help me please?
Hi Salam,
Did you replace ‘user’ with your username and ‘host’ with the computer name? If you post the statement you used I can check for any problems.
Nick
I am totally new to SQL and was searching the part for accessing database in a network. After doing these tasks in PC1 for example which is the host, how can I access the database from PC2 which is in the same network
Thanks in advance
Hi Nisha,
To connect to PC1 from PC2 you would need the local IP address or computer name of PC1 which you would then use as the ‘server host’ for the connection from PC2.
Nick
I couldn’t get you.. Let me make it simple. If i open MySql in PC2 and use same commands like select query etc as in PC1 will i get the same results or should I include some additional query to make the data accessible from PC2
Pls attach pic explaining it if possible
Again Thanks in advance 🙂
I sent you an email with a bit more information so I hope that helped.
Hi nick, could you please send me also the email u sent to Nisha. I want to setup a mysql server that can be used in lan connected computers as well. Thanks in advance.
Hi vtsoft. I’ve emailed those details I sent Nisha. The details in the post should cover the basics though.
i cant see your explanations on windows 7
For Windows 7 use the Start Menu and search for ‘Allow a program through Windows Firewall’. From there the approach is quite similar.
Hi im really a beginner here. I have a lot of questions, but let me start with this:
After doing those things, what should I do so that my program(in PC 2) can access the database on other computers(PC 1)??
THANKS in advance
It sounds like you have a similar query to Nisha above.
Instead of using localhost as the host you specify the computer name or IP address of the PC you want to access.
Hi Nick.
Can you forward me your email to Nisha? [your post on Apr 25th] I am trying to do the same as she so need the guide on how to do it. Thanks
I’ve sent you that email Zai.
kindly forward the email to me. am also stuck
Sorry, this is no longer available.