Mysql Root Login
 

I just installed mysql with the rpm file and I can't seem to login as the root mysql user. Do I need to be logged in as the root computer user to login as the root mysql user or anything like that? I''ve tried uninstalling it and reinstalling it with no luck at all so any suggestions would be appreciated.

Could you post how you are trying to log in and what the error messages are? You do NOT need to be logged into your machine as root as the MySQL root and the system root are two different things. Also, my understanding of rpm installation is that the root user does NOT have a password to begin with, so if you are trying to use one that may cause an error.

Basically you should be able to log in with this:

mysql -u root

I have tried logging in with that and many other methods all come back with this error pretty much

ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Ok you can try:

Admin user id: root
Default password: blank

The first task is to assign a password:

[prompt]$ mysqladmin -u root password 'new-password'

Note: the following SQL commands will also work:

mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD('new-password') WHERE user='root';
mysql> FLUSH PRIVILEGES;


and then

mysql -h localhost -u root -ppassword

Hope Works..

___________________
r0oT
2004

I tried doing that through mysqladmin and got the following error message
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: 'root@localhost' (Using password: NO)'

Than I try to use the mysql database but I get this since im not logged in as root mysql user
ERROR 1044: Access denied for user: '@localhost' to database 'mysql'

I've tried flushing privileges but I get this error
ERROR 1227: Access denied. You need the RELOAD privilege for this operation

Ok.. You can the do the procedure for resetting the root password is as follows:

1. Log on to your system as either the Unix root user or as the same user that the mysqld server runs as.
2. Locate the `.pid' file that contains the server's process ID. The exact location and name of this file depend on your distribution, hostname, and configuration. Common locations are: `/var/lib/mysql/', `/var/run/mysqld/', and `/usr/local/mysql/data/'. Generally, the filename has the extension of `.pid' and begins with either `mysqld' or your system's hostname. Now you can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the name of the `.pid' file in the following command:

shell> kill `cat /mysql-data-directory/host_name.pid`

Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.
3. Restart the MySQL server with the special --skip-grant-tables option:

shell> mysqld_safe --skip-grant-tables &

4. Set a new password for the root@localhost MySQL account:

shell> mysqladmin -u root flush-privileges password "newpwd"

Replace ``newpwd'' with the actual root password that you want to use.
5. You should now be able to connect using the new password.

hope works..

I did the above and it didn't seem to work. So I tried some other things mainly uninstalling the rpm and trying to get the binary to work but I must of messed something up :newbie:. Now my mysql.sock seems to have gone missing and im wondering as how to replace it? Also I have reinstalled the rpm and I would of thought that the mysql.sock file would been remade if it was deleted but it apparently it wasn't. Any help would greatly be appreciated.

Usually what Gerardoj and Hangdog42 say works, but... do you have RH9? I had the same problem with it. No problems with 7.3 or Debian.

I first tried to log in as admin, succeeded but had no access to the databases.

So well I did what sould never be done:


And then I could log in with the password from my other box!

I think you can use instead of rh73box a Knoppix CD (there should be a mysql server in there, and so you can get the mysql directory and log in as root with no pw, as said before).

Well I figured out what was wrong and fixed it. Apparently when I was uninstalling the rpm it wasn't deleting all the files. So I did a locate mysql and deleted all the files be hand and installed it from the binary version and it worked perfectly.

In my experience, I've always had to use (minimally) 'mysql -u root -p' and then it prompts for a password. The line "error: 'Access denied for user: 'root@localhost' (Using password: NO)'" usually indicates that you're not supplying a password (even a blank password must be "supplied").

In my experience anyway :(.

MySQL question
 

nt

I had similar problems and just thought I could add my 2¢ worth.
Originally I had changed my host settings in
/usr/local/phpMyAdmin-2.6.0-rc1/config.inc.php
which I later discovered that I should keep the setting localhost instead of my hostname frodo.
But I did! have to change the authentication type in that file to http!

< $cfg['Servers'][$i]['auth_type'] = 'http'; // Authentication method (config, http or cookie based)?
---
> $cfg['Servers'][$i]['auth_type'] = 'config'; // Authentication method (config, http or cookie based)?

Still learning this PHP and MySql, just got phpMyAdmin up and running, but I haven't seen this solution posted here and it did help me!

Cheers,
alar

BTW: Just browsing the manual and found this:

5.5.8 Causes of Access denied Errors
http://dev.mysql.com/doc/mysql/en/Access_denied.html