FULL BACKUPS

The innobackupex program is a symlink to the xtrabackup C program. It lets you perform point-in-time backups
of InnoDB / XtraDB tables together with the schema definitions, MyISAM tables, and other portions of the server. In previous versions innobackupex was implemented as a Perl script.

Warning: The innobackupex program is deprecated. Please switch to xtrabackup

Creating a backup

1
xtrabackup --backup --target-dir=/data/backups/

Preparing a backup

1
xtrabackup --prepare --target-dir=/data/backups/

Restoring a Backup

有三种方式

  1. #将备份文件拷贝到 datadir
xtrabackup --copy-back --target-dir=/data/backups/  

    1
    2
    3
    4

    2. ```bash
       #将备份文件移动到 datadir
       xtrabackup --move-back --target-dir=/data/backups/
    

  2. rsync -avrP /data/backup/ /var/lib/mysql/  
chown -R mysql:mysql /var/lib/mysql

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15


    ## Incremental Backup  

    ### Creating an Incremental Backup  

    ```bash
    #先创建一个全备
    xtrabackup --backup --target-dir=/data/backups/base  

    #第一次增备
    xtrabackup --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base
     
    #第二次增备
    xtrabackup --backup --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1

每次的进度信息可以查看 xtrabackup_checkpoints 文件

1
2
3
4
5
6
7
8
cat xtrabackup_checkpoints

backup_type = incremental
from_lsn = 4124244
to_lsn = 6938371
last_lsn = 7110572
compact = 0
recover_binlog_info = 1

Preparing the Incremental Backup

前面的备份会生成如下文件夹

1
2
3
/data/backups/base
/data/backups/inc1
/data/backups/inc2  
1
2
3
4
5
6
7
8
# prepare the base backup
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base  

# apply the first incremental backup to the full backup
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base  --incremental-dir=/data/backups/inc1

# Preparing the second incremental backup 
xtrabackup --prepare --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2

重要提示：在 merge 所有的增备文件的时候，除了最后一次操作，其他都需要指定 –apply-log-only 参数。

因为 –prepare 做了两个操作，一是提交该提交的事务，而是回滚该回滚的事务，–apply-log-only 是说不回滚当前log 里的事务，因为这些事务可能正在进行中，还没结束。

文档原文是：

The xtrabackup –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 xtrabackup –apply-log-only option to prevent the rollback phase.

COMPRESSED BACKUP

Creating Compressed Backups

1
2
3
4
xtrabackup --backup --compress --target-dir=/data/compressed/  

#或
xtrabackup --backup --compress --compress-threads=4 --target-dir=/data/compressed/

Preparing the backup

1
2
3
4
5
6
7
8
# prepare 之前要先解压
xtrabackup --decompress --target-dir=/data/compressed/  
#可以增加 --parallel 参数加快速度
xtrabackup --decompress --parallel --target-dir=/data/compressed/  
#可以增加 --remove-original 移除压缩文件(默认解压不删除压缩文件)
xtrabackup --decompress --parallel --remove-original --target-dir=/data/compressed/  

xtrabackup --prepare --target-dir=/data/compressed/

Restoring the backup

1
2
xtrabackup --copy-back --target-dir=/data/backups/
chown -R mysql:mysql /var/lib/mysql

Partial Backups

部分备份的前置条件：开启了 innodb_file_per_table ，下面的实验假设 有个 test 库，里面有 t1 ，t2 两张表

Creating a backup

使用 xtrabackup –tables

这种方式只支持正则匹配

1
2
3
4
5
6
# 备份 test 库的所有表
 xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ --tables="^test[.].*"
 
# 备份 test.t1
 xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ --tables="^test[.]t1"

xtrabackup –tables-file

这种方式只支持 databasename.tablename 方式，大小写敏感，不支持正则写法

1
2
$ echo "mydatabase.mytable" > /tmp/tables.txt
$ xtrabackup --backup --tables-file=/tmp/tables.txt

xtrabackup –databases and xtrabackup –databases-file

1
2
3
4
# xtrabackup --databases 只接受空格分隔的 databasename[.tablename] 形式的库表，而且需要额外备份 mysql, sys, performance_schema  三个库，用于 xtrabackup --copy-back 使用。
xtrabackup --databases='mysql sys performance_schema ...'

# xtrabackup --databases-file 指定 databasename[.tablename] 形式的库表，每个一换行

Preparing the Backup

1
2
# 还是常规的备份方式  
xtrabackup --prepare --export

如果看到如下的 warnings about tables that don’t exist 日志是正常的。

1
2
3
4
5
InnoDB: Reading tablespace information from the .ibd files...
101107 22:31:30 InnoDB: Error: table 'test1/t'
InnoDB: in InnoDB data dictionary has tablespace id 6,
InnoDB: but tablespace with that id or name does not exist. It will be removed from
˓→data dictionary.

Advanced Features

Throttling Backups

1
xtrabackup --throttle

–throttle=#
This option limits the number of chunks copied per second. The chunk size is 10 MB. To limit the bandwidth to
10 MB/s, set the option to 1: –throttle=1.

Point-In-Time recovery

基于时间点你的恢复(使用 innobackupex ，虽然已经标记为弃用状态，但官方没有提供 xtrabackup 方案)

原理：全备+binlog

全备一次

1
2
$ innobackupex /path/to/backup --no-timestamp
$ innobackupex --apply-log /path/to/backup

获取备份文件的点位信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 查看备份时间点的位点信息
$ cat /path/to/backup/xtrabackup_binlog_info
mysql-bin.000003      57

# 查看系统当前 binlog 文件列表
mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       126 |
| mysql-bin.000002 |      1306 |
| mysql-bin.000003 |       126 |
| mysql-bin.000004 |       497 |
+------------------+-----------+

# 查看当前位点信息
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      497 |              |                  |
+------------------+----------+--------------+------------------+

将备份文件回写

1
2
$ innobackupex --copy-back /path/to/backup
-- 此时新系统位点信息为 mysql-bin.000003  57

导出新产生的binlog

导出新产生的binlog，确定下一步的恢复时间点

1
$ mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 --start-position=57 > mybinlog.sql

恢复

指定需要恢复的时间点(–stop-datetime)

1
2
$ mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 --start-position=57 --stop-datetime="11-12-25 01:00:00" | mysql -u root -p
-- (不能加 --base64-output='DECODE-ROWS' -vv)

innobackupex

新版本已经移除，简单了解。

Full Backup

1
2
3
4
5
6
7
8
9
10
# backup
innobackupex --defaults-file=/tmp/other-my.cnf --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

# preparing 
innobackupex --apply-log /path/to/BACKUP-DIR

#restore
innobackupex --copy-back /path/to/BACKUP-DIR
or
scp && chown -R mysql:mysql /var/lib/mysql

Incremental Backup

Creating a backup

1
2
3
4
5
6
7
8
9
10
11
12
#full backup
innobackupex /data/backups 
-- 假设生成 /data/backups/2013-03-31_23-01-18 ,称为 BASEDIR.

# incremental 1  
innobackupex --incremental /data/backups --incremental-basedir=BASEDIR
-- 假设生成 /data/backups/2013-04-01_23-01-18 ,称为 INCREMENTAL-DIR-1.

# incremental 2
innobackupex --incremental /data/backups --incremental-basedir=INCREMENTAL-DIR-1
-- 假设生成 /data/backups/2013-04-02_23-01-18 ,称为 INCREMENTAL-DIR-2.

Restoring a Backup

1
2
3
4
innobackupex --apply-log --redo-only BASE-DIR
innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

Note: innobackupex –redo-only should be used when merging all incrementals except the last one. That’s
why the previous line doesn’t contain the innobackupex –redo-only option. Even if the innobackupex
–redo-only was used on the last step, backup would still be consistent but in that case server would perform the rollback phase.

Once you merge the base with all the increments, you can prepare it to roll back the uncommitted transactions:

1
innobackupex --apply-log BASE-DIR

Restoring

1
2
3
4
5
xtrabackup --copy-back --target-dir=BASE-DIR

#如果压缩过，需要先解压
xtrabackup --decompress --target-dir=BASE-DIR
xtrabackup --copy-back --target-dir=BASE-DIR

常用备份命令

1
xtrabackup --defaults-file=/etc/my_3911.cnf --host=10.204.11.75 --user=dba --password="dba@2019" --port=3911 --backup --parallel=4   --target-dir=/data0/backup/20200518/
1
xtrabackup --defaults-file=/etc/my_3605.cnf --prepare --export  --user=dba --password="dba@2019" --host=10.204.11.65 --port=3605 --tables="^queue_leju_com[.]queue$" --target-dir=/data0/20200520_q