MySQL 5.7.16 and later initial root password change

change-user-password-in-MySQL

If you work with MySQL 5.7.16 or later, you have probably noticed that a temporal root password has been generated and written in the mysql log file, this is great for security reasons, you can copy that password and change it manually, however if you are automating the installation, then this could be kind of inconvenient.

I’ve written this bash script to get that temporal password from the log file and change it for you, as well as setting up the /root/.my.conf so you don’t have to worry anymore about passwords if you have the super user rights.

Save it with whatever name you prefer, I’ve used chmyinipswd.sh, you must setup you log file path and the new password, I suggest to delete it after running it to avoid keeping the new password in the filesystem.

#!/bin/bash

LOG_FILE=<your_path_to_mysql_log_file>
NEW_PASSWORD=<you_new_passowrd>

if [ "$(id -u)" != "0" ]; then
echo "This script must be run as root" 1>&2
exit 1
fi
mysql -e exit
if [ $? == 0 ]; then
echo "Password has been already changed, nothing to do" 1>&2
exit 1
fi
TMP_PASSWORD=`grep 'temporary.*root@localhost' ${LOG_FILE} | sed 's/.*root@localhost: //'`
echo -e "[client]\nuser=root\npassword=${TMP_PASSWORD}" > /root/.my.cnf
mysql --connect-expired-password -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '${NEW_PASSWORD}';"
if [ $? != 0 ]; then
echo "Something went wrong, please change the password manually" 1>&2
exit 1
fi
echo -e "[client]\nuser=root\npassword=${NEW_PASSWORD}" > /root/.my.cnf
echo "Password changed successfuly!"
exit 0

Basilio Briceño

DevOps evangelist, SoftwareLibre activist, sometimes speaker & eclectic metalhead.

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>