В кратце:
# 1) Full:
innobackupex --include=monitoring.* --parallel=8 --extra-lsndir=/root/backup/lsn \
--stream=xbstream --no-timestamp /root/backup/full 2>/root/backup/logs/full.log | lzop -c > /root/backup/streams/full-monitoring.xbstream.lzo
# 2) 1st incremental:
innobackupex --include=monitoring.* --parallel=8 --incremental --extra-lsndir=/root/backup/lsn \
--incremental-lsn=$(grep "to_lsn" /root/backup/lsn/xtrabackup_checkpoints | awk -F'=' '{print $2}' | sed 's/^[ \t]*//;s/[ \t]*$//') \
--stream=xbstream /root/backup/inc 2>/root/backup/logs/1st.log | lzop -c > /root/backup/streams/inc-1st-monitoring.xbstream.lzo
# 3) 2nd incremental:
innobackupex --include=monitoring.* --parallel=8 --incremental --extra-lsndir=/root/backup/lsn \
--incremental-lsn=$(grep "to_lsn" /root/backup/lsn/xtrabackup_checkpoints | awk -F'=' '{print $2}' | sed 's/^[ \t]*//;s/[ \t]*$//') \
--stream=xbstream /root/backup/inc 2>/root/backup/logs/2nd.log | lzop -c > /root/backup/streams/inc-2nd-monitoring.xbstream.lzo
# 4) 3rd incremental:
innobackupex --include=monitoring.* --parallel=8 --incremental --extra-lsndir=/root/backup/lsn \
--incremental-lsn=$(grep "to_lsn" /root/backup/lsn/xtrabackup_checkpoints | awk -F'=' '{print $2}' | sed 's/^[ \t]*//;s/[ \t]*$//') \
--stream=xbstream /root/backup/inc 2>/root/backup/logs/3rd.log | lzop -c > /root/backup/streams/inc-3rd-monitoring.xbstream.lzo
# 5) etc... etc...
Далее восстанавливаем:
#### Restore from the backup (stream) ####
# 1) Unpack full backup
mkdir -p /root/backup/data/full
lzop -dcfU /root/backup/streams/full-monitoring.xbstream.lzo | xbstream -x --directory=/root/backup/data/full
# 2) Unpack incremental backups
mkdir -p /root/backup/data/1st
lzop -dcfU /root/backup/streams/inc-1st-monitoring.xbstream.lzo | xbstream -x --directory=/root/backup/data/1st
mkdir -p /root/backup/data/2nd
lzop -dcfU /root/backup/streams/inc-2nd-monitoring.xbstream.lzo | xbstream -x --directory=/root/backup/data/2nd
mkdir -p /root/backup/data/3rd
lzop -dcfU /root/backup/streams/inc-3rd-monitoring.xbstream.lzo | xbstream -x --directory=/root/backup/data/3rd
# 3) Replay the commited transactions on full (base) backup:
innobackupex --apply-log --redo-only /root/backup/data/full --use-memory=1G
# 4) Apply incrementals backups (1st, 2nd and 3rd in our case) to the base backup
innobackupex --apply-log --redo-only /root/backup/data/full --incremental-dir=/root/backup/data/1st --use-memory=1G
innobackupex --apply-log --redo-only /root/backup/data/full --incremental-dir=/root/backup/data/2nd --use-memory=1G
innobackupex --apply-log --redo-only /root/backup/data/full --incremental-dir=/root/backup/data/3rd --use-memory=1G
# 5) Preparing again the full backup (base + incrementals) once again to rollback the pending transactions:
innobackupex-1.5.1 --apply-log /root/backup/data/full --use-memory=1G
# 6) Have fun :)
# Now your backup is ready to be used immediately after restoring it. This preparation step is optional, as if you restore it without doing it,
# the database server will assume that a crash occurred and will begin to rollback the uncommitted transaction
# (causing some downtime which can be avoided).
Коменты на английском, потому что скрипт готовится для одной испанской компании, которая имеет базы по несколько сот гигабайт.
Для получения всех вкусностей инкрементального бэкапа необходимо чтобы все таблицы были типа InnoDB и была установлена опция innodb_file_per_table=1. Физически таблица будет представлять два файла - определение таблицы и данные таблицы:
acknowledges.frm
acknowledges.ibd
actions.frm
actions.ibd
alerts.frm
alerts.ibd
applications.frm
applications.ibd
Восстановленный бекап можно просто подложить под другой сервер тупо файлами.
