| View previous topic :: View next topic |
| Author |
Message |
appleshampoo
Tux's lil' helper
Joined: 26 Sep 2004
Posts: 116
|
 Posted: Sun Nov 06, 2005 7:29 pm Post subject: MySQL remote access |
 |
|
For some reason I can't connect to my MySQL database remotely. I'm trying to use | Code: | | mysql -u root -h hostname -p |
but it's not working because port 3306 isn't open...this is my first time adminning a DB, so I don't really know what I'm doing and have been trolling around the MySQL manual, but can't seem to find anything that tells you how to enable/disable networking. The only thing I have found is the "skip-networking" option, which is set to "off", so remote connections should be working...
Thanks,
appleshampoo |
|
| Back to top |
|
 |
adaptr
Watchman
Joined: 06 Oct 2002
Posts: 6730
Location: Rotterdam, Netherlands
|
| Posted: Sun Nov 06, 2005 7:44 pm Post subject: |
|
|
Not really, or rather, partly...
You also have to set the listening IP in my.cnf to something other than 127.0.0.1 - the machine's true IP will work, or 0.0.0.0 to enable networking on all interfaces.
_________________
>>> emerge (3 of 7) mcse/70-293 to /
Essential tools: gentoolkit eix profuse screen |
|
| Back to top |
|
|
appleshampoo
Tux's lil' helper
Joined: 26 Sep 2004
Posts: 116
|
| Posted: Sun Nov 06, 2005 8:06 pm Post subject: |
|
|
adaptr, you were correct, I was binding to localhost. However, after binding to my public IP address, I still cannot connect. I get the folloing message:
| Code: |
root@hashbrown-lappy ~ # mysql -u root -p -h hostname
Enter password:
ERROR 1045: Access denied for user 'root'@'cpe-24-160-204-65.ma.res.rr.com' (using password: YES)
|
I did run this command from the mysql prompt on the host :
| Code: |
mysql> grant all privileges on *.* to 'root'@'%';
|
Shouldn't this allow the root user to connect from anywhere with the correct password? |
|
| Back to top |
|
|
appleshampoo
Tux's lil' helper
Joined: 26 Sep 2004
Posts: 116
|
| Posted: Sun Nov 06, 2005 8:24 pm Post subject: |
|
|
Okay, so, I'm not sure if what I said in my above post should work or not (since it's the root user), so I tried to create a new account with remote access enabled. I did the following commands at the mysql prompt:
| Code: | mysql> grant all privileges on prs_data.* to 'username'@'%' identified by 'change';
mysql> grant all privileges on prs_data.* to 'username'@'localhost' identified by 'change'; |
Now, when I try to connect from a different host, I get the following message:
| Code: | root@hashbrown-lappy ~ # mysql -u username -p -h hostname
Enter password:
ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client
|
The server is running MySQL 4.1.14, and the client is 4.0.24. |
|
| Back to top |
|
|
kashani
Advocate
Joined: 02 Sep 2002
Posts: 2032
Location: San Francisco
|
| Posted: Sun Nov 06, 2005 8:26 pm Post subject: |
|
|
That should work unless you forgot to run flush privileges; to make the add active. I also prefer to comment out the bind-address line so that Mysql binds to both localhost and the ether interface.
kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape. |
|
| Back to top |
|
|
appleshampoo
Tux's lil' helper
Joined: 26 Sep 2004
Posts: 116
|
| Posted: Sun Nov 06, 2005 9:14 pm Post subject: |
|
|
| According to the MySQL doc, you don't have to flush privileges when you use the grant command; but only when you insert into the user table manually. Anyway, I tried flushing and got the same result. I also asked this question over on the MySQL forum, so we'll see if that helps. |
|
| Back to top |
|
|
appleshampoo
Tux's lil' helper
Joined: 26 Sep 2004
Posts: 116
|
| Posted: Sun Nov 06, 2005 9:32 pm Post subject: |
|
|
Okay, so I'm retarded, I just found the following in the MySQL doc in the section on common problems:
| Quote: |
A.2.3. Client does not support authentication protocol
MySQL 4.1 and up uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older clients. If you upgrade the server to 4.1, attempts to connect to it with an older client may fail with the following message:
shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client
|
So, yeah, the server was 4.1.x and the client was 4.0.x. However now I have a new problem; really the only place this is going to be important is connecting with query browser on administrator from a Windows box, so I guess what I need to know is if the windows binaries use the 4.1 password libraries or not. Hopefully so... |
|
| Back to top |
|
|
vrm-ol
n00b
Joined: 04 Jul 2003
Posts: 42
Location: Euskirchen, Germany
|
| Posted: Fri Nov 11, 2005 8:42 am Post subject: |
|
|
Hi appleshampoo,
I've got the same problem, ... php 4.x doesn't use the new password style either. But there is a method, to tell mysqld to use the old password scheme. Somle lines down from your quote the MySQLdoc states ...
| Quote: |
* Tell the server to use the older password hashing algorithm:
1. Start mysqld with the --old-passwords option.
2. Assign an old-format password to each account that has had its password updated to the longer 4.1 format.
|
The latter isn't really the problem for me, there are no other users configured than root at the moment. So just add "old_passwords" to your mysql.conf (Section [mysqld]):
| Code: |
(/etc/mysql/my.cnf)
[.. lots of other stuff ...]
[mysqld]
character-set-server = latin1
default-character-set = latin1
user = mysql
port = 3306
[.. some more options ...]
old-passwords
[and again lots of other stuff]
|
and then change, as mysql-root-user, all relevant password back to old-style-passwords again. eg
| Code: |
mysql> set password for root@localhost=OLD_PASSWORD('yourpassword');
|
yours,
vrm |
|
| Back to top |
|
|
|
Networking & Security
