Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

cannot change password and cannot create database in Mysql

SOLVED
Go to solution
Kenneth_61
Frequent Advisor

cannot change password and cannot create database in Mysql

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>


5 REPLIES
Ermin Borovac
Honored Contributor
Solution

Re: cannot change password and cannot create database in Mysql

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
Frequent Advisor

Re: cannot change password and cannot create database in Mysql

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
Honored Contributor

Re: cannot change password and cannot create database in Mysql

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
Frequent Advisor

Re: cannot change password and cannot create database in Mysql

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
Occasional Visitor

Re: cannot change password and cannot create database in Mysql

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!!!!