error: 'Access denied for user 'root'@'localhost' (using password: NO)'

[amit saha]

the below command gives:

/$ netstat -an | grep -i mysql

unix 2 [ ACC ] STREAM LISTENING 44425 /var/lib/mysql/mysql.sock
____________________________________________

the below command gives:

/$ lsof -i4 -P | grep -i mysql

mysqld 10710 mysql 10u IPv4 44424 TCP *:3306 (LISTEN)
_________________________________________

the below command gives:

/$ ps -ef | grep mysql

root 8330 1 0 2008 ? 00:00:00 gedit file:///etc/httpd/conf.d/auth_mysql.conf
root 10650 1 0 01:43 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid
mysql 10710 10650 0 01:43 ? 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock
root 12524 12416 0 13:12 ? 00:00:00 grep mysql
__________________________________________________ ___

this command gives:

/$ netstat -nlp | grep -i mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 10710/mysqld
unix 2 [ ACC ] STREAM LISTENING 44425 10710/mysqld /var/lib/mysql/mysql.sock
_________________________________________________

My.cnf file has:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
__________________________________________________

I am using SQL Manager 2007 lite for Mysql to connect to mysql database remotely

when i try to connect through this software: using public IP it takes me to the firewall local IP where a permisson has already been given to the firewall to connect it to port 3306 with required user/password but it still says root@IP access denied. i am in a big soup...kindly help urgently anyone.

[Jesper de Jong]

MySQL by default does not allow remote connections. I don't know exactly how you have to enable remote connections; you can most likely find that in the MySQL manual.

If you're logging into MySQL locally from a terminal window, try this:

mysql -u root -p

It will then ask you for the MySQL administrator password.
[ January 01, 2009: Message edited by: Jesper Young ]

[Charles Lyons]

MySQL by default does not allow remote connections. I don't know exactly how you have to enable remote connections; you can most likely find that in the MySQL manual.

You need to add this line into your config file, under the [mysqld] section (near the port=3306 line):

bind-address=IP-ADDRESS

where IP-ADDRESS is what you want it to listen on. The older way involved the "skip-networking" line---comment it out if present. This is no longer used in recent versions; the default is to bind only to localhost.

Where the config file is may depend on your OS/distro; on recent Debian it's /etc/mysql/my.cnf

Finally, don't forget to grant the user permission to login from outside the database. Usually a user is configured like "user@localhost" or similar, so you need to create a new user configuration or use "user@%" for 'user' from all hosts.

[Charles Lyons]

On second thoughts, having re-read your post, it seems you are able to connect successfully, but can't login. Check that the logins are available with correct passwords (i.e. make sure you are able to login that user on localhost), and that you don't have any proxies like your firewall. If you do, the IP address seen by MySQL might not be that of your client, but it might be that of the firewall instead. Also try setting the user login to 'username'@'%' which means "from all hosts". See if that helps; if so, check your IP addresses and change the config. If not, post back.

[Tim Holloway]

Your message is coming directly from the MySQL server, so you can ignore Charles' suggestion on network configuration. You're already connecting.

However, MySQL is fairly paranoid about security.

It's not really recommended that you use the root account to talk to your DBMS (PostgreSQL completely forbids it). The reason for that is root already allows you to plunder the OS, but that's no excuse to allow it to plunder the database as well. MySQL, however, normally is set up to allow root to be the primary manager.

OK, so much for background. Unless someone has deleted the access rule for root access on the local machine, you need to provide a password when you run the mysql client, and according to the message, you did not do that.

If you don't know what the root password is and you can't find it out - or someone has deleted the root@localhost rule from the MySQL users table, you'll have to shut down the server, start it up with security disabled, and force a new, known password on root access from localhost. Then shut down the insecure server and restart it in its normal secure mode. There are some good web pages that describe how to do this if you search the web for MySQL lost password.

There are also some "gotcha"s for security when accessing from remote machines, but I'll leave that for another day.

[Jesper de Jong]

Tim Holloway wrote:It's not really recommended that you use the root account to talk to your DBMS (PostgreSQL completely forbids it). The reason for that is root already allows you to plunder the OS, but that's no excuse to allow it to plunder the database as well. MySQL, however, normally is set up to allow root to be the primary manager.

As far as I know, the root account in MySQL is not the same thing as the root account of your operating system. So logging into MySQL with MySQL's root user is not the same as logging in with the operating system's root user, and won't give you unlimited access to the operating system.

[Tim Holloway]

Jesper Young wrote:

Tim Holloway wrote:It's not really recommended that you use the root account to talk to your DBMS (PostgreSQL completely forbids it). The reason for that is root already allows you to plunder the OS, but that's no excuse to allow it to plunder the database as well. MySQL, however, normally is set up to allow root to be the primary manager.

As far as I know, the root account in MySQL is not the same thing as the root account of your operating system. So logging into MySQL with MySQL's root user is not the same as logging in with the operating system's root user, and won't give you unlimited access to the operating system.

You're quite correct. It's actually more like the other way around. The rationale goes like this:

1. When you know a userID, you know 50% of what you need to break in via the login system. Since "root" is a *n*x universal, that makes it easier. Of course, since Microsoft SQL Server has "sa" and PostgreSQL comes with "postgres", that's virtually no protection, but it does add to the number of user IDs that an intruder would have to know to plunder a machine.

2. The default database user ID for most DBMS command-line clients (including MySQL) is the user's login ID. That helps encourage people to login as root, which leaves them vulnerable outside the MySQL client. Plus making it possible to do potentially catastrophic things as a client, since the user will have root's filesystem access rights.

3. Using a different account name allows the system administrator and the database administrator to be 2 different people with 2 different sets of privileges and responsibilities.

I believe that PostgreSQL's user ID isn't actually even able to log in by default on a Red Hat install, just to make it that much harder.

None of the above are what you'd call major security benefits, but the idea was that every little bit helps.