Playbook to backup and restore galera from dump sql

galera-restore.yaml

---
- name: Restore backup
  hosts: controller
  become: yes
  vars:
    tripleo_container_cli: podman
    run_rear: false
    nfs_server: 14.195.11.200
    mysql_db_file: openstack-backup-mysql.sql
    mysql_grants_file: openstack-backup-mysql-grants.sql
    backup_path: /var/lib/mysql
    internalapi_domain: internalapi.localdomain
    tripleo_backup_and_restore_hiera_config_file: /etc/puppet/hiera.yaml
    tripleo_backup_and_restore_hide_sensitive_logs: false

  tasks:

  - name: Identify MySQL VIP
    command: hiera -c '{{ tripleo_backup_and_restore_hiera_config_file }}' mysql_vip
    register: mysql_vip
    tags:
      - iptables
      
  - name: Obtain MySQL root password
    command: hiera -c '{{ tripleo_backup_and_restore_hiera_config_file }}' mysql::server::root_password
    register: mysql_password
    tags:
      - mysql_password

  - name: Drop database connections through the VIP
    iptables:
      chain: INPUT
      destination: "{{ mysql_vip.stdout }}"
      destination_port: 3306
      protocol: tcp
      jump: DROP
      comment: Temporarily block connections to MySQL VIP
    tags:
      - iptables
      - iptables_drop

  - name: Unmanage Galera resource
    command: pcs resource unmanage galera-bundle
    when: inventory_hostname == groups.controller[0]
    tags:
      - pacemaker_management

  - name: Stop MySQL containers on all controllers
    shell: |
      "{{tripleo_container_cli}}" container stop $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" --filter=name=galera-bundle)
    tags:
      - mysql

  - name: Backup the current database directory
    shell: |
      set -o pipefail
      mv /var/lib/mysql /var/lib/mysql-save
    tags:
      - mysql_directory

  - name: Set selinux attributes properly
    file:
      path: "/var/lib/mysql"
      state: directory
      seuser: system_u
      serole: object_r
      setype: container_file_t
      mode: "0755"
      owner: 42434
      group: 42434
    tags:
      - mysql
      - mysql_create_directory

  - name: Start MySQL container on all controllers
    shell: |
      set -o pipefail
      "{{tripleo_container_cli}}" container start $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" --filter=name=galera-bundle)
    tags:
      - mysql

  - name: Create MySQL directory
    shell: |
      set -o pipefail
      "{{tripleo_container_cli}}" exec -i $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" \
      --filter=name=galera-bundle) bash -c "mysql_install_db --datadir=/var/lib/mysql --user=mysql"
    tags:
      - mysql
      - mysql_init

  - name: Start the MySQL databases
    shell: |
      set -o pipefail
      "{{tripleo_container_cli}}" exec $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" \
      --filter=name=galera-bundle) bash -c "mysqld_safe --skip-networking --wsrep-on=OFF" &
    async: 1800
    tags:
      - mysql
      - mysql_init

  - name: Pause for 10 seconds
    pause:
      seconds: 10
    tags:
      - mysql
      - mysql_init

  - name: Move the .my.cnf file inside the galera container
    shell: |
      set -o pipefail
      "{{tripleo_container_cli}}" exec $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" \
      --filter=name=galera-bundle) bash -c "mv /root/.my.cnf /root/.my.cnf.bck"
    tags:
      - mysql_password
      - mysql_init

  - name: Reset root password
    shell: |
      set -o pipefail
      "{{tripleo_container_cli}}" exec $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" \
      --filter=name=galera-bundle) bash -c "mysql -uroot -e'use mysql;update user set password=PASSWORD(\"{{mysql_password.stdout}}\")where User=\"root\";flush privileges;'"
    tags:
      - mysql_password
      - mysql_init

  - name: Restore the .my.cnf file inside the galera container
    shell: |
      set -o pipefail
      "{{tripleo_container_cli}}" exec $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" \
      --filter=name=galera-bundle) bash -c "mv /root/.my.cnf.bck /root/.my.cnf"
    tags:
      - mysql_password
      - mysql_init

  - name: Copy the backup db file to /var/lib/MySQL
    copy:
      src: "{{ mysql_db_file }}"
      dest: "{{ backup_path }}/{{ mysql_db_file }}"
      remote_src: yes
    when: inventory_hostname == groups.controller[0]
    tags:
      - mysql
      - mysql_init

  - name: Copy the grant db file to /var/lib/MySQL
    copy:
      src: "{{ mysql_grants_file }}"
      dest: "{{ backup_path }}/{{ mysql_grants_file }}"
      remote_src: yes
    when: inventory_hostname == groups.controller[0]
    tags:
      - mysql
      - mysql_init

  - name: Restore MySQL database
    shell: |
      "{{tripleo_container_cli}}" exec -i $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" \
      --filter=name=galera-bundle) bash -c "mysql -u root -p{{mysql_password.stdout}} < \"{{ backup_path }}/{{ mysql_db_file }}\"  "
    when: inventory_hostname == groups.controller[0]
    tags:
      - mysql
      - mysql_init

  - name: Restore MySQL grants database
    shell: |
      "{{tripleo_container_cli}}" exec -i $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" \
      --filter=name=galera-bundle) bash -c "mysql -u root -p{{mysql_password.stdout}} < \"{{ backup_path }}/{{ mysql_grants_file }}\" "
    when: inventory_hostname == groups.controller[0]
    tags:
      - mysql
      - mysql_init

  - name: Shutdown the MySQL database
    shell: |
      "{{tripleo_container_cli}}" exec -i $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" \
      --filter=name=galera-bundle) bash -c "mysqladmin shutdown"
    tags:
      - mysql
      - mysql_init
      
  - name: Start database on bootstrap node
    shell: >
      "{{tripleo_container_cli}}" exec $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" --filter=name=galera-bundle) \
        /usr/bin/mysqld_safe --pid-file=/var/run/mysql/mysqld.pid --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql \
        --log-error=/var/log/mysql_cluster.log  --user=mysql --open-files-limit=16384 \
        --wsrep-cluster-address=gcomm:// &
    when: inventory_hostname == groups.controller[0]
    tags:
      - mysql
      - mysql_sync

  - name: Check the mysql cluster
    shell: |
         "{{tripleo_container_cli}}" exec -i $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" \
         --filter=name=galera-bundle) bash -c "clustercheck"
    register: clustercheck
    until: "clustercheck.stdout.find('Galera cluster node is synced') != -1"
    retries: 40
    delay: 5
    when: inventory_hostname == groups.controller[0]
    tags:
      - mysql
      - mysql_sync

  - name: Get the gcomm parameter inside the container
    shell: |
      set -o pipefail
      "{{tripleo_container_cli}}" exec -i $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" \
      --filter=name=galera-bundle) bash -c "grep wsrep_cluster_address /etc/my.cnf.d/galera.cnf" | awk '{print $3}'
    register: gcomm_cluster_address
    when: inventory_hostname != groups.controller[0]
    tags:
      - mysql
      - mysql_sync

  - name: Start dababase on remaining nodes
    shell: >
      "{{tripleo_container_cli}}" exec $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" \
      --filter=name=galera-bundle) /usr/bin/mysqld_safe --pid-file=/var/run/mysql/mysqld.pid --socket=/var/lib/mysql/mysql.sock \
      --datadir=/var/lib/mysql --log-error=/var/log/mysql_cluster.log  --user=mysql --open-files-limit=16384 \
      --wsrep-cluster-address={{gcomm_cluster_address.stdout}} &
    when: inventory_hostname != groups.controller[0]
    tags:
      - mysql
      - mysql_sync

  - name: Check the mysql cluster
    shell: |
     "{{tripleo_container_cli}}" exec -i $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" \
         --filter=name=galera-bundle) bash -c "clustercheck"
    register: clustercheck
    until: "clustercheck.stdout.find('Galera cluster node is synced') != -1"
    when: inventory_hostname != groups.controller[0]
    retries: 40
    delay: 5
    tags:
      - mysql
      - mysql_sync

  - name: Stop MySQL on all nodes
    shell: >
      "{{tripleo_container_cli}}" exec $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" --filter=name=galera-bundle) \
        /usr/bin/mysqladmin -u root shutdown
    tags:
      - mysql
      - mysql_sync

  - name: Remove firewall rule and allow database connections through the VIP
    iptables:
      chain: INPUT
      destination: "{{ mysql_vip.stdout }}"
      destination_port: 3306
      protocol: tcp
      jump: DROP
      comment: Temporarily block connections to MySQL VIP
      state: absent
    tags:
      - iptables
      - iptables_accept

  # Restarting containers just in case some undesired process is still running
  - name: Restart MySQL container
    shell: |
      "{{tripleo_container_cli}}" container restart $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" --filter=name=galera-bundle)
    tags:
      - mysql
      - mysql_restored

  - name: Restart clustercheck container
    shell: |
      "{{tripleo_container_cli}}" container restart $("{{tripleo_container_cli}}" container ls --all --format "{{ '{{' }} .Names {{ '}}' }}" --filter=name=clustercheck)
    tags:
      - mysql
      - mysql_restored

  - name: Manage Galera resource
    command: pcs resource manage galera-bundle
    when: inventory_hostname == groups.controller[0]
    tags:
      - pacemaker_management
      - mysql_restored

  - name: What to do next
    debug:
      msg:
        - 'Things to do next:'
        - '- Run "pcs status" and check the Galera resource bundle recovers correctly'
        - '  ** Eventually the 3 nodes should be identified as "Master" **'
        - '- Cleanup cluster resources'
        - '- Test OpenStack APIs'
        - "- Remove backup files from /var/lib/mysql on node {{ groups.controller[0] }}"
        - "  ** files {{ mysql_db_file }} and {{ mysql_grants_file }}"
    run_once: True
    tags:
      - end_message