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.

Mar 24, 2025 - 10:17

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.