Reset root password on Galera Cluster (Percona or MariaDB)

We have known methods to reset MySQL root password but in this post we’ll see how that works for a Galera Cluster; may it be Percona XtraDB Cluster or MariaDB Galera cluster.

Basically a Galera Cluster is bunch of MySQL nodes working together in synchronous replication. The user credential is common to all (Ideally of-course)! Let’s see how straight  forward it is to reset password on a Percona Xtradb Cluster lab.

To reset the root password on a standard MySQL instance you may choose either of init-file or skip-grant-tables way.

Reset MySQL password using init-file

Add init-file to mysql configuration file:
vi /etc/my.cnf
init-file=/tmp/init.sql

Create an init-file:
vi /tmp/init.sql
SET PASSWORD FOR 'kedar'@'localhost' = PASSWORD('do-not-forget-passwords');

Restart mysqld: 
service mysqld restart

Reset MySQL password using skip-grant-tables

Add skip-grant-tables to my.cnf under [mysqld] section
vi /etc/my.cnf
skip-grant-tables

Restart mysqld: 
service mysqld restart

Execute update SQL:
mysql> update mysql.user set password=password('unforgetful-password') where user='kedar' and host='localhost';
(You cannot issue commands to GRANT-tables)

Remove skip-grant-tables from my.cnf

Restart mysqld:
service mysqld restart

## /etc/init.d/mysqld restart --skip-grant-tables (use this if you don't like to edit my.cnf)

I would not say these are the only methods to recover a lost root (or equivalent) password as people have been doing crazy things since ages.

For a single MySQL instance this would be the end of the quest and you’d have the new password. What about our cluster which is not one node!

If I say “we will have to perform this exercise on all the cluster nodes – restart all the nodes.”

You’d scream: “Why – o – why?”

I slang RTFineM: “The user changes are not replicated across the cluster as they’re poor little non-transactional MyISAM tables. Galera would replicate only InnoDB and XtraDB storage engines.“
(http://galeracluster.com/documentation-webpages/userchanges.html)

So ofcourse MyISAM table modifications (DMLs) will not replicate, which we did through init-file. Galera Cluster does replicate the DDLs and that’s how the privilege system is sustained!

You’d say: With above password reset steps, we have already gained access to one of the node on Galera cluster and we know a fact that “Galera Cluster does replicate the DDLs.” Does that light a bulb here!?

You can reset password on one of the cluster node and once it rejoin the cluster, reissue the DDL to change the password! That should replicate, shouldn’t it? We don’t need to restart all the cluster nodes!

I am like ¯\_(ツ)_/¯ let’s see…

[root@PXC-02 ~]# cat /tmp/init.sql
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('reset-password-node');
[root@PXC-02 ~]# service mysqld restart
Shutting down MySQL (Percona XtraDB Cluster)..... SUCCESS!
Starting MySQL (Percona XtraDB Cluster).... SUCCESS!
[root@PXC-02 ~]# read -s pw
[root@PXC-02 ~]# mysql -ukedar -p$pw
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.5.34-25.9-log Percona XtraDB Cluster (GPL) 5.5.34-25.9, Revision 607, wsrep_25.9.r3928

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, 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.

(kedar@localhost) [(none)]>SET PASSWORD FOR 'kedar'@'localhost' = PASSWORD('this-one-replicates');
Query OK, 0 rows affected (0.04 sec)


... on other node ...


[root@PXC-03 ~]# mysql -ukedar -p$pw
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.34-25.9-log Percona XtraDB Cluster (GPL) 5.5.34-25.9, Revision 607, wsrep_25.9.r3928

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, 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.

(kedar@localhost) [(none)]>

oh wow… thanks to you TIL that we do not need to restart all the nodes on Galera Cluster to reset root password.

Conclusion

To reset the password on a Galera Cluster, it’s essential to reset it on one of the cluster nodes and then reissue the Data Definition Language (DDL) or GRANT command. To reset password on Galera Cluster you need to reset it on one of the cluster node and reissue the DDL/GRANT command to get it replicated to all nodes.