db host wildcard

Archives Ansible Project
1

Hi there

I’m trying to set up mysql so that I can access the database from a remote client.

This is how I manually do that:

GRANT ALL PRIVILEGES ON . TO ‘root’@‘%’ IDENTIFIED BY ‘some pass’ WITH GRANT OPTION;

How do I achieve that with ansible?

I’m having problems getting ansible to work with the ‘%’ as a host option.

2

Can you please share the command you are sending to ansible as well as the ansible version?

Thanks!

3

You can use the mysql_user module for this, see the examples in http://docs.ansible.com/mysql_user_module.html

Specifically, you’ll want something like this example:

# Creates database user 'bob' and password '12345' with all database privileges and 'WITH GRANT OPTION'
- mysql_user: name=bob password=12345 priv=*.*:ALL,GRANT state=present
4

Hi James

I’ve poured over the docs on the ansible site and I can’t find an example with the host being set to wildcard.

Did you specify the host in your example below?

5

Here’s the command I’m sending:

  • name: Create/assign database user to db and grant permissions
    mysql_user: name={{ item.env.db_user }}
    password={{ item.env.db_password }}
    priv={{ item.env.db_name | default(item.site_name) }}.*:ALL,GRANT
    state=present
    login_host={{ item.env.db_host | default(‘localhost’) }}
    with_items: web_site

Where the variable web_site.env.db_host is the one I want to set to wildcard so I can access from remote.

With ansible version 1.6.

6

The “host” parameter for that module controls the host portion of the user login information. So you should set host=“*” to allow connections from any remote host.

7

host=“*” allows ansible to provision the server, but doesn’t allow me to connect via remote db client.

8

Is anyone who can successfully provision with ansible and configure the database host so that you
can access the database from a remote location?

(I’m trying to get this working a local Vagrant setup where I use Heidi to access the guest Ubuntu machine

host=“%” is the only option that will allow me to remote access the database.

host=“*” does not work for me.

9

That sounds about right, you want a 'sql wildcard' so % not *.

Another gotcha - mysql treats 'localhost' specially so you might want
a grant for that as well as %.

(I don't think this has anything to do with Ansible BTW, it's just how
mysql works).

10

Do you mean that host=“%” does not work for you as a parameter to mysql_user module? This would be really strange because I have tested it with Ansible 1.6.1 and it works as expected. If you can connect from the local machine, run the following SQL “use mysql;select * from user;” to see what has been added (or what is missing).

11

Excellent, thanks for confirming what I had found.

12

host=“%” works perfectly for remote access.