How do i connect mysql workbench to another computer?

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.

asked Feb 13, 2012 at 8:27

revolverrevolver

2,3445 gold badges23 silver badges40 bronze badges

2

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!

1. Go to MySQL server
2. Type the following code to grant access for other pc:

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

3. then type:

   FLUSH PRIVILEGES;
   

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

answered Nov 27, 2014 at 12:27

user3811169user3811169

2272 silver badges7 bronze badges

5

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.

1. Connect to MySQL Server

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

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

1. The click Apply at the bottom.

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

answered Jan 20, 2019 at 5:02

ImtiazeAImtiazeA

1,20013 silver badges18 bronze badges

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

or

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

Login to the remote server and repeat Step 1-3

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

cursorrux

1,3343 gold badges8 silver badges19 bronze badges

answered Feb 13, 2012 at 18:48

RolandoMySQLDBARolandoMySQLDBA

43.2k16 gold badges89 silver badges130 bronze badges

1

Follow a simple checklist:

1. Try pinging the machine ping 192.168.1.2
2. Ensure MySQL is running on the specified port 3306 i.e. it has not been modified.
3. 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.
4. It would be nice if you could post the exact error as it is displayed when you attempt to make that connection.

answered Feb 13, 2012 at 8:30

Rohan PrabhuRohan Prabhu

6,9665 gold badges36 silver badges66 bronze badges

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

El Developer

3,3101 gold badge20 silver badges40 bronze badges

answered Feb 13, 2012 at 14:47

zdrsoftzdrsoft

2,04717 silver badges10 bronze badges

0

In Ubuntu Follow these steps:

1. Set bind-address at /etc/mysql/mysql.conf.d

   Change bind-address = 127.0.0.1 to bind-address = 192.24.805.50 # your IP

2. Grant permission for the remote machine

   mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'[remoteip]' IDENTIFIED
   BY 'anypassword' WITH GRANT OPTION;

3. Then try connect from remote machine

   mysql -u root -h 192.24.805.50 -p

cursorrux

1,3343 gold badges8 silver badges19 bronze badges

answered Nov 8, 2019 at 10:35

Connecting to any mysql database should be like this:

$mysql -h hostname -Pportnumber -u username -p [then enter]

Then it will ask for password. Note: Port number should be closer to -P or it will show error. Make sure you know what is your mysql port. Default is 3306 and is optional to specify the port in this case. If its anything else you need to mention port number with -P or else it will show error.

For example: $mysql -h 10.20.40.5 -P3306 -u root -p [then enter]

Password:My_Db_Password

Gubrish about product you using.

mysql>_

Note: If you are trying to connect a db at different location make sure you can ping to that server/computer.

$ping 10.20.40.5

It should return TTL with time you got back PONG. If it says destination unreachable then you cannot connect to remote mysql no matter what.

In such case contact your Network Administrator or Check your cable connection to your computer till the end of your target computer. Or check if you got LAN/WAN/MAN or internet/intranet/extranet working.

answered Feb 15, 2016 at 11:38

sarathkmsarathkm

1,1468 silver badges10 bronze badges

actually you shouldn't specify port in the host name. Mysql has special option for port [if port differs from default]

kind of

mysql --host=192.168.1.2 --port=3306

answered Feb 13, 2012 at 8:32

You don't have to specify ':3306' after the IP, it's the default port for MySQL.

And if your MySQL server runs with another port than 3306, then you have to add '-P [port]' instead of adding it to the IP address.

The MySQL client won't recognize the syntax "host:port", you HAVE to use -P [port] instead.

And btw, if you use '-p password', it won't work and will ask you the password again. You have to stick the password to the -p : -ppassword. [still, it's a very bad habit, because anyone that could do a PS on your server could see the plain password...]

answered Feb 13, 2012 at 8:31

huelboishuelbois

6,5321 gold badge18 silver badges21 bronze badges

You should use this:

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

or this:

>mysql -u user -h 192.168.1.2 -ppassword

...because 3306 is a default port number.

mysql Options

answered Feb 13, 2012 at 8:33

DevartDevart

117k22 gold badges161 silver badges181 bronze badges

How do I remotely access a MySQL database from another computer?

How can I access my database from another computer?

How do I connect to MySQL workbench?

How do I make my MySQL database accessible remotely?