How to start MySQL with --skip-grant-tables?

Question

I locked my root user out from our database. I need to get all privileges back to the root user. I have my password and I can log in to MySQL. But the root user has no all privileges.

Answer 1

I had the same problem as the title of this question, so incase anyone else googles upon this question and wants to start MySql in 'skip-grant-tables' mode on Windows, here is what I did.

Stop the MySQL service through Administrator tools, Services.

Modify the my.ini configuration file (assuming default paths)

C:\Program Files\MySQL\MySQL Server 5.5\my.ini

or for MySQL version >= 5.6

C:\ProgramData\MySQL\MySQL Server 5.6\my.ini 

In the SERVER SECTION, under [mysqld], add the following line:

skip-grant-tables

so that you have

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
[mysqld]

skip-grant-tables

Start the service again and you should be able to log into your database without a password.

Answer 2

Use the following command (notice the "d"): mysqld --skip-grant-tables

Answer 3

How to re-take control of the root user in MySQL.

DANGER: RISKY OPERATTION

• Start session ssh (using root if possible).

• Edit my.cnf file using.

  sudo vi /etc/my.cnf
  

• Add line to mysqld block.*

  skip-grant-tables
  

• Save and exit.

• Restart MySQL service.

  service mysql restart
  

• Check service status.

  service mysql status
  

• Connect to mysql.

  mysql
  

• Using main database.

  use mysql;
  

• Redefine user root password.

  UPDATE user SET `authentication_string` = PASSWORD('myNuevoPassword') WHERE `User` = 'root'; 
  

• Edit file my.cnf.

  sudo vi /etc/my.cnf
  

• Erase line.

  skip-grant-tables
  

• Save and exit.

• Restart MySQL service.

  service mysqld restart
  

• Check service status.

  service mysql status
  

• Connect to database.

  mysql -u root -p
  

• Type new password when prompted.

This action is very dangerous, it allows anyone to connect to all databases with no restriction without a user and password. It must be used carefully and must be reverted quickly to avoid risks.

Answer 4

After trying lots of things, this is what worked for me:

sudo mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword'; 

So first we use sudo to log in mysql as root without needing a password. Then we just update root's password.

After that, I restarted mysqld:

sudo service mysql restart

And the newpassword logged root in!