Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
Description
mysql_install_db prints at the end
echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !" |
echo "To do so, start the server, then issue the following commands:" |
echo |
echo "'$bindir/mysqladmin' -u root password 'new-password'" |
echo "'$bindir/mysqladmin' -u root -h $hostname password 'new-password'" |
echo |
echo "Alternatively you can run:" |
echo "'$bindir/mysql_secure_installation'" |
echo |
But in fact it creates four root accounts, root@localhost, root@hostname, root@127.0.0.1, root@::1, the last two only accessible with skip_name_resolve=1 and (for the last one) with ipv6 support.
How to fix the instruction to cover all root accounts? There are various possibilities.
Option one
echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !" |
echo "To do so, start the server with the --skip-name-resolve, then issue the following commands:" |
echo |
echo "'$bindir/mysqladmin' -u root password 'new-password'" |
echo "'$bindir/mysqladmin' -u root -h $hostname password 'new-password'" |
echo "'$bindir/mysqladmin' -u root -h 127.0.0.1 password 'new-password'" |
echo "'$bindir/mysqladmin' -u root -h ::1 password 'new-password'" |
echo |
echo "Alternatively you can run:" |
echo "'$bindir/mysql_secure_installation'" |
echo |
This explicitly tells the user to "start the server with the --skip-name-resolve", not the easiest approach.
Option two
echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !" |
echo "To do so, start the server, then issue the following commands:" |
echo |
echo "'$bindir/mysql' -u root -e 'UPDATE mysql.user SET password=password(\"new-password\") WHERE user=\"root\";flush privileges'" |
echo |
echo "Alternatively you can run:" |
echo "'$bindir/mysql_secure_installation'" |
echo |
This updates all root users and only one command instead of four (or two). But more sensitive to correct pasting and to quotes inside a password. And it does exactly what the manual tells not to do — direct update of a privilege table.
Option three
echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !" |
echo "To do so, start the server, then issue the following commands:" |
echo |
echo "'$bindir/mysql' -u root -e 'SET PASSWORD FOR root@\"$hostname\" = PASSWORD(\"new-password\")'" |
echo "'$bindir/mysql' -u root -e 'SET PASSWORD FOR root@\"127.0.0.1\" = PASSWORD(\"new-password\")'" |
echo "'$bindir/mysql' -u root -e 'SET PASSWORD FOR root@\"::1\" = PASSWORD(\"new-password\")'" |
echo "'$bindir/mysql' -u root -e 'SET PASSWORD FOR root@\"localhost\" = PASSWORD(\"new-password\")'" |
echo |
echo "Alternatively you can run:" |
echo "'$bindir/mysql_secure_installation'" |
echo |
No direct updates, but four commands to copy, four times to repeat the password. And as sensitive to correct pasting and quotes as the option two.
Option four
echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !" |
echo "To do so, start the server, then run:" |
echo "'$bindir/mysql_secure_installation'" |
echo |
The shortest possible. But will ask more questions than just about root password.
Option five
If our task is to make it as easy as possible for the end user, how about this:
mariadb_set_password --user=root [--for-user=root] [--for-hostname=hostname, hostname] |
If for-hostname is not set, then it would set for all accounts for that user. If for-user is not set, then change the account for the current user.
This would also work nicely for installations where the root user is renamed.
The benefit of a script is that in that one we can, in case of no hostname, we can directly manipulate that user table if we would need to do that.
Another option is to extend mariadb_admin to do this, but with the same options as above.
This would allow us to write:
echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !" |
echo "To do so, start the server, then run:" |
echo "mariadb_set_password --user=root" |
echo "or" |
echo "'$bindir/mysql_secure_installation'" |
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Activity
10.4+ versions are also affected, they print this text if unix_socket authentication was disabled.
In this case I suggest to keep only the part about mysql_secure_installation, as a clear official recommendation.
It doesn't eliminate other possibilities which advanced users would know about anyway and could use if they want to.
The printout is mainly targeted at first-timers, and for them it makes most sense to use the interactive and most secure option. It is not that bad that it asks more questions than just a password change – it is not very long and can be helpful.
As part of MDEV-23494 I was planning on adding a mysql_install_db --auth-root-password-env and --auth-root-hostname option. I've got a shell/sql escaping mechanism ready - https://github.com/MariaDB/mariadb-docker/pull/356 .
I'd rather never see an option 2 regardless of release version, because users will try it later anyway, put it in blogs, SO answers and cause everyone to suffer.
Like https://mariadb.com/kb/en/root-password-change-how-can-i-stop-the-mysqld-in-the-mariadb-container/ it seems the use of mysql_secure_installation is still quite popular. There does seem to be an excessive amount of copy/paste of it (https://github.com/vitessio/vitess/pull/7318, https://github.com/MariaDB/mariadb-docker/blob/master/docker-entrypoint.sh#L277), so I'd like to see some cleanup of it eventually so it has more portability than it really needs (https://github.com/vitessio/vitess/pull/7460).
My preference is option 3 so people actually know about `set password`.
Lets remove mysql references as if people search there for mysql_install_db they'll see 8.0 removed documentation which won't help.
When can we drop the root@<hostname>? Its really ugly and depends on proper reverse DNS resolution.
I too like option 3, though would reverse it, i.e. mention mysql_secure_installation first, and then the commands to directly set the passwords as the alternative. This gives beginner users an easy method, and leaves the handy commands there for pro users to copy/paste.
Also, changing 'Alternatively you can run:' to 'Alternatively you can run (after starting the server):' makes the text somewhat more clear imho, even if it is mentioned earlier already.
Probably option 3 is the best (with Roel's reverse idea considered, it may be counter productive if the text scrolls up on the terminal).
As a side note, what if we create some stored procedure like mysql.set_root_password('new_password') with the new root password as an argument, and update instructions to call it? Then no matter how many local root accounts are present and what method of update we use, it will be a single stored procedure call for the user.
Option 3 one runs mysql four times as passwordless root, so SET PASSWORD FOR root@localhost must be the last. Unless one has protocol=tcp in the ~/.my.cnf in which case some other SET PASSWORD line must be the last. One way out of it would be to run mysql only once, like
echo "To do so, start the server, then issue the following commands:" |
echo |
echo "'$bindir/mysql' -u root -e 'SET PASSWORD FOR root@\"$hostname\" = PASSWORD(\"new-password\"); SET PASSWORD FOR root@\"127.0.0.1\" = PASSWORD(\"new-password\")'; SET PASSWORD FOR root@\"::1\" = PASSWORD(\"new-password\")'; SET PASSWORD FOR root@\"localhost\" = PASSWORD(\"new-password\")'" |
echo |
or even
echo "To do so, start the server, then issue the following commands:" |
echo |
echo "'$bindir/mysql' -u root" |
echo "SET PASSWORD FOR root@'$hostname' = PASSWORD('new-password');" |
echo "SET PASSWORD FOR root@'127.0.0.1' = PASSWORD('new-password')';" |
echo "SET PASSWORD FOR root@'::1' = PASSWORD('new-password')';" |
echo "SET PASSWORD FOR root@localhost = PASSWORD('new-password');" |
echo "\q" |
echo |
perhaps modified as
echo "To do so, start the server, then issue the following commands:" |
echo |
echo "'$bindir/mysql' -u root" |
echo "SET @password= PASSWORD('new-password');" |
echo "SET PASSWORD FOR root@'$hostname' = @password;" |
echo "SET PASSWORD FOR root@'127.0.0.1' = @password;" |
echo "SET PASSWORD FOR root@'::1' = @password;" |
echo "SET PASSWORD FOR root@localhost = @password;" |
echo "\q" |
echo |
but really, I'd rather prefer a simple solution and just go with option four.
I'm ok with option four. I like the last option above too.
I'd rather not end up with another less maintained script like option 5.
I think the main users are going to be packagers/sysadmins rather than first time users, but if we can account for both, more the merrier.
It doesn't really affect 10.4+, only 10.2 and 10.3, as 10.4+ creates different accounts and prints an entirely different message.
For 10.2/10.3, I think it's sufficient to print something like