DEV Community

Cover image for Accessing MySQL Server from a Remote Machine in the Same Network
AYON KARMAKAR
AYON KARMAKAR

Posted on

3 3 3 3 3

Accessing MySQL Server from a Remote Machine in the Same Network

#mysql #networking #database

Overview

This guide explains how to connect to a MySQL server running on a local network from a different machine. If you receive the error Host 'your_host' is not allowed to connect to this MySQL server, follow these steps to resolve it.

Step 1: Verify Network Connectivity

Before configuring MySQL, check if the server is reachable from the client machine.

1.1 Ping the MySQL Server

Run this command from the client machine:

ping 192.168.2.51
Enter fullscreen mode Exit fullscreen mode

If you receive replies, the server is reachable.

1.2 Test MySQL Port Connectivity

Use PowerShell or Command Prompt:

Test-NetConnection 192.168.2.51 -Port 3306
Enter fullscreen mode Exit fullscreen mode

If TcpTestSucceeded : True, MySQL is listening on port 3306.

Step 2: Grant Remote Access in MySQL

2.1 Log into MySQL on the Server

On the MySQL server (192.168.2.51), open a terminal or command prompt and log in:

mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

Enter the password when prompted.

2.2 Check Existing User Privileges

Run the following command:

SELECT host, user FROM mysql.user;
Enter fullscreen mode Exit fullscreen mode

If the root user only has localhost access, update its privileges.

2.3 Grant Remote Access to a Specific IP

To allow connections from 192.168.2.57, run:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.2.57' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

2.4 (Optional) Allow Access from Any IP

If you want to allow connections from any machine in the network:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

Step 3: Modify MySQL Configuration to Accept Remote Connections

By default, MySQL only listens on 127.0.0.1. Update this setting to allow external connections.

3.1 Edit MySQL Configuration File

Windows:

Edit C:\ProgramData\MySQL\MySQL Server X.X\my.ini

Linux:

Edit /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf

Find this line:

bind-address = 127.0.0.1
Enter fullscreen mode Exit fullscreen mode

Change it to:

bind-address = 0.0.0.0
Enter fullscreen mode Exit fullscreen mode

3.2 Restart MySQL Service

Windows: 

net stop mysql
net start mysql
Enter fullscreen mode Exit fullscreen mode

Linux: 

sudo systemctl restart mysql
Enter fullscreen mode Exit fullscreen mode

Step 4: Connect to MySQL from the Remote Machine

Now, on 192.168.2.57, try connecting:

mysql -h 192.168.2.51 -P 3306 -u root -p
Enter fullscreen mode Exit fullscreen mode

Conclusion

Following these steps, you should be able to connect to MySQL from another machine on the same network. If issues persist, check firewall rules and MySQL user privileges.

profile
Heroku
 Promoted

Heroku

Deploy with ease. Manage efficiently. Scale faster.

Leave the infrastructure headaches to us, while you focus on pushing boundaries, realizing your vision, and making a lasting impression on your users.

Get Started

Top comments (1)

Collapse
 
ayon_ssp profile image
AYON KARMAKAR • • Edited 
C:\Windows\system32>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5558
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.2.57' IDENTIFIED BY 'root' WITH GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'root' WITH GRANT OPTION' at line 1
mysql> CREATE USER 'root'@'192.168.2.57' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.2.57' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'root'@'192.168.2.57' IDENTIFIED BY 'root';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'192.168.2.57'
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.2.57' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Enter fullscreen mode Exit fullscreen mode
profile
ACI.dev
 Promoted

ACI image

ACI.dev: Fully Open-source AI Agent Tool-Use Infra (Composio Alternative)

100% open-source tool-use platform (backend, dev portal, integration library, SDK/MCP) that connects your AI agents to 600+ tools with multi-tenant auth, granular permissions, and access through direct function calling or a unified MCP server.

Check out our GitHub!