Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
MySQL remote access
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Networking & Security
View previous topic :: View next topic  
Author Message
appleshampoo
Tux's lil' helper
Tux's lil' helper


Joined: 26 Sep 2004
Posts: 116
Location: Seattle, WA

PostPosted: Sun Nov 06, 2005 7:29 pm    Post subject: MySQL remote access Reply with quote

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
View user's profile Send private message
adaptr
Watchman
Watchman


Joined: 06 Oct 2002
Posts: 6730
Location: Rotterdam, Netherlands

PostPosted: Sun Nov 06, 2005 7:44 pm    Post subject: Reply with quote

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
View user's profile Send private message
appleshampoo
Tux's lil' helper
Tux's lil' helper


Joined: 26 Sep 2004
Posts: 116
Location: Seattle, WA

PostPosted: Sun Nov 06, 2005 8:06 pm    Post subject: Reply with quote

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
View user's profile Send private message
appleshampoo
Tux's lil' helper
Tux's lil' helper


Joined: 26 Sep 2004
Posts: 116
Location: Seattle, WA

PostPosted: Sun Nov 06, 2005 8:24 pm    Post subject: Reply with quote

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
View user's profile Send private message
kashani
Advocate
Advocate


Joined: 02 Sep 2002
Posts: 2032
Location: San Francisco

PostPosted: Sun Nov 06, 2005 8:26 pm    Post subject: Reply with quote

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
View user's profile Send private message
appleshampoo
Tux's lil' helper
Tux's lil' helper


Joined: 26 Sep 2004
Posts: 116
Location: Seattle, WA

PostPosted: Sun Nov 06, 2005 9:14 pm    Post subject: Reply with quote

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
View user's profile Send private message
appleshampoo
Tux's lil' helper
Tux's lil' helper


Joined: 26 Sep 2004
Posts: 116
Location: Seattle, WA

PostPosted: Sun Nov 06, 2005 9:32 pm    Post subject: Reply with quote

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
View user's profile Send private message
vrm-ol
n00b
n00b


Joined: 04 Jul 2003
Posts: 40
Location: Euskirchen, Germany

PostPosted: Fri Nov 11, 2005 8:42 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Networking & Security All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum