Homestead: Root mysql login not working

[josecybertill]

I updated to the latest version of the Homestead box and am no longer able to login to mySQL as root, I used to do it using 'secret' as the password, has something changed?

[svpernova09]

Please fill out the entire issue template so we don't have to ask you what versions of everything you're using to properly help you. https://github.com/laravel/homestead/issues/new

[josecybertill]

Hi @svpernova09, here are the details, just so you know, it may be 'settler' related, same problem with box version 1.1.0 and 1.0.1

I have tested with 0.2.7 and it works just fine, haven't tested any other box versions.

Versions

• Vagrant 1.9.1
• Provider: Virtualbox
• Homestead: I installed manually, don't have the homestead command, but am in the latest commit '5570bcb', I believe tagged as 4.0.5

Host operating system

Windows 10

Homestead.yaml

---
ip: "192.168.10.10"
memory: 2048
cpus: 1
provider: virtualbox

authorize: ~/.ssh/id_rsa.pub

keys:
  - ~/.ssh/id_rsa

folders:
  - map: ~/projects
    to: /home/vagrant/projects

sites:
  - map: cybertill.app
    to: /home/vagrant/projects/info

[svpernova09]

Yep, the issue is in settler. Here https://github.com/laravel/settler/blob/master/scripts/provision.sh#L186-L189 we are setting debconf-set-selections for the mysql-community-server package but then we install the mysql-server package. So MySQL thinks we're not setting a password, so sets the auth to a socket plugin.

This will require a box update to fix, but in the meantime you can add this line to your Homestead after.sh script to fix the issue:

mysql --user="homestead" --password="secret" -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secret';"

[svpernova09]

Also worth mentioning the homestead mysql user should be able to do most things that root can. since we're doing:

mysql --user="root" --password="secret" -e "GRANT ALL ON *.* TO 'homestead'@'0.0.0.0' IDENTIFIED BY 'secret' WITH GRANT OPTION;"
mysql --user="root" --password="secret" -e "GRANT ALL ON *.* TO 'homestead'@'%' IDENTIFIED BY 'secret' WITH GRANT OPTION;"

[josecybertill]

Thanks @svpernova09 will try it out tomorrow, I understand the benefits of using the homestead user, however, I am tied to an app that requires root access.

[svpernova09]

no worries, the one liner https://github.com/laravel/homestead/issues/453#issuecomment-278694703 in that comment should get you fixed up.

[josecybertill]

Thanks @svpernova09, however, I am getting this error with the 'one liner'

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

[svpernova09]

You have to run it as the homestead user

mysql --user="homestead" --password="secret" -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secret';"

[josecybertill]

thanks @svpernova09, works really well