Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In short: my goal is to reset mysql root password on ubuntu.

Background: I set up a ubuntu desktop and installed LAMP stack last week. I then went to do something else, and just got back to carry on today. Either I did not set mysql password, or I have forgotten what I typed in.

So I tried to reset it.

I stopped mysql by

sudo stop mysql

I tried to start mysql using command line options, but it seems start only takes service name(??)

So I ran:

sudo /etc/init.d/mysql start --skip-network  --skip-grant-tables

Went into mysql, fine. Then

mysql> UPDATE mysql.user SET Password=PASSWORD('newpasswd') WHERE User='root';
ERROR 1142 (42000): UPDATE command denied to user ''@'localhost' for table 'user'
mysql> show grants for ''@'localhost';
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)

If ''@'localhost' has USAGE on all-schemal.all-tables, I'd think I can just update the password like this... Where did I miss?

I used the instruction in mysql official website, the last part. I also tried the unix one but with no luck. And I was unsure when it says 'unix', does it mean all *nix system or just unix???

Anyway, any thoughts are welcome! many thx!!!

share|improve this question
add comment

migrated from stackoverflow.com Jun 16 '13 at 21:30

This question came from our site for professional and enthusiast programmers.

2 Answers

Here is a more secure way to reset password without skip-grant-tables

Suppose you want root to have myn3wp@ssw0rd as the password

Step 01 : Create a script to execute when mysqld first start up

Create a file called /var/lib/mysql/init-file.sql with these two lines

GRANT ALL PRIVILEGES ON *.* to root@localhost
IDENTIFIED BY 'myn3wp@ssw0rd' WITH GRANT OPTION;

Step 02 : Run these three(3) lines in the OS

chown mysql:mysql /var/lib/mysql/init-file.sql
service mysql restart --init-file=/var/lib/mysql/init-file.sql
rm -f /var/lib/mysql/init-file.sql

Step 03 : THERE IS NO STEP 03. YOU ARE DONE !!!

Give it a Try !!!

CAVEAT

UPDATE 2013-06-17 07:05 EDT

OK Since mysqld for Ubuntu does not like init-file on the command, you must edit the my.cnf. Please do these next two steps

STEP 01) Add these lines to my.cnf under the [mysqld] group header

[mysqld]
init-file=/var/lib/mysql/init-file.sql

STEP 02) Allow mysql to read files from /etc/mysql

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Add the line:

/etc/mysql/*.sql r,

STEP 03) Restart MySQL

This should work for you. Give it a Try !!!

share|improve this answer
    
Thx very much for your reply! I tried your suggestion, but "service mysql start --init-file=/var/lib/mysql/init-file.sql" gives an error, "start: invalid option: --init-file=./init-file.sql". Basically ubuntu does not like any options after start? –  user1866880 Jun 17 '13 at 10:55
    
I updated my answer on what to do next... –  RolandoMySQLDBA Jun 17 '13 at 11:05
    
I proposed an edit. the [mysqld] section is missing the init-file=, also need to include apparmor section I found here: serverfault.com/questions/330034/… –  mbdev Nov 19 '13 at 3:08
add comment

From the error given it seems that you are not logged in at all.

1)Stop the mySql server:

sudo /etc/init.d/mysql stop

2)Start the mysqld configuration.

sudo mysqld --skip-grant-tables &

3)Login to MySQL as root.

mysql -u root mysql

4)Replace YOURNEWPASSWORD with your new password!

UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.