\n\tThis 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:\n<\/p>\n
\n\tERROR 1045 (28000): Access denied for user 'root'@'mysql03.nix.mds.xyz' (using password: YES)<\/span>\n<\/p>\n \n\tDo the following:\n<\/p>\n \n\t[root@mysql01 mysql]# \n\tCopyright (c) 2000, 2018, Oracle and\/or its affiliates. All rights reserved.<\/span>\n<\/p>\n \n\tOracle is a registered trademark of Oracle Corporation and\/or its \n\tType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.<\/span>\n<\/p>\n \n\tmysql> \n\tmysql> show grants;<\/strong> \n\tmysql> \n\tmysql> show grants;<\/strong> \n\tTest it out and alter the database once more:\n<\/p>\n \n\t <\/span> \n\tCopyright (c) 2000, 2018, Oracle and\/or its affiliates. All rights reserved.<\/span>\n<\/p>\n \n\tOracle is a registered trademark of Oracle Corporation and\/or its \n\tType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.<\/span>\n<\/p>\n \n\tmysql> show grants;<\/strong> \n\tmysql> flush privileges;<\/strong> \n\tmysql> quit<\/span>\n<\/p>\n \n\tBye<\/span>\n<\/p>\n \n\tTest the full access:\n<\/p>\n \n\t \n\t# \n\tOLDVAL=$(systemctl show-environment | grep '^MYSQLD_OPTS=')<\/span>\n<\/p>\n \n\tif [ -z “$OLDVAL” ]; then \n\t[root@mysql01 mysql]# <\/span>systemctl<\/span><\/strong> unset-environment MYSQLD_OPTS<\/strong> \n\t[root@mysql01 mysql]# mysql -u root -p<\/strong> \n\tCopyright (c) 2000, 2018, Oracle and\/or its affiliates. All rights reserved.<\/span>\n<\/p>\n \n\tOracle is a registered trademark of Oracle Corporation and\/or its \n\tType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.<\/span>\n<\/p>\n \n\tmysql><\/span>\n<\/p>\n \n\tYou may still run into issues. This is how to resolve this particular one:\n<\/p>\n \n\tmysql> \n\tmysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '<PASS>'<\/strong> \n\tmysql> \n\tmysql> create user 'root'@'localhost' identified by '<PASS>';<\/strong> \n\tmysql><\/span>\n<\/p>\n \n\t \n<\/p>\n
\n\t[root@mysql01 mysql]#<\/span>systemctl<\/span> set-environment MYSQLD_OPTS="–<\/span>wsrep<\/span><\/strong>-new-cluster –skip-grant-tables"<\/strong>
\n\t[root@mysql01 mysql]#<\/span>systemctl<\/span> start <\/span>mysqld<\/span><\/strong>
\n\t[root@mysql01 mysql]#<\/span>mysql<\/span><\/strong> -u root -p<\/strong>
\n\tEnter password:
\n\tWelcome to the MySQL monitor. Commands end<\/span>with ;<\/span> or \\g.
\n\tYour MySQL connection id is 20
\n\tServer version: 5.7.24-log MySQL Community Server – (GPL), wsrep_25.16<\/span>\n<\/p>\n
\n\taffiliates. Other names may be trademarks of their respective
\n\towners.<\/span>\n<\/p>\n
\n\tmysql> UPDATE <\/strong><\/span>mysql<\/span><\/strong>.user SET password=password("MYSECRET") WHERE user='root';<\/strong>
\n\tERROR 1054 (42S22): Unknown column 'password' in 'field list'<\/span>
\n\tmysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MYSECRET');<\/strong>
\n\tERROR 1290 (HY000): The MySQL server is running with the –skip-grant-tables option so it cannot execute this statement<\/span>
\n\tmysql>
\n\tmysql>
\n\tmysql> update <\/strong><\/span>mysql<\/span><\/strong>.user set authentication_string=password('MYSECRET') where user='root';<\/strong>
\n\tQuery OK, 1 row affected, 1 warning (0.03 sec)
\n\tRows matched: 1 Changed: 1 <\/span>Warnings<\/span>: 1<\/span>\n<\/p>\n
\n\tERROR 1290 (HY000): The MySQL server is running with the –skip-grant-tables option so it cannot execute this statement
\n\tmysql>
\n\tmysql> flush privileges;<\/strong>
\n\tQuery OK, 0 rows affected (0.03 sec)<\/span>\n<\/p>\n
\n\tmysql> show grants;<\/strong>
\n\tERROR 1141 (42000): There is no such grant defined for user 'skip-grants user' on host 'skip-grants host'
\n\tmysql> flush privileges;
\n\tQuery OK, 0 rows affected (0.01 sec)<\/span>\n<\/p>\n
\n\tERROR 1141 (42000): There is no such grant defined for user 'skip-grants user' on host 'skip-grants host'
\n\tmysql> quit
\n\tBye<\/span>\n<\/p>\n
\n\t[root@mysql01 mysql]# mysql -uroot -pMYSECRET -e'show grants'<\/strong>
\n\tmysqlPlease use –connect-expired-password option or
\n\t: [Warning] Using a password on the command line interface can be insecure.invoke<\/span>mysql<\/span> in interactive mode.
\n\t[root@mysql01 mysql]#<\/span>mysql<\/span> –<\/span>uroot<\/span> -p –<\/span>e'show<\/span><\/strong> grants'<\/strong>
\n\tEnter password:
\n\tPlease use –connect-expired-password option or invoke<\/span>mysql<\/span> in interactive mode.
\n\t[root@mysql01 mysql]#<\/span>mysql<\/span><\/strong> -u root -p<\/strong>
\n\tEnter password:
\n\tWelcome to the MySQL monitor. Commands end<\/span>with ;<\/span> or \\g.
\n\tYour MySQL connection id is 712
\n\tServer version: 5.7.24-log<\/span> <\/span>\n<\/p>\n
\n\taffiliates. Other names may be trademarks of their respective
\n\towners.<\/span>\n<\/p>\n
\n\tERROR 1820 (HY000): You must reset your password using<\/span>ALTER<\/span> USER statement before executing this statement.
\n\tmysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MYSECRET';<\/strong>
\n\tQuery OK, 0 rows affected (0.01 sec)<\/span>\n<\/p>\n
\n\tQuery OK, 0 rows affected (0.01 sec)<\/span>\n<\/p>\n
\n\t[root@mysql01 mysql]# cat \/<\/strong><\/span>usr<\/span><\/strong>\/bin\/mysqld_bootstrap<\/strong>
\n\t# Copyright (c) 2016, Codership Oy. All rights reserved.
\n\t#
\n\t# This program is free software; you can redistribute it and\/or modify
\n\t# it under the terms of the GNU General Public License as published by
\n\t# the Free Software Foundation; version 2 of the License.
\n\t#
\n\t# This program is distributed in the hope that it will be useful,
\n\t# but WITHOUT ANY WARRANTY; without even the implied warranty of
\n\t# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
\n\t# GNU General Public License for more details.
\n\t#
\n\t# You should have received a copy of the GNU General Public License
\n\t# along with this program; if not, write to the Free Software
\n\t# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301<\/span>USA<\/span>\n<\/p>\n
\n\t# A<\/span>wsrep<\/span>-enabled MySQL server daemon needs a special option if it is to start
\n\t# as the first node of a Galera cluster.
\n\t# With SystemV init, this could be passed as a "bootstrap" (rather than "start") command:
\n\t# sudo service<\/span>mysqld<\/span> bootstrap
\n\t#
\n\t# With<\/span>systemd<\/span>, such alternative commands are not possible.
\n\t# However,<\/span>systemd<\/span> passes a set of environment variables to the service process
\n\t# it starts, and this set can be modified.
\n\t# Such a modification would be persistent, so it must be undone after use.
\n\t#
\n\t# If other options are set already, make sure to use them, and to restore them.<\/span>\n<\/p>\n
\n\t systemctl set-environment MYSQLD_OPTS="–wsrep-new-cluster"
\n\t systemctl start mysqld
\n\t systemctl unset-environment MYSQLD_OPTS
\n\telse
\n\t <\/span>systemctl<\/span> set-environment "$OLDVAL —<\/span>wsrep<\/span>-new-cluster"
\n\t <\/span>systemctl<\/span> start <\/span>mysqld<\/span>
\n\t <\/span>systemctl<\/span> set-environment "$OLDVAL"
\n\tfi<\/span>\n<\/p>\n
\n\t[root@mysql01 mysql]# \/<\/strong><\/span>usr<\/span>\/bin\/mysqld_bootstrap<\/span><\/strong>\n<\/p>\n
\n\tEnter password:
\n\tWelcome to the MySQL monitor. Commands endwith ; or \\g.
\n\tYour MySQL connection id is 29
\n\tServer version: 5.7.24-log MySQL Community Server – (GPL), wsrep_25.16<\/span>\n<\/p>\n
\n\taffiliates. Other names may be trademarks of their respective
\n\towners.<\/span>\n<\/p>\n
\n\tmysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '<PASS>';<\/strong>
\n\tERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
\n\tmysql> flush privileges;<\/strong>
\n\tQuery OK, 0 rows affected (0.01 sec)<\/span>\n<\/p>\n
\n\t -> ;
\n\tERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
\n\tmysql> FLUSH PRIVILEGES;
\n\tQuery OK, 0 rows affected (0.01 sec)<\/span>\n<\/p>\n
\n\tmysql>
\n\tmysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '<PASS>'<\/strong>
\n\t -> ;
\n\tERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
\n\tmysql> create user 'root'@'localhost' identified by 'john123';<\/strong>
\n\tQuery OK, 0 rows affected (0.01 sec)<\/span>\n<\/p>\n
\n\tERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'
\n\tmysql> alter user 'root'@'localhost' identified by '<PASS>';<\/strong>
\n\tQuery OK, 0 rows affected (0.00 sec)<\/span>\n<\/p>\n