Ubuntu – How to restart MySQL with –skip-grant-tables if you can’t use the root password

MySQLUbuntu

I need to restart MySQL in Ubuntu 16.04 with the --skip-grant-tables option enabled, but either I don't know my root password or it isn't working. How can I set --skip-grant-tables without the password?

When I try it as a regular user:

mysqld --skip-grant-tables

I see this:

mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13 - Permission denied)

So, I dug this example out of /etc/init.d/mysql and added the --skip-grant-tables parameter:

su - mysql -s /bin/bash -c "/usr/sbin/mysqld --skip-grant-tables"
Password: 
su: Authentication failure

So su doesn't work and the root password didn't work either. I also tried this:

sudo su - mysql -s /bin/bash -c "/usr/sbin/mysqld --skip-grant-tables"
No directory, logging in with HOME=/

How can I start mysql with –skip-grant-tables?

Best Answer

When you don't know your root password (or an error like 'ERROR 1045 (28000): Access denied for user 'root'@'localhost' prevents access) you can get access by adding the option to the MySQL config file. First open it for editing:

sudo nano /etc/mysql/my.cnf

Then search for [mysqld] and enter these values below it:

[mysqld]
# For debugging and recovery only #
skip-grant-tables
skip-networking
###################################

As you can see, the trick to adding command line parameters here is dropping the -- from the front of the parameter. Now restart the mysql service and you can access your tables to reset your root user password or almost anything you need to do. (However, you can't do anything with the grant tables because they aren't loaded.)

Beware. While you're in this mode, any logged-in user has access to your whole database. That's why I added the skip-networking option above, so remote users can't access the tables while you're recovering.

Be sure to comment out those lines out and restart mysql once again when you're done, to re-secure the server.

Related Question