Connect to mysql server on another PC on the local network

Question

I have mySQL setup on a PC on a local network, how do I connect? I also have mySQL installed on this computer (which I want to use to connect to the database).

I tried the following but did not work

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 am connecting without 3306, and I have one more 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.

Answer 1

That was a very helpful question! Since we need to run the application with a centralized database, we must grant privileges to this computer on the local network to access a specific database located on the PC on the local network. Here is the solution for this!

1. Go to MySQL server
2. Enter the following code to grant access to another computer

mysql> grant all privileges to. in "root" @ "%", identified as "root_password"; mysql> privilege reset;

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

Answer 2

Follow a simple checklist:

• Try pinging ping 192.168.1.2
• Make sure that MySQL is running on the specified port 3306 , i.e. it has not been modified.
• Make sure that another computer is not blocking incoming connections on this port. If so, add a firewall exception to allow connections on port 3306 and allow incoming connections in general.
• It would be nice if you could post the exact error as it appears when you try to make this connection.

Answer 3

Since you have mysql on your local computer, you do not need to worry about the IP address of the device. Just use localhost:

 mysql -u user -p 

or

 mysql -hlocalhost -u user -p 

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

Step 01) Starting mysql so that passwords do not require passwords and refuse TCP / IP connections

 service mysql restart --skip-grant-tables --skip-networking 

Keep in mind that standard SQL for adding users, granting and revoking privileges is disabled.

Step 02) Show Users and Hosts

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

Step 03) Verify your password to make sure it works.

 select user,host from mysql.user where password=password('YourMySQLPassword'); 

If your password does not display the result for this request, you have the wrong password.

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

Create the user "root" @ "localhost".

Once you have done what you need, just restart mysql normally

 service mysql restart 

If you are successfully connecting to a macbook, run this query:

 SELECT USER(),CURRENT_USER(); 

USER () reports how you tried to authenticate with MySQL

CURRENT_USER () tells how you were allowed to authenticate with MySQL

Let us know what will happen !!!

UPDATE 2012-02-13 20:47 EDT

Log in to the remote server and repeat step 1-3

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

Answer 4

 mysql -u user -h 192.168.1.2 -p 

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

Hi

Answer 5

The connection to any mysql database should be like this:

$mysql -h hostname -Pportnumber -u username -p (then enter)

Then he will ask for a password. Note. The port number should be closer to -P or an error will be displayed. Make sure you know what your mysql port is. The default value is 3306 and it is not necessary to specify a port in this case. If this is something else, you need to specify the port number with -P, otherwise it will show an error.

For example: $mysql -h 10.20.40.5 -P3306 -u root -p (then enter)

 Password:My_Db_Password 

Gubrish about the product you are using.

 mysql>_ 

Note. If you are trying to connect db elsewhere, make sure you can ping to this server / computer.

 $ping 10.20.40.5 

It should return TTL with the time when you returned PONG. If it says the destination is unavailable, then you cannot connect to the remote mysql no matter what.

In this case, contact your network administrator or check if your cable is connected to the computer to the end of your target computer. Or check the operation of LAN / WAN / MAN or Internet / Intranet / Extranet.

Answer 6

You do not need to specify ': 3306' after the IP, this is the default port for MySQL.

And if your MySQL server is running with a port other than 3306, you need to add '-P [port]' instead of adding it to the IP address.

The MySQL client does not recognize the host: port syntax; instead, you must use -P [port].

And btw, if you use the "-p password", it will not work and will ask for the password again. You must insert the password in -p: -ppassword. (however, this is a very bad habit, because anyone who could make a PS on your server could see a simple password ...)

Answer 7

in fact, you should not specify the port in the host name. Mysql has a special option for the port (if the port is different from the standard)

view

 mysql --host=192.168.1.2 --port=3306 

Answer 8

You should use this:

 >mysql -u user -h 192.168.1.2 -P 3306 -ppassword 

or that:

 >mysql -u user -h 192.168.1.2 -ppassword 

... because 3306 is the default port number.

mysql options

Answer 9

Users who can install MySQL Workbench on MySQL Server Machine

If you use MySQL Workbench or use it on a computer running MySQL Server, you can do this with just a few clicks. Recommend for development environment only.

1. Connect to MySQL server

2. Find this option " Users and Privileges Navigator and click on it.

3. Select the root and change the value for the Limit to Hosts Matching % parameter.

4. Click Apply below.

This should allow the root user to access the MySQL Server from a remote computer.