MySQL, resetting root password

From Ubuntuwiki.net

Jump to: navigation, search

The simplest method: stop the mysql service, temporarily restart it with the --skip-grant-tables option, log in with no password, reset it, then start it again normally.

user@server:~$ sudo /etc/init.d/mysql stop
user@server:~$ which mysqld
/usr/sbin/mysqld
user@server:~$ /usr/sbin/mysqld --skip-grant-tables &
user@server:~$ mysql -u root
mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> QUIT
Bye
user@server:~$ sudo killall mysqld
user@server:~$ sudo /etc/init.d/mysql start

The only problem with this is, you do temporarily have a window where someone else could log in with no password. If that bugs you, you can start mysql with an init-file option instead. First, create a file with the SQL command to update the root password:

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

Now, assuming you've created that file at ~/passwordreset.sql, you can do the following:

user@server: sudo /etc/init.d/mysql stop
user@server: which mysqld
/usr/sbin/mysqld
user@server: /usr/sbin/mysqld --init-file=~/passwordreset.sql &
user@server: sudo killall mysqld
user@server: sudo /etc/init.d/mysql start

And this will reset your root password WITHOUT creating a window in which a bad guy might log in as root without having the root password. (You will probably want to rm ~/passwordreset.sql after you're done, just to avoid giving anybody else any bad ideas.)

Personal tools