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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
To reset the root password on a standard MySQL instance you may choose either of init-file or skip-grant-tables way.<\/p>\n\n\n\n\n\n\n\n
Add init-file to mysql configuration file:\nvi \/etc\/my.cnf\ninit-file=\/tmp\/init.sql\n\nCreate an init-file:\nvi \/tmp\/init.sql\nSET PASSWORD FOR 'kedar'@'localhost' = PASSWORD('do-not-forget-passwords');\n\nRestart mysqld: \nservice mysqld restart\n<\/code><\/pre>\n\n\n\nReset MySQL password using skip-grant-tables<\/h2>\n\n\n\nAdd skip-grant-tables to my.cnf under [mysqld] section\nvi \/etc\/my.cnf\nskip-grant-tables\n\nRestart mysqld: \nservice mysqld restart\n\nExecute update SQL:\nmysql> update mysql.user set password=password('unforgetful-password') where user='kedar' and host='localhost';\n(You cannot issue commands to GRANT-tables)\n\nRemove skip-grant-tables from my.cnf\n\nRestart mysqld:\nservice mysqld restart\n\n## \/etc\/init.d\/mysqld restart --skip-grant-tables<\/span> (use this if you don't like to edit my.cnf)\n<\/pre>\n\n\n\nI 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.<\/em><\/p>\n\n\n\nFor 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!<\/p>\n\n\n\n
If I say “we will have to perform this exercise on all the cluster nodes – restart all the nodes.”<\/p>\n\n\n\n
You’d scream: “Why – o – why?”<\/p>\n\n\n\n
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.<\/em>“
(http:\/\/galeracluster.com\/documentation-webpages\/userchanges.html)<\/p>\n\n\n\nSo ofcourse MyISAM table modifications (DMLs) will not replicate, which we did through init-file. Galera Cluster does replicate the DDLs<\/span> and that’s how the privilege system is sustained!<\/p>\n\n\n\nYou’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!?<\/p>\n\n\n\n
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!<\/p>\n\n\n\n
I am like \u00af\\_(\u30c4)_\/\u00af let’s see…<\/p>\n\n\n\n
[root@PXC-02 ~]# cat \/tmp\/init.sql\nSET PASSWORD FOR 'root'@'localhost' = PASSWORD('reset-password-node');\n[root@PXC-02 ~]# service mysqld restart\nShutting down MySQL (Percona XtraDB Cluster)..... SUCCESS!\nStarting MySQL (Percona XtraDB Cluster).... SUCCESS!\n[root@PXC-02 ~]# read -s pw\n[root@PXC-02 ~]# mysql -ukedar -p$pw\nWelcome to the MySQL monitor. Commands end with ; or \\g.\nYour MySQL connection id is 18\nServer version: 5.5.34-25.9-log Percona XtraDB Cluster (GPL) 5.5.34-25.9, Revision 607, wsrep_25.9.r3928\n\nCopyright (c) 2009-2013 Percona LLC and\/or its affiliates\nCopyright (c) 2000, 2013, Oracle and\/or its affiliates. All rights reserved.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\n(kedar@localhost) [(none)]>SET PASSWORD FOR 'kedar'@'localhost' = PASSWORD('this-one-replicates');\nQuery OK, 0 rows affected (0.04 sec)\n\n\n... on other node ...\n\n\n[root@PXC-03 ~]# mysql -ukedar -p$pw\nWelcome to the MySQL monitor. Commands end with ; or \\g.\nYour MySQL connection id is 11\nServer version: 5.5.34-25.9-log Percona XtraDB Cluster (GPL) 5.5.34-25.9, Revision 607, wsrep_25.9.r3928\n\nCopyright (c) 2009-2013 Percona LLC and\/or its affiliates\nCopyright (c) 2000, 2013, Oracle and\/or its affiliates. All rights reserved.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\n(kedar@localhost) [(none)]>\n<\/pre>\n\n\n\noh wow… thanks to you TIL that we do not need to restart all the nodes on Galera Cluster to reset root password.<\/span><\/p>\n\n\n\nConclusion<\/h2>\n\n\n\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"Reset MySQL root password for Galera Cluster ( Percona Xtradb \/ MariaDB )\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[864,8,377,865],"tags":[973,972,421,974,422],"class_list":{"0":"post-2401","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql-galera-cluster","7":"category-mysql","8":"category-mysql-articles","9":"category-percona-xtradb-cluster","10":"tag-reset-galera-cluster-password","11":"tag-reset-mysql-root-password","12":"tag-reset-password","13":"tag-reset-pxc-root-password","14":"tag-skip-grant-tables"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2401","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2401"}],"version-history":[{"count":8,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2401\/revisions"}],"predecessor-version":[{"id":3327,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2401\/revisions\/3327"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2401"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2401"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}