MySql 'not allowed to connect to host...']

[ Firstly, if you’re getting the list in digest form, please make sure
that you change the subject line to be relevant, and not that of the
digest number. This helps people following archives and doing
subject-based sorting ]

Sorry - I normally do but I was in a hurry and forgot to change the
subject back. :-[> On 18 Nov 2002, Fred.Purdue wrote:

From: darren chamberlain

shell> mysql -h helpdesk.corp.newoppinc.org -u root
ERROR 1130: Host ‘helpdesk.corp.newoppinc.org’ is not allowed to
connect to this MySQL server

No password?

If it was a password error I would be either be getting asked for a
password or I would get a bad password response.

Depending on which varient of the mysql client you have, you should get
either ‘blah blah is not allowed’ or ‘Access is denied for blah (using
password: NO)’.

Try snarfing the rt mysql username and password out of the config.pm file
and specifying them directly, eg:

$ mysql -u rt_user -p rt_pass -h db.host rt2

( ‘rt_pass’ can be absent, and MySQL will prompt you )

The MySQL access controls also limit by database requested, although if
you are connecting as the mysql root user (and failing), then you probably
have more serious problems.

You could also try connecting to ‘localhost’ instead of ‘helpdesk.blah’.

I not only flushed the privileges but I also restarted mysql.

Then you have mysql root (admin) access.

    [root@helpdesk fpurdue]# mysql -u rt_user -h helpdesk rt2
    -ppassword
    
    ERROR 1130: Host 'helpdesk.corp.newoppinc.org' is not allowed to
    connect to this MySQL server
    
    [root@helpdesk fpurdue]# mysql -u rt_user -h localhost rt2
    -ppassword
    
    Reading table information for completion of table and column
    names
    You can turn off this feature to get a quicker startup with -A
    
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    [...etc]

The problem isn’t being able to connect to mysql from the localhost, the
problem is connecting over the network…

Fred.Purdue Fred.Purdue@Pobox.com

Consultants Calling: Remember - If you’re not part of the solution,
there’s money to made in prolonging the problem

mysql.com/doc/en has pretty thorough docu for most problems. Just search
on what you think, like ‘user privileges’. You will be amazed!!
http://www.mysql.com/doc/en/Adding_users.html

You can add new users by issuing |GRANT| statements:

shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON . TO monty@localhost
-> IDENTIFIED BY ‘some_pass’ WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON . TO monty@"%"
-> IDENTIFIED BY ‘some_pass’ WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON . TO admin@localhost;
mysql> GRANT USAGE ON . TO dummy@localhost;

These |GRANT| statements set up three new users:

|monty|
A full superuser who can connect to the server from anywhere, but
who must use a password |‘some_pass’| to do so. Note that we must
issue|GRANT| statements for both |monty@localhost| and|monty@"%"|.
If we don’t add the entry with |localhost|, the anonymous user entry
for |localhost| that is created by|mysql_install_db| will take
precedence when we connect from the local host, because it has a
more specific |Host| field value and thus comes earlier in the
|user| table sort order. |admin|
A user who can connect from |localhost| without a password and who
is granted the |RELOAD| and |PROCESS| administrative privileges.
This allows the user to execute the |mysqladmin reload|, |mysqladmin
refresh|, and |mysqladmin flush-*| commands, as well as|mysqladmin
processlist| . No database-related privileges are granted. (They can
be granted later by issuing additional |GRANT| statements.) |dummy|
A user who can connect without a password, but only from the local
host. The global privileges are all set to |‘N’|�the |USAGE|
privilege type allows you to create a user with no privileges. It is
assumed that you will grant database-specific privileges later.

You can also add the same user access information directly by issuing
|INSERT| statements and then telling the server to reload the grant tables:

Actually Ron you’re pretty close. I did set it up to allow other hosts
to connect to it, but I didn’t notice that you need to setup different
USERS to be allowed to connect from different hosts.

Once I fixed that it worked like a breeze.

FredOn Wed, 2002-11-20 at 13:16, Ron Alexander wrote:

mysql.com/doc/en has pretty thorough docu for most problems. Just search
on what you think, like ‘user privileges’. You will be amazed!!

http://www.mysql.com/doc/en/Adding_users.html

You can add new users by issuing |GRANT| statements:

shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON . TO monty@localhost
-> IDENTIFIED BY ‘some_pass’ WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON . TO monty@"%"
-> IDENTIFIED BY ‘some_pass’ WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON . TO admin@localhost;
mysql> GRANT USAGE ON . TO dummy@localhost;

These |GRANT| statements set up three new users:

|monty|
A full superuser who can connect to the server from anywhere, but
who must use a password |‘some_pass’| to do so. Note that we must
issue|GRANT| statements for both |monty@localhost| and|monty@"%"|.
If we don’t add the entry with |localhost|, the anonymous user entry
for |localhost| that is created by|mysql_install_db| will take
precedence when we connect from the local host, because it has a
more specific |Host| field value and thus comes earlier in the
|user| table sort order. |admin|
A user who can connect from |localhost| without a password and who
is granted the |RELOAD| and |PROCESS| administrative privileges.
This allows the user to execute the |mysqladmin reload|, |mysqladmin
refresh|, and |mysqladmin flush-*| commands, as well as|mysqladmin
processlist| . No database-related privileges are granted. (They can
be granted later by issuing additional |GRANT| statements.) |dummy|
A user who can connect without a password, but only from the local
host. The global privileges are all set to |‘N’|—the |USAGE|
privilege type allows you to create a user with no privileges. It is
assumed that you will grant database-specific privileges later.

You can also add the same user access information directly by issuing
|INSERT| statements and then telling the server to reload the grant tables:


   [root@helpdesk fpurdue]# mysql -u rt_user -h helpdesk rt2
   -ppassword
   
   ERROR 1130: Host 'helpdesk.corp.newoppinc.org' is not allowed to
   connect to this MySQL server
   
   [root@helpdesk fpurdue]# mysql -u rt_user -h localhost rt2
   -ppassword
   
   Reading table information for completion of table and column
   names
   You can turn off this feature to get a quicker startup with -A
   
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   [...etc]

The problem isn’t being able to connect to mysql from the localhost, the
problem is connecting over the network…

– Fred.Purdue Fred.Purdue@Pobox.com

Fred.Purdue Fred.Purdue@Pobox.com