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

score 3 · Answer 1 · answered Jan 12 '12 at 01:32

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

answered Jan 12 '12 at 01:32

Zack Zatkin-Gold

814
9
29

Well when I do a fresh install it seems to remember the user credentials from old which is strange. but I dont know what the old password is upon a fresh install so I have to use a method where I dont need to specify the old password. – user983022 Jan 12 '12 at 07:56
Stop the mysqld server, add `skip-grant-tables=1` to `my.cnf` under `[mysqld]`, start the mysqld server, connect using `mysql -u root` and do `use mysql;` then do `UPDATE user SET Password = PASSWORD('new_pwd') WHERE User = 'root';`, clean up my.cnf and restart the mysqld server, test with `mysql -u root -p` by entering your new password at the prompt. – Zack Zatkin-Gold Jan 12 '12 at 23:46

score 0 · Answer 2 · answered Jan 12 '12 at 01:35

0

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"

answered Jan 12 '12 at 01:35

Adrian Cornish

23,227
13
61
77

score 0 · Answer 3 · answered Jan 12 '12 at 01:35

0

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

answered Jan 12 '12 at 01:35

atxdba

5,158
5
24
30

mysql root password rest

3 Answers3

Linked