Accessing MySQL Server from a Remote Machine in the Same Network

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

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

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 the password when prompted.

2.2 Check Existing User Privileges

Run the following command:

SELECT host, user FROM mysql.user;

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;

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;

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

Change it to:

bind-address = 0.0.0.0

3.2 Restart MySQL Service

Windows:

net stop mysql
net start mysql

Linux:

sudo systemctl restart mysql

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

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.