How to restart MySQL with --skip-grant-tables if you can't use the root password?

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?

2 Answers

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.

From comments.

For init.rc configurations:

The easiest method would be to temporarily modify /etc/init.d/mysql to include the option --skip-grant-tables and then start it with this script (/etc/init.d/mysql start).

On upstart systems like Ubuntu 16.04 this needs to be done in /lib/systemd/system/mysql.service.