mysql root password rest

Question

For some reason, the script below doesn't reset the root password. The script works in that it performs the steps, but when I try to log back in to mysql as root with the new password, it doesn't recognize the change and insists I use the old one.

Here is the script below:

#!/bin/bash

echo "Resetting root password"
sudo /etc/init.d/mysql stop
sleep 2


if [ -f /root/mysql.reset.sql ]; then
    rm -f /root/mysql.reset.sql
    touch /root/mysql.reset.sql
else
    touch /root/mysql.reset.sql
fi

echo "UPDATE mysql.user SET Password=PASSWORD('akimbo') WHERE User='root'; FLUSH        
PRIVILEGES;" >> /root/mysql.reset.sql
mysqld_safe --init-file=/root/mysql.reset.sql &
/etc/init.d/mysql start
sleep 2

echo "Done setting mysql password for user root. Password is password."

So when I try mysql -uroot -pakimbo it complains and only works when i use the old password.

Any ideas?

cheers

Answer 1

See http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

There's a --skip-grant-tables option you can run on startup. Then connect as root, reset your password and then restart w/o the skip grant option

Answer 2

Why do you want to do it from SQL use mysqladmin as in the manual

shell> mysqladmin -u root password "newpwd"
shell> mysqladmin -u root -h host_name password "newpwd"

Answer 3

Why not just use the mysqladmin command?

mysqladmin -u root -p'oldpassword' password newpass

This can also be used for changing other user's passwords as well:

mysqladmin -u sql_username -p oldpassword password newpass