Bug #81965 | mysqladmin password fails silently if user plugin is not password auth | ||
---|---|---|---|
Submitted: | 22 Jun 2016 6:55 | Modified: | 9 Aug 2016 10:11 |
Reporter: | Philippe Florent | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Command-line Clients | Severity: | S2 (Serious) |
Version: | 5.7 | OS: | Debian (8.5) |
Assigned to: | CPU Architecture: | Any | |
Tags: | root password access denied headless install |
[22 Jun 2016 6:55]
Philippe Florent
[22 Jun 2016 17:21]
Lars Tangvald
Hi, What is new in 5.7 is that it tries to be more secure by default, so if you enter an empty password during installation it will enable unix socket authentication for the root user. What this means is simply that only the unix root user can log in as root, so: sudo mysql -uroot should work. If you later wish to set a password, log in as above and change the password with ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'yourpassword'; Setting the password by directly altering the mysql.user table with an UPDATE statement is discouraged in 5.7, and in this case would not work because the authentication method has been set to unix socket and not password. Hope that helps!
[22 Jun 2016 17:25]
Lars Tangvald
As an extra note, the UPDATE statement might work if you also set the plugin field to 'mysql_native_password', but we recommend using ALTER USER instead.
[22 Jun 2016 17:30]
Lars Tangvald
However, mysqladmin not setting the password when socket auth is enabled sounds like a bug, and should be investigated further
[23 Jun 2016 7:33]
Philippe Florent
socket or not, I cant login with root so alternatively is there a way to setup a default password for a headless install of the community server ?
[23 Jun 2016 7:46]
Lars Tangvald
sudo debconf-set-selections <<EOT mysql-community-server mysql-community-server/root-pass password mypass mysql-community-server mysql-community-server/re-root-pass password mypass EOT then install as before.
[23 Jun 2016 7:56]
Philippe Florent
ok I can login with root but how can I provide the debian package not to use any password or a default one ? I use sudo dpkg -i /home/eradmin/mysql57/mysql-community-server_5.7.12-1debian8_amd64.deb
[23 Jun 2016 8:05]
Lars Tangvald
Using the above debconf-set-selection command before running the dpkg command will let you set a default password, just replace mypass.
[23 Jun 2016 8:06]
Lars Tangvald
Ah, I may have misunderstood, you mean setting it not to use any password and no socket auth?
[23 Jun 2016 8:15]
Lars Tangvald
To set the server back to normal password authentication and empty password after installing with a blank password, you should be able to run: sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '';" If you installed with a password, use -uroot -p instead of sudo
[23 Jun 2016 8:27]
Philippe Florent
ok I found it sudo dpkg-preconfigure --frontend=noninteractive :-)
[23 Jun 2016 10:03]
Lars Tangvald
Were you able to get your setup working again?
[11 Jul 2016 9:32]
Philippe Florent
??? well after the setup ran , the root password was invalidated I had to login as root, then run the mysql client as root user, change the pass... works so fine the only issue (dont know if it is related) is that the SET PASSWORD/ALTER USER does not work on the command line still waiting someon to come back to me for this http://bugs.mysql.com/bug.php?id=82051&thanks=4 as a workaround Iset the password field in mysql.user table, but I have to figure the value first, a pain in the a$$
[9 Aug 2016 9:38]
Lars Tangvald
For the issue with setting the password with the command line: Can you reproduce with a completely plain password? e.g. just 'pass' Might be a specific symbol that isn't passed correctly through
[9 Aug 2016 10:11]
Lars Tangvald
Original synopsis: mysql 5.7 : empty password during instals invalidates the root account If MySQL is installed with an empty password on Debian/Ubuntu, the root user as UNIX socket authentication enabled for better default security. If the user then tries to set a password using mysqladmin, the command will appear to succeed, but it won't be possible to log in with that password, since mysqladmin won't set the user plugin to mysql_native_password.
[12 Aug 2016 11:00]
Lars Tangvald
Posted by developer: Same issue shows up when running mysql_secure_installation, as noted in https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1610574