Ansible Role: gcoop-libre.mysqldump

This role sets up access to MySQL/MariaDB/Percona on your local machine and creates a cron job for backups using the mysqldump command.

Requirements

• python-mysqldb
• MySQL/MariaDB/Percona

Optional

The Ansible Pass Lookup Plugin lets you use a password store that is compatible with pass to create encrypted passwords. It works like the usual password lookup but uses pass instead of plain text files.

Role Variables

You can find available variables with their default values in defaults/main.yml.

gcoop_mysqldump_cron_entries:

  - name: monthly
    minute: 0
    hour: 2
    day: '*/7'
    format: '%b'
    database: "{{ gcoop_mysqldump_database }}"

  - name: daily
    minute: 0
    hour: 1
    day: '*'
    format: '%a'
    database: "{{ gcoop_mysqldump_database }}"

  - name: hourly
    minute: 30
    hour: '*'
    day: '*'
    format: ''
    database: 'mysql'

The default gcoop_mysqldump_cron_entries creates the following backup files in the /backup/sql directory:

One file for each weekday:

    hostname-dbname1-lun.sql.gz
    hostname-dbname1-mar.sql.gz
    hostname-dbname1-mie.sql.gz
    hostname-dbname1-jue.sql.gz
    hostname-dbname1-vie.sql.gz
    hostname-dbname1-sab.sql.gz
    hostname-dbname1-dom.sql.gz
    hostname-dbname2-lun.sql.gz
    hostname-dbname2-mar.sql.gz
    hostname-dbname2-mie.sql.gz
    hostname-dbname2-jue.sql.gz
    hostname-dbname2-vie.sql.gz
    hostname-dbname2-sab.sql.gz
    hostname-dbname2-dom.sql.gz

One file for each month (updated every 7 days):

    hostname-dbname1-ene.sql.gz
    hostname-dbname1-feb.sql.gz
    ...
    hostname-dbname1-dic.sql.gz
    hostname-dbname2-ene.sql.gz
    ...
    hostname-dbname2-feb.sql.gz
    hostname-dbname2-dic.sql.gz

One file that gets updated with the latest dump (updated every hour):

    hostname-dbname1.sql.gz
    hostname-dbname2.sql.gz

Syslog

By default, this role logs the cron job activity to syslog:

root@dbserver:/backup/sql# tail -f /var/log/syslog

Jun 25 02:30:02 dbserver mysqldump: Start: 2016-06-25 02:30 End: 2016-06-25 02:30 Duration: 00:00:01 File: c2340030bd3ec4c5b890a7e7ae80fd18  /backup/sql/dbserver-mysql.sql.gz
Jun 25 02:30:02 dbserver mysqldump: Start: 2016-06-25 02:30 End: 2016-06-25 02:30 Duration: 00:00:00 File: 5b5f36528df547e08f42db615e22ded6  /backup/sql/dbserver-information_schema-jun.sql.gz
Jun 25 02:30:02 dbserver mysqldump: Start: 2016-06-25 02:30 End: 2016-06-25 02:30 Duration: 00:00:00 File: 6d952bdd7edf0e5663bbd487dbcaf46c  /backup/sql/dbserver-mysql-jun.sql.gz

Dependencies

None.

Example Playbook

To create a new MySQL user with the pass plugin:

    - hosts: localhost
      become: yes
      remote_user: debian

      roles:
        - role: gcoop-libre.mysqldump
          gcoop_mysqldump_create_mysql_user: yes
          gcoop_mysqldump_create_system_user: yes
          gcoop_mysqldump_mysql_username: mysqlbackup
          gcoop_mysqldump_mysql_password: "{{ lookup('pass', 'mysql/' + gcoop_mysqldump_mysql_username) }}"
          gcoop_mysqldump_mysql_root_password: "{{ lookup('pass', 'mysql/root') }}"
          gcoop_mysqldump_system_password: "{{ lookup('pass', 'users/' + gcoop_mysqldump_mysql_username) }}"

If you already have a system and MySQL user:

    - hosts: localhost
      become: yes
      remote_user: debian

      roles:
        - role: gcoop-libre.mysqldump
          gcoop_mysqldump_mysql_username: mark
          gcoop_mysqldump_mysql_password: dadada
          gcoop_mysqldump_mysql_root_password: 123456

License

GNU General Public License, GPLv3.

Author Information

This role was created in 2016 by Osiris Alejandro Gomez, from the worker cooperative gcoop Cooperativa de Software Libre.