from https://www.percona.com/doc/percona-xtrabackup/2.2/installation/yum_repo.html

Percona XtraBackup

Percona XtraBackup is a free, open source, complete online backup solution for all versions of Percona Server for MySQL, MySQL ® and MariaDB®. With over 2,100,000 downloads, Percona XtraBackup performs online non-blocking, tightly compressed, highly secure backups on transactional systems so that applications remain fully available during planned maintenance windows.

Installing Percona XtraBackup from Percona yum repository

1. Install the Percona repository
   You can install Percona yum repository by running the following command as a root user or with sudo:

   yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

 rpm -Uvh  libev-4.15-7.el7.x86_64.rpm
rpm -Uvh percona-xtrabackup-2.3.10-1.el7.x86_64.rpm


ref https://www.submit.ne.jp/428

# vi /etc/my.cnf
datadir=/var/lib/mysql

XtraBackupでMySQLのバックアップ

バックアップディレクトリ作成。
# mkdir -p /backup/xtrabackup/

# time /usr/bin/innobackupex --user root --password jacky --slave-info /backup/xtrabackup/


 



real    11m31.851s
user    0m16.311s
sys     0m20.164s



11分掛かりました。



when error happen
 Error: failed to execute query FLUSH NO_WRITE_TO_BINLOG TABLES: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation


do
GRANT RELOAD ON *.* TO 'your_user'@'localhost';








from https://www.percona.com/doc/percona-xtrabackup/LATEST/howtos/recipes_ibkx_inc.html

Making an Incremental Backup

Every incremental backup starts with a full one, which we will call the base backup:


innobackupex --user=USER --password=PASSWORD /path/to/backup/dir/




Note that the full backup will be in a timestamped subdirectory of /path/to/backup/dir/ (e.g. /path/to/backup/dir/2011-12-24_23-01-00/).

Assuming that variable $FULLBACKUP contains /path/to/backup/dir/2011-5-23_23-01-18, let’s do an incremental backup an hour later:


innobackupex --incremental /path/to/inc/dir \
  --incremental-basedir=$FULLBACKUP --user=USER --password=PASSWORD




Now, the incremental backup should be in /path/to/inc/dir/2011-12-25_00-01-00/. Let’s call $INCREMENTALBACKUP=2011-5-23_23-50-10.

Preparing incremental backups is a bit different than full ones:

First you have to replay the committed transactions on each backup,


innobackupex --apply-log --redo-only $FULLBACKUP \
 --use-memory=1G --user=USER --password=PASSWORD




The --use-memory option is not necessary, it will speed up the process if it is used (provided that the amount of RAM given is available).

If everything went fine, you should see an output similar to:


111225 01:10:12 InnoDB: Shutdown completed; log sequence number 91514213




Now apply the incremental backup to the base backup, by issuing:


innobackupex --apply-log --redo-only $FULLBACKUP
 --incremental-dir=$INCREMENTALBACKUP
 --use-memory=1G --user=DVADER --password=D4RKS1D3




Note the $INCREMENTALBACKUP.

The final data will be in the base backup directory, not in the incremental one. In this example, /path/to/backup/dir/2011-12-24_23-01-00 or $FULLBACKUP.

If you want to apply more incremental backups, repeat this step with the next one. It is important that you do this in the chronological order in which the backups were done.

You can check the file xtrabackup_checkpoints at the directory of each one.

They should look like: (in the base backup)


backup_type = full-backuped
from_lsn = 0
to_lsn = 1291135




and in the incremental ones:


backup_type = incremental
from_lsn = 1291135
to_lsn = 1291340




The to_lsn number must match the from_lsn of the next one.

Once you put all the parts together, you can prepare again the full backup (base + incrementals) once again to rollback the pending transactions:


innobackupex-1.5.1 --apply-log $FULLBACKUP --use-memory=1G \
  --user=$USERNAME --password=$PASSWORD




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).

1 full backup





innobackupex --user=root --password=jacky  /backup/xtrabackup/





2 incremental backup


innobackupex --incremental /backup/xtrabackup/   --incremental-basedir=/backup/xtrabackup/2017-12-11_11-18-17 --user=root --password=jacky






3 replay the committed transactions on each backup

innobackupex --apply-log --redo-only /backup/xtrabackup/2017-12-11_11-18-17  --use-memory=1G --user=root --password=jacky





4 integerate incremental backup to full backup 

innobackupex --apply-log --redo-only /backup/xtrabackup/2017-12-11_11-18-17 --incremental-dir=/backup/xtrabackup/2017-12-11_11-20-13 --use-memory=1G --user=root --password=jacky





5 rollback

innobackupex --apply-log /backup/xtrabackup/2017-12-11_11-18-17 --use-memory=1G --user=root --password=jacky



time innobackupex --copy-back /backup/xtrabackup/2017-12-11_11-18-17 

from https://read01.com/zh-tw/JgGg.html#.Win11FWWaUk

3、完整備份+增量備份與恢復

 




3.1 完整備份




# innobackupex --user=root --password=jacky /backup/xtrabackup/ #備份後位置是：/mysql_backup/2015-02-08_11-56-48

3.2 增量備份1




# innobackupex --user=root --password=jacky --incremental /backup/xtrabackup/ --incremental-basedir=/backup/xtrabackup/2017-12-08_11-05-56 #指定上次完整備份目錄

3.3 增量備份2




# innobackupex --user=root --password=jacky --incremental /backup/xtrabackup/ --incremental-basedir=/backup/xtrabackup/2017-12-08_11-06-45 #指定上次增量備份目錄

3.4 查看xtrabackup_checkpoints文件，一目了然，可以看到根據日誌序號來增量備份



原文網址：https://read01.com/JgGg.html













4.3 壓縮歸檔備份

innobackupex --databases=test --user=root  --password=jacky  --stream=tar /mysql_backup |gzip > /backup/xtrabackup/`date +%F`.tar.gz
解壓：tar -izxvf `date +%F`.tar.gz

恢復

time innobackupex --apply-log /backup/xtrabackup/2017-12-08_11-48-39

nnobackupex为Percona Xtrabackup工具之一




from https://qiita.com/sunny_510/items/9bb9f55d5e9dfc8fbf52

実作業

バックアップ作成 & 移動

移行元サーバにてバックアップ作成と、移行先への移動を一気に行います。1　2

移行元サーバ

# time innobackupex --user=<mysqluser> --stream=xbstream --compress --no-timestamp /tmp/backup \
| ssh user@otherhost "xbstream -x -C /path/to/backupdir/"

解凍

qpressが必要になりますので、未インストールである場合は導入してください。

移行先サーバ

# yum install -y qpress
# time innobackupex --decompress /path/to/backupdir

ログ適用

Write Ahead Logを適用します。

移行先サーバ

# time innobackupex --apply-log /path/to/backupdir

これにてバックアップ先のディレクトリ内にxtrabackup_binlog_infoが作成されます。
スレーブ設定する場合はログポジションにこれを利用してください。

データ移行

以下の操作は、前もってmysqlを停止しておいてください
事前に移行先サーバのdatadir内を一度削除するか、バックアップをとります。

移行先サーバ

# cp -R /var/lib/mysql /path/to/mysql_backupdir
or
# rm -rf /var/lib/mysql/*

以下のコマンドを実行することで、datadirに対してバックアップデータのコピーが行われます。

移行先サーバ

# time innobackupex --copy-back /path/to/backupdir

移行完了後の作業

copy-backupが完了したのち、datadirの所有権を修正します。

移行先サーバ

# chown -R mysql: /var/lib/mysql

以上で完了です。mysqldを起動し、DB移行が完了しているか確認してみましょう。