Connecting to MySQL Server Across a Local Windows Network

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:

  1. Open Windows Firewall.
  2. Select the Advanced tab.
  3. Under the network connection settings select Local Area Connection and then Settings.
  4. Select Add a new service.
  5. Name it something sensible like MySQL Server.
  6. Enter the IP address or computer name of the computer running MySQL.
  7. Specify the port number 3306 for both external and internal port numbers and select TCP.
    Windows Firewall Service Settings
  8. Confirm your settings.
  9. The new service should now be listed under services and selected. Now choose OK to exit each window that appears to save the changes.
    Local Area Network Settings

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.

This entry was posted in Programming and tagged , . Bookmark the permalink. Both comments and trackbacks are currently closed.

24 Comments

  1. Posted April 4, 2014 at 7:32 pm | Permalink

    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.

    • Posted April 5, 2014 at 11:20 am | Permalink

      You’re welcome Mahadi. I’m pleased it helped!

  2. Posted April 19, 2015 at 5:41 pm | Permalink

    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

  3. Posted April 20, 2015 at 4:57 pm | Permalink

    Thank you so much. I got it right. Thank you and Gob bless you

    • Posted April 20, 2015 at 5:07 pm | Permalink

      Glad you got there! I think the settings are quite similar under Windows 7.

  4. Posted June 13, 2016 at 7:33 am | Permalink

    Thanks Admin,

    For the Article it’s save lot of time for me.Thanks again

  5. Posted November 24, 2016 at 12:04 pm | Permalink

    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

    • Posted November 24, 2016 at 2:58 pm | Permalink

      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.

  6. Posted February 22, 2017 at 4:08 pm | Permalink

    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?

    • Posted March 8, 2017 at 3:28 pm | Permalink

      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

  7. Posted April 20, 2017 at 6:13 am | Permalink

    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

    • Posted April 20, 2017 at 8:58 am | Permalink

      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

      • Posted April 20, 2017 at 1:36 pm | Permalink

        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 🙂

        • Posted April 25, 2017 at 5:53 pm | Permalink

          I sent you an email with a bit more information so I hope that helped.

          • Posted July 9, 2017 at 12:16 pm | Permalink

            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.

          • Posted July 10, 2017 at 11:19 am | Permalink

            Hi vtsoft. I’ve emailed those details I sent Nisha. The details in the post should cover the basics though.

  8. Posted July 18, 2017 at 10:53 am | Permalink

    i cant see your explanations on windows 7

    • Posted July 19, 2017 at 8:19 am | Permalink

      For Windows 7 use the Start Menu and search for ‘Allow a program through Windows Firewall’. From there the approach is quite similar.

  9. Posted July 4, 2018 at 12:15 pm | Permalink

    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

    • Posted July 4, 2018 at 2:59 pm | Permalink

      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.

  10. Posted January 9, 2019 at 3:13 am | Permalink

    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

    • Posted January 9, 2019 at 9:55 am | Permalink

      I’ve sent you that email Zai.

      • Posted January 26, 2021 at 7:21 pm | Permalink

        kindly forward the email to me. am also stuck

        • Posted January 27, 2021 at 8:40 pm | Permalink

          Sorry, this is no longer available.