freehck.script_mysql_backup

Panoramica Versioni Approfondimenti

freehck.script_mysql_backup

=========

This tool helps create a script for backing up MySQL databases.

It saves the database, can compress it with gzip, and encrypt the backup using AES256.

It also has the ability to send notifications to Slack.

The backup can be stored on S3 or transferred to another server using SCP.

Role Variables

Basic Variables

mysql_backup_host: Address of the MySQL server.
mysql_backup_port: Port for MySQL (default is 3306).
mysql_backup_user: Username for MySQL.
mysql_backup_pass: Password for MySQL.
mysql_backup_db: Specific database to backup. If not provided, it backs up all databases.
mysql_backup_backend_use_s3: Set to true to store the backup in S3.
mysql_backup_backend_use_scp: Set to true to transfer the backup to another server with SCP.

S3 Configuration

mysql_backup_s3cfg_template: Template for S3 configuration (default is provided).
mysql_backup_s3: Your S3 settings, like username, access key, secret key, and bucket name.

SCP Configuration

mysql_backup_scp_host: Server to store the backup.
mysql_backup_scp_user: User to log into the storage server.
mysql_backup_scp_dst: Path on the storage server for the backup.
mysql_backup_scp_identity_src: Private key for logging into the storage server.

Naming and File Structure

mysql_backup_archive_prefix: Name prefix for the backup file.
mysql_backup_archive_stamp: Format for timestamps (default is %F-%Hh%Mm%Ss).
mysql_backup_script_name: Rename the base backup script if desired.
mysql_backup_custom_script_name: Name for the actual backup job script (default is based on database name).
mysql_backup_scp_identity_name: Default is id_rsa, but can be changed for different SCP setups.
mysql_backup_encrypt_aes_key_name: Default is aes256.key, useful for different encryption keys.

Notifications

mysql_backup_warn_size: Size in GiB to compare against. If the backup is smaller, a warning will be issued.
mysql_backup_hostname: Hostname to show in Slack messages.
mysql_backup_slack_webhook: Slack webhook URL to receive notifications.

To get a Slack webhook, visit: https://get.slack.help/hc/en-us/articles/115005265063-Incoming-WebHooks-for-Slack

Compression and Encryption

mysql_backup_gzip: To enable gzip compression for the backup file.
mysql_backup_encrypt_aes: To encrypt the backup file (encryption occurs after gzip if enabled).
mysql_backup_encrypt_aes_key_src: AES256 key for encrypting the backup.

To create an AES256 key (32 bytes of random data), run: dd if=/dev/urandom of=aes256.key count=1 bs=32. For a string password (less secure), you can use: pwgen -n1 -s 32 | tr -d '\n' >aes256.key

Directories

mysql_backup_script_dir: Where to store the main script.
mysql_backup_custom_script_dir: Where to keep specific backup job scripts.
mysql_backup_conf_dir: Directory for backup script config files.
mysql_backup_encrypt_aes_key_dir: Directory for the AES256 encryption key.
mysql_backup_tmpdir: Temporary results storage (default is /tmp).

Passing Data Outside the Role

mysql_backup_save_facts_about_custom_script: Set to true to save the generated job script path as mysql_backup_last_generated_custom_script. Use this for scheduling cron jobs.

Example Playbook

# Create MySQL backup job script
- role: freehck.script_mysql_backup
  mysql_backup_host: "{{ db_host }}"
  mysql_backup_user: "{{ db_user }}"
  mysql_backup_pass: "{{ db_pass }}"
  mysql_backup_db: "{{ db_name }}"
  mysql_backup_backend_use_s3: no
  mysql_backup_backend_use_scp: yes
  mysql_backup_scp_host: "{{ hostvars['storage'].ansible_host }}"
  mysql_backup_scp_identity_src: "{{ playbook_dir }}/files/id_rsa.bkp.db01"
  mysql_backup_scp_user: 'file'
  mysql_backup_scp_dst: '/var/www/file/public/mysql-db-prod-backup'
  mysql_backup_gzip: yes
  mysql_backup_encrypt_aes: yes
  mysql_backup_encrypt_aes_key_src: "{{ playbook_dir }}/files/aes256.bkp.key"
  mysql_backup_save_facts_about_custom_script: yes
  mysql_backup_logfile: "/var/log/mysql-backup.log"
  tags: [ backup, mysql ]

# Create a cron job for this script
- role: freehck.crontask
  crontask_file: "backups"
  crontask_name: "backup database"
  crontask_hour: "12"
  crontask_minute: "0"
  crontask_job: "{{ mysql_backup_last_generated_custom_script }}"
  crontask_user: "root"
  crontask_commented_out: false
  tags: [ backup, mysql ]

Important Information

After deploying the MySQL backup script, it's a good idea to run the job script from /opt/scripts without any parameters. If it runs smoothly and you see the backup file, everything is working. If not, you may have issues, such as forgetting to add the storage host to known_hosts or having a mistake in your s3cfg template. Always double-check everything, as backing up data is critical.

Once you confirm the backup is created and in the right place, check it carefully to ensure you can restore from it.