# 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:** 20\
**Page:** 3

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [June 17, 2024, 1:51pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/41 "2024-06-17T13:51:26Z")

</div>

Only `loop` if you want to generate more then one key pair, I think your error might be because you have something like this:

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

```

When you should have:

```yaml
    - name: Create SSH keypair in /tmp
      ansible.builtin.openssh_keypair:
        path: /tmp/id_rsa_remcpyusr
        type: rsa
        force: true
        comment: "remcpyusr"
      delegate_to: localhost # 👈 note the leading white space
      run_once: true

```

[ansible-lint](https://ansible-lint.readthedocs.io) is handy for catching some errors like this.

---

<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 17, 2024, 2:13pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/42 "2024-06-17T14:13:24Z")

</div>

Yeah got it - i had some spacing issues but this is something cht gpt can do quite good, but have to tell to not change anything else or it will do scary stuff!.  
 ![grafik](https://us1.discourse-cdn.com/ansible/original/2X/d/d262ad82d0cc4aca61dc192120e9b23a29c4d1a1.png)

```auto
---
- name: Generate SSH keypair on remote hosts and distribute keys
  hosts:
    - 192.168.19.201
    - 192.168.151.237
    # - localhost
  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_src
  tasks:
    - name: Distribute the SSH public key
      ansible.builtin.copy:
        src: /tmp/id_rsa_remcpyusr.pub
        dest: /tmp/id_rsa_remcpyusr.pub
        mode: '0644'
        force: yes

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

```

---

<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 17, 2024, 2:16pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/43 "2024-06-17T14:16:40Z")

</div>

now i have to set everything together and create two test sql server and a test workflow - thank you aagin for you extremely valuable help!

---

<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

---

<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, 8:22am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/45 "2024-06-18T08:22:40Z")

</div>

i ruined it myself because i didnt know this item variable in the loop is just filles with the vars from loop - i removed the variable i set and its still looping araound - i now edited the playbook above with the actual versiuon with rsync - i use now to be able to continue:

```auto
- name: Synchronize files to target host
  hosts: sync_src
  tasks:
    - name: Synchronize files to target host
      ansible.builtin.command: rsync -avz -e "ssh -i /home/remcpyusr/.ssh/id_rsa" remcpyusr@192.168.19.201:/tmp/test2.txt /tmp/test2.txt

```

 ![grafik](https://us1.discourse-cdn.com/ansible/original/2X/d/d8b175b6d49d68782818ca590353f81ddb482643.png)

---

<div class="post-metadata">

**Author:** ![utoddl](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/utoddl/32/249_2.png) [@utoddl](https://forum.ansible.com/u/utoddl)\
**Post date:** [June 18, 2024, 12:00pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/46 "2024-06-18T12:00:34Z")

</div>

Sorry, @gothsome , I’m not sure what that [image](https://us1.discourse-cdn.com/ansible/original/2X/d/d8b175b6d49d68782818ca590353f81ddb482643.png) is supposed to be showing me.

Why do you have separate steps to set the owner and group of the public and private keys instead of setting them when you copy them? Both `owner:` and `group:` are options for both `ansible.builtin.file` and `ansible.builtin.copy`.

Are you changing from using `ansible.builtin.synchronize` to `ansible.builtin.command`? What’s wrong with the “`Synchronize files to target host`” task from [#44](http://forum.ansible.com/t/sql-migration-playbooks-questions/5805/44)? What’s the job log for that task?

BTW, I’ve never seen this pattern where you start another play with different hosts for single tasks rather than using a `when:` condition to limit the task to the hosts of interest. I keep wanting to dislike it, but it’s actually cleaner in some ways than putting `when:` conditions on every task. I suppose it comes down to how much overhead is involved with setting up each play in the playbook. Does it do fact gathering on each play, or only on the first play involving a particular host? That’s really interesting, and I don’t believe I would have thought of trying it that way. As the kids say, “Today I Learned …”

---

<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, 12:21pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/47 "2024-06-18T12:21:25Z")

</div>

Thank you - i would like to keeop syncronize, but the creen s show the playbook which stops, because the rsync module cant auth the user correctly - i had this in the beginnig and then i added for testing the awx private key to the source host in ~/.ssh/id\_rsa and the module could copy, but i dont like thins for two reasons: its root access and the private key of the awx privkey should just be at the server itself  
I learned then that the playbook with this key auth issue schould fail, but it just keeps on looping around all day long in my awxsrv - the problem is i cant see an error where i can see where the auth fails. the log just stopps proceeding at an auth step.  
I cant tell if its an bug i schould report at the git?

heres the log of the task i made the screenshot with full verbosity.  
I stopped it after 3 hours - i add the last task log because the log has 600 lines.

```auto

TASK [Synchronize files to target host] ****************************************
task path: /runner/project/zzz_testplaybooks_and_archive/111movefiles_rsync3.yaml:72
redirecting (type: modules) ansible.builtin.synchronize to ansible.posix.synchronize
redirecting (type: action) ansible.builtin.synchronize to ansible.posix.synchronize
redirecting (type: action) ansible.builtin.synchronize to ansible.posix.synchronize
<192.168.151.237> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.151.237> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/runner/cp/63d63d053c"' 192.168.151.237 '/bin/sh -c '"'"'echo ~root && sleep 0'"'"''
<192.168.151.237> (0, b'/root\\n', b'')
<192.168.151.237> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.151.237> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/runner/cp/63d63d053c"' 192.168.151.237 '/bin/sh -c '"'"'( umask 77 && mkdir -p "` echo /root/.ansible/tmp `"&& mkdir "` echo /root/.ansible/tmp/ansible-tmp-1718701712.9134433-213-60173677516755 `" && echo ansible-tmp-1718701712.9134433-213-60173677516755="` echo /root/.ansible/tmp/ansible-tmp-1718701712.9134433-213-60173677516755 `" ) && sleep 0'"'"''
<192.168.151.237> (0, b'ansible-tmp-1718701712.9134433-213-60173677516755=/root/.ansible/tmp/ansible-tmp-1718701712.9134433-213-60173677516755\\n', b'')
Using module file /usr/share/ansible/collections/ansible_collections/ansible/posix/plugins/modules/synchronize.py
<192.168.151.237> PUT /runner/.ansible/tmp/ansible-local-2295bqx_n8/tmpokmyow7d TO /root/.ansible/tmp/ansible-tmp-1718701712.9134433-213-60173677516755/AnsiballZ_synchronize.py
<192.168.151.237> SSH: EXEC sftp -b - -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/runner/cp/63d63d053c"' '[192.168.151.237]'
<192.168.151.237> (0, b'sftp> put /runner/.ansible/tmp/ansible-local-2295bqx_n8/tmpokmyow7d /root/.ansible/tmp/ansible-tmp-1718701712.9134433-213-60173677516755/AnsiballZ_synchronize.py\\n', b'')
<192.168.151.237> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.151.237> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/runner/cp/63d63d053c"' 192.168.151.237 '/bin/sh -c '"'"'chmod u+x /root/.ansible/tmp/ansible-tmp-1718701712.9134433-213-60173677516755/ /root/.ansible/tmp/ansible-tmp-1718701712.9134433-213-60173677516755/AnsiballZ_synchronize.py && sleep 0'"'"''
<192.168.151.237> (0, b'', b'')
<192.168.151.237> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.151.237> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/runner/cp/63d63d053c"' -tt 192.168.151.237 '/bin/sh -c '"'"'/usr/bin/python3 /root/.ansible/tmp/ansible-tmp-1718701712.9134433-213-60173677516755/AnsiballZ_synchronize.py && sleep 0'"'"''

```

---

<div class="post-metadata">

**Author:** ![utoddl](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/utoddl/32/249_2.png) [@utoddl](https://forum.ansible.com/u/utoddl)\
**Post date:** [June 18, 2024, 4:17pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/48 "2024-06-18T16:17:46Z")

</div>

Okay, not sure what’s happening, but this feels like the `ssh` connection is waiting for interactive input (despite `StrictHostKeyChecking=no`), then the timeout is kicking in, and it retries… forever. (All speculation of course.)

Just to clarify: is it the case that your AWX is connecting as `root` to the managed nodes rather than as a dedicated userid with sudo abilities? I mean, I’m seeing “root” all over the log entries, so I guess that’s the case. If so, does that also mean you never deal with privilege escalation because you’re already `root` everywhere? And you therefore never deal with `become_user:` either, again because you’re already `root`? If so, it seems like this _would_ make some things easier, but at the same time, not, in the sense that _ssh as root all over the place is scary beyond imagining._ I don’t even do that at home when I’m just testing one-off throw-away stuff. (I’m trying to imagine how to replicate your set-up.)

Humor me, please, and copy-n-paste the final task as it was when it produced the log above. I want to make sure I understand what code you’re starting with.

---

<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 19, 2024, 6:54am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/49 "2024-06-19T06:54:14Z")

</div>

Yeah its somewat weird so either the syncronize does not transmit the usersetting part, but the cert part. I gave my mental limited chatgpt the log an it sayd it could be an issuebecause my private key of awx has an password, but it would be the first playbook with this issue, and i switched the key wo pw and no diffrence in the end - but from this i came to the thought:  
when i sync between two remote hosts does the awx somehow uses its private key for ath from source srv to the target srv: no you have to add the keys you need yourself.  
And thats what i think schpuld be mentioned at least in the descrition of the mdule - for ppl like me its not that obious.  
Playbook:  
I tried probing around with the user added in the rsync\_opts, but it fails and seem to try to connect with username@ip path username@ip path twice the data

```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 }}
        #dest: {{ ansible_default_ipv4.address }}:{{ item }}
        rsync_opts:
          - "--rsh='ssh -i /home/remcpyusr/.ssh/id_rsa'"
          #- "--rsh='ssh -i /home/remcpyusr/.ssh/id_rsa -o StrictHostKeyChecking=no -l remcpyusr'"
      delegate_to: '{{ groups["sync_src"] | random }}'
      when: "'sync_dst' in group_names"
      loop:
        - /tmp/test.txt

```

The log is too tedious to split - the posts have a limit of 3k letters and the log has almost 19k  
I hope you have an google account:

> **[Google Drive: Sign-in](https://accounts.google.com/v3/signin/identifier?continue=https%3A%2F%2Fdrive.google.com%2Ffile%2Fd%2F1loWpjLm4IMBuUQznmE7MX_XxyTvmiDHX%2Fview%3Fusp%3Ddrive_link&followup=https%3A%2F%2Fdrive.google.com%2Ffile%2Fd%2F1loWpjLm4IMBuUQznmE7MX_XxyTvmiDHX%2Fview%3Fusp%3Ddrive_link&ifkv=AS5LTASU3UFyj2l-4UhtBeSF5XBzGP0wrLZtvoRZzz2MBghYSqxWM-N7VO5GwELVDYQO8eqAUak0ww&osid=1&passive=1209600&service=wise&flowName=GlifWebSignIn&flowEntry=ServiceLogin&dsh=S1057259733%3A1718780055551817&ddm=0)**
>
> Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

---

<div class="post-metadata">

**Author:** ![utoddl](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/utoddl/32/249_2.png) [@utoddl](https://forum.ansible.com/u/utoddl)\
**Post date:** [June 19, 2024, 5:48pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/50 "2024-06-19T17:48:41Z")

</div>

> [@gothsome](#):
>
> The log is too tedious to split

Too true. I went through the whole thing line by line (forcing myself awake every couple of lines).

Alas, the only interesting thing is _what’s not there_. At the end, we see the guts of the `synchronize.py` module get copied to your `delegate_to:` host, and a `chmod u+x` on the containing directory and the file itself:

```auto
Using module file /usr/share/ansible/collections/ansible_collections/ansible/posix/plugins/modules/synchronize.py
<192.168.151.237> PUT /runner/.ansible/tmp/ansible-local-2295bqx_n8/tmpokmyow7d TO /root/.ansible/tmp/ansible-tmp-1718701712.9134433-213-60173677516755/AnsiballZ_synchronize.py
<192.168.151.237> SSH: EXEC sftp -b - -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/runner/cp/63d63d053c"' '[192.168.151.237]'
<192.168.151.237> (0, b'sftp> put /runner/.ansible/tmp/ansible-local-2295bqx_n8/tmpokmyow7d /root/.ansible/tmp/ansible-tmp-1718701712.9134433-213-60173677516755/AnsiballZ_synchronize.py\\n', b'')
<192.168.151.237> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.151.237> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/runner/cp/63d63d053c"' 192.168.151.237 '/bin/sh -c '"'"'chmod u+x /root/.ansible/tmp/ansible-tmp-1718701712.9134433-213-60173677516755/ /root/.ansible/tmp/ansible-tmp-1718701712.9134433-213-60173677516755/AnsiballZ_synchronize.py && sleep 0'"'"''
<192.168.151.237> (0, b'', b'')

```

After that, we see it get run under `/bin/sh` with the `-c` parameter:

```auto
<192.168.151.237> ESTABLISH SSH CONNECTION FOR USER: root
<192.168.151.237> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="root"' -o ConnectTimeout=10 -o 'ControlPath="/runner/cp/63d63d053c"' -tt 192.168.151.237 '/bin/sh -c '"'"'/usr/bin/python3 /root/.ansible/tmp/ansible-tmp-1718701712.9134433-213-60173677516755/AnsiballZ_synchronize.py && sleep 0'"'"''

```

Notably absent is any hint of the parameters you used on `ansible.builtin.synchronize`, or of any activity from that point forward.

Also crucially absent is any hint of the synchronize task running as `remcpyusr` rather than `root`. At this point of desperation, **I would try adding “`become_user: remcpyusr`” on that final task** at the same indentation level as `delegate_to:` and `loop:`. It’s grasping at straws really, but at this point we’re just trying to get it to behave differently. We don’t even care particularly whether that difference is effective; we’re just hoping for something _noticeable_.

**If anybody else has another suggestion, we’d love to hear it!**

The log you shared answers one of my other questions. Every new play causes a new round of facts gathering for each host. Most of the time (ignoring the infinite amount of time at the last task) is spent re-gathering host facts. That’s why you’d want to use `when: "'blah_blah' in group_names"` on tasks instead of starting new plays. Play setup is eating up your clock.

_[Edit: you could add `gather_facts: false` on those later plays.]_

Finally, drop those “`Set permissions […]`” tasks, and move the `owner:`, `group:`, and `mode:` settings up to the “`Distribute […]`” tasks. (The “`Set permissions […]`” tasks aren’t actually doing anything; the owner and group are already defaulting to `remcpyusr`, but it’s better to be explicit about them in the “`Distribute […]`” tasks.)

---

<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 20, 2024, 8:01am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/51 "2024-06-20T08:01:26Z")

</div>

Thank you!  
I now have a second testvm with sql and changed the playbook a little like you tolt me and i have noted my global wars i will test today with a test workflow.

```auto
---
- name: Generate SSH keypair on remote hosts and distribute keys
  hosts:
    - 192.168.151.141 # {{ sql_dest_srv }}
    - 192.168.151.237 # {{ sql_src_srv }}
  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' # {{ sql_src_srv }}

    - name: Create a "sync_dst" group
      ansible.builtin.group_by:
        key: sync_dst
      when: ansible_default_ipv4.address == '192.168.151.141' # {{ sql_dest_srv }}

- 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_src
  tasks:
    - name: Distribute the SSH public key
      ansible.builtin.copy:
        src: /tmp/id_rsa_remcpyusr.pub
        dest: /home/remcpyusr/.ssh/authorized_keys
        owner: remcpyusr
        mode: '0644'
        force: yes

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

- name: Synchronize files to target host
  hosts: sync_dst
  tasks:
    - name: Synchronize files to target host
      ansible.builtin.command:
        cmd: rsync -avz -e "ssh -i /home/remcpyusr/.ssh/id_rsa" remcpyusr@192.168.151.237:/tmp/test2.txt /tmp/ # {{ sql_src_srv }}

- name: Ensure key pair is absent on the controller and hosts
  hosts: all
  tasks:
    - name: Ensure key pair is absent on the controller and hosts
      ansible.builtin.file:
        path: "{{ item }}"
        state: absent
      loop:
        - /tmp/remcpyusr_key
        - /tmp/remcpyusr_key.pub
        - /home/remcpyusr/.ssh/authorized_keys
        - /home/remcpyusr/.ssh/id_rsa

```

---

<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 20, 2024, 10:04am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/52 "2024-06-20T10:04:17Z")

</div>

I have to debug the grouping now:

```auto
[WARNING]: conditional statements should not include jinja2 templating
delimiters such as {{ }} or {% %}. Found: ansible_default_ipv4.address == '{{
sql_src_srv }}'
fatal: [192.168.151.141]: FAILED! => {
    "msg": "The conditional check 'ansible_default_ipv4.address == '{{ sql_src_srv }}'' failed. The error was: Conditional is marked as unsafe, and cannot be evaluated.\n\nThe error appears to be in '/runner/project/functions/SQL/migration/rsync_copy_workflow.yaml': line 9, column 7, but may\nbe elsewhere in the file depending on the exact syntax problem.\n\nThe offending line appears to be:\n\n tasks:\n - name: Create a \"sync_src\" group\n ^ here\n"
}
[WARNING]: conditional statements should not include jinja2 templating
delimiters such as {{ }} or {% %}. Found: ansible_default_ipv4.address == '{{
sql_src_srv }}'
fatal: [192.168.151.237]: FAILED! => {
    "msg": "The conditional check 'ansible_default_ipv4.address == '{{ sql_src_srv }}'' failed. The error was: Conditional is marked as unsafe, and cannot be evaluated.\n\nThe error appears to be in '/runner/project/functions/SQL/migration/rsync_copy_workflow.yaml': line 9, column 7, but may\nbe elsewhere in the file depending on the exact syntax problem.\n\nThe offending line appears to be:\n\n tasks:\n - name: Create a \"sync_src\" group\n ^ here\n"
}

```

```auto
---
- name: Generate SSH keypair on remote hosts and distribute keys
  hosts:
    - "{{ sql_dest_srv }}" # {{ sql_dest_srv }} 192.168.151.141
    - "{{ sql_src_srv }}" # {{ sql_src_srv }} 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 == '{{ sql_src_srv }}' # {{ sql_src_srv }} 192.168.151.237

    - name: Create a "sync_dst" group
      ansible.builtin.group_by:
        key: sync_dst
      when: ansible_default_ipv4.address == '{{ sql_dest_srv }}' # {{ sql_dest_srv }} 192.168.151.141

- 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_src
  tasks:
    - name: Distribute the SSH public key
      ansible.builtin.copy:
        src: /tmp/id_rsa_remcpyusr.pub
        dest: /home/remcpyusr/.ssh/authorized_keys
        owner: remcpyusr
        mode: '0644'
        force: yes

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

- name: Synchronize files to target host
  hosts: sync_dst
  tasks:
    - name: Synchronize files to target host
      ansible.builtin.command:
        cmd: rsync -avz -e "ssh -i /home/remcpyusr/.ssh/id_rsa" remcpyusr@{{ sql_src_srv }}:/tmp/test2.txt /tmp/ # {{ sql_src_srv }} 192.168.151.237

- name: Ensure key pair is absent on the controller and hosts
  hosts: all
  tasks:
    - name: Ensure key pair is absent on the controller and hosts
      ansible.builtin.file:
        path: "{{ item }}"
        state: absent
      loop:
        - /tmp/id_rsa_remcpyusr
        - /tmp/id_rsa_remcpyusr.pub
        - /home/remcpyusr/.ssh/authorized_keys
        - /home/remcpyusr/.ssh/id_rsa

```

Done need to use the variables without {{}} at grouping

---

<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 20, 2024, 1:26pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/53 "2024-06-20T13:26:15Z")

</div>

Now i have a question for a difrent playbook- i need to add another user to the sql server - i added him aleready as env, but the task fails because of permission issues:

```auto

TASK [Create MySQL user with necessary privileges] *****************************
fatal: [192.168.151.237]: FAILED! => {"changed": false, "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"}

```

I thought when im logged in as root and set become true would be enough:  
I dont have the root passwords of every server and it would be not good to seet the root password, because later other services or collegues cant reach the servers anymore.

```auto
---
- name: Create MySQL user with specific privileges and install required plugins to the host
  hosts:
    - "{{ sql_dest_srv }}" # {{ sql_dest_srv }} 192.168.151.141
    - "{{ sql_src_srv }}" # {{ sql_src_srv }} 192.168.151.237
  become: true
  vars:
    ansible_python_interpreter: /opt/myenv/bin/python3

  tasks:
    - name: Ensure pip and venv are installed
      ansible.builtin.apt:
        name: 
          - python3-pip
          - python3-venv
          - python3-dev
          - build-essential
          - libssl-dev
          - libffi-dev
          - libmysqlclient-dev
          - pkg-config
        state: present
      #become: yes

    - name: Create a virtual environment
      ansible.builtin.command:
        cmd: python3 -m venv /opt/myenv
      #become: yes
      args:
        creates: /opt/myenv

    - name: Install required Python packages in virtual environment
      ansible.builtin.pip:
        executable: /opt/myenv/bin/pip
        name:
          - pymysql
          - mysqlclient
      #become: yes

    - name: Create MySQL user with necessary privileges
      community.mysql.mysql_user:
        name: "{{ lookup('env', 'sql_user') }}"
        password: "{{ lookup('env', 'sql_passwd') }}"
        priv: '*.*:ALL,GRANT'
        host: '%'
        state: present
      #become: yes

```

My bot adds everywhere the become everywhere - even when i delete them and fter asking to just check for indentations WITHOUT changin anything else it sets the become everywhere again as if it want to mock me!?

How could i manage here the auth the best way?

Thank you again!

---

<div class="post-metadata">

**Author:** ![utoddl](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/utoddl/32/249_2.png) [@utoddl](https://forum.ansible.com/u/utoddl)\
**Post date:** [June 20, 2024, 1:41pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/54 "2024-06-20T13:41:15Z")

</div>

I don’t know what this “bot” is, but you might consider firing it.

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [June 25, 2024, 2:33pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/57 "2024-06-25T14:33:40Z")

</div>

> [@gothsome](#):
>
> i get the error that the credentiels in /root/.my.cnf is not correct to connect to ‘root’@‘localhost’ ,but when i enter locally “mysql” it uses the file to automatically login as root

I would expect that a socket connection is used by `root` when connecting to `localhost`, not the details from `/root/.my.cnf`?

Another user was having problems like this the other day, perhaps there might be some [suggestions in this thread that help you](http://forum.ansible.com/t/unable-to-reset-mysql-root-password-after-fresh-install-mariadb-server/6596/6)?

---

<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 25, 2024, 3:01pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/58 "2024-06-25T15:01:44Z")

</div>

Thank you!  
I studied stackoverflow for the error code and found the solution:

```auto
login_unix_socket: /var/run/mysqld/mysqld.sock

```

but cant tell why.

Now i would set my sql server rootusers without login-get them a secure password and could now use the mysql\_user and other modules to add a temp user with full permissions and remove him after the migration is done.  
Should i add teste after each step to veryfy everything?

---

<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 28, 2024, 10:22am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/59 "2024-06-28T10:22:42Z")

</div>

Hello!  
My test vms are eunning fine , but a question came up about the necessary .my.cnf:  
Is there a suggested method to hash the content to its not in clear text?  
Did anyone of you did anything like this?

Thank you!

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [June 28, 2024, 10:29am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/60 "2024-06-28T10:29:31Z")

</div>

There is the [MySQL 5.6 Obfuscated Authentication Credential Option](https://mariadb.com/kb/en/configuring-mariadb-with-option-files/#mysql-56-obfuscated-authentication-credential-option-file):

> MySQL 5.6 and above support an obfuscated authentication credential option file called `.mylogin.cnf` that is created with `mysql_config_editor`.
> 
> MariaDB does not support this. The passwords in MySQL’s `.mylogin.cnf` are only obfuscated, rather than encrypted, so the feature does not really add much from a security perspective. It is more likely to give users a false sense of security, rather than to seriously protect them.

These days the `/root/.my.cnf` file is not required because MariaDB and MySQL default to using socket authentication for the `root` user via `localhost` (connecting to `127.0.0.1` doesn’t use socket based authentication) on distros such as Debian and Ubuntu and I would assume that this is also the case on other distros? 🤷

---

<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 28, 2024, 1:06pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/61 "2024-06-28T13:06:58Z")

</div>

The question is if i can auth like this , does this [modules](https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_user_module.html) still functions?

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [June 28, 2024, 1:09pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/62 "2024-06-28T13:09:18Z")

</div>

Yes, it is how it works by default, [this is clearly documented](https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_user_module.html#parameter-login_host).

[Previous page](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805.md?page=2)

[Next page](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805.md?page=4)