vuutla / ansible-mysql-backup

Ansible playbook to create MySQL database backup from remote server.

Description

I have wordpress websites running on VPS server and I need a solution to backup these databases regulerly. We will create an ansible playbook (step by stepp process) to automate the solution.

Prerequisites

• ssh key (make sure public key has been uploaded to remote server)
• ansible
• A running instance of MySQL

Step 1: Create inventory file

Create new file as ‘server.inventory.ini’

touch server.inventory.ini

Add the following details

[Server-VPS]
# server address here

[Server-VPS:vars]
ansible_connection=ssh
ansible_user= # remote server username

Step 2: Create vault file

Step 2.1: Create file with secrets

Create new file as ‘database_vault.yaml’

touch database_vault.yaml

Add the following details

db_name: # database name here
db_user_name: # database username here
db_user_password: # database user password here

Step 2.2: Encrypt secret file

Please run the following command to encrypt newly created secrets file.

ansible-vault encrypt database_vault.yaml

# output
New Vault password: 
Confirm New Vault password:
Encryption successful

Step 3: Create playbook

Create new file as ‘database_backup_playbook.yaml’

touch database_backup_playbook.yaml

Add the following details

---
- hosts: Server-VPS
  gather_facts: true
  vars_files:
    - database_vault.yaml
  tasks:
    - name: Create variables
      set_fact:
        db_file_name: "{{ db_name }}_{{ ansible_date_time.date | replace('-','') }}.sql"
    
    - name: Confirm hostname
      debug:
        msg: Logged into the server.

    - name: Download Database to server
      shell: |
        mysqldump -u {{ db_user_name}} -p"{{ db_user_password }}" {{ db_name }} --quick --lock-tables=false > "{{ db_file_name }}" --no-tablespaces
      no_log: true

    - name: Wait until the database backup completed on server
      wait_for:
        path: "{{ db_file_name }}"
        state: present
        msg: "Timeout to find file {{ db_file_name }}"

    - name: Downloading backup to local computer
      ansible.builtin.fetch:
        src: "{{ db_file_name }}"
        dest: "{{ db_file_name }}"
        flat: yes

    - name: Download completed
      debug:
        msg: Database have been downloaded successfully.

Step 4: Time to playbook to run manually in command-line:

Step 4.1 - Run ssh-agent

If you have the passpharace added to ssh key then it will be easier to run it manually.

eval $(ssh-agent)
ssh-add ./path/to/ssh/key

Step 4.2 - Execute playbook

Please execute following command to run the playbopk and start the process.

ansible-playbook -i server.inventory.ini database_backup_playbook.yaml --ask-vault-password

Step 4.3: Enter vault password.

Please enter the vault password so that ansible can decyrpt the `database_vault.yaml` variables to use in the playbook.

Install `AWX` on `k3s`cluster

• Please use the following link to view step by step docmentation to install `awx` on `k3s`.
• Please use the following link to view Step by step process to build ubuntu-server on raspberry-pi with k3s, kubernetes-dashboard, awx and awx job template with ansible paybooks (mysql datbase backup from remote server).