Use systemd timers to make MariaDB backups#
Systemd timers are a great way to replace cron jobs. They are more flexible and can have dependencies that can defined in easy statements instead of creating a script to check for the dependency. One of the use-cases is to use systemd timers to create MariaDB backups and remove old backups. In this example, we will create a backup of two databases, db1 and db2, and remove backups older than 10 days.
Install MariaDB#
First step is to install MariaDB on the system. This example is for Debian or Ubuntu and uses the root user to connect to the database. It is recommended to create a user with limited privileges to connect to the database.
$ sudo apt-get install mariadb-server
The second step is to create the databases and users. This example creates two databases, db1 and db2, and two users, db1 and db2. The passwords are set to cangetin. It is recommended to use a more secure password.
$ mysql -u root -p"secret" -e "CREATE DATABASE db1"
$ mysql -u root -p"secret" -e "GRANT ALL ON db1.* TO 'db1' identified by 'cangetin'"
$ mysql -u root -p"secret" -e "CREATE DATABASE db2"
$ mysql -u root -p"secret" -e "GRANT ALL ON db2.* TO 'db2' identified by 'cangetin'"
Create the systemd unit files#
The next step is to create the systemd unit files. The first file is the target file. The target file is used to group the services together. The second file is the service file. The service file is used to create the mysqldump command. The third file is the timer file. The timer file is used to define when the service should run.
/etc/systemd/system/mariadb-dump.target#[Unit]
Description=MariaDB dump target
After the target unit file is created the service file can be created. The service file is used to check if the backup directory exists and then create the backup with the mysqldump command. The mysqldump command is run as the root user and the password is set to secret. This service file is similar to the unit files in post Manage multiple systemd service instances where the %i is used to define the instance name. In this example, the instance name is the database name.
/etc/systemd/system/mariadb-dump@.service#[Unit]
Description=MariaDB dump for %i
Requires=mariadb.service
[Service]
Type=simple
ExecStartPre=/usr/bin/test -d /srv/backups/mariadb/daily
ExecStart=/bin/sh -c “/usr/bin/mysqldump -u root -psecret %i > /srv/backups/mariadb/daily/%i.sql”
[Install]
WantedBy=mariadb-dump.target
Warning
The password is in the service file. It is recommended to use a more secure method by using a password file or a password prompt.
The last file is the timer file. The timer file is used to define when the service should run. In this example, the service is run daily, but it can also run on other intervals.
/etc/systemd/system/mariadb-dump.timer#[Unit]
Description=Execute MariaDB dumps
[Timer]
OnCalendar=daily
Unit=mariadb-dump.target
[Install]
WantedBy=multi-user.target
The final step is to reload the systemd daemon to load the new unit files.
$ sudo systemctl daemon-reload
Now that the systemd unit files are created the timer can be enabled. The timer is enabled to run daily.
$ sudo systemctl enable mariadb-dump.timer
Enabling the systemd units#
Now that everything is in place the instances can be enabled. In this example, the instances are db1 and db2.
$ sudo systemctl enable mariadb-dump@db1
$ sudo systemctl enable mariadb-dump@db2
Note
Disabling a systemd unit can be done with the disable command.
$ sudo systemctl disable mariadb-dump@db1
The new systemd units will be started by the timer, but the timer can also be started manually.
$ sudo systemctl start mariadb-dump.timer
Verify the systemd timers#
By using systemd you can easily list the timers and see when they will run next or when they last ran.
$ sudo systemctl list-timers
Removing old MariaDB backups#
Removing old backups can be done with the tmpfiles.d configuration. The tmpfiles.d configuration is used to remove old files and directories. In this example, the tmpfiles.d configuration is used to remove backups older than 10 days.
/etc/tmpfiles.d/mariadb-dump.conf for tmpfiles#d /srv/backups/mariadb/daily 0750 root root 10d -
The clean-up can be done manually or by using the systemd-tmpfiles-clean command, but is also done automatically by systemd on an interval.