Connecting to a MySQL Database Over LAN Using SQL Workbench

Introduction

Connecting to a MySQL database over a local area network (LAN) is a common task for developers and database administrators. This article will walk you through the steps needed to connect to a MySQL database running on another machine in the same network using SQL Workbench and ipconfig.

Step 1. Get the IP address of the MySQL server machine

First, you need to identify the IP address of the machine where the MySQL server is installed (let's call this Machine A).

1. On Machine A, open a command prompt.
2. Type ipconfig (on Windows) or ifconfig (on Linux/macOS) and press Enter.
3. Note down the IPv4 address of the machine. This address usually looks like 192.168.x.x or 10. x.x.x.

Step 2. Configure MySQL server for Remote connections

Ensure that the MySQL server on Machine A is configured to accept connections from remote machines.

1. Connect to MachineA Localhost Database.
   
2. Go to Administration and click on user root. It will show the default set to localhost.
   
3. Change Limit to Hosts Machines to "%". as shown in the below image and click on Apply.
   
4. After successfully applying it, it will show the user root from % allowed. It can now be accessible from other machines over LAN.
   

Step 3. Allow Firewall Access (if Necessary)

If there is a firewall on Machine A, you need to allow incoming connections on the MySQL port (default is 3306).

1. On Windows, open the Control Panel and go to System and Security > Windows Defender Firewall > Advanced settings.
2. Create a new inbound rule to allow traffic on port 3306.

Step 4. Connect from SQL Workbench

Now, set up the connection from SQL Workbench on Machine B (the machine from which you want to connect).

1. Install (if not) and open SQL Workbench.
   
2. Create a new connection profile.
   
   • Enter the IP address of Machine A in the Hostname field.
   • Use 3306 for the Port field unless MySQL is running on a different port.
     
   • Provide the MySQL username and password for the user account you want to connect with.
     
3. Test the connection to ensure it works. You will see databases of Machine A.
   

Security Considerations

Connecting over a LAN involves several security considerations.

• Use strong passwords for MySQL user accounts.
• Limit access rights to specific IP addresses if possible.
• Ensure your network is secure against unauthorized access.

Conclusion

By following these steps, you should be able to connect to a MySQL database running on another machine over a LAN using SQL Workbench. This setup is useful for various development and administrative tasks but always remember to follow best practices for network and database security.