cannot change password and cannot create database in Mysql

[Kenneth_61]

Hi All, I am running Mysql 5.0.4 beta in HP-UX 11.0.

1) In Manual, it tell us to run the following :
===========================================
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h server.mydomain.com password 'new-password'
=========================================

However, I cannot change password and cannot create database in Mysql. Error comes out when I run as below: e.g. I hope to change to password '123'. It seems no need to use password when I run #mysql -u mysql. It load automatically with no password. I hope to add password. I cannot create database. Why ?

# pwd
/usr/local/mysql
# ./bin/mysqladmin -u root password '123'
./bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
# ./bin/mysqladmin -u root -h server.mydomain.com password '123'
./bin/mysqladmin: connect to server at 'server.mydomain.com' failed
error: 'Access denied for user 'root'@'server.mydomain.com' (using password: NO)'
# ./bin/mysqladmin -u mysql password '123'
./bin/mysqladmin: unable to change password; error: 'Access denied for user ''@'localhost' to database 'mysql''
# mysql -u mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47 to server version: 5.0.4-beta-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>create database testdatabase
-> ;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'testdatabase'
mysql> create database testdatabase;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'testdatabase'
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)

mysql>

[Ermin Borovac · Accepted solution]

You will need to reset the password.

First start mysql with --skip-grant-tables option.

Then run

# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('your_new_password') where USER='root';
mysql> FLUSH PRIVILEGES;

Finally, restart mysql without --skip-grant-tables option.

You should be able to connect with your new password.

# mysql -u root -p
Enter password: your_new_password

[Kenneth_61]

It does not work. no need to ask password. Now, I am very confuse. Should I use root or mysql to login to MySQL server ? :-( Should I use 'mysql -u root' or 'mysql -u mysql'.

# ./mysql.server stop
Shutting down MySQL
SUCCESS!
#cd /usr/local/mysql
# bin/mysqld_safe --user=mysql --skip-grant-tables &
[1] 6915
# Starting mysqld daemon with databases from /usr/local/mysql/data

#ps -e | grep mysql
6915 pts/ta 0:00 mysqld_safe
6931 pts/ta 0:01 mysqld
#mysql -u root mysql
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.
Your MySQL connection id is 1 to server version: 5.0.4-beta-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> UPDATE user SET Password='123' where USER='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

# cd support-files
# ./mysql.server start
Starting MySQL
SUCCESS!
# mysql -u root -p
Enter password: ***
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.4-beta-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye
# mysql -u mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.4-beta-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

[Ermin Borovac]

UPDATE user SET Password='123' where USER='root';

Please note the SQL command above should be changed to

UPDATE user SET Password=PASSWORD('123') where USER='root';

This will use encryption function PASSWORD to make sure encrypted password string is stored in the table 'user'.

[Kenneth_61]

WAH !!!! it works . I can change password and I can create database !!! Very happy , many thanks. Many thanks. Give you full mark. U know all the things.

[poops]

thanx man ...
I search it on c-panel forum but still it is use less
but u are great
i don't have any word to explain ur greatness
but one thing which i never forget that
U r really gr8!!!!