connecting to mysql server on another PC in LAN

mysql

135,710

Solution 1

That was a very useful question! Since we need to run the application with a centralized database, we should give the privileges to that computer in LAN to access the particular database hosted in LAN PC. Here is the solution for that!

Go to MySQL server

Type the following code to grant access for other pc:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root_password';

then type:
```
FLUSH PRIVILEGES;
```

Replace % with the IP you want to grant access for!

Solution 2

Users who can Install `MySQL Workbench` on `MySQL Server` Machine

If you use or have MySQL Workbench on the MySQL Server PC you can do this with just a few clicks. Recommend only for development environment.

Connect to MySQL Server

Find this option Users and Privileges from Navigator and click on it.

Select root user and change value for Limit to Hosts Matching to %.

The click Apply at the bottom.

This should enable root user to access MySQL Server from remote machine.

Solution 3

Since you have MySQL on your local computer, you do not need to bother with the IP address of the machine. Just use localhost:

mysql -u user -p

mysql -hlocalhost -u user -p

If you cannot login with this, you must find out what usernames (user@host) exist in the MySQL Server locally. Here is what you do:

Step 01) Startup MySQL so that no passwords are require no passwords and denies TCP/IP connections
```
service mysql restart --skip-grant-tables --skip-networking
```
Keep in mind that standard SQL for adding users, granting and revoking privileges are disabled.

Step 02) Show users and hosts

select concat(''',user,'''@''',host,'''') userhost,password from mysql.user;

Step 03) Check your password to make sure it works
```
select user,host from mysql.user where password=password('YourMySQLPassword');
```
If your password produces no output for this query, you have a bad password.

If your password produces output for this query, look at the users and hosts. If your host value is '%', your should be able to connect from anywhere. If your host is 'localhost', you should be able to connect locally.

Make user you have 'root'@'localhost' defined.

Once you have done what is needed, just restart mysql normally
```
service mysql restart
```
If you are able to connect successfully on the macbook, run this query:
```
SELECT USER(),CURRENT_USER();
```
USER() reports how you attempted to authenticate in MySQL

CURRENT_USER() reports how you were allowed to authenticate in MySQL

Let us know what happens !!!

UPDATE 2012-02-13 20:47 EDT

See if any user allows remote access (i.e, host in mysql.user is '%'). If you do not, then add 'user'@'%' to mysql.user.

Solution 4

Follow a simple checklist:

Try pinging the machine ping 192.168.1.2
Ensure MySQL is running on the specified port 3306 i.e. it has not been modified.
Ensure that the other PC is not blocking inbound connections on that port. If it is, add a firewall exception to allow connections on port 3306 and allow inbound connections in general.
It would be nice if you could post the exact error as it is displayed when you attempt to make that connection.

Solution 5

mysql -u user -h 192.168.1.2 -p

This should be enough for connection to MySQL server. Please, check the firewall of 192.168.1.2 if remote connection to MySQL server is enabled.

Regards

View more solutions

135,710

Author by

revolver

Updated on July 09, 2022

Comments

revolver almost 2 years
I have mySQL setup in a PC on my local network, how do I connect to it? I also have mySQL installed in this computer (which I want to use to connect to the database).

I tried the following but it's not working
```
mysql -u user -h 192.168.1.28:3306 -p password
ERROR 2005 (HY000): Unknown MySQL server host '192.168.1.28:3306' (0)
```
EDIT: Thanks for your help. Anyway, I connect without 3306 and I have another problem. MACBOOK is the name of my client computer.
```
mysql -u user -ppassword -h 192.168.1.28 
ERROR 1045 (28000): Access denied for user 'user'@'MACBOOK' (using password: YES)
```
Thanks.
revolver over 12 years

I do have mysql in my localhost, but I want to connect to mysql on the other PC.
RiggsFolly about 8 years

Its rather dangerous allowing root the SuperUser to be used from any ip in the universe. It would be better to create a new user account with access to only the database in question, and with only the minimum privilages to do what is required by the remote access.
user55924 about 7 years

This answer is really helpful! And yes as @RiggsFolly said create a new account first then do it .
mlissner almost 7 years

I agree it's dangerous, BUT is it crazy to do this on a dev machine in a small home office?
Kasun Siyambalapitiya over 6 years

@user3811169 how to remove the privilege after ward
Dmytro about 6 years

ERROR 1133 (42000): Can't find any matching row in the user table