reset mysql root password on ubuntu

Question

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

Answer 1

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

• I used to recommend two restarts of mysql that required editing my.cnf
• I learned a better approach from @ShlomiNoach
• This answer improves on it more by not editing my.cnf at all

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

Answer 2

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;