Monthly ArchiveNovember 2019

Linux : Update mysql root password when lost

Sometimes, on a server used as a dev environment, you might have lost the mysql root user password.

This is how you can you can start mysql in safe mode with –skip-grant-table options and reset the root user password.

# start mysql in safe mode
sudo -i
killall mysqld
mkdir /var/run/mysqld
chown mysql:mysql /var/run/mysqld
touch /var/run/mysqld/mysqld.sock
chmod 777 -R /var/run/mysqld # this is nto recommend and is only done temporay
sudo mysqld_safe --skip-grant-tables &

# in a new terminal tab
mysql -u -root
# once connected to mysql :
use mysql;
UPDATE mysql.user SET authentication_string=PASSWORD('<your password>') WHERE USER='root' AND Host='localhost';

# exit mysql
killall mysqld

# restart mysql normaly
service mysql start

# connect normaly using the root password and the new password
mysql -u root -p