A script to reset the MySQL root password

It’s a pain if you ever forget your MySQL root password. Fortunately it’s a fairly straightforward process to reset it, here’s how:

pkill -9 mysqld;
echo "UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;" > /tmp/reset-pass.sql
mysqld_safe --init-file=/tmp/reset-pass.sql &
sleep 10
pkill -9 mysqld;

A bash script to reset the mysql root password

To make the process easier, I’ve wrapped these commands up in a script and put it on our open source respository here:

mysql_reset_root_password.sh script

Update: This script has been improved with Andy’s suggestion in the comments, ¬†which is a simpler and more secure method.

killall -15 mysqld
read -s -p 'Enter a new root password: ' MYSQL_ROOT_PASSWORD
echo "UPDATE mysql.user SET Password=PASSWORD('$MYSQL_ROOT_PASSWORD') WHERE User='root';" | mysqld --bootstrap

5 thoughts on “A script to reset the MySQL root password”

  1. I wouldn’t recommend storing the new root password in /tmp (whether it gets deleted or not) even root defaults to group/other read permissions. /root would be more appropriate.

    Is there any way you can set the mysql process to read the init script from stdin? e.g. echo “blagh” | mysqld_safe –init-file=-

    Also, kill -9ing is probably a bit overkill. It’d probably be better to find a better signal to allow it to shutdown more cleanly, and also make use of mysql’s pid file rather than kill all mysql processes

  2. I’ve gotten it down to this:

    killall -15 mysqld

    read -s -p ‘Enter a new root password: ‘ MYSQL_ROOT_PASSWORD

    echo “UPDATE mysql.user SET Password=PASSWORD(‘$MYSQL_ROOT_PASSWORD’) WHERE User=’root';” | mysqld –bootstrap

    MYSQL_ROOT_PASSWORD=

    –bootstrap stops mysqld running as a daemon, and shuts it down after running the command.

    I’m not keen on not using the /etc/init.d scripts for shutting down the server, restarting it after, as they could have custom settings for that distribution.

    I don’t think flushing privileges is necissary if you are going to shut down the server anyway. It seems fine without

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>