MariaDB backup

From Notes to self

Revision as of 18:19, 20 October 2022 by Verbovet (talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Jump to navigation Jump to search

/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
# Ensure that the datadir (/var/lib/mysql) is empty
# /etc/rc.d/rc.mysqld stop
# 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.

Retrieved from "https://alik.ejik.org/index.php?title=MariaDB_backup&oldid=2368"

Linux