How to get all privileges back to the root user in MySQL?

Question

I have logged in to MySQL with the --skip-grant-tables option. But I don't know how to get all privileges back to the root user.

I tried:

GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost';

MySQL said:

# 1290 - The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Trying:

GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost'
WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

MySQL said:

# 1290 - The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Answer 1

For mysql 8.0.12+
I've tried the documentation (not working) I've tried various other options, all failed.

PASSWORD() is deprecated, SET PASSWORD is disabled, ALTER USER is disabled as well.

1. stop mysql service
2. Start it this way: /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
3. Login (mysql) and exec: update user set authentication_string='' where User='root';
4. killall mysqld
5. Start service again

Now you can use set password or alter user after logging in without a password