How to reset a lost mysql root password

Question

I have done some research in how to reset a mySQL root password but when I try to run this command:

sudo stop mysql

and it outputs: stop unknown instance

Then I try and run:

# mysqld_safe --skip-grant-tables &

I get: A mysqld process already exists

Update:

Tried the other way that is suggested and ran into this error:

Answer 1

Use the following command.

sudo /etc/init.d/mysql stop

or

sudo service mysql stop

Answer 2

Improving on your research: to reset a root password, may I suggest that you do not use --skip-grant-tables. This advice is so common but mostly pointless and dangerous; it requires two restarts of your server. Please see my post Dangers of skip-grant-tables.

An easier solution is to use the init-file configuration variable, and point it to a file where you SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456') as explained in the above post.

There are further hacks which actually allow you to change the password without downtime, and those can be found in comments to this blog post: Recovering a MySQL root password: the fourth solution

With regard the fact you are unable to start MySQL, follow similar advice of @drew010 's comment by killall -TERM mysqld or killall -9 mysqld if the former does not do the trick. You may also take a look at the error log (typically /var/log/mysqld.log but otherwise as configured in your my.cnf settings file).

Answer 3

I am not having a big exposure on how to reset mysql root password, but according to the question and error A mysqld process already exists it means some other instance or process might have started the stopped service.

According to Recover MySQL root password the command which is used to stop the My Sql Service is

/etc/init.d/mysql stop

So try that command, and try to follow the steps and if you get the same error again then after stopping the My Sql service, just run this command to see which process are still running
ps -ef | grep 'mysql'
Output will look like

mysql 2370 1 0 Dec04 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql 2504 2370 0 Dec04 ? 00:25:22 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/mysqld.pid --socket=/var/lib/mysql
/mysql.sock root 38107 22476 0 08:54 pts/1 00:00:00 grep --color=auto mysql

And try to manually kill the process with kill - 9 pid