Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Previously i just shared the full backup script. Now we will take incremental backups using last_checkpoints.
Basically, what this script will do is that it reads the last lsn_checkpoint and start taking incrmenetal from that point and finally update the recent lsn to the file LSN_FILE_NAME.
We don’t need full backup directory for taking incrementals.We can go with the LSN no also.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | #!/bin/sh LSN_FILE_NAME=$(ls -td /home/db/lsns/* | head -n 1) LAST_LSN_CHECKPOINT=$(cat /home/db/lsns/$LSN_FILE_NAME/xtrabackup_checkpoints | grep to_lsn | cut -d'=' -f2) INCREMENTAL_PATH=/home/db/ FILE=$(date +%Y-%m-%d_%H_%M_%S) echo $FILE innobackupex --extra-lsndir=/home/db/lsns/$LSN_FILE_NAME --user=bkp_usr --password='bkppwd@2018' --datadir=/var/lib/mysql -S /var/lib/mysql/mysql.sock --incremental $INCREMENTAL_PATH/$FILE --incremental-lsn $LAST_LSN_CHECKPOINT --no-timestamp if [ $? -eq 0 ]; then cd $INCREMENTAL_PATH tar -cv $FILE | gzip > $FILE.tar.gz fi cd /tmp/ cd $INCREMENTAL_PATH FILE_TYPE="*.gz" find ${FILE_TYPE} -type f -mtime +2 -delete find * -type d -delete cd /tmp/ cd /home/db/lsns find * -type d -mtime +2 -delete |
–extra-lsndir: save an extra copy of the xtrabackup_checkpoints and xtrabackup_info files in this directory.
–incremental-lsn: This option accepts a string argument that specifies the log sequence number (LSN) to use for the incremental backup. It is used with the –incremental option. It is used instead of specifying –incremental-basedir.
Restoring incremental Backups
1.Preparing the backup
1 2 3 4 5 6 7 | xtrabackup --prepare --apply-log-only --target-dir=/data/fullbackup/2018-09-29_18_00_00 xtrabackup --prepare --apply-log-only --target-dir=/data/fullbackup/2018-09-29_18_00_00 \ --incremental-dir=/data/incremental/2018-09-29_19_00_00 xtrabackup --prepare --apply-log-only --target-dir=/data/fullbackup/2018-09-29_18_00_00 \ --incremental-dir=/data/incremental/2018-09-29_20_00_00 |
2.Restoring the backup
1 2 3 4 5 6 7 | xtrabackup --copy-back --target-dir=/data/fullbackup/2018-09-29_18_00_00 or cp -R /data/fullbackup/2018-09-29_18_00_00 /var/lib/mysql chown -R mysql:mysql /var/lib/mysql |
Note – Please go through with previous percona articles for better understanding
1 . https://dbtut.com/index.php/2018/09/01/mysql-physical-backup-using-percona-xtrbackup-tool/
2 . https://dbtut.com/index.php/2018/09/29/mysql-database-backup-using-percona-xtrabackup-automated-bash-script/
