How to change the root password after memsql install?

Question

I've installed MemSQL community edition (single host cluster) and all is working well. I need to allow remote access to the database, but MemSQL installs the user root without a password. If I open up the 3306 port on the firewall, memSQL happily allows anyone to log in as root without a password.

I've tried to change the root user password via

mysqladmin -u root -h 127.0.0.1  password abc123

but I get the error of

mysqladmin: unable to change password; error: 'Unknown system variable 'password''

I also tried to change after connecting as root. all of these fail:

mysql> SET PASSWORD = 'abc123';
ERROR 1193 (HY000): Unknown system variable 'PASSWORD'

mysql> SET PASSWORD = PASSWORD('abc123');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('abc123')' at line 1

mysql> SET PASSWORD = OLD_PASSWORD('abc123');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('abc123')' at line 1

So I'm stuck. The docs for MemSQL are pretty lightweight on this issue as well, and they don't seem to have a community page where I can ask about this. I figure it's something really simple that I'm trying to do, not sure why it's so difficult, and for that matter, I'm not sure why MemSQL defaults to no security at all.

Any ideas?

Answer 1

In the June 2015 release of MemSQL Ops, you can now change the root password with one command using the memsql-update-root-password command. See these links for more information:

• http://docs.memsql.com/4.0/ops/cli/MEMSQL-UPDATE-ROOT-PASSWORD/
• http://docs.memsql.com/4.0/admin/security/#adding-or-updating-the-root-password

I hope this helps!