MySQL Backup with Ansible

MySQL Backup with Ansible

As someone with a lot of technical knowledge, I sometimes forget the finer details, like specific command-line options. Instead of wasting time looking them up every time, I like to create small Ansible playbooks to handle these tasks. This not only saves me time but also ensures consistency. Plus, it makes it easy for others in the team to run the exact same commands without missing a step.

For example, here's a simple Ansible playbook that backs up a MySQL server, compresses the data with gzip, and securely transfers it to a remote server using SCP:

---
- name: Backup MySQL server
  hosts: mysql
  become: true
  tasks:
    - name: Create backup directory
      file:
        path: /tmp/backup
        state: directory

    - name: Dump MySQL database
      shell: mysqldump -u{{ mysql_user }} -p{{ mysql_password }} {{ mysql_database }} | gzip > /tmp/backup/{{ mysql_database }}.sql.gz

    - name: Copy backup to remote server
      synchronize:
        src: /tmp/backup/
        dest: {{ backup_dest }}
        remote_user: {{ remote_user }}
        private_key: {{ ssh_key }}

This playbook uses the mysqldump command to create a backup of a MySQL database, piping the output through gzip to compress the data. The compressed backup is saved to the /tmp/backup directory on the local server.

Next, the synchronize module is used to securely copy the backup file to a remote server. The remote_user and private_key are specified within the playbook to handle the connection.

Before running the playbook, you'll need to update the following variables with the appropriate values:

• mysql_user: The MySQL user with permissions to perform the backup.
• mysql_password: The password for the MySQL user.
• mysql_database: The name of the MySQL database you want to back up.
• backup_dest: The path on the remote server where the backup file will be copied.
• remote_user: The username for the remote server.
• ssh_key: The SSH key Ansible will use to connect to the remote server.

Note: Don't put credentials in a plaintext readable file. Put them in Ansible Vault instead!

To run the playbook, use the following command:

ansible-playbook -i <inventory_file> playbook.yml

Where <inventory_file> is the file listing your target servers, and playbook.yml is the name of your playbook.

It's essential to first test this playbook in a non-production environment to ensure the backup and restore processes work correctly. Also, make sure the remote server has enough storage space for the backups and that you have permission to write to the destination directory.