CODEWITHSUNDEEP
mysqlroot
BarryDevSF
BarryDevSF

Reputation: 405

Can't log into MySQL as root, even after restarting with --skip-grant-tables

I'm running MySQL 5.0.77 on FreeBSD 7.3. The MySQL root password was lost, and I want to reset it. But I have the following problem.

I stopped MySQL and re-started with the --skip-grant-tables option: 

/usr/local/etc/rc.d/mysqld start --skip-grant-tables

The service starts, but then when I try to log in without a password: 

 mysql -u root

I get this response:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I can log in as other users (that lack the SUPERUSER privilege), but not root. Anyone know what I'm doing wrong?

Upvotes: 2

Views: 4468

Answers (2)

plong0
plong0

Reputation: 2188

I tried with sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking" restarted the mariadb server and still got the access denied error with sudo mysql -u root.

What worked for me was this:

sudo systemctl stop mariadb
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
sudo mysql -u root

Now in the mysql console:

FLUSH PRIVILEGES;

Got an error when I did:

SET PASSWORD FOR root@'localhost' = PASSWORD('password');

But this worked:

UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

Finished up with:

FLUSH PRIVILEGES;
exit

Back on shell:

sudo /etc/init.d/mysql stop

For me, I could still connect using mysql -u root so I stopped the server in a bad way: get PID(s) with ps -aux | grep mysql and terminate them with sudo kill -9 <pid>

Once we are sure mysql server is not running, restart normal way:

sudo systemctl start mariadb

Now password will be required again, but we know what root password is.

Before final systemctl start mariadb, might want to run sudo systemctl set-environment MYSQLD_OPTS="" to be safe in case you had attempted with that method.

Upvotes: 1

Fernando Garcia
Fernando Garcia

Reputation: 151

I've just passed by some similiar situation, after some frustration I've realized that I have 2 MySQL installations running in my computer, one in my OS and one in a local server package. In my case this annoying situation just got resolved shutting down the SQL service of my OS

Upvotes: 0

Related Questions