MariaDB backup

• /usr/local/sbin/mysql-backup

#!/bin/bash

TS=$(date '+%Y%m%d')
/usr/bin/mariabackup --user=root --backup --stream=xbstream \
    | gzip > /usr/local/mysqlbackups/backup-$TS.xb.gz || exit 1

TS=$(date -d "10 days ago" '+%Y%m%d')
rm -f /usr/local/mysqlbackups/backup-$TS.xb.gz

• root's crontab:

# Run mysql backup at 4:00 every day
0 4 * * * chronic /usr/local/sbin/mysql-backup

• To restore full backup:

• mkdir /root/backupdb
• gunzip -c /usr/local/mysqlbackups/backup-XXXXXXXX.xb.gz | mbstream -x -C /root/backupdb
• mariabackup --prepare --target-dir=/root/backupdb
• /etc/rc.d/rc.mysqld stop
• Ensure that the datadir (/var/lib/mysql) is empty
• mariabackup --copy-back --target-dir=/root/backupdb
• chown -R mysql:mysql /var/lib/mysql
• /etc/rc.d/rc.mysqld sta

• To restore a single database or even a single table, see here.