Script to handle full and incremental mysql backups using xtrabackup/innobackupex
Get the latest relese of qpress and Xtrabackup and install it on the origin and recipient system.
Generate an SSH-key on the recipient-system without a passphrase.
$ sudo useradd xtrabackup
$ sudo su - xtrabackup
$ ssh-keygen -b 4096 -t rsa -f /tmp/sshkey -q -N ""
Add the public key and limitations to the mysql-user on the origin system ~/.ssh/authorized_keys
command="/usr/bin/innobackupex $SSH_ORIGINAL_COMMAND",no-port-forwarding,no-x11-forwarding,no-agent-forwarding key_type key
Create a .my.cnf file for the mysql user on the origin system
$ echo '[client]
user=USERNAME
password=PASSWORD' >> ~/.my.cnf
Within the script you can set the backup path
h_path # Backup path, default /backup/xtrabackup/${host}
f_freq # Full backup frequency
retention # Retentionperiod
Run it as follows:
$ ./innobackupex-remote.sh <origin-hostname> [Incremental]
Backups will be Full unless specified as Incremental. If the host is unknown or if there hasn't been a full backup since f_freq days ago the backup will be Full. As you run it will store your backups in the destination-folder divided by dates and hours.
$ find /backup/xtrabackup/ -maxdepth 2
/backup/xtrabackup/
/backup/xtrabackup/origin-hostname
/backup/xtrabackup/origin-hostname/2019-12-06_23
/backup/xtrabackup/origin-hostname/2019-12-07_00
Within the folders you'll also find the xtrabackup.log which contains the output of the backup-operation.
The recover process will identify the last available full backup and apply any potential incremental backups. The script expects docker to be present on the system. Once run a docker instance will be made available on localhost:3306
./restoredb.sh /backup/xtrabackup/origin-hostname/YYYY-MM-DD-HH