connecting to mysql server on another PC in LAN
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.
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
fromNavigator
and click on it.
- Select
root
user and change value forLimit 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
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.
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.