MySQL, resetting root password

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.)