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

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.