unable to connect to mysql

[pc]

hi everyone,

I am trying to connect to a new mysql installation. I have given root
permission to connect to all databases from anywhere;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'passwd' WITH
GRANT OPTION;

I can log on locally by specifying the host name:

[me@au101 me]$ mysql -h 127.0.0.1 -u root -p test
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.18-standard

However, when I try to log in locally without specifying the host name
I receive the following error:

[me@au101 me]$ mysql -u root -p mysql
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2)

If I try to connect remotely as root using MySQLyog I receive a similar

error message:

Access denied for user root@[my ip]

Any assistance would be appreciated.

cheers,

eddiec :-)

[Bill Karwin]

"pc" <ch***@netspace.net.au> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I am trying to connect to a new mysql installation. I have given root
permission to connect to all databases from anywhere;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'passwd' .... However, when I try to log in locally without specifying the host name
I receive the following error:

Make sure there is not already an entry for 'root' @ 'localhost' in the
`mysql`.`user` table.
Since 'localhost' is more specific than the wildcard entry '%', MySQL's
privilege system will use it instead of the '%' entry you created.

If an entry exists, it likely does not have the same password as the entry
you just created. It may have an empty field for the password, which means
the connection works only when you specify no password.

See also the following pages for more information:
http://dev.mysql.com/doc/refman/5.0/...on-access.html
http://dev.mysql.com/doc/refman/5.0/...ss-denied.html

Regards,
Bill K.

[Robert Billing]

Among the wreckage we found a fragment on which pc had scratched:

I can log on locally by specifying the host name:

[me@au101 me]$ mysql -h 127.0.0.1 -u root -p test Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL
connection id is 2 to server version: 5.0.18-standard

However, when I try to log in locally without specifying the host name I
receive the following error:

[me@au101 me]$ mysql -u root -p mysql Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2)

In the first case you are connecting through a TCP/IP socket, in the
second you are trying to connect through a unix domain socket. Check that
/var/lib/mysql/mysql.sock actually exists, the permissions allow you to
access it, and that the server is listening to it by doing:

mysqladmin version

[pc]

hi Bill,

I had:

mysql> select host, user from user;
+--------------+------+
| host | user |
+--------------+------+
| % | % |
| % | root |
| 192.168.1.10 | root |
| fc4 | |
| fc4 | root |
| localhost | |
| localhost | root |
+--------------+------+

in my user table. I deleted everything except for one line:

mysql> select host, user from user;
+------+------+
| host | user |
+------+------+
| % | % |
| % | root |
+------+------+
2 rows in set (0.00 sec)

and can now log on through mysqlyog from my pc!

thanks a lot

eddiec :-)