Make MySQL root user password settings more reliable about ansible-role-mysql HOT 7 CLOSED

I'll add, it also doesn't sit well with me that the securing of the passwords only happens the first run.

Possible Solution: what about reading in the password from .my.cnf and using that as the password to be set if it exists? Otherwise use the one passed in from the playbook.

This kind of bakes in a "fix" where a person changes the root password and updated the .my.cnf that all the root passwords are also updated the next time ansible is run.

from ansible-role-mysql.

Per the mysql documentation, on *nix based installations there is also a 'root'@'host_name' user. I don't see this user's password being updated in the secure-installation.yml.

Last time I worked on that part of the setup, I remember manually inspecting the accounts, and didn't see any other root user accounts that needed updating (at least on Ubuntu or CentOS). But if there are other accounts, they should definitely be updated.

We should verify that there are other user records that need updating (if so, this is a more serious bug!), and then I also like the idea of making the root user password setting able to happen beyond the first run of the playbook; it would even help with things like Drupal VM.

from ansible-role-mysql.

I had actually just tested this on a centos vagrant box before creating this issue.

mysql> select Host,User from mysql.user;
+--------------------------+------+
| Host                     | User |
+--------------------------+------+
| 127.0.0.1                | root |
| ::1                      | root |
| localhost                |      |
| localhost                | root |
| vagrantdev.example.com |      |
| vagrantdev.example.com | root |
+--------------------------+------+
6 rows in set (0.00 sec)

from ansible-role-mysql.

Hmm, right you are—it looks like there's an additional root account set up for the hostname. (Honestly, MySQL—especially on Debian—is one of the most annoying packages to configure, since it preconfigures a ton of junk out of the box!).

from ansible-role-mysql.

Working on this now...

from ansible-role-mysql.

One method I tried to get all the current root MySQL user accounts in a list to pass to the 'update password' command was:

- name: Get a list of hosts for the MySQL root account.
  command: mysql -e 'SELECT Host from mysql.user WHERE User = "root";'
  register: mysql_root_hosts
  changed_when: false

Then you can access mysql_root_hosts.stdout_lines[1:] to remove the first 'Host' entry. However, doing this pops the current hostname into an arbitrary order (e.g. if you have a hostname of ansible, it might appear at the top of the list, but if you have zebra, it's at the bottom). This causes the password change to fail, since localhost must be at the end.

I was trying contortions with pop and remove and other Python/Jinja-based filtering to try to pull out localhost from the list then run it last, but I couldn't.

I think, for now, the change I'm going to commit for this issue should work for 99% of the use cases I've seen—plus it should now allow users to change the MySQL root password (which couldn't be done before), which is a nice bonus!

from ansible-role-mysql.

@solomongifford - Could you open a separate issue for the case where running a playbook over a local connection causes {{ ansible_hostname }} to break?

from ansible-role-mysql.