So this weekend I was trying to get into a mysql server I co-administer and nobody knew the root password anymore. Here is a short procedure to regain admin privileges to the database. I assign myself (rather than root user) admin privileges  so that I don't interfere with any cron jobs etc. that may have been set up to take advantage of the root account.

Edit as root the /etc/init.d/mysql script and change the listing in the 'start' section from this:

case "${1:-''}" in
 'start')
 sanity_checks;
 # Start daemon
 log_daemon_msg "Starting MySQL database server" "mysqld"
 if mysqld_status check_alive nowarn; then
 log_progress_msg "already running"
 log_end_msg 0
 else
 /usr/bin/mysqld_safe > /dev/null 2>&1 &

Now change the last line listed above to look like this:

/usr/bin/mysqld_safe --init-file=/tmp/mysql-pwd-reset.sql > /dev/null 2>&1 &

The contents of the above script should look something like this:

CREATE USER 'foouser'@'localhost' IDENTIFIED BY 'somepassword';
GRANT ALL PRIVILEGES ON *.* TO 'foouser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Next start mysql again:

sudo /etc/init.d/mysql start

You should now have root access to the database:

mysqlshow -u foouser -p

Finally shutdown mysql, remove your changes in /etc/init.d/mysql and then restart the database.

by Tim Sutton at 12:43 AM under mysql