Jimbo at 17:55, 14 May 2009

2009-05-14T17:55:40Z

New page

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.

% '''su'''
server# '''/usr/local/etc/rc.d/mysql-server stop'''
server# '''which mysqld'''
/usr/local/bin/mysqld
server# '''/usr/local/bin/mysqld --skip-grant-tables &'''
server# '''mysql -u root'''
mysql> '''UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';'''
mysql> '''FLUSH PRIVILEGES;'''
mysql> '''QUIT'''
Bye
server# '''killall mysqld'''
server# '''/usr/local/etc/rc.d/mysql-server 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:

% '''su'''
server# '''sudo /etc/init.d/mysql stop'''
server# '''which mysqld'''
/usr/sbin/mysqld
server# '''/usr/sbin/mysqld --init-file=~/passwordreset.sql &'''
server# '''sudo killall mysqld'''
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.)

[[Category:Common Tasks]]

MySQL, resetting root password - Revision history

Jimbo at 17:55, 14 May 2009