MySQL root password recovery script

[Wilhelm]

Hi i made a booboo a while back and figured out how to reset the root password on my mysql database. This is all common knowledge but even so i built a script so i can recover my mysql nice easy and quick.

Code: Select all

#!/bin/bash
#
# resetRootPass script
#
# Recover lost root password of mysql database.
#
# By Willem Bermon
#

echo
echo "Mysql password recovery utility"
echo

# Stop the mysql server
/etc/init.d/mysql stop
/etc/init.d/mysql zap > /dev/null
/bin/killall mysqld > /dev/null

# Run mysqld in permissionless mode
/sbin/start-stop-daemon --start --quiet --exec /usr/bin/mysqld_safe \
        --background -- --skip-grant-tables >/dev/null 2>&1

sleep 1

# Execute queries
mysql -u root mysql -e "UPDATE user SET Password=PASSWORD('$1') WHERE \
                        user='root'; \
                        FLUSH PRIVILEGES;"
if [[ $? -eq 0 ]]
then
        echo " ** SQL root password updated"
else
        echo " ** SQL root password update unsuccesful"
fi

# Restart the mysql server
/bin/killall mysqld > /dev/null
/etc/init.d/mysql start

echo "Succesfully updated password!!"
echo
echo
exit 0

As you can see i did a little overkill on stopping mysql. It was done for testing however i never bothered taking it out.

Hopefully some people will find this a useful script for in your root user home directory.

Have fun

[froke]

And then your mysql root password is stored in plain text in your .bash_history

[kronon]

If you are THAT paranoid then you could delete the bash history.

[Wilhelm]

I'm no linux guru but your bash history is in your root directory. If they have access to your history they can already trash your DB anyway since they probably have root access.

This is a recovery script. You can always go in manually and change it so it doesn't show up in your history.

[froke]

Yes, i understand that the bash history can be edited and is stored in /~

I was just pointing out that the password would be saved in clear text, which is never a good idea for any password. The script should remove it from bash history or use another method that doesn't echo the input password in plaintext.

[Wilhelm]

Yes, i understand that the bash history can be edited and is stored in /~

I was just pointing out that the password would be saved in clear text, which is never a good idea for any password. The script should remove it from bash history or use another method that doesn't echo the input password in plaintext.

Your right but this was like my second bash script ever and my first year of hardcore linuxing. I simply wasn't that paranoid when writing the script.

I take it, it would simply be a 'sed -e "s/<password>/xxxxxxxx/g' of the bash history to fix this problem. However the sed would come up in your history so you will need to counter that as well by copying bash beforehand and sedding the copy and making it your curretn history.
Would you be kind enough to show how it is done by you? I think it's pretty interresting tool I might be able to use in the future.

[froke]

I'm sorry, I don't know how to make the script remove the password. I had to reset a mysql root password a while back and there were instructions on how to do so in the mysql documentation. Of course, there are many ways to do this. There was a procedure that had a mysql prompt ask you for a password, so your password wouldn't be stored in plaintext. I don't remember the specifics, but it should be in the mysql documention at www.mysql.com

[verbatim]

Just a nitpick but password recovery and resetting a password are entirely different things. Don't confuse people by making them think this does something other than what it actually does.

Other than that, good job, thanks for the script.

[linuxkrn]

Few tips,

As noted, don't require the password to be put on the command line.

This can be done with a simple "bash read" command to read it from stdin instead of an arg from command line.

Code: Select all

#!/bin/bash

read -p "New password: " PASSWORD
echo "The password you typed was: ${PASSWORD}"

Second, if you don't want bash to "save" your current history (~/.bash_history) you can always term your bash PID.

Easy way is

Code: Select all

kill -9 $$

This will keep bash from saving the file on exit.

And last, if you enter a blank password, that should "remove" the password from the login.

[sburnett]

Thanks for this tip! I used it just now and it works fine. My solution to the password problem was to read the password into a file using backticks directly in the command, like this:

Code: Select all

mysql -u root mysql -e "UPDATE user SET Password=PASSWORD('`cat /tmp/pass`') WHERE user='root'; FLUSH PRIVILEGES;"

But I think that Wilhelm's solution is better for using it as a full script.

[tilos]

I'd just fought with MySQL for an hour trying to figure out what the root password was when I found your script. THANK YOU

[beandog]

If youre really screwed, you could just copy all the data from /var/lib/mysql to a fresh mysql install. Works between windows <--> linux too.

edit: more specifically

Code: Select all

$ rm -fr /var/lib/mysql/mysql
$ ebuild /var/db/pkg/dev-db/mysql-4.0.20/mysql-4.0.20.ebuild config

and you're done.

[garris0n]

And then your mysql root password is stored in plain text in your .bash_history

export HISTFILE=/dev/null will prevent your current session's history from being saved.

[beatryder]

This is pure gold!

[umrguy76]

And then your mysql root password is stored in plain text in your .bash_history

export HISTFILE=/dev/null will prevent your current session's history from being saved.

FYI, history -c will clear your current bash history.

[vitec]

I have the same problem. I'm trying to reset my mysql's root password and he keeps saying that user ''@'localhost not found or something. Anyway, I'm re-emerging it to see if it fixes. But anyone knows a way to like "RESET" every sql based stuph? I can afford to loose the tables and the data for now! Actually that's not my concearn. I just need to add new tables and cannot remeber the pass.

OK I'm editing this because I've re-emerged mysql to install a MTA and got this answer:

mail etc # mysqladmin -u root -p create mailsql
Enter password:
mysqladmin: CREATE DATABASE failed; error: 'Access denied for user ''@'localhost' to database 'mailsql''
mail etc #

I'm following this guide http://www.gentoo.org/doc/en/virt-mail- ... =printable
and on page 6 I stop because of that mysql problem.

I've used this guide on this machine before and it worked. There's something very wrong with my mysql server! Please HELP!