MYSQL root password forgotton

My old post seems to have been deleted... dunno why..


Well I found the following on google telling you how to reset it
18.9 How to reset a forgotten password. If you have forgotten the root user password for MySQL, you can restore it with the following procedure. Take down the mysqld server by sending a kill (not kill -9) to the mysqld server. The pid is stored in a .pid file which is normally in the MySQL database directory: kill `cat /mysql-data-directory/hostname.pid` You must be either the UNIX root user or the same user the server runs as to do this. Restart mysqld with the --skip-grant-tables option. Connect to the mysqld server with mysql -h hostname mysql and change the password with a GRANT command. See section 7.26 GRANT and REVOKE syntax. You can also do this with mysqladmin -h hostname -u user password 'new password' Load the privilege tables with: mysqladmin -h hostname flush-privileges or with the SQL command FLUSH PRIVILEGES.

So... I took down the sqld

But then I couldn't find a way to restart it with --skip-grant-tables ..... I looked at the --help for mysql and mysqladmin ... nothing like that caught my eye


NOW...

Once I get all the above figured out thus far...

when I connect to the mysql with
"mysql -h hostname"

Since my box often says stuff like localhost.localdomain will I do a mysql -h localhost.localdomain
or do
mysql -h mydomain.org........?
(i'm guessing localhost.localdomain)

then that's when I type GRANT <my desired new pass> ?

thanks for clearing my confusion... I feel like a real idiot... and I'm embarassed...

I seem to be posting here alot... because it's the quickest way to get help and also the people here are the friendliest... hopefully when I become really slick with Linux... I can help peope in my position on these forums one day too...

linuxquestions has become a shrine for me...
Thank you!

Try this as root:

shell>service mysql stop
shell>safe_mysqld --skip-grant-tables &
shell>mysql -u root
mysql>use mysql;
mysql>UPDATE user SET Password=PASSWORD('foo!bar') WHERE User = 'root';
mysql>FLUSH PRIVILEGES;
mysql>exit;
shell>kill %1
shell>service mysql start

Don't copy the "shell>" and "mysql>" prompts, those are for reference only. Don't know why they make the instructions so complicated sometimes

-G

BTW, "-h localhost" should work just fine. I tried following their instructions after my post and mysqladmin refused to let me update the password, but mysql worked just fine. I suggest making sure that there be only one entry for root in the user table and that the Host be localhost, but that's just me.

-G

in reply to gab's post

mysql> UPDATE user SET Password=PASSWORD('damn*sql') WHERE User='root';
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> FLUSH PRIVILAGES;
ERROR 1064: You have an error in your SQL syntax near 'PRIVILAGES' at line 1

That's privilEges not privilAges

In any case, killing safe_mysqld with a simple kill %1 as mentioned earlier should flush tables for you so you don't really need to do it from within mysql.

-G

OK... i got it.... great... thanks