How to reset mysql root password

Ever forgotten your mysql root password? Indeed. Unless you know how to reset the mysql root password, you may find yourself either stuck, or re-installing mysql (as i did a few times many moons ago before i knew any better!)

So lets reset mysql root password (seo-tastic I know)

First thing to do is to stop the mysql server, of course though, you need to actually be root on your server to do this, but you know that.

[code]/etc/init.d/mysql stop[/code]

Next thing to do, is start mysql back up again, but without any permissions (–skip-grant-tables literally doesnt bother looking at the grant tables where the permissions are stored)

[code]mysqld_safe –skip-grant-tables[/code]

That will start mysql again, only this time you will be able to log in, without providing a password, as root…

[code]mysql> mysql -u root[/code]

…and we need to use the database called mysql

[code]mysql> use mysql;[/code]

Great stuff. Now you can reset the root user password:

[code language=”sql”]mysql> UPDATE user SET Password = PASSWORD(‘y0uR_n3W_PassWord’) WHERE user = ‘root’;[/code]

We will need to re-read the privileges table too:

[code language=”sql”]mysql> flush privileges;[/code]

Nearly done, you should now exit mysql, stop the server (we are running with --skip-grant-tables remember?) and start a fresh mysql:

[code language=”sql”]mysql> exit[/code]

[code]/etc/init.d/mysql stop
/etc/init.d/mysql start[/code]

Mysql is now back up and running but you now know your root password, lovely stuff. Log in as root and do what you need to do…

Leave a Reply

Your email address will not be published. Required fields are marked *