# MySQL remote access

## appleshampoo

For some reason I can't connect to my MySQL database remotely.  I'm trying to use 

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

----------

## adaptr

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.

----------

## appleshampoo

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:

```

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 :

```

mysql> grant all privileges on *.* to 'root'@'%';

```

Shouldn't this allow the root user to connect from anywhere with the correct password?

----------

## appleshampoo

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:

```
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:

```
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.

----------

## kashani

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

----------

## appleshampoo

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.

----------

## appleshampoo

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:
> ...

 

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

----------

## vrm-ol

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

 

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]):

```

(/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

```

mysql> set password for root@localhost=OLD_PASSWORD('yourpassword');

```

yours,

   vrm

----------

