Reset the MySQL root password in Open edX

Summary

Prior the Koa open-release the root password of the MySQL service was left unset so that you could access mysql on the command line as follows

mysql -u root -p

But to improve security of the platform, beginning with the Koa release the root password is set. As of this writing I’ve never figured out where the root password is stored and so as a workaround I figured out how to reset the MySQL password. I found detailed instructions here, “How to Reset the MySQL Root Password on Ubuntu“, which provides instructions on how to reset the root password on MySQL installations version 5.7.x which is what Open edX Koa runs on.

Following are the annotated item-by-item commands. Note that you’ll need to open TWO terminal windows to complete these commands because the mysql service, while operating in Safe Mode, will render one of these two windows “busy”.

# stop the local mysql service
sudo /etc/init.d/mysql stop

# These next two lines are arcane technical requirements of the mysql service 
# which allow it to be launched in "Safe Mode". The folder contains the process ID
# for the mysql service when it is running in Safe Mode.
sudo mkdir /var/run/mysqld
sudo chown mysql /var/run/mysqld

# in the first terminal window:
# Launch the mysql service in Safe Mode. Note that the "&" character is required.
# Note that this will launch the mysql service on a foreground thread which will 
# appear like the terminal window has hung. This is the expected behavior.
# 
sudo mysqld_safe --skip-grant-tables&
sudo mysql --user=root mysql

# in the second terminal window:
# Issue sql commands to modify the root password.
update user set authentication_string=PASSWORD('YOUR NEW PASSWORD') where user='root';
update user set plugin="mysql_native_password" where User='root';
flush privileges;
exit

# terminate all active mysql service processes by issuing a Linux kill statement.
sudo killall -u mysql

# Re-start the mysql daemon normally.
sudo /etc/init.d/mysql start

# Test the connection. You should be able to connect to mysql as root using your new
# password.
sudo mysql -u root -p