connecting to mysql server on another PC in LAN

Question

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

User · Answer

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 locallly. 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 privs 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.

User · Answer

In Ubuntu Follow these steps    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   Grant permission for the remote machine  mysql GRANT ALL PRIVILEGES ON   TO  root    remoteip   IDENTIFIED BY  anypassword  WITH GRANT OPTION  Then try connect from remote machine  mysql -u root -h 192 24 805 50 -p

User · Answer

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

User · Answer

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 gt     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

User · Answer

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

User · Answer

You should use this    gt mysql -u user -h 192 168 1 2 -P 3306 -ppassword   or this    gt mysql -u user -h 192 168 1 2 -ppassword      because 3306 is a default port number   mysql Options

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] connecting to mysql server on another PC in LAN

Examples related to mysql