README.md

This role sets up a cronjob on one Management machine to perform a mysql dump.(via a remote mysql dump to SQL machine) This file is then gzipped and a 'scp' done to transfer it to the destination machine for storage.(SQL_BK_DEST_HOST)

As this script is meant to be portable across clusters, some variables need to defined when called.

Architecture: As SQL machine may not be able to access SQL_BK_DEST_HOST, a management machine must do a dump. Then this is scp'd to SQL_BK_DEST_HOST MySQL password is stored in mysql.conf in the BIN dir of Management Machine Only one Management should do the dump for performance reasons A cron job on SQL_BK_DEST_HOST deletes any backups > 7 days

EXAMPLE USAGE:

ansible-playbook -i static --limit=monarch-mgmt1 slurm_bk.yml

where slurm_bk.yml contains

- hosts: 'ManagementNodes'
  gather_facts: True
  roles:
  - { role: slurm_sql_bk, create_key: "True", SQL_BK_DEST_HOST: "118.138.235.215", SQL_BK_DEST_DIR: "/mnt/backup/monarch", SQL_IP: "172.16.226.88" , sqlUsername: "slurmdb", sqlPasswd: "{{ slurmdb_passwd }}" }

or 

      - { role: slurm_sql_bk, copy_key: "True", SQL_BK_DEST_HOST: "118.138.235.215", SQL_BK_DEST_DIR: "/mnt/backup/monarch", SQL_IP: "172.16.226.88" , sqlUsername: "slurmdb", sqlPasswd: "{{ slurmdb_passwd }}", PRIVATE_KEY: slm_db_backup.private , PUBLIC_KEY: slm_db_backup.public  }

Meaning:

• create_key: If defined to True then ssh-keygen is called on local machine and then:
  • private key copied to ~/.ssh on management node
  • public key inserted into authorized_keys on SQL_BK_DEST_HOST
• copy_key If defined, it copies ssh keys to the SQL and BACKUP HOST. If defined you must also define the variables {{ PUBLIC_KEY }} and {{ PRIVATE_KEY }} which are the local files on the Ansible machine.
• SQL_BK_DEST_HOST: IP number of Machine where we store mysql dumps
• SQL_BK_DEST_DIR: Directory on backup machine for mysql dumps. Note this is owned by ec2-user and is manually created. It should be cluster-specific, i.e. different for each cluster
• SQL_IP: IP number of slurm mysql machine
• sqlUsername,sqlPasswd Mysql username/password, same as in slurmdbd.conf

Other VARIABLES:

defined in default/main.yml

• BIN_DIR: "/root/bin" #where the backup shell script and mysql.conf exists on Management Node
• SQL_BK_DATA_DIR: "/tmp" # where the inital dump exists on the management node. The file is deleted after a scp so it should not fill up disk
• SQL_USER: "ec2-user" The user account on SQL_BK_DEST_HOST. Normally ec2-user but could be something else for security reasons
• PRIVATE_KEY the SSH key on the local machine when copy_key is defined
• PUBLIC_KEY the SSH key ont the local machine when copy_key is defined