Install MySQL and schedule backups with Ansible

Mar 29, 2023 ansible bash homelab mysql

In this article, we will install and configure MySQL and schedule database backups in Ubuntu using Ansible.

Prerequisites

Ansible
An Ubuntu (or other Debian-based distro) host
Package python3-pip installed in host

A variable mysql_password is required in the playbook. It is recommended to store it as a secret.

Run the tasks as root. Either in the host definintion:

mysql ansible_user=me ansible_become=yes

or at play level:

hosts: mysql
become: yes

Installation and basic configuration

Install the required APT packages and the Python development headers and libraries (required by the MySQL collection in Ansible):

- name: install mysql
  apt:
    name:
      - mysql-server
      - default-libmysqlclient-dev
    update_cache: yes

- name: enable and start mysql
  service:
    name: mysql
    state: started
    enabled: yes

- name: install mysql python library
  pip:
    name: mysqlclient

Credentials can be saved in $HOME/.my.cnf, so we set the password for the root user and save the credentials in that config file. We set the default user password before creating the config file, otherwise, we will have an authentication error:

- stat:
    path: /root/.my.cnf
  register: mycnf

- name: set default user password
  mysql_user:
    name: root
    password: "{{ mysql_password }}"
  when: "{{ not mycnf.stat.exists }}"

- name: save credentials
  copy:
    dest: /root/.my.cnf
    content: |
      [mysql]
      user = root
      password= {{ mysql_password }}
      host = localhost
      [mysqldump]
      user = root
      password= {{ mysql_password }}
      host = localhost

Allow remote access

If we want to access the MySQL server from a different host, we need to edit the MySQL settings and grant priviledges to the roor user (and allow the port used by the server).

Listen all addresses to allow remote access:

- name: allow remote access
  lineinfile:
    dest: /etc/mysql/mysql.conf.d/mysqld.cnf
    regexp: ^(bind-address\s+=) 127.0.0.1
    line: \1 0.0.0.0
    backrefs: yes
    state: present
  notify:
    - restart mysql

Allow root user to connect remotelly:

- name: allow remote access for default user
  mysql_user:
    name: root
    host: '%'
    priv: '*.*:ALL,GRANT'
    password: "{{ mysql_password }}"
    login_user: root
    login_password: "{{ mysql_password }}"

And allow the port 3306 in the firewall:

- name: allow port 3306
  ufw:
    rule: allow
    port: '3306'
    proto: tcp

We will need to grant priviledges to other users in case we want allow them to connect remotelly.

When changing the configuration file we’ll trigger the handler restart mysql to restart the MySQL service:

handlers:
  - name: restart mysql
    service:
      name: mysql
      state: restarted

Schedule backups

We can create a simple script to backup all or some databases. Here’s an example of a script to backup all the databases and upload the files to a host named backups:

#!/bin/bash

set -e
set -o pipefail

dir=`mktemp -d`
echo Saving tmp mysql backups to $dir...
suffix=$(date +%Y%m%d).sql
query="
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name not in ('mysql','sys','information_schema','performance_schema');
"
for db in `sudo -i mysql -e "$(echo $query)" | grep -v SCHEMA_NAME`; do
    echo Saving mysql database $db...
    path=$dir/$db.$suffix
    sudo -i mysqldump $db > $path
done
for filename in `ls $dir`; do
    echo Uploading mysql database $db...
    scp $dir/$filename backups:/mysql/
done
rm -rf $dir

Note that we are running the mysql commands as user root.

We can then copy that script and schedule its execution, say everyday at 01:00:

- name: copy backup script
  copy:
    src: files/mysql/backup.sh
    dest: "{{ lookup('env', 'HOME') }}/backup_mysql.sh"
    owner: "{{ ansible_user }}"

- name: schedule backup
  cron:
    name: "backup mysql databases"
    minute: "0"
    hour: "1"
    job: "bash {{ lookup('env', 'HOME') }}/backup_mysql.sh 2>&1 | logger -t {{ ansible_user }}-cron"
  become_user: "{{ ansible_user }}"

The file and the cron job can be executed by any other sudoer, in this example, the ansible user configured in the hosts file.