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
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
I did initially have the init-scripted stored in /root/ but mysql had problems reading it.
Can you take a look and commit your suggestions to the repository?
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
This script doesn’t appear to work in the Centos 5.3 install I’m using, there is no mysqld executable.
Centos 5.3 appears to have moved it to /usr/libexec/mysqld, which is not in the PATH environmental variable