# [mysql]    I can only connect from localhost

## MaheS

I can only connect to my perfectly working mysql server from localhost

What could be the problem?    How to fix?

----------

## moocha

Have you granted access to the remote users? I'm referring to MYSQL access/users.

----------

## MaheS

Yes I have

 it is not a  'user can't login'  problem,   it's   can't connect to database  problem! :s

----------

## Spooky Ghost

Check bind_address in /etc/mysql/my.cnf

----------

## MaheS

bind_adress = 127.0.0.1

Ok    I  putted this line in comment,   works!    thx vry mutch

Now I need to take care of the   'Acces denied for user: ... '  error

but that shouldn't be much of a problem

----------

## tecknojunky

I just installed mysql for the first time and I'm trying to set root passwords, as specified by the ebuild at the end of the merge.

Similar problem:

```
# /usr/bin/mysqladmin -u root -h manitou password 'thinkagain'

/usr/bin/mysqladmin: connect to server at 'manitou' failed

error: 'Host 'manitou.inet' is not allowed to connect to this MySQL server'
```

bin-address IS commented and there are no firewalls on the box.  Anyhow, manitou = localhost

What's wrong?

----------

## kashani

 *tecknojunky wrote:*   

> 
> 
> Similar problem:
> 
> ```
> ...

 

According the logs you posted, manitou actually equals manitou.inet which is most likely the problem. Also I'd give localhost a shot first before doing much troubleshooting.

kashani

----------

## tecknojunky

The locolhost part is done and worked on the 1st shot.

# /usr/bin/mysqladmin -u root -h manitou.inet password 'sayagain'

/usr/bin/mysqladmin: connect to server at 'manitou.inet' failed

error: 'Host 'manitou.inet' is not allowed to connect to this MySQL server'

----------

## kashani

ping manitou and see which IP you get. I'd guess that it goes to the interface and not 127.0.0.1

kashani

----------

## tecknojunky

 *kashani wrote:*   

> ping manitou and see which IP you get. I'd guess that it goes to the interface and not 127.0.0.1
> 
> kashani

 Which is the normal behavior.  What I meant is that manitou is the hostname of localhost for the lan.

```
manitou root # ping manitou.inet

PING manitou.inet (192.168.1.11) 56(84) bytes of data.

64 bytes from manitou.inet (192.168.1.11): icmp_seq=1 ttl=64 time=0.126 ms

64 bytes from manitou.inet (192.168.1.11): icmp_seq=2 ttl=64 time=0.147 ms

 

--- manitou.inet ping statistics ---

2 packets transmitted, 2 received, 0% packet loss, time 1000ms

rtt min/avg/max/mdev = 0.126/0.136/0.147/0.015 ms

manitou root # ping manitou

PING manitou.inet (192.168.1.11) 56(84) bytes of data.

64 bytes from manitou.inet (192.168.1.11): icmp_seq=1 ttl=64 time=0.179 ms

64 bytes from manitou.inet (192.168.1.11): icmp_seq=2 ttl=64 time=0.148 ms

 

--- manitou.inet ping statistics ---

2 packets transmitted, 2 received, 0% packet loss, time 1001ms

rtt min/avg/max/mdev = 0.148/0.163/0.179/0.020 ms

manitou root # ifconfig eth0

eth0      Lien encap:Ethernet  HWaddr 00:D0:09:AC:20:F9

          inet adr:192.168.1.11  Bcast:192.168.1.255  Masque:255.255.255.0

          adr inet6: fe80::2d0:9ff:feac:20f9/64 Scope:Lien

          UP BROADCAST NOTRAILERS RUNNING MULTICAST  MTU:1500  Metric:1

          RX packets:282785 errors:0 dropped:0 overruns:0 frame:0

          TX packets:385550 errors:0 dropped:0 overruns:0 carrier:0

          collisions:0 lg file transmission:1000

          RX bytes:55406999 (52.8 Mb)  TX bytes:294970525 (281.3 Mb)

          Interruption:11 Adresse de base:0xde00

 

```

----------

## kashani

Right, but the important distinction is that only localhost has access after that first command you ran. To see, do this.

mysql -u root -p

use mysql;

select * from user;

You'll most likely see only a localhost entry. You'll need to do something like this:

GRANT ALL PRIVILEGES ON *.* TO root@manitou IDENTIFIED by 'password' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED by 'password' WITH GRANT OPTION;

kashani

----------

## tecknojunky

But, but, I'm just doing verbatim what was said here:

```
manitou root # ebuild /var/db/pkg/dev-db/mysql-4.0.20-r1/mysql-4.0.20-r1.ebuild config

 * Press ENTER to create the mysql database and set proper

 * permissions on it, or Control-C to abort now...

 

Preparing db table

Preparing host table

Preparing user table

Preparing func table

Preparing tables_priv table

Preparing columns_priv table

Installing all prepared tables

 

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

/etc/init.d/mysql start (you have to start the server first!)

/usr/bin/mysqladmin -u root -h manitou password 'new-password'

/usr/bin/mysqladmin -u root password 'new-password'

See the manual for more instructions.

 

Please report any problems with the /usr/bin/mysqlbug script!

 

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at https://order.mysql.com

```

Now, if I do as you suggest:

```
manitou root # mysql -p Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 7 to server version: 4.0.20

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> select host,user from user;

+-----------+------+

| host      | user |

+-----------+------+

| localhost |      |

| localhost | root |

| manitou   |      |

| manitou   | root |

+-----------+------+

4 rows in set (0.01 sec)

 

mysql> GRANT ALL PRIVILEGES ON *.* TO root@manitou IDENTIFIED by 'password' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

 

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED by 'password' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

 

mysql>
```

After that: 

```
manitou root # /usr/bin/mysqladmin -u root -h manitou password 'StillNoLuck' /usr/bin/mysqladmin: connect to server at 'manitou' failed

error: 'Access denied for user: 'root@manitou.inet' (Using password: NO)'
```

----------

## fleed

You're close!

You can either restart mysql after you do the grants (not really necessary but might be easiest for you)

Or you could connect again:

```

mysql -p 

```

And do:

```

flush privileges;

```

then you should be set.

----------

## tecknojunky

I give up.  I don't understand why ebuild's instructions do not work only for me.  :Crying or Very sad: 

----------

## fleed

You have to know what you're doing!

----------

## tecknojunky

 *fleed wrote:*   

> You have to know what you're doing!

 As far as i know, I emerged mysql, and then did the ebuild mysql config thing.  Why is it I can't expect the given instructions to work at this stage? (the passwords settings)

----------

## fleed

Sure you can expect anything! (just kidding)

I think the instructions are meant as more of a reminder of what you should do. And you should know how to use the software before you're actually able to use it, the ebuild only gives you a few pointers.

----------

## trossachs

Just to butt in here rather than start another post, but when I try and connect to my database remotely using the GUI mysqladmin, I get this error:

```

Could not connect to host 'box.foo.co.uk'.

MySQL Error Nr. 2003

Can't connect to MySQL server on 'box.foo.co.uk' (111)

Click the 'Ping' button to see if there is a networking problem.

```

I can connect to the mysql box locally via ssh so there is no networking problem. And I can ping, connect and whatever else without probs. Mysql is the latest project I am working here so cut me some slack. What should I be looking at?

----------

## Ian Tindale

I get exactly the same problem. Follow the instructions on the ebuild to the letter, it doesn't work. Give up - waste of time.

----------

## trossachs

Does nobody esle have an angle on this?

----------

## kashani

What does netstat -ptln say?

kashani

----------

## trossachs

My box says this:

```

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name

tcp        0      0 0.0.0.0:10082           0.0.0.0:*               LISTEN      2170/xinetd

tcp        0      0 0.0.0.0:10083           0.0.0.0:*               LISTEN      2170/xinetd

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1241/mysqld

tcp        0      0 0.0.0.0:139             0.0.0.0:*               LISTEN      2024/smbd

tcp        0      0 0.0.0.0:143             0.0.0.0:*               LISTEN      12368/couriertcpd

tcp        0      0 127.0.0.1:783           0.0.0.0:*               LISTEN      15419/spamd.pid -a

tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      1747/portmap

tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      11535/apache2

tcp        0      0 0.0.0.0:8080            0.0.0.0:*               LISTEN      2259/python2.1

tcp        0      0 0.0.0.0:21              0.0.0.0:*               LISTEN      8706/ncftpd

tcp        0      0 0.0.0.0:8021            0.0.0.0:*               LISTEN      2259/python2.1

tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1300/sshd

tcp        0      0 0.0.0.0:25              0.0.0.0:*               LISTEN      1973/master

tcp        0      0 0.0.0.0:445             0.0.0.0:*               LISTEN      2024/smbd

tcp        0      0 0.0.0.0:32765           0.0.0.0:*               LISTEN      1759/rpc.statd

tcp        0      0 0.0.0.0:32767           0.0.0.0:*               LISTEN      1780/rpc.mountd

```

----------

## fleed

```

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1241/mysqld

```

shows that mysqld is only listening on the local interface - you won't be able to connect to it remotely. Comment out the bind/listen line in /etc/my.cnf and restart the daemon.

----------

## trossachs

Sorted, it works. thanks

----------

