Sign inTry Free

Recovering from Loss of Root Credentials

On this page

If the affected node is using Tools for managing the cluster you may be able to reset a root password using the the sdb-admin change-root-password command .

If the root credential is lost on only the master aggregator and there are existing child aggregators in the cluster, then a child aggregator can be promoted to master aggregator. The old master aggregator can be deleted after a child aggregator is deployed in its place.. See Tools: Aggregator Failures.

If all else fails, the insecure_accept_any_password_for_all_users option can be set to true at startup. For example:

sudo /ebs/memsql/master-3306-MI63dd6b39/memsqld --defaults-file=/ebs/memsql/master-3306-MI63dd6b39/memsql.cnf \
--pid-file=/ebs/memsql/master-3306-MI63dd6b39/data/memsqld.pid --user=memsql --insecure_accept_any_password_for_all_users=true
----------------------------------------------------------------------------------------
2019-12-07 01:52:09.822   WARN: MemSQL is running with option "insecure_accept_any_password_for_all_users". Please fix your user account and turn off this mode.
2019-12-07 01:52:09.822   INFO: Successfully became user 'memsql' (uid 999, gid 999)
2019-12-07 01:52:10.059   INFO: Entering command loop
2019-12-07 01:52:11.545   INFO: Initializing OpenSSL
2019-12-07 01:52:11.546   INFO: MemSQL version hash: e973c625ae6d372c2d41d39b19612202c244fd7a (Sun Oct 20 22:22:04 2019 -0400)
2019-12-07 01:52:11.630   INFO: ./memsqld: ready for connections.
2019-12-07 01:52:11.630   INFO: Version:  '6.8.11'  Socket:  '/ebs/memsql/master-3306-MI63dd6b39/data/memsql.sock'  Port:  '3306'
2019-12-07 01:52:11.723   INFO: Replaying snapshots/memsql_snapshot_0: started replaying from offset 0
2019-12-07 01:52:11.724   INFO: Replaying snapshots/memsql_snapshot_0: Log record expected at offset 89.
2019-12-07 01:52:11.724   INFO: Replaying snapshots/memsql_snapshot_0: completed replaying at offset 89

A node started with the --insecure_accept_any_password_for_all_users=true option will allow a user to log in without a password only once. Subsequent login attempts will fail.

The following warning will be logged in the memsql.log file:

WARN: Thread 99996: AuthenticateUser: User logged in using "insecure_accept_any_password_for_all_users" mode. Please restart the server without this option after fixing your accounts.

Distributed User

The distributed user is an internal user that has all privileges in the system. You can sign in with this internal user only if the non-sync global variable, insecure_accept_any_password_for_all_users=true. This variable can be set only at startup. It is not available in the single-box mode.

The distributed user can be used to log in if you have dropped the root user.

memsql> show users;
Empty set (0.000 sec)

memsql> select current_user();
+-----------------------+
| current_user()        |
+-----------------------+
| distributed@localhost |
+-----------------------+
1 row in set (0.000 sec)

memsql> create user root@'%';
Query OK, 0 rows affected (0.009 sec)

memsql> grant all on *.* to root@'%';
Query OK, 0 rows affected (0.009 sec)

memsql> show grants for root;
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.000 sec)

Last modified: July 27, 2023

Was this article helpful?

On this page

Was this article helpful?