4

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?

  • 1
    The easiest method would be to temporarily modify /etc/init.d/mysql to include the option and then start it with this script (/etc/init.d/mysql start) – Marek Rost Sep 22 '16 at 19:52
  • Not so easy.. An xtrace of the start script seems to run the command from here: /bin/systemctl --no-pager stop mysql.service .. It is not running the mysqld commands in the start script. It is going to take me a while to reverse engineer mysql.service (a property file). – jcalfee314 Sep 22 '16 at 20:12
  • 1
    It did work.. I added --skip-grant-tables /lib/systemd/system/mysql.service .. Wow, I can't imagine this getting more complicated. – jcalfee314 Sep 22 '16 at 20:16
  • ahh i forgot ubuntu runs on systemd already. well you figured it out :) – Marek Rost Sep 22 '16 at 21:42
10

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.

  • 1
    After days searching on the internet, this solved my problem. Thank you so much. – mungaih kamau Oct 31 '17 at 22:20
  • This works with a Linux system that uses systemd. Solutions that start mysqld from the command line don't seem to work. – Craig S. Anderson Mar 2 '18 at 2:21
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.