mysql xtrabackup 增量复制

╰+哭是因爲堅強的太久メ 2022-10-05 14:58 137阅读 0赞

--------------------

### mysql xtrabackup 增量复制 ###

**官方文档：[https://www.percona.com/doc/percona-xtrabackup/LATEST/backup\_scenarios/incremental\_backup.html][https_www.percona.com_doc_percona-xtrabackup_LATEST_backup_scenarios_incremental_backup.html]**

### \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* ###

**增量复制**

**xtrabackup支持增量复制：**只复制自上次备份后发生变化的数据

xtrabackup supports incremental backups, which means that they 
    can copy only the data that has changed since the last backup
    
    You can perform many incremental backups between each full backup, 
    so you can set up a backup process such as 
    a full backup once a week and an incremental backup every day, 
    or full backups every day and incremental backups every hour
    # 每周进行一次全量复制，并且每天进行一次增量复制
    # 或者每天进行一次全量复制，每小时进行一次增量复制

**增量复制原理**

# InnoDB数据页有一个LSN，增量复制复制当前LSN比上次全量复制或者增量复制LSN大的数据页
    An incremental backup copies each page whose LSN is newer than the previous incremental or 
    full backup’s LSN. 
    
    # 有两种算法查找需要复制的数据页
    There are two algorithms in use to find the set of such pages to be copied. 
    
    # 第一种：扫描所有的数据页
    The first one, available with all the server types and versions, is to check the 
    page LSN directly by reading all the data pages. 
    
    # 第二种：Percona服务器会将记录修改过的数据页信息记录在bitmap文件中
    The second one, available with Percona Server for MySQL, is to enable the changed page 
    tracking feature on the server, which will note the pages as they are being changed. This 
    information will be then written out in a compact separate so-called bitmap file

## ##

### \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* ###

**使用注意**

**增量复制不会和之前备份的数据文件做比较，在部分复制后做增量复制可能或导致数据不一致**

Incremental backups do not actually compare the data files to the previous backup’s data 
    files. For this reason, running an incremental backup after a partial backup may lead to 
    inconsistent data

**增量复制只是读取数据页，将其和上次备份的LSN做比对，增量复制需配合全量复制使用**

Incremental backups simply read the pages and compare their LSN to the last backup’s LSN. 
    You still need a full backup to recover the incremental changes, however; 
    
    # 没有全量复制做基础，增量复制没有意义
    without a full backup to act as a base, the incremental backups are useless.

**如果知道上一次复制的LSN，也可以直接进行增量复制**

You can use the --incremental-lsn option to perform an incremental backup without even 
    having the previous backup, if you know its LSN

## ##

### \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* ###

**相关参数**

**--incremental-basedir：**增量复制基础目录，为上一次备份目录（全量或者增量复制目录）

**--apply-log-only：**只应用日志，不进行数据回滚

# prepare阶段，增量复制和全量复制不同
    The --prepare step for incremental backups is not the same as for full backups. 
    
    # 全量复制：事务提交则保存数据文件中的相关数据，事务未提交则回滚数据文件中的相关数据
    In full backups, two types of operations are performed to make the database consistent: 
    committed transactions are replayed from the log file against the data files, and 
    uncommitted transactions are rolled back. 
    
    # 增量复制：数据不回滚，事务可能在增量备份后提交，如果回滚会导致后续备份数据丢失，从而造成数据不一致
    You must skip the rollback of uncommitted transactions when preparing an incremental 
    backup, because transactions that were uncommitted at the time of your backup may be in 
    progress, and it’s likely that they will be committed in the next incremental backup. You 
    should use the --apply-log-only option to prevent the rollback phase

### \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* ###

**示例**

**创建 mysql 容器**

docker run -it  -d --net fixed --ip 172.18.0.3 -p 3306:3306 \
    -v /usr/mysql/single/data:/var/lib/mysql \
    --privileged=true -e  MYSQL_ROOT_PASSWORD=123456 --name mysql mysql

**全量备份**

xtrabackup -u root --password=123456 -H 192.168.57.120 -P 3306 --backup \
    --log-bin=/usr/mysql/single/data/binlog --log-bin-index=/usr/mysql/single/data/binlog.index \
    --datadir=/usr/mysql/single/data --target-dir=/usr/mysql/single/backup

**增量备份 **

# 增量备份 1
    xtrabackup -u root --password=123456 -H 192.168.57.120 -P 3306 --backup \
    --log-bin=/usr/mysql/single/data/binlog --log-bin-index=/usr/mysql/single/data/binlog.index \
    --datadir=/usr/mysql/single/data --target-dir=/usr/mysql/single/incr \
    --incremental-basedir=/usr/mysql/single/backup
    
    # 增量备份 2
    xtrabackup -u root --password=123456 -H 192.168.57.120 -P 3306 --backup \
    --log-bin=/usr/mysql/single/data/binlog --log-bin-index=/usr/mysql/single/data/binlog.index \
    --datadir=/usr/mysql/single/data --target-dir=/usr/mysql/single/incr2 \
    --incremental-basedir=/usr/mysql/single/incr

**xtrbackup\_checkpinots(记录备份LSN信息)**

# 全量备份
    [root@centos backup]# cat xtrabackup_checkpoints
    backup_type = full-backuped     #备份类型：全量备份
    from_lsn = 0                    #from_lsn：起始lsn
    to_lsn = 28622501               #to_lsn：last chechpoint
    last_lsn = 28622501             #last_lsn：最后复制的lsn
    flushed_lsn = 28622501          #flushed_lsn：刷盘lsn
    
    
    # 增量备份
    [root@centos single]# cat incr/xtrabackup_checkpoints
    backup_type = incremental
    from_lsn = 28622501             # from_lsn = 上次复制的 to_lsn
    to_lsn = 28622521               
    last_lsn = 28622975
    flushed_lsn = 28622521
    
    
    # 增量备份 2
    [root@centos single]# cat incr2/xtrabackup_checkpoints
    backup_type = incremental
    from_lsn = 28622521             # from_lsn = 上次复制的 to_lsn
    to_lsn = 28622995
    last_lsn = 28622995
    flushed_lsn = 28622995

**prepare数据：\--apply-log-only，不回滚数据**

xtrabackup --prepare --apply-log-only --target-dir=/usr/mysql/single/backup
    xtrabackup --prepare --apply-log-only --target-dir=/usr/mysql/single/backup --incremental-dir=/usr/mysql/single/incr
    xtrabackup --prepare --apply-log-only --target-dir=/usr/mysql/single/backup --incremental-dir=/usr/mysql/single/incr2

**restore 数据到目录 data2：data2需为空目录**

xtrabackup --copy-back --datadir=/usr/mysql/single/data2 --target-dir=/usr/mysql/single/backup

[https_www.percona.com_doc_percona-xtrabackup_LATEST_backup_scenarios_incremental_backup.html]: https://www.percona.com/doc/percona-xtrabackup/LATEST/backup_scenarios/incremental_backup.html