# # This play will create databases and user for an application. # It can be run like so: # # ansible-playbook -c local -i 'localhost,' create_dbs_and_users.yml -e@./db.yml # # If running ansible from a python virtualenv you will need a command like the following # # ansible-playbook -c local -i 'localhost,' create_dbs_and_users.yml -e@./db.yml -e "ansible_python_interpreter=$(which python)" # # where the content of db.yml contains the following dictionaries # # database_connection: &default_connection # login_host: "mysql.example.org" # login_user: "root" # login_password: "super-secure-password" # DEFAULT_ENCODING: "utf8" # databases: # reports: # state: "present" # encoding: "{{ DEFAULT_ENCODING }}" # <<: *default_connection # application: # state: "present" # encoding: "{{ DEFAULT_ENCODING }}" # <<: *default_connection # database_users: # migrate: # state: "present" # password: "user-with-ddl-privs" # host: "%" # privileges: # - "reports.*:SELECT,INSERT,UPDATE,DELETE,ALTER,CREATE,DROP,INDEX" # - "wwc.*:SELECT,INSERT,UPDATE,DELETE,ALTER,CREATE,DROP,INDEX" # <<: *default_connection # runtime: # state: "present" # password: "user-with-dml-privs" # host: "%" # privileges: # - "reports.*:SELECT" # - "wwc.*:SELECT,INSERT,UPDATE,DELETE" # <<: *default_connection - name: Create databases and users hosts: all gather_facts: False tasks: # Install required library, currently this needs to be available # to system python. - name: install python mysqldb module pip: name={{item}} state=present with_items: - MySQL-python - name: create mysql databases mysql_db: db: "{{ item.name}}" state: "{{ item.state }}" encoding: "{{ item.encoding }}" login_host: "{{ item.login_host }}" login_user: "{{ item.login_user }}" login_password: "{{ item.login_password }}" with_items: "{{ databases }}" tags: - dbs - name: create mysql users and assign privileges mysql_user: name: "{{ item.name }}" state: "{{ item.state | default('present') }}" priv: "{{ '/'.join(item.privileges) }}" password: "{{ item.password }}" host: "{{ item.host }}" login_host: "{{ item.login_host }}" login_user: "{{ item.login_user }}" login_password: "{{ item.login_password }}" append_privs: yes with_items: "{{ database_users }}" tags: - users