I have service and MySql database on my Raspberry Pi 2B. Yesterday, my SD card in my phone died. I started thinking about database backup on my Raspberry Pi.
Anybody have any experience with this? I want to backup my Database to the USD flash driver every night.
The official documentation gives step by step instructions for backup.
Incremental backup allows you to just store the changes:
https://mariadb.com/kb/en/incremental-backup-and-restore-with-mariabackup/
You can use cron to schedule the task to run nightly.
All of the regular MySQL backup methods work:
mysqldump - adequate for small databases, up to a few GB. Typical invocation:
mysqldump --single-transaction --all-databases | pigz -1 > /path/to/backup.sql.gz
xtrabackup - adequate for backups of most sizes
Snapshot based backups (e.g. using ZFS or LVM snapshots). Take a snapshot, create a tarball off the snapshotted data, or use zfs send if using zfs on both the source and target servers.
The mysqldump command will output a text file that is an SQL script for restoring the database. You can use mysqldump in a script launched using cron if you leave the system running.
The text file can be zipped.
Remember that the least reliable part of a computer system is the one between the chair and the keyboard. You are far more likely to accidentally delete data than lose it because of hardware failure. For that reason rsync is pretty useless as a backup tool. Instead use cp with the --backup=numbered parameter. As a rule of thumb don't consider your data to be safe unless you have copies on at least three devices in at least two locations.
