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. Post a comment or leave a trackback: Trackback URL.

14 Comments

  1. Mahadi Hassan Razib
    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. Hammed Taofeek
    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. Hammed Taofeek
    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. Sarabjeet Singh
    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. Roy
    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. Salam
    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. Nisha
    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

      • Nisha
        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.

Post a Comment

*
*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

* = Required