Changing the Root Password

You can use Toolbox to update the SingleStore database root password in a cluster. The following steps can be performed online as it is not necessary to restart the cluster's hosts or nodes to update the database root password.

In the following examples:

• The database resides on host1

• The sync_permissions variable is enabled

Perform the following steps to change the database root password.

1. Connect to the Master Aggregator host via ssh (or to the host where Toolbox has been installed) and verify that all hosts and nodes are online and connected to the cluster by running sdb-admin list-nodes.

   Shell

   Copy

   sdb-admin list-nodes

2. Change the database root password on all nodes in the cluster by running the sdb-admin change-root-password with the --all option. This will update the database root password for all nodes by syncing it across the cluster.

   Shell

   Copy

   sdb-admin change-root-password --all
   ***
   Enter the password for the database user: 
   Toolbox is about to run 'memsqlctl change-root-password --password ●●●●●●' on the following nodes:
     · On host host1:
       - 84CF1CCED09A4005F57D74204A50E302F37D9F61
       - B863FC18E39B7898B07E90C31E1C25FB832E73EC
   
   Would you like to continue? [y/N]: y
   ✓ Changed password for nodes on host host1
   Operation completed successfully

3. After the database root password has been updated, check the cluster status.

   Shell

   Copy

   sdb-admin list-nodes

   For each node where the Connectable column reflects True, the password has been updated successfully.

   For each node where the Connectable column reflects False, perform the following steps.

   1. Use the singlestore client (or the MySQL client) to connect to the host that the node resides on. You may need to use the former database root password to log in.

      Shell

      Copy

      singlestore -h host1 -P <port> -u root -p<password>

   2. If you cannot log in with the new database root password, the database root password has not been updated for the node.

      Update the database root password for all nodes on this host by running the following SQL statement.

      SQL

      Copy

      GRANT ALL ON *.* TO 'root'@'host1' IDENTIFIED BY '<new-database-root-password>' WITH GRANT OPTION;

   3. To update the memsqlctl account on host1 with the new database root password, connect to host1 via ssh and run the following command.

      Shell

      Copy

      sudo memsqlctl change-root-password --password "<new-database-root-password>";

      +-------+------------+--------+------+---------------+---------+
      | Index | MemSQL ID  |  Role  | Port | Process State | Version |
      +-------+------------+--------+------+---------------+---------+
      | 1     | 84CF1CCED0 | Master | 3306 | Running       |         |
      | 2     | B863FC18E3 | Leaf   | 3307 | Running       | 7.8.17  |
      | 3     | All Nodes  |        |      |               |         |
      +-------+------------+--------+------+---------------+---------+
      Select an option: 3
      memsqlctl will perform the following actions
        · On node with node ID 84CF1CCED09A4005F57D74204A50E302F37D9F61:
          - memsqlctl will update the password it has on file for this node
      
        · On node with node ID B863FC18E39B7898B07E90C31E1C25FB832E73EC:
          - Run 'SET PASSWORD FOR root@% = PASSWORD()'
      
      Would you like to continue? [y/N]: y
      ✓ Corrected memsqlctl's record of an existing password for node with node ID 84CF1CCED09A4005F57D74204A50E302F37D9F61
      ✓ Set new password for node with node ID B863FC18E39B7898B07E90C31E1C25FB832E73EC