HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Forgot mysql owner/root username and password

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
ownerusernamepasswordmysqlrootandforgot

Problem

Recently, I changed my mysql root user to someother username (say kevin). But, now both the accounts don't login with the old password. It says access denied.

I'm also not sure if the username is that (kevin). How can I retrieve both the root username and password?

I also tried resetting my mysql root password but I cannot, it says Access denied. Even if I use the root username, it logs in but I cannot see any database like mysql (Clearly, I don't have any privilege). Just the two basic databases are accessible. I am using Ubuntu 13.10, if that helps.

PS - I cannot reinstall mysql as I have important data stored in the databases.

Solution

You have a variety of options to reset the password http://code.openark.org/blog/mysql/recovering-a-mysql-root-password-the-fourth-solution

UPD:
Following the Max's advice let me elaborate. The easiest way to reset any password is to restart MySQL with skip-grant-tables in my.cnf. Then MySQL won't ask the password. To set new password execute this:

mysql -e "UPDATE mysql.user SET password = PASSWORD('newpassword') WHERE user = 'root' and host = ''"


where is host where user connects form.

Then restart MySQL again, but without skip-grant-tables option.

Code Snippets

mysql -e "UPDATE mysql.user SET password = PASSWORD('newpassword') WHERE user = 'root' and host = '<host>'"

Context

StackExchange Database Administrators Q#59114, answer score: 4

Revisions (0)

No revisions yet.