Re: [tlug] Suddenly can't access MySQL [SOLVED]

[Dave M G <martin@example.com>]

TLUG,

Here's another problem recently solved. Not by updating to Edgy, though. 
Just by finally finding the right info on the web.

This is how I was able to reset my root password for MySQL and regain 
full control over it again. Hope this info is useful to anyone facing 
similar issues.

To Reset root password (Works on Ubuntu Edgy, 6.10, and MySQL 5.0):

1. Open a command line and stop the MySQL Server

$ sudo killall mysqld

2. Start it again, and Login, with the following parameters:

$ sudo mysqld --skip-grant-tables --user=root

3. Update the password for the root account:

mysql> USE mysql UPDATE user SET password=password("newpassword") WHERE 
user="root";

4. Hopefully you should get the following output:

Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

5. Flush privileges:

mysql> FLUSH PRIVILEGES;

6. Output should be:

Query OK, 0 rows affected (0.01 sec)

7. Exit MySQL

mysql> exit;

8. Stop the running server, because it's currently running with the 
"skip-grant-tables" parameter, and we don't want that:

$ sudo killall mysqld

9. Restart MySQL as normal:

sudo /etc/init.d/mysql start

10. Test that it's working by logging in as root:

mysql -u root -p

11. Hopefully you're in!

ï--
Dave M G
Ubuntu 6.10 Edgy Eft
Kernel 2.6.17.7
Pentium D Dual Core Processor
PHP 5, MySQL 5, Apache 2