How to fix MySQL --skip-grant-tables ERROR 129006.11.22

The MySQL --skip-grant-tables option is used to start the MySQL server without loading the grant tables.

This option is generally used to connect to the database server when you forgot the root user password.

SQL statements related to accounts management are disabled when the --skip-grant-tables option is active.

For example, you can’t use the ALTER USER or SET PASSWORD statements. Running such statements will cause the ERROR 1290 as shown below:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables 
option so it cannot execute this statement

To fix this error, you need to load the grant tables to the MySQL server using the FLUSH PRIVILEGES command:

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.01 sec)

By reloading the grant tables, you should be able to execute the statements that are previously unavailable.

Alternatively, you can also restart your server in normal mode using the following command:

sudo service mysql stop
sudo service mysql start

If you’re using a configuration file to start your server, you need to check on the config file to make sure the skip-grant-tables option is not added to the [mysqld] section.

Here’s an example .ini file with the skip-grant-tables option enabled:

# 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

You need to either comment or remove the highlighted line above.

And that’s how you fix the ERROR 1290 caused by the --skip-grant-tables option. Nice work!

https://sebhastian.com/mysql-skip-grant-tables-error-fix/

Anonymous

How to fix MySQL --skip-grant-tables ERROR 129006.11.22

Information Epoch 1669866540