Skip to content

Instantly share code, notes, and snippets.

@basz
Last active October 13, 2015 19:20
Show Gist options
  • Select an option

  • Save basz/c74dff4fb6a333433ec6 to your computer and use it in GitHub Desktop.

Select an option

Save basz/c74dff4fb6a333433ec6 to your computer and use it in GitHub Desktop.
---
- debug: msg="{{ item | to_json }}"
with_items:
- unzip
- python-pip
- name: mysql | s3 import | setup
apt: name={{ item }} state=present
with_items:
- unzip
- python-pip
# - name: mysql | s3 import | setup
# apt: name=python-pip state=present
- name: mysql | s3 import | setup
pip: name={{ item }} state=present
with_items:
- boto
- name: mysql | s3 import | retrieve latest backup object
s3:
mode: getstr
aws_access_key: "{{ mysql_s3_sql_backup_key }}"
aws_secret_key: "{{ mysql_s3_sql_backup_secret }}"
region: "{{ mysql_s3_sql_backup_region }}"
bucket: "{{ mysql_s3_sql_backup_bucket }}"
object: "{{ mysql_database.name }}/latest.txt"
register: result_latest
ignore_errors: yes
- set_fact: target_path="/tmp/db-import-{{ mysql_database.name }}-latest.sql.gz"
- name: mysql sql import | get latest backup
s3:
mode: get
aws_access_key: "{{ mysql_s3_sql_backup_key }}"
aws_secret_key: "{{ mysql_s3_sql_backup_secret }}"
region: "{{ mysql_s3_sql_backup_region }}"
bucket: "{{ mysql_s3_sql_backup_bucket }}"
object: "/{{ mysql_database.name }}/{{ result_latest.contents | trim }}"
dest: "{{ target_path }}"
register: result_object
ignore_errors: yes
when: "result_latest.msg == 'GET operation complete'"
- name: mysql sql import | decompress and import
shell: "gzip --decompress --to-stdout {{ target_path }} | mysql {{ mysql_database.name }} && rm {{ target_path }}"
when: "result_latest.msg == 'GET operation complete' and result_object.msg == 'GET operation complete'"
---
- hosts: db
tags:
- db
roles:
# - { role: geerlingguy.mysql }
pre_tasks:
- include_vars: group_vars/db-secret.yml
- name: mysql | s3 import | setup
apt: name={{ item }} state=present
with_items:
- unzip
- python-pip
tasks:
- name: mysql | importing sql from backup
include: "roles/provision.mysql/tasks/import_s3_backup.yml mysql_database={{ item }}"
with_items: "{{ mysql_databases | list }}"
when: "{{ item.import_s3_backup | default(false) }} == true"
rattletrap:provisioning bas$ time ansible-playbook -i ./ansible/inventory/local.yml ./ansible/playbook-provision-db.yml --limit db --tags db
PLAY ***************************************************************************
TASK [setup] *******************************************************************
ok: [s02.localhost -> localhost]
TASK [include_vars] ************************************************************
ok: [s02.localhost -> localhost]
TASK [mysql | s3 import | setup] ***********************************************
ok: [s02.localhost] => (item=unzip,python-pip)
TASK [mysql | importing sql from backup] ***************************************
included: roles/provision.mysql/tasks/import_s3_backup.yml for s02.localhost => (item={u'collation': u'utf8_general_ci', u'import_s3_backup': True, u'replicate': 0, u'name': u'plhw_stg_sandalinos', u'encoding': u'utf8'})
included: roles/provision.mysql/tasks/import_s3_backup.yml for s02.localhost => (item={u'collation': u'utf8_general_ci', u'import_s3_backup': True, u'replicate': 0, u'name': u'plhw_dev_sandalinos', u'encoding': u'utf8'})
included: roles/provision.mysql/tasks/import_s3_backup.yml for s02.localhost => (item={u'collation': u'utf8_general_ci', u'import_s3_backup': True, u'replicate': 0, u'name': u'plhw_prd_sandalinos', u'encoding': u'utf8'})
included: roles/provision.mysql/tasks/import_s3_backup.yml for s02.localhost => (item={u'collation': u'utf8_general_ci', u'import_s3_backup': True, u'replicate': 0, u'name': u'plhw_stg_data', u'encoding': u'utf8'})
included: roles/provision.mysql/tasks/import_s3_backup.yml for s02.localhost => (item={u'collation': u'utf8_general_ci', u'import_s3_backup': True, u'replicate': 0, u'name': u'plhw_dev_data', u'encoding': u'utf8'})
included: roles/provision.mysql/tasks/import_s3_backup.yml for s02.localhost => (item={u'collation': u'utf8_general_ci', u'import_s3_backup': True, u'replicate': 0, u'name': u'plhw_prd_data', u'encoding': u'utf8'})
TASK [debug msg={{ item | to_json }}] ******************************************
skipping: [s02.localhost] => (item=unzip)
skipping: [s02.localhost] => (item=python-pip)
TASK [mysql | s3 import | setup] ***********************************************
skipping: [s02.localhost] => (item=)
TASK [mysql | s3 import | setup] ***********************************************
skipping: [s02.localhost] => (item=boto)
TASK [mysql | s3 import | retrieve latest backup object] ***********************
^C
PLAY RECAP *********************************************************************
s02.localhost : ok=4 changed=0 unreachable=0 failed=0
Using /Users/bas/Documents/Projects/PLHW.Sandalinos.ProjectHealthyFeet/provisioning/ansible.cfg as config fileon-db.yml --limit db --tags db -vvvv
1 plays in ./ansible/playbook-provision-db.yml
Loaded callback default of type stdout, v2.0
PLAY ***************************************************************************
TASK [setup] *******************************************************************
<s02.localhost> ESTABLISH SSH CONNECTION FOR USER: root
<s02.localhost> SSH: EXEC ssh -C -vvv -o ForwardAgent=yes -o ControlPersist=60s -o 'IdentityFile="/Users/bas/.vagrant.d/insecure_private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=root -o ConnectTimeout=10 -o ControlPath=/Users/bas/.ansible/cp/ansible-ssh-%h-%p-%r -tt s02.localhost mkdir -p "$(echo $HOME/.ansible/tmp/ansible-tmp-1444763921.47-16465435842533)" && echo "$(echo $HOME/.ansible/tmp/ansible-tmp-1444763921.47-16465435842533)"
<s02.localhost> PUT /var/folders/8q/kjslxh3s4zg8ghmz466mvqk40000gn/T/tmp4k0yFp TO /root/.ansible/tmp/ansible-tmp-1444763921.47-16465435842533/setup
<s02.localhost> SSH: EXEC sftp -b - -C -vvv -o ForwardAgent=yes -o ControlPersist=60s -o 'IdentityFile="/Users/bas/.vagrant.d/insecure_private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=root -o ConnectTimeout=10 -o ControlPath=/Users/bas/.ansible/cp/ansible-ssh-%h-%p-%r [s02.localhost]
<s02.localhost> ESTABLISH SSH CONNECTION FOR USER: root
<s02.localhost> SSH: EXEC ssh -C -vvv -o ForwardAgent=yes -o ControlPersist=60s -o 'IdentityFile="/Users/bas/.vagrant.d/insecure_private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=root -o ConnectTimeout=10 -o ControlPath=/Users/bas/.ansible/cp/ansible-ssh-%h-%p-%r -tt s02.localhost LANG=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8 LC_CTYPE=en_US.UTF-8 /usr/bin/python /root/.ansible/tmp/ansible-tmp-1444763921.47-16465435842533/setup; rm -rf "/root/.ansible/tmp/ansible-tmp-1444763921.47-16465435842533/" > /dev/null 2>&1
ok: [s02.localhost -> localhost]
TASK [include_vars] ************************************************************
task path: /Users/bas/Documents/Projects/PLHW.Sandalinos.ProjectHealthyFeet/provisioning/ansible/playbook-provision-db.yml:11
ok: [s02.localhost -> localhost] => {"ansible_facts": {"mysql_databases": [{"collation": "utf8_general_ci", "encoding": "utf8", "import_s3_backup": true, "name": "plhw_stg_sandalinos", "replicate": 0}, {"collation": "utf8_general_ci", "encoding": "utf8", "import_s3_backup": true, "name": "plhw_dev_sandalinos", "replicate": 0}, {"collation": "utf8_general_ci", "encoding": "utf8", "import_s3_backup": true, "name": "plhw_prd_sandalinos", "replicate": 0}, {"collation": "utf8_general_ci", "encoding": "utf8", "import_s3_backup": true, "name": "plhw_stg_data", "replicate": 0}, {"collation": "utf8_general_ci", "encoding": "utf8", "import_s3_backup": true, "name": "plhw_dev_data", "replicate": 0}, {"collation": "utf8_general_ci", "encoding": "utf8", "import_s3_backup": true, "name": "plhw_prd_data", "replicate": 0}], "mysql_root_password": "xht-tsu-u5F-q6X", "mysql_root_password_update": false, "mysql_s3_sql_backup_bucket": "plhw-backup-sql", "mysql_s3_sql_backup_key": "AKIAIFGFQKM7SPGC4VTQ", "mysql_s3_sql_backup_prefix": "development", "mysql_s3_sql_backup_region": "eu-central-1", "mysql_s3_sql_backup_secret": "M+q1eXn0dxCLoAdQyU1UvQ7saXW05LL9jztgddin", "mysql_users": [{"host": "127.0.0.1", "name": "stg_sndlns", "password": "srT-he3-neA-maW", "priv": "*.*:USAGE"}, {"host": "127.0.0.1", "name": "dev_sndlns", "password": "xqs-lY6-D9Y-Yq3", "priv": "*.*:USAGE"}, {"host": "127.0.0.1", "name": "prd_sndlns", "password": "34J-hqW-neX-bl5", "priv": "*.*:USAGE"}, {"host": "127.0.0.1", "name": "stg_data", "password": "srT-he3-neA-maW", "priv": "plhw_stg_data.*:USAGE"}, {"host": "127.0.0.1", "name": "dev_data", "password": "xqs-lY6-D9Y-Yq3", "priv": "plhw_dev_data.*:USAGE"}, {"host": "127.0.0.1", "name": "prd_data", "password": "34J-hqW-neX-bl5", "priv": "plhw_prd_data.*:USAGE"}]}, "changed": false}
TASK [mysql | s3 import | setup] ***********************************************
task path: /Users/bas/Documents/Projects/PLHW.Sandalinos.ProjectHealthyFeet/provisioning/ansible/playbook-provision-db.yml:13
<s02.localhost> ESTABLISH SSH CONNECTION FOR USER: root
<s02.localhost> SSH: EXEC ssh -C -vvv -o ForwardAgent=yes -o ControlPersist=60s -o 'IdentityFile="/Users/bas/.vagrant.d/insecure_private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=root -o ConnectTimeout=10 -o ControlPath=/Users/bas/.ansible/cp/ansible-ssh-%h-%p-%r -tt s02.localhost mkdir -p "$(echo $HOME/.ansible/tmp/ansible-tmp-1444763929.08-278916192705914)" && echo "$(echo $HOME/.ansible/tmp/ansible-tmp-1444763929.08-278916192705914)"
<s02.localhost> PUT /var/folders/8q/kjslxh3s4zg8ghmz466mvqk40000gn/T/tmpkU95n0 TO /root/.ansible/tmp/ansible-tmp-1444763929.08-278916192705914/apt
<s02.localhost> SSH: EXEC sftp -b - -C -vvv -o ForwardAgent=yes -o ControlPersist=60s -o 'IdentityFile="/Users/bas/.vagrant.d/insecure_private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=root -o ConnectTimeout=10 -o ControlPath=/Users/bas/.ansible/cp/ansible-ssh-%h-%p-%r [s02.localhost]
<s02.localhost> ESTABLISH SSH CONNECTION FOR USER: root
<s02.localhost> SSH: EXEC ssh -C -vvv -o ForwardAgent=yes -o ControlPersist=60s -o 'IdentityFile="/Users/bas/.vagrant.d/insecure_private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=root -o ConnectTimeout=10 -o ControlPath=/Users/bas/.ansible/cp/ansible-ssh-%h-%p-%r -tt s02.localhost LANG=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8 LC_CTYPE=en_US.UTF-8 /usr/bin/python /root/.ansible/tmp/ansible-tmp-1444763929.08-278916192705914/apt; rm -rf "/root/.ansible/tmp/ansible-tmp-1444763929.08-278916192705914/" > /dev/null 2>&1
ok: [s02.localhost] => (item=unzip,python-pip) => {"cache_update_time": 0, "cache_updated": false, "changed": false, "item": "unzip,python-pip"}
TASK [mysql | importing sql from backup] ***************************************
task path: /Users/bas/Documents/Projects/PLHW.Sandalinos.ProjectHealthyFeet/provisioning/ansible/playbook-provision-db.yml:20
included: roles/provision.mysql/tasks/import_s3_backup.yml for s02.localhost => (item={u'collation': u'utf8_general_ci', u'import_s3_backup': True, u'replicate': 0, u'name': u'plhw_stg_sandalinos', u'encoding': u'utf8'})
included: roles/provision.mysql/tasks/import_s3_backup.yml for s02.localhost => (item={u'collation': u'utf8_general_ci', u'import_s3_backup': True, u'replicate': 0, u'name': u'plhw_dev_sandalinos', u'encoding': u'utf8'})
included: roles/provision.mysql/tasks/import_s3_backup.yml for s02.localhost => (item={u'collation': u'utf8_general_ci', u'import_s3_backup': True, u'replicate': 0, u'name': u'plhw_prd_sandalinos', u'encoding': u'utf8'})
included: roles/provision.mysql/tasks/import_s3_backup.yml for s02.localhost => (item={u'collation': u'utf8_general_ci', u'import_s3_backup': True, u'replicate': 0, u'name': u'plhw_stg_data', u'encoding': u'utf8'})
included: roles/provision.mysql/tasks/import_s3_backup.yml for s02.localhost => (item={u'collation': u'utf8_general_ci', u'import_s3_backup': True, u'replicate': 0, u'name': u'plhw_dev_data', u'encoding': u'utf8'})
included: roles/provision.mysql/tasks/import_s3_backup.yml for s02.localhost => (item={u'collation': u'utf8_general_ci', u'import_s3_backup': True, u'replicate': 0, u'name': u'plhw_prd_data', u'encoding': u'utf8'})
TASK [debug msg={{ item | to_json }}] ******************************************
task path: /Users/bas/Documents/Projects/PLHW.Sandalinos.ProjectHealthyFeet/provisioning/ansible/roles/provision.mysql/tasks/import_s3_backup.yml:2
skipping: [s02.localhost] => (item=unzip) => {"changed": false, "item": "unzip", "skip_reason": "Conditional check failed", "skipped": true}
skipping: [s02.localhost] => (item=python-pip) => {"changed": false, "item": "python-pip", "skip_reason": "Conditional check failed", "skipped": true}
TASK [mysql | s3 import | setup] ***********************************************
task path: /Users/bas/Documents/Projects/PLHW.Sandalinos.ProjectHealthyFeet/provisioning/ansible/roles/provision.mysql/tasks/import_s3_backup.yml:7
skipping: [s02.localhost] => (item=) => {"changed": false, "item": "", "skip_reason": "Conditional check failed", "skipped": true}
TASK [mysql | s3 import | setup] ***********************************************
task path: /Users/bas/Documents/Projects/PLHW.Sandalinos.ProjectHealthyFeet/provisioning/ansible/roles/provision.mysql/tasks/import_s3_backup.yml:16
skipping: [s02.localhost] => (item=boto) => {"changed": false, "item": "boto", "skip_reason": "Conditional check failed", "skipped": true}
TASK [mysql | s3 import | retrieve latest backup object] ***********************
task path: /Users/bas/Documents/Projects/PLHW.Sandalinos.ProjectHealthyFeet/provisioning/ansible/roles/provision.mysql/tasks/import_s3_backup.yml:21
<s02.localhost> ESTABLISH SSH CONNECTION FOR USER: root
<s02.localhost> SSH: EXEC ssh -C -vvv -o ForwardAgent=yes -o ControlPersist=60s -o 'IdentityFile="/Users/bas/.vagrant.d/insecure_private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=root -o ConnectTimeout=10 -o ControlPath=/Users/bas/.ansible/cp/ansible-ssh-%h-%p-%r -tt s02.localhost mkdir -p "$(echo $HOME/.ansible/tmp/ansible-tmp-1444763933.97-72639397395590)" && echo "$(echo $HOME/.ansible/tmp/ansible-tmp-1444763933.97-72639397395590)"
<s02.localhost> PUT /var/folders/8q/kjslxh3s4zg8ghmz466mvqk40000gn/T/tmpuxyxVX TO /root/.ansible/tmp/ansible-tmp-1444763933.97-72639397395590/s3
<s02.localhost> SSH: EXEC sftp -b - -C -vvv -o ForwardAgent=yes -o ControlPersist=60s -o 'IdentityFile="/Users/bas/.vagrant.d/insecure_private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=root -o ConnectTimeout=10 -o ControlPath=/Users/bas/.ansible/cp/ansible-ssh-%h-%p-%r [s02.localhost]
<s02.localhost> ESTABLISH SSH CONNECTION FOR USER: root
<s02.localhost> SSH: EXEC ssh -C -vvv -o ForwardAgent=yes -o ControlPersist=60s -o 'IdentityFile="/Users/bas/.vagrant.d/insecure_private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=root -o ConnectTimeout=10 -o ControlPath=/Users/bas/.ansible/cp/ansible-ssh-%h-%p-%r -tt s02.localhost LANG=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8 LC_CTYPE=en_US.UTF-8 /usr/bin/python /root/.ansible/tmp/ansible-tmp-1444763933.97-72639397395590/s3; rm -rf "/root/.ansible/tmp/ansible-tmp-1444763933.97-72639397395590/" > /dev/null 2>&1
changed: [s02.localhost -> localhost] => {"changed": true, "contents": "20151013125733+0200.sql", "msg": "GET operation complete"}
TASK [set_fact target_path=/tmp/db-import-{{ mysql_database.name }}-latest.sql.gz] ***
task path: /Users/bas/Documents/Projects/PLHW.Sandalinos.ProjectHealthyFeet/provisioning/ansible/roles/provision.mysql/tasks/import_s3_backup.yml:32
ok: [s02.localhost -> localhost] => {"ansible_facts": {"target_path": "/tmp/db-import-plhw_stg_sandalinos-latest.sql.gz"}, "changed": false}
TASK [mysql sql import | get latest backup] ************************************
task path: /Users/bas/Documents/Projects/PLHW.Sandalinos.ProjectHealthyFeet/provisioning/ansible/roles/provision.mysql/tasks/import_s3_backup.yml:34
<s02.localhost> ESTABLISH SSH CONNECTION FOR USER: root
<s02.localhost> SSH: EXEC ssh -C -vvv -o ForwardAgent=yes -o ControlPersist=60s -o 'IdentityFile="/Users/bas/.vagrant.d/insecure_private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=root -o ConnectTimeout=10 -o ControlPath=/Users/bas/.ansible/cp/ansible-ssh-%h-%p-%r -tt s02.localhost mkdir -p "$(echo $HOME/.ansible/tmp/ansible-tmp-1444763937.54-1141973695985)" && echo "$(echo $HOME/.ansible/tmp/ansible-tmp-1444763937.54-1141973695985)"
<s02.localhost> PUT /var/folders/8q/kjslxh3s4zg8ghmz466mvqk40000gn/T/tmp04ILIK TO /root/.ansible/tmp/ansible-tmp-1444763937.54-1141973695985/s3
<s02.localhost> SSH: EXEC sftp -b - -C -vvv -o ForwardAgent=yes -o ControlPersist=60s -o 'IdentityFile="/Users/bas/.vagrant.d/insecure_private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=root -o ConnectTimeout=10 -o ControlPath=/Users/bas/.ansible/cp/ansible-ssh-%h-%p-%r [s02.localhost]
<s02.localhost> ESTABLISH SSH CONNECTION FOR USER: root
<s02.localhost> SSH: EXEC ssh -C -vvv -o ForwardAgent=yes -o ControlPersist=60s -o 'IdentityFile="/Users/bas/.vagrant.d/insecure_private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=root -o ConnectTimeout=10 -o ControlPath=/Users/bas/.ansible/cp/ansible-ssh-%h-%p-%r -tt s02.localhost LANG=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8 LC_CTYPE=en_US.UTF-8 /usr/bin/python /root/.ansible/tmp/ansible-tmp-1444763937.54-1141973695985/s3; rm -rf "/root/.ansible/tmp/ansible-tmp-1444763937.54-1141973695985/" > /dev/null 2>&1
^C
PLAY RECAP *********************************************************************
s02.localhost : ok=6 changed=1 unreachable=0 failed=0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment