Reset the Mysql password

by

How to reset the mysql password on a linux server.

CentOS

First, stop the MySQL service/daemon. On Centos/RHEL you would run:

# service mysqld stop

Next, edit the MySQL config file (/etc/my.cnf on CentOS/RHEL) and add the following to the [mysqld] section.

# skip-grant-tables

Start MySQL back up

# service mysqld start

You will now be able to connect as user root without any password.

Run the following SQL queries:

mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root'; 
FLUSH PRIVILEGES;

Remove the skip-grant-tables line from the MYSQL config file, and restart MySQL one final time.

Ubuntu 16

First off stop mySQL

# sudo service mysql stop

Now manually create the socket directory for MySQLD to be able to start up and give mysql permissions to it. (THIS is the most important step that all guides fail to mention leading people into doing very stupid thing when they cannot get mysqld to start)

# sudo mkdir /var/run/mysqld; sudo chown mysql /var/run/mysqld

Now start mysql with the –skip-grant-tables option because you are not checking user privs at this point

# sudo mysqld_safe --skip-grant-tables &

Now log into mysql as root

# sudo mysql -u root

Now run the following commands in the mysql console

mysql> use mysql;
mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR root@'localhost' = PASSWORD('yournewpassword');
mysql> FLUSH PRIVILEGES;
mysql> exit

Now stop mySQL and Restart it

# sudo service mysql stop
# sudo service mysql start

or

# sudo /etc/init.d/mysql stop
# sudo /etc/init.d/mysql start

Check if mySQL started properly by running

# sudo service mysql status

Now to make sure everything is OK reboot your server and after reboot run

# sudo service mysql status

Now you can test logging into mySQL with your new password by running

# mysql -u root -p

Leave a Comment