Can't start MySQL to reset root password

I've installed mysql-server on a Linux box and forgot (oops) the root password. Having looked around the internet, the general method is so:

Stop MySQL (sudo service mysql stop)
Start MySQL in special mode (sudo mysqld_safe --skip-grant-tables)
Login to local server and reset password (mysql -u root)

My problem is at number 2. When I run the command, it says some stuff about logging and starting the daemon, then ends with the line

mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

and using sudo service mysql status confirms that MySQL has, indeed stopped.

Why does it stop so suddenly? And (more importantly) how can I get it to keep running so I can reset my password?

Thanks in advance

EDIT Here's the full log of "stuff about loggin and starting the daemon":

$ mysqld_safe --skip-grant-tables 
141219 16:55:20 mysqld_safe Logging to syslog.
141219 16:55:20 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141219 16:55:20 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
$

EDIT 2 And the output of /var/log/syslog (I've replaced my hostname with <hostname>)

Dec 20 10:20:09 <hostname> mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Dec 20 10:20:09 <hostname> mysqld: 141220 10:20:09 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
Dec 20 10:20:09 <hostname> mysqld: 141220 10:20:09 [Warning] Can't create test file /var/lib/mysql/<hostname>.lower-test
Dec 20 10:20:09 <hostname> mysqld: 141220 10:20:09 [Warning] Can't create test file /var/lib/mysql/<hostname>.lower-test
Dec 20 10:20:09 <hostname> mysqld: #007/usr/sbin/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13)
Dec 20 10:20:09 <hostname> mysqld: 141220 10:20:09 [ERROR] Aborting
Dec 20 10:20:09 <hostname> mysqld: 
Dec 20 10:20:09 <hostname> mysqld: 141220 10:20:09 [Note] /usr/sbin/mysqld: Shutdown complete
Dec 20 10:20:09 <hostname> mysqld: 
Dec 20 10:20:09 <hostname> mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended

Luke Moll

Posted 2014-12-19T16:17:22.190

Reputation: 143

Please can you add all of the "stuff about logging and starting the daemon" to the question. It's not possible to diagnose a problem if you edit out all of the diagnostic information. – Mike Scott – 2014-12-19T16:30:13.773

OK, thanks for that data. Now can you look in syslog to see what it's logged there? – Mike Scott – 2014-12-19T17:59:46.850

Where do I access syslog? – Luke Moll – 2014-12-19T20:21:15.250

I'm afraid that depends on how you've configured your system, but you could look for /var/syslog. – Mike Scott – 2014-12-19T21:11:50.227

It was in /var/log/syslog, I've re-run the command to get the output as it looks like syslog only keeps the most recent messages. – Luke Moll – 2014-12-20T10:30:05.763

Answers

OK, it looks like either /var/lib/mysql doesn't exist or the mysql user doesn't have permission to access it. Either way, you have worse problems than having forgotten the root password -- either you don't have a database at all, or something has changed the file permissions or ownership.

Mike Scott

Posted 2014-12-19T16:17:22.190

Reputation: 4 220

Would reinstalling mysql-server help? – Luke Moll – 2014-12-20T17:41:29.630

Probably, yes, if you don't have any data that you want to keep. – Mike Scott – 2014-12-20T18:45:55.977

No idea what the problem was, but ran #apt-get purge mysql-server mysql-server-5.5 mysql-core-5.5 and then reinstalled MySQL server (making sure to write down the password :D) – Luke Moll – 2014-12-23T23:36:08.673

This issue might be caused due to several issue. You can find the exact error from "tail /var/log/mysql/error.log". I have added the default fix for these kind of issues. If the below is not working then update us the print from log where we can try to get some info and move further

Kill all the MySQL process using the following command.

ps aux | grep mysql kill pid

Find the path of mysqld daemon using the "which mysqld_safe" command

Start MySQL without grant tables from the mysqld_safe location

/mysqld_safe_available_directory/mysqld_safe --skip-grant-tables &

ex /bin/mysqld_safe

mysql -u root

Steps to set the new password:

use mysql;
update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
flush privileges;
quit

vembutech

Posted 2014-12-19T16:17:22.190

Reputation: 5 693

Gave it a try but $ ps aux | grep mysql kill pid gives grep: kill: No such file or directory grep: pid: No such file or directory, then rest of steps same problem as question (... pid ended). When I tried just ps aux | grep mysql then kill (pid) where pid it the number in the pid column, it says No such process. – Luke Moll – 2014-12-19T16:50:33.313

I faced same problem Aborting come from permission on /var/lib/mysql the folder belong to user mysql, then root has no fully access

solved it by:

$ sudo su - mysql

$ mysqld_safe --skip-grant-tables &

raksa eng

Posted 2014-12-19T16:17:22.190

Reputation: 161