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!