# SQL migration playbooks questions

**URL:** <https://forum.ansible.com/t/sql-migration-playbooks-questions/5805>\
**Category:** Get Help\
**Tags:** playbook\
**Created:** [May 14, 2024, 2:08pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805 "2024-05-14T14:08:34Z")\
**Posts on this page:** 1\
**Showing post:** 44

<div class="post-metadata">

**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\
**Post date:** [June 18, 2024, 7:25am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/44 "2024-06-18T07:25:05Z")

</div>

I got now the full playbook and edited the part with permision settings so it sets just the needen ones and copy the keys directly to the user .ssh folder.

```auto
---
---
- name: Generate SSH keypair on remote hosts and distribute keys
  hosts:
    - 192.168.19.201
    - 192.168.151.237
  become: true
  tasks:
    - name: Create a "sync_src" group
      ansible.builtin.group_by:
        key: sync_src
      when: ansible_default_ipv4.address == '192.168.151.237'

    - name: Create a "sync_dst" group
      ansible.builtin.group_by:
        key: sync_dst
      when: ansible_default_ipv4.address == '192.168.19.201'

- name: Create SSH keypair in /tmp
  hosts: localhost
  tasks:
    - name: Create SSH keypair in /tmp
      ansible.builtin.openssh_keypair:
        path: /tmp/id_rsa_remcpyusr
        type: rsa
        force: true
        comment: "remcpyusr"
      run_once: true

- name: Distribute the SSH public key
  hosts: sync_dst
  tasks:
    - name: Distribute the SSH public key
      ansible.builtin.copy:
        src: /tmp/id_rsa_remcpyusr.pub
        dest: /home/remcpyusr/.ssh/authorized_keys
        mode: '0644'
        force: yes

- name: Distribute the SSH private key
  hosts: sync_src
  tasks:
    - name: Distribute the SSH private key
      ansible.builtin.copy:
        src: /tmp/id_rsa_remcpyusr
        dest: /home/remcpyusr/.ssh/id_rsa
        mode: '0600'
        force: yes

- name: Set permissions for private key
  hosts: sync_src
  tasks:
    - name: Set permissions for private key
      ansible.builtin.file:
        path: /home/remcpyusr/.ssh/id_rsa
        owner: remcpyusr
        group: remcpyusr
        mode: '0600'

- name: Set permissions for public key
  hosts: sync_dst
  tasks:
    - name: Set permissions for public key
      ansible.builtin.file:
        path: /home/remcpyusr/.ssh/authorized_keys
        owner: remcpyusr
        group: remcpyusr
        mode: '0644'

- name: Synchronize files to target host
  hosts: sync_dst
  tasks:
    - name: Synchronize files to target host
      ansible.builtin.synchronize:
        src: '{{ item }}'
        dest: remcpyusr@{{ ansible_default_ipv4.address }}:{{ item }}
        rsync_opts:
          - "--rsh='ssh -i /home/remcpyusr/.ssh/id_rsa'"
      delegate_to: '{{ groups["sync_src"] | random }}'
      when: "'sync_dst' in group_names"
      loop:
        - /tmp/test.txt

```

it seems to have some issue with the key auth, because the sync task is taking its time aleready…  
@utoddl should the private key not be on the source host?  
I tried to change it but still the copy gets stuck in a loop it seems.  
Its a little nerve wracking - i can copy just fine when i add the keys to ~/.ssh/ as root.  
I can copy with:

```auto
 rsync -avz -e "ssh -i /home/remcpyusr/.ssh/id_rsa" remcpyusr@192.168.19.201:/tmp/test2.txt /tmp/test2.txt

```

Somehow it doesnt get the path to the private key from the options correct

---

_[View the full topic](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805)._