Header Shadow Image


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

This is how to fix root password on MySQL Galera clusters if the environment doesn't let you login with your old one.  In other words, if you're getting this:

ERROR 1045 (28000): Access denied for user 'root'@'mysql03.nix.mds.xyz' (using password: YES)

Do the following:

[root@mysql01 mysql]#
[root@mysql01 mysql]#
systemctl set-environment MYSQLD_OPTS="–wsrep-new-cluster –skip-grant-tables"
[root@mysql01 mysql]#
systemctl start mysqld
[root@mysql01 mysql]#mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end
with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.24-log MySQL Community Server – (GPL), wsrep_25.16

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> UPDATE 
mysql.user SET password=password("MYSECRET") WHERE user='root';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MYSECRET');
ERROR 1290 (HY000): The MySQL server is running with the –skip-grant-tables option so it cannot execute this statement
mysql>
mysql>
mysql> update 
mysql.user set authentication_string=password('MYSECRET') where user='root';
Query OK, 1 row affected, 1 warning (0.03 sec)
Rows matched: 1  Changed: 1  
Warnings: 1

mysql> show grants;
ERROR 1290 (HY000): The MySQL server is running with the –skip-grant-tables option so it cannot execute this statement
mysql> 
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> show grants;
ERROR 1141 (42000): There is no such grant defined for user 'skip-grants user' on host 'skip-grants host'
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants;
ERROR 1141 (42000): There is no such grant defined for user 'skip-grants user' on host 'skip-grants host'
mysql> quit
Bye

Test it out and alter the database once more:

 
[root@mysql01 mysql]# mysql -uroot -pMYSECRET -e'show grants'
mysqlPlease use –connect-expired-password option or
: [Warning] Using a password on the command line interface can be insecure.invoke
mysql in interactive mode.
[root@mysql01 mysql]#
mysqluroot -p –e'show grants'
Enter password:
Please use –connect-expired-password option or invoke
mysql in interactive mode.
[root@mysql01 mysql]#
mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end
with ; or \g.
Your MySQL connection id is 712
Server version: 5.7.24-log
 

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
ERROR 1820 (HY000): You must reset your password using
ALTER USER statement before executing this statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MYSECRET';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> quit

Bye

Test the full access:


[root@mysql01 mysql]# cat /usr/bin/mysqld_bootstrap
# Copyright (c) 2016, Codership Oy. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301
USA

#
# A
wsrep-enabled MySQL server daemon needs a special option if it is to start
# as the first node of a Galera cluster.
# With SystemV init, this could be passed as a "bootstrap" (rather than "start") command:
#     sudo service
mysqld bootstrap
#
# With
systemd, such alternative commands are not possible.
# However,
systemd passes a set of environment variables to the service process
# it starts, and this set can be modified.
# Such a modification would be persistent, so it must be undone after use.
#
# If other options are set already, make sure to use them, and to restore them.

OLDVAL=$(systemctl show-environment | grep '^MYSQLD_OPTS=')

if [ -z “$OLDVAL” ]; then
    systemctl set-environment   MYSQLD_OPTS="–wsrep-new-cluster"
    systemctl start             mysqld
    systemctl unset-environment MYSQLD_OPTS
else
   
systemctl set-environment   "$OLDVAL —wsrep-new-cluster"
   
systemctl start             mysqld
   systemctl set-environment   "$OLDVAL"
fi

[root@mysql01 mysql]# systemctl unset-environment MYSQLD_OPTS
[root@mysql01 mysql]# /
usr/bin/mysqld_bootstrap

[root@mysql01 mysql]#mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands endwith ; or \g.
Your MySQL connection id is 29
Server version: 5.7.24-log MySQL Community Server – (GPL), wsrep_25.16

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

You may still run into issues.  This is how to resolve this particular one:

mysql>
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '<PASS>';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '<PASS>'
    -> ;
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '<PASS>'
    -> ;
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
mysql> create user 'root'@'localhost' identified by 'john123';
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'root'@'localhost' identified by '<PASS>';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'
mysql> alter user 'root'@'localhost' identified by '<PASS>';
Query OK, 0 rows affected (0.00 sec)

mysql>

 

REF: https://bugs.mysql.com/bug.php?id=79027

Cheers,
TK

Leave a Reply

You must be logged in to post a comment.


     
  Copyright © 2003 - 2013 Tom Kacperski (microdevsys.com). All rights reserved.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 Unported License