Hi everyone. After a clean 8.1 install I do: mysql_install_db (no errors) then /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)' I've deleted /var/lib/mysql/mysql contents and reinstalled the rpm. The mysql manual is OK but I'm not sure how I can create the root account properly. I know it's been up before but finding it is really hard! Thanks, Steve.
steve wrote:
Hi everyone. After a clean 8.1 install I do: mysql_install_db (no errors) then /usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)'
mysqladmin -u root -password='new-password' or mysqladmin -u root -p'new password' (NB, no space between p and password) hth sjb
On Thursday 07 November 2002 08:55, sjb wrote:
steve wrote:
Hi everyone. After a clean 8.1 install I do: mysql_install_db (no errors) then /usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)'
mysqladmin -u root -password='new-password'
or
mysqladmin -u root -p'new password'
(NB, no space between p and password)
Hi. Both ideas give a list of commands so I think the syntax must be wrong. Any ideas what it should be? Do I literally type 'new-password' or 'new password'? anything else gives unknown command. Thanks, Steve.
steve wrote:
Both ideas give a list of commands so I think the syntax must be wrong. Any ideas what it should be? Do I literally type 'new-password' or 'new password'? anything else gives unknown command.
Are you literally enclosing the password with single quotes? (You don't need to, BTW) sjb
On Thursday 07 November 2002 10:48, you wrote:
steve wrote:
Both ideas give a list of commands so I think the syntax must be wrong. Any ideas what it should be? Do I literally type 'new-password' or 'new password'? anything else gives unknown command.
Are you literally enclosing the password with single quotes? (You don't need to, BTW)
sjb
I've tried all combinations, even writing the password on the command line. All I get is access denied to root@localhost. This applies even after a reinstall of the mysql package. What a pain. Any ideas?
On Thursday 07 November 2002 11:00, you wrote:
steve wrote:
reinstall of the mysql package. What a pain. Any ideas?
The default is no password (or, at least it used to be) .. have you tried
mysqladmin -u root
sjb
It gives a list of options and commands only.
steve wrote:
It gives a list of options and commands only.
Are you actually giving mysqladmin a command? mysqladmin -u root -pPASS status If you're trying to get a command line interface to the database mysql -u root -pPASS Is mysqld running? mysqld -u root -pPASS & sjb
On Thursday 07 November 2002 11:12, you wrote:
steve wrote:
It gives a list of options and commands only.
Are you actually giving mysqladmin a command?
mysqladmin -u root -pPASS status
If you're trying to get a command line interface to the database
mysql -u root -pPASS
Is mysqld running?
mysqld -u root -pPASS &
sjb
Yes, I started mysql with rcmysql start Is that the same as the mysqld? Here is an example: # mysql -u root -pPASS ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES) and another: # mysqladmin -u root -pPASS status mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: YES)' Does this look good?
On Thursday 07 November 2002 11:41, you wrote:
steve wrote:
Does this look good?
No ;-)
Have you set a password for mysql at all? If you haven't and you try to use one, you'll see that message.
Try it without the password.
mysqladmin -u root status
sjb
# mysqladmin -u root status mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)' As a user I can just type mysql and make tables in the test database no problem: ~> mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 to server version: 3.23.52-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.03 sec) mysql> It's just root who can't login. I know it's working because of this. I just want to be able to get it right before I let my students loose on the 'net with php. Thanks for your patience. Steve.
steve wrote:
As a user I can just type mysql and make tables in the test database no problem: ~> mysql
So what happens if you try
mysqladmin status
sjb as a user: ~> mysqladmin status Uptime: 4925 Threads: 1 Questions: 7 Slow queries: 0 Opens: 7 Flush
On Thursday 07 November 2002 11:59, sjb wrote: tables: 1 Open tables: 1 Queries per second avg: 0.001 as root: # mysqladmin status mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)'
On Thursday 07 November 2002 12:17, you wrote:
steve wrote:
as root: # mysqladmin status mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)'
What do you see in ..
more /etc/mysqlaccess.conf
sjb
# more /etc/mysqlaccess.conf # ------------------------------------------------------------------------- # # MySQLaccess version 2.0p2 # # (c) Yves.Carlier@rug.ac.be, 1997 # # # # *** Configuration file *** # # # # -Default values read by mysqlaccess during initialisation. # # This file is looked for in # # 1) the current directory # # 2) /etc/ # # -Options given on the command-line override the values given in here # # -Given options can't be overruled by empty/blanc options!! # # ------------------------------------------------------------------------- # # ----------------# # Global settings # # --------------- # #$Param{'host'} = ''; $Param{'user'} = 'nobody'; $Param{'db'} = 'test'; $Param{'password'} = 'foobar'; $Param{'debug'} = 0; # --------------------------# # Settings for Command-line # # ------------------------- # if ($CMD) { $Param{'superuser'} = 'root'; $Param{'rhost'} = 'localhost'; $Param{'spassword'} = ''; $Param{'brief'} = 1; } # ---------------------# # Settings for CGI-BIN # # -------------------- # if ($CGI) { $Param{'superuser'} = 'root'; $Param{'rhost'} = 'localhost'; $Param{'spassword'} = ''; $Param{'table'} = 1; } 1; #to make require happy
On Thursday 07 November 2002 12:47, you wrote:
steve wrote:
# more /etc/mysqlaccess.conf
Which is exactly the same as mine .. hmm
OK, try
rcmysql stop mysqld -u root & mysqladmin status
.. maybe it's something in the init script that's screwing things.
sjb
# rcmysql stop Shutting down service mysql # mysqld -u root & [1] 4295 # 021107 14:51:27 mysqld: Table 'mysql.host' doesn't exist # mysqladmin status mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)' Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists! Thanks for your patience. Steve.
steve wrote:
# 021107 14:51:27 mysqld: Table 'mysql.host' doesn't exist
Ahh .. now we're getting somewhere. OK, can you start mysql again rcmysql start Then login as an ordinary user mysql Then 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> desc host; +-----------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-----------------+------+-----+---------+-------+ | Host | char(60) binary | | PRI | | | | Db | char(64) binary | | PRI | | | | Select_priv | enum('N','Y') | | | N | | | Insert_priv | enum('N','Y') | | | N | | | Update_priv | enum('N','Y') | | | N | | | Delete_priv | enum('N','Y') | | | N | | | Create_priv | enum('N','Y') | | | N | | | Drop_priv | enum('N','Y') | | | N | | | Grant_priv | enum('N','Y') | | | N | | | References_priv | enum('N','Y') | | | N | | | Index_priv | enum('N','Y') | | | N | | | Alter_priv | enum('N','Y') | | | N | | +-----------------+-----------------+------+-----+---------+-------+ 12 rows in set (0.01 sec) And if you don't see a table def like that above (which you probably won't) it means that the mysql_install_db probably failed somewhere along the line. As for fixing it .. err .. uninstall mysql, then re-install it and (as root) run the mysql_install_db script again. sjb
On Thursday 07 November 2002 14:09, you wrote:
steve wrote:
# 021107 14:51:27 mysqld: Table 'mysql.host' doesn't exist
Ahh .. now we're getting somewhere.
OK, can you start mysql again
rcmysql start
Then login as an ordinary user
mysql
Then
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> desc host; +-----------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | | | Db | char(64) binary | | PRI | | | | Select_priv | enum('N','Y') | | | N | | | Insert_priv | enum('N','Y') | | | N | | | Update_priv | enum('N','Y') | | | N | | | Delete_priv | enum('N','Y') | | | N | | | Create_priv | enum('N','Y') | | | N | | | Drop_priv | enum('N','Y') | | | N | | | Grant_priv | enum('N','Y') | | | N | | | References_priv | enum('N','Y') | | | N | | | Index_priv | enum('N','Y') | | | N | | | Alter_priv | enum('N','Y') | | | N | |
+-----------------+-----------------+------+-----+---------+-------+ 12 rows in set (0.01 sec)
And if you don't see a table def like that above (which you probably won't) it means that the mysql_install_db probably failed somewhere along the line.
As for fixing it .. err .. uninstall mysql, then re-install it and (as root) run the mysql_install_db script again.
Exactly the same! Let's give up no? If anyone thinks of anything, them please let me know. THanks. Steve.
steve wrote:
Exactly the same! Let's give up no? If anyone thinks of anything, them please let me know. THanks. Steve.
That's weird .. the mysql database is created by mysql_install_db along with all the tables. Sorry .. I don't know what's up with it! sjb
On Thursday 07 November 2002 8:55 am, sjb wrote:
/usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)'
My understanding of the problem (and please, someone, correct me if I'm wrong) is that MySQL views the local physical machine as two virtual machines - localhost, and hostname (as you can see if you look at the user table of the mysql database). The normal command that is recommended to set the password only changes the hostname record, but when you then go to log in, the localhost one is used by default. That doesn't have a password in the record, so it is rejected. But it won't accept a blank password either, since one has been set for that user on the hostname twin! So you're stuck. What you need to do is specify localhost as the login source, as follows: mysql -u root -h localhost -p then give your password. Hopefully that should get you in. (If it does not, your password is incorrect, and we will need to do another Cunning Trick to sort that out.) Once there, run: update user set password=password("mypassword") where user="root"; then: delete from user where user=""; (that's a double quote followed by another double quote). Then exit mysql, and run: mysqladmin -u root reload and give your password - this will reload the grant tables you just amended. Then run: mysql -uroot -p and give your root password - you should then be logged in as root. You have just set BOTH hostname and localhost to the SAME password, so you don't need to specify localhost from now on. You have also got rid of the blank test user, which is a security risk. This comes up so often I think it should be put on the SuSE support db somewhere. Something like: Immediately after install of MySQL, run: mysql and it will allow you to log in without a password. Now set the password for root by running: update etc, as above. This would save an awful lot of hair-pulling. HTH Kevin
On Thu, Nov 07, 2002 at 10:36:24AM +0000, steve wrote:
Hi everyone. After a clean 8.1 install I do: mysql_install_db (no errors)
Did you do mysql_install_db --user mysql ? Anyway, you shouldn't have done it in the first place since the init script /etc/init.d/mysql does that for you when you start it for the first time (rcmysql start). I don't know but there is a remote possibility that if you did things by hand you missed a step or did it in a different way than the system expects it. Starting mysql by the init script is all you need to do to get it running (and set the admin password as the init script tells you. BTW, a correct way to _initially_ set the password (i.e. while there is an empty password) is: /usr/bin/mysqladmin -u root password 'secret' After that, you need to use /usr/bin/mysqladmin -u root -p password 'anotherpassword' if you want to change it, and enter the current password on the tty. Everything else will give you the 'Access denied for user: 'root@localhost' message. Peter
On Thursday 07 November 2002 15:03, poeml@cmdline.net wrote:
On Thu, Nov 07, 2002 at 10:36:24AM +0000, steve wrote:
Hi everyone. After a clean 8.1 install I do: mysql_install_db (no errors)
Did you do mysql_install_db --user mysql ?
Anyway, you shouldn't have done it in the first place since the init script /etc/init.d/mysql does that for you when you start it for the first time (rcmysql start).
I don't know but there is a remote possibility that if you did things by hand you missed a step or did it in a different way than the system expects it. Starting mysql by the init script is all you need to do to get it running (and set the admin password as the init script tells you.
BTW, a correct way to _initially_ set the password (i.e. while there is an empty password) is:
/usr/bin/mysqladmin -u root password 'secret'
After that, you need to use
/usr/bin/mysqladmin -u root -p password 'anotherpassword'
if you want to change it, and enter the current password on the tty. Everything else will give you the 'Access denied for user: 'root@localhost' message.
Peter
Hi. Thanks. That did it. In other words following the (awful) mysql documentation and actually doing what it says messes you up. Had I not done the mysql_install_db first then it would have worked. I should have read the readme_suse doc instead. Anyway, I've now got root access and can create databases once more.
participants (4)
-
Kevin Donnelly
-
poeml@cmdline.net
-
sjb
-
steve