# SQL migration playbooks questions **URL:** \ **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:** 1
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [May 14, 2024, 2:08pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/1 "2024-05-14T14:08:34Z")
Hello its me again! I have some customer enviroments wo all use the SQL CE as databases and were migrating the Contants manually at migrations and serverupdates. For testing i installed MYSQL with the sakilla demo DB I created a test playbook but it seems that i somehow have a misunderstanding in ansible.buildin.shell/command, because i get the error: ERROR! ‘ansible.builtin.shell/commend’ is not a valid attribute for a Play. I tried the playbook with and without “”. Wen i got the principles i will replace the user/pw with env stuff. My playbook is: ```auto --- - name: backup Sakila-Db hosts: localhost become: yes - name: backup Sakila-DB ansible.builtin.shell: cmd: "mysqldump -u root -pmysuperinsecuretestpassword sakila > /tmp/sakila_backup.sql" register: result ignore_errors: yes ``` Thank you in advance again! ---
**Author:** ![iyami5](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/iyami5/32/3449_2.png) [@iyami5](https://forum.ansible.com/u/iyami5)\ **Post date:** [May 14, 2024, 5:57pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/2 "2024-05-14T17:57:56Z")
It might be a spacing issue: in the `cmd:` it seems there’s four spaces instead of two. ---
**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:** [May 14, 2024, 9:36pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/3 "2024-05-14T21:36:33Z")
Definitely a spacing/indentation issue. The first four lines are fine, down through `become: ` ~~`yes`~~ ` true`. But after that, lined up with `become:`, you need `tasks:` Then shift all the following lines to the right 4 spaces, starting with the second `- name: backup Sakila-DB` line. It’s trying to interpret your `ansible.builtin.shell` task as the 2nd play in your playbook. ---
**Author:** ![markuman](https://avatars.discourse-cdn.com/v4/letter/m/7bcc69/32.png) [@markuman](https://forum.ansible.com/u/markuman)\ **Post date:** [May 15, 2024, 5:45am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/4 "2024-05-15T05:45:39Z")
Alternatively, you can use `community.mysql.mysql_db` to create a dump. So there is no need to use the shell or command module. ```yml - name: dump sakila database community.mysql.mysql_db: login_host: localhost login_password: mysuperinsecuretestpassword login_port: 3306 login_user: root single_transaction: true state: dump name: sakila target: "/tmp/sakila_backup.sql" ``` ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [May 15, 2024, 6:27am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/5 "2024-05-15T06:27:44Z")
This looks nice! Thank you! ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [May 15, 2024, 6:44am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/6 "2024-05-15T06:44:38Z")
To me it seems you could use [both](https://www.middlewareinventory.com/blog/ansible-sudo-ansible-become-example/) - i see everywhere the become yes version and mostly the true when connected to a user. This playbook still fails with the same error: ```auto --- - name: backup Sakila-Db hosts: localhost become: yes tasks: - name: backup Sakila-DB ansible.builtin.shell: cmd: mysqldump -u root -ppasswort sakila > /tmp/sakila_backup.sql register: result ignore_errors: yes ``` ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [May 15, 2024, 6:48am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/7 "2024-05-15T06:48:54Z")
I drop everything in the yamlchecker and it seemed alright. I even removed one spacing after another and the checker had no issues from 4 to 2 spaces… rather wird for me - maybe a bug. ---
**Author:** ![markuman](https://avatars.discourse-cdn.com/v4/letter/m/7bcc69/32.png) [@markuman](https://forum.ansible.com/u/markuman)\ **Post date:** [May 15, 2024, 7:45am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/8 "2024-05-15T07:45:15Z")
`cmd` must be not on the same level as `shell` (_the module name_), because it is a parameter of the module itself…it must be a child item. So yes, both are valid yaml. But only the code pasted below is valid ansible “code” ```yml --- - name: backup Sakila-Db hosts: localhost become: yes tasks: - name: backup Sakila-DB ansible.builtin.shell: cmd: mysqldump -u root -ppasswort sakila > /tmp/sakila_backup.sql register: result ignore_errors: yes ``` And `become: true` does not change anything here for you. That means only, that the `shell` task is executed with privilges. So it’s not needed, because you use root credentials for your mysqldump. ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [May 15, 2024, 7:53am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/9 "2024-05-15T07:53:27Z")
I now habe a better idea what the playbook/s need to do in the end: migrate databases vor dev/showcases and overwrite/delete if a DB with the same name exists. Is there a method to move the dumps between the two servers, or do i need a network share to store the dump? ---
**Author:** ![markuman](https://avatars.discourse-cdn.com/v4/letter/m/7bcc69/32.png) [@markuman](https://forum.ansible.com/u/markuman)\ **Post date:** [May 15, 2024, 7:58am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/10 "2024-05-15T07:58:43Z")
That’s not really ansible-related. It’s more a DBA topic. But theoretically you can do ```sh mysqldump -h somehost somedb | mysql -h otherhost somedb ``` ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [May 15, 2024, 8:33am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/11 "2024-05-15T08:33:40Z")
Thank you! This looks good. I thouught it would be possible with community.mysql.mysql\_db: and set an ssh/scp target. This project will be my first workflow template, where a user choose the source host, DB name and traget host and maybe a required approvement. I have to look this further up how to. ---
**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:** [May 15, 2024, 12:03pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/12 "2024-05-15T12:03:12Z")
> [@gothsome](#): > > ```auto > --- > - name: backup Sakila-Db > hosts: localhost > become: yes > > tasks: > - name: backup Sakila-DB > ansible.builtin.shell: > cmd: mysqldump -u root -ppasswort sakila > /tmp/sakila_backup.sql > register: result > ignore_errors: yes > > ``` You didn’t finish fixing your indentation. It’s valid YAML, but it isn’t valid Ansible. Your task needs to be indented under the `tasks:` keyword, thus: ```yaml --- - name: backup Sakila-Db hosts: localhost become: yes tasks: - name: backup Sakila-DB ansible.builtin.shell: cmd: mysqldump -u root -ppasswort sakila > /tmp/sakila_backup.sql register: result ignore_errors: yes ``` ---
**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:** [May 15, 2024, 12:05pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/13 "2024-05-15T12:05:50Z")
> [@gothsome](#): > > I drop everything in the yamlchecker and it seemed alright. YAML checkers are fine, but you should try `ansible-lint`. `"valid YAML" != "valid Ansible"` ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [May 15, 2024, 12:14pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/14 "2024-05-15T12:14:30Z")
Looks interesting is this some kind of github/lab vs code plugin? ---
**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:** [May 15, 2024, 12:20pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/15 "2024-05-15T12:20:11Z")
`ansible-lint` is a stand-alone CLI tool, like `ansible-playbook`. See [https://ansible.readthedocs.io/projects/lint/installing/](https://ansible.readthedocs.io/projects/lint/installing/) Although, in my case, my distribution packages it up: `python3-ansible-lint-24.2.0-1.fc40.noarch`. ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [May 15, 2024, 12:33pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/16 "2024-05-15T12:33:41Z")
I will take a look tomorrow. Can you tell me how a workflow sees if a playbook was “successfully” or not? I want to include a test to check if someone made some impossible choices in the survey. I have 3: 3source server, 3 dest server and DB Table and the servers are the same so i need to check if someone has the same source and dest server set. ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [May 15, 2024, 1:47pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/17 "2024-05-15T13:47:23Z")
I got the solution for may playbook errors: Its been a while since the last time i used awx. I used at hosts: localhost so the playbooks will be run at the AWX Kubernetes machine local where no sql stuff ist around. set it to all and everything is alright. ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [May 16, 2024, 6:54am UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/18 "2024-05-16T06:54:03Z")
But i got an endless running playbook for the file transfer: 16 hours for a file with the size of 3MB. I stopped it and first update my setup to latest version: ```auto --- - name: Transfer MySQL Database Dump File hosts: all gather_facts: false tasks: - name: Transfer dump file from 192.168.151.237 to 192.168.19.201 ansible.builtin.synchronize: src: "/tmp/sakila_backup.sql" dest: "/tmp/sakila_backup.sql" mode: push delegate_to: 192.168.151.237 when: inventory_hostname == "192.168.19.201" ``` ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [May 16, 2024, 1:38pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/19 "2024-05-16T13:38:38Z")
My ChatBot - Buddy mentioned its better to remove the passwort of the private key. It would be the first playbook with a problem like this, but ill try it anyway. the old log: ```auto Enter passphrase for /runner/artifacts/2761/ssh_key_data: Identity added: /runner/artifacts/2761/ssh_key_data (/runner/artifacts/2761/ssh_key_data) ansible-playbook [core 2.15.12rc1] config file = None configured module search path = ['/runner/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules'] ansible python module location = /usr/local/lib/python3.9/site-packages/ansible ansible collection location = /runner/requirements_collections:/runner/.ansible/collections:/usr/share/ansible/collections executable location = /usr/local/bin/ansible-playbook python version = 3.9.18 (main, Jan 24 2024, 00:00:00) [GCC 11.4.1 20231218 (Red Hat 11.4.1-3)] (/usr/bin/python3) jinja version = 3.1.4 libyaml = True No config file found; using defaults host_list declined parsing /runner/inventory/hosts as it did not pass its verify_file() method Parsed /runner/inventory/hosts inventory source with script plugin redirecting (type: action) ansible.builtin.synchronize to ansible.posix.synchronize Skipping callback 'awx_display', as we already have a stdout callback. Skipping callback 'default', as we already have a stdout callback. Skipping callback 'minimal', as we already have a stdout callback. Skipping callback 'oneline', as we already have a stdout callback. PLAYBOOK: sql_remote_dump-test.yaml ******************************************** 1 plays in functions/SQL/migration/sql_remote_dump-test.yaml PLAY [Transfer MySQL Database Dump File] *************************************** redirecting (type: action) ansible.builtin.synchronize to ansible.posix.synchronize TASK [Transfer dump file from 192.168.151.237 to 192.168.19.201] *************** task path: /runner/project/functions/SQL/migration/sql_remote_dump-test.yaml:7 redirecting (type: action) ansible.builtin.synchronize to ansible.posix.synchronize skipping: [192.168.151.237] => { "changed": false, "false_condition": "inventory_hostname == \\"192.168.19.201\\"", "skip_reason": "Conditional result was False" } 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"Warning: Permanently added '192.168.151.237' (ED25519) to the list of known hosts.\\r\\n") <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-1715783402.0129042-28-89502946805035 `" && echo ansible-tmp-1715783402.0129042-28-89502946805035="` echo /root/.ansible/tmp/ansible-tmp-1715783402.0129042-28-89502946805035 `" ) && sleep 0'"'"'' <192.168.151.237> (0, b'ansible-tmp-1715783402.0129042-28-89502946805035=/root/.ansible/tmp/ansible-tmp-1715783402.0129042-28-89502946805035\\n', b'') <192.168.19.201> Attempting python interpreter discovery <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 PLATFORM; uname; echo FOUND; command -v '"'"'"'"'"'"'"'"'python3.11'"'"'"'"'"'"'"'"'; command -v '"'"'"'"'"'"'"'"'python3.10'"'"'"'"'"'"'"'"'; command -v '"'"'"'"'"'"'"'"'python3.9'"'"'"'"'"'"'"'"'; command -v '"'"'"'"'"'"'"'"'python3.8'"'"'"'"'"'"'"'"'; command -v '"'"'"'"'"'"'"'"'python3.7'"'"'"'"'"'"'"'"'; command -v '"'"'"'"'"'"'"'"'python3.6'"'"'"'"'"'"'"'"'; command -v '"'"'"'"'"'"'"'"'python3.5'"'"'"'"'"'"'"'"'; command -v '"'"'"'"'"'"'"'"'/usr/bin/python3'"'"'"'"'"'"'"'"'; command -v '"'"'"'"'"'"'"'"'/usr/libexec/platform-python'"'"'"'"'"'"'"'"'; command -v '"'"'"'"'"'"'"'"'python2.7'"'"'"'"'"'"'"'"'; command -v '"'"'"'"'"'"'"'"'/usr/bin/python'"'"'"'"'"'"'"'"'; command -v '"'"'"'"'"'"'"'"'python'"'"'"'"'"'"'"'"'; echo ENDFOUND && sleep 0'"'"'' <192.168.151.237> (0, b'PLATFORM\\nLinux\\nFOUND\\n/usr/bin/python3.10\\n/usr/bin/python3\\nENDFOUND\\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 '"'"'/usr/bin/python3.10 && sleep 0'"'"'' <192.168.151.237> (0, b'{"platform_dist_result": [], "osrelease_content": "PRETTY_NAME=\\\\"Ubuntu 22.04.4 LTS\\\\"\\\\nNAME=\\\\"Ubuntu\\\\"\\\\nVERSION_ID=\\\\"22.04\\\\"\\\\nVERSION=\\\\"22.04.4 LTS (Jammy Jellyfish)\\\\"\\\\nVERSION_CODENAME=jammy\\\\nID=ubuntu\\\\nID_LIKE=debian\\\\nHOME_URL=\\\\"https://www.ubuntu.com/\\\\"\\\\nSUPPORT_URL=\\\\"https://help.ubuntu.com/\\\\"\\\\nBUG_REPORT_URL=\\\\"https://bugs.launchpad.net/ubuntu/\\\\"\\\\nPRIVACY_POLICY_URL=\\\\"https://www.ubuntu.com/legal/terms-and-policies/privacy-policy\\\\"\\\\nUBUNTU_CODENAME=jammy\\\\n"}\\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-22df7j6lru/tmpv1jqciiq TO /root/.ansible/tmp/ansible-tmp-1715783402.0129042-28-89502946805035/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-22df7j6lru/tmpv1jqciiq /root/.ansible/tmp/ansible-tmp-1715783402.0129042-28-89502946805035/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-1715783402.0129042-28-89502946805035/ /root/.ansible/tmp/ansible-tmp-1715783402.0129042-28-89502946805035/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-1715783402.0129042-28-89502946805035/AnsiballZ_synchronize.py && sleep 0'"'"'' ``` ---
**Author:** ![iyami5](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/iyami5/32/3449_2.png) [@iyami5](https://forum.ansible.com/u/iyami5)\ **Post date:** [May 17, 2024, 6:18pm UTC](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805/20 "2024-05-17T18:18:52Z")
looks like part of the log is missing? is it failing to connect after removing the password ? [Next page](https://forum.ansible.com/t/sql-migration-playbooks-questions/5805.md?page=2)