Ⅰ、xtrabackup 介绍
- xtrabackup 只能备份 innodb 引擎的数据, 不能备份表结构,percona 开源的, 强烈推荐最新版本 (旧版本 bug 多)
- innobackupex 可以备份 myisam 和 innodb 两种引擎的数据和表结构, 一般用这个
- 备份时, 默认读取 MySQL 配置文件 (datadir)
Ⅱ、xtrabackup 安装使用
2.1 安装
[root@VM_0_5_centos src]# yum install perl-DBD-MySQL
不安装这个备份会报错：Failed to connect to MySQL server: DBI connect
[root@VM_0_5_centos src]# cd /usr/local/src
[root@VM_0_5_centos src]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/tarball/percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz
[root@VM_0_5_centos src]# tar zxvf percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz -C ..
添加环境变量
[root@VM_0_5_centos src]# cd ..
[root@VM_0_5_centos src]# ln -s percona-xtrabackup-2.4.7-Linux-x86_64/ xtrabackup
[root@VM_0_5_centos src]# echo "PATH=/usr/local/xtrabackup/bin:$PATH" >> /etc/profile
[root@VM_0_5_centos src]# source /etc/profile2.2 玩一手
[root@VM_0_5_centos src]# innobackupex --compress --compress-threads=8 --stream=xbstream -S /tmp/mysql.sock --parallel=4 /data/backup/ > /data/backup/backup.xbstream
建议用 - S 连接, 默认走 socket, 不用 - S 可能报连不上
常用参数：throttle
指定备份时用到的 iops 是多少, 限制速度 8 个压缩线程,4 个备份线程
输出内容（简化）
190620 19:47:53 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
190620 19:47:53 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root' (using password: YES).
190620 19:47:53 version_check Connected to MySQL server
190620 19:47:53 version_check Executing a version check against the server...
190620 19:47:53 version_check Done.
190620 19:47:53 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /tmp/mysql.sock
Using server version 5.7.20-log
innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
xtrabackup: uses posix_fadvise().
# 连接数据库并做两次版本检查
xtrabackup: cd to /mdata/mysql_test_data
xtrabackup: open files limit requested 0, set to 100001
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
190620 19:47:53 >> log scanned up to (10304795)
# 读取配置文件, 寻找对应的文件及日志位置
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 51 for dump_test/dump_inno, old maximum was 0
xtrabackup: Starting 4 threads for parallel data files transfer
190620 19:47:53 [04] Compressing and streaming ./ibdata1
190620 19:47:53 [03] Compressing and streaming ./dump_test/dump_inno.ibd
190620 19:47:53 [03] ...done
190620 19:47:53 [03] Compressing and streaming ./test/test.ibd
190620 19:47:53 [02] Compressing and streaming ./test/sbtest1.ibd
190620 19:47:53 [03] ...done
...
190620 19:47:54 >> log scanned up to (10304795)
190620 19:47:54 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
190620 19:47:54 Executing FLUSH TABLES WITH READ LOCK...
190620 19:47:54 Starting to backup non-InnoDB tables and files
190620 19:47:54 [01] Compressing and streaming ./dump_test/dump_inno.frm to <STDOUT>
190620 19:47:54 [01] ...done
190620 19:47:54 [01] Compressing and streaming ./dump_test/db.opt to <STDOUT>
190620 19:47:54 [01] ...done
...
190620 19:47:55 Finished backing up non-InnoDB tables and files
# 拷贝数据
190620 19:47:55 [00] Compressing and streaming xtrabackup_binlog_info
190620 19:47:55 [00] ...done
# 获取二进制文件日志点
190620 19:47:55 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '10304786'
190620 19:47:55 >> log scanned up to (10304795)
xtrabackup: Stopping log copying thread.
190620 19:47:55 Executing UNLOCK TABLES
190620 19:47:55 All tables unlocked
# 停止拷贝, 释放锁
190620 19:47:55 [00] Compressing and streaming ib_buffer_pool to <STDOUT>
190620 19:47:55 [00] ...done
190620 19:47:55 Backup created in directory '/data/backup'
MySQL binlog position: filename 'bin.000006', position '154'
190620 19:47:55 [00] Compressing and streaming backup-my.cnf
190620 19:47:55 [00] ...done
190620 19:47:55 [00] Compressing and streaming xtrabackup_info
190620 19:47:55 [00] ...done
xtrabackup: Transaction log of lsn (10304786) to (10304795) was copied.
190620 19:47:55 completed OK!
190620 19:47:55 [00] ...done
190620 19:47:55 Backup created in directory '/data/backup'
MySQL binlog position: filename 'bin.000006', position '154'
190620 19:47:55 [00] Compressing and streaming backup-my.cnf
190620 19:47:55 [00] ...done
190620 19:47:55 [00] Compressing and streaming xtrabackup_info
190620 19:47:55 [00] ...done
xtrabackup: Transaction log of lsn (10304786) to (10304795) was copied.
190620 19:47:55 completed OK!
# 生成各种文件, 备份结束 Ⅲ、xtrabackup 原理分析
3.1 xtrabackup 全备步骤
| – | 操作 | 解析 |
|---|---|---|
| step1 | Connecting to MySQL server host | 连接登录 |
| step2 | using the following InnoDB configuration | 读相关配置文件 |
| step3 | start xtrabackup_log | 启用日志文件, 记录 redo 的 lsn, 同时持续扫描 redo log, 将新产生的 redo 拷贝到 xtrabackup_logfile |
| step4 | copy innodb tables .ibd、.ibdata1、undo logs | 拷贝 innodb 表的独立表空间、共享表空间、undo 日志 |
| step5 | flush no_write_to_binlog tables、flush tables with read lock | 强制将 commit log 刷入 redo 防止数据丢失 (5.6 之前没有), 锁表 |
| step6 | copy non-innodb tables .MYD、.MYI、.opt、misc files 和 innodb tables .frm、.opt、misc files | 拷贝 myisam 表相关内容和 innodb 表的表结构文件 |
| step7 | Get binary log position | 获取二进制日志位置点, 写入到 xtrabackup_binlog_info 文件 |
| step8 | flush no_write_to_binlog engine logs | 将 redo 刷盘 |
| step9 | stopping log copying thread | 停止拷贝 |
| step10 | unlock tables | 释放锁 |
| step11 | completed OK | 生成各种文件, 备份结束 |
tips：
①简单点说：一个线程备份 redo, 贯穿整个过程始终, 另外的线程备份表空间文件, 直到 completed OK, 备份成功
②5.6 之前的 xtrabackup 有丢数据的风险, 强烈建议使用最新版本
③和 mysqldump、mydumper 相比,xtrabackup 备份的是结束时间点的数据 (二进制文件位置点不一样), 所以物理备份除了本身恢复块之外, 同步也快, 因为不用拉数据, 做一个一小时的备份, 逻辑备份需要做一个小时的数据同步, 物理备份不需要
④备份过程中遇到 myisam 还是会阻塞, 数据一致性需求
Ⅳ、xtrabackup 备份恢复
4.1 查看备份文件
由于我这里用的是流文件的方式备份的, 所以要先打开流文件
[root@VM_0_5_centos backup]# xbstream -x < backup.xbstream
[root@VM_0_5_centos backup]# ll
total 2792
drwxr-x--- 2 root root 4096 July 20 19:47 abc
-rw-r----- 1 root root 417 July 20 19:47 backup-my.cnf.qp
-rw-r--r-- 1 root root 1822257 July 20 19:46 backup.xbstream
drwxr-x--- 2 root root 4096 July 20 19:47 dump_test
-rw-r----- 1 root root 370 July 20 19:47 ib_buffer_pool.qp
-rw-r----- 1 root root 969374 July 20 19:47 ibdata1.qp
drwxr-x--- 2 root root 4096 July 20 19:47 mysql
drwxr-x--- 2 root root 4096 July 20 19:47 performance_schema
drwxr-x--- 2 root root 12288 July 20 19:47 sys
drwxr-x--- 2 root root 4096 July 20 19:47 test
-rw-r----- 1 root root 102 July 20 19:47 xtrabackup_binlog_info.qp
-rw-r----- 1 root root 115 July 20 19:47 xtrabackup_checkpoints
-rw-r----- 1 root root 494 July 20 19:47 xtrabackup_info.qp
-rw-r----- 1 root root 391 July 20 19:47 xtrabackup_logfile.qp
看到很多 qp 文件, 是因为备份时做了压缩, 我们需要将其解压
[root@VM_0_5_centos backup]# for f in `find ./ -iname "*\.qp"`; do qpress -dT4 $f $(dirname $f) && rm -f $f; done
[root@VM_0_5_centos backup]# ll
drwxr-x--- 2 root root 4096 July 20 19:57 abc
-rw-r--r-- 1 root root 427 July 20 19:57 backup-my.cnf
-rw-r--r-- 1 root root 1822257 July 20 19:46 backup.xbstream
drwxr-x--- 2 root root 4096 July 20 19:57 dump_test
-rw-r--r-- 1 root root 413 July 20 19:57 ib_buffer_pool
-rw-r--r-- 1 root root 12582912 July 20 19:57 ibdata1
drwxr-x--- 2 root root 4096 July 20 19:57 mysql
drwxr-x--- 2 root root 12288 July 20 19:57 performance_schema
drwxr-x--- 2 root root 12288 July 20 19:57 sys
drwxr-x--- 2 root root 4096 July 20 19:57 test
-rw-r--r-- 1 root root 15 July 20 19:57 xtrabackup_binlog_info
-rw-r----- 1 root root 115 July 20 19:47 xtrabackup_checkpoints
-rw-r--r-- 1 root root 521 July 20 19:57 xtrabackup_info
-rw-r--r-- 1 root root 2560 July 20 19:57 xtrabackup_logfile
可以看到, 除了备份表空间等, 还生成了 4 个文件 看下 4 个文件
[root@VM_0_5_centos backup]# cat xtrabackup_binlog_info # 记录 binlog 文件名和 position
bin.000006 154
------
[root@VM_0_5_centos backup]# cat xtrabackup_checkpoints # 记录备份过程中 checkpoint、lsn 信息
backup_type = full-backuped
from_lsn = 0
to_lsn = 10304786
last_lsn = 10304795
compact = 0
recover_binlog_info = 0
------
[root@VM_0_5_centos backup]# cat xtrabackup_info # 整个备份过程中的信息
uuid = 48febc78-0012-11e8-b724-525400a4dac1
name =
tool_name = innobackupex
tool_command = --compress --compress-threads=8 --stream=xbstream -S /tmp/mysql.sock --parallel=4 ./
tool_version = 2.4.7
ibbackup_version = 2.4.7
server_version = 5.7.20-log
start_time = 2019-06-20 19:47:51
end_time = 2019-06-20 19:47:56
lock_time = 0
binlog_pos = filename 'bin.000006', position '154'
innodb_from_lsn = 0
innodb_to_lsn = 10304786
partial = N
incremental = N
format = xbstream
compact = N
compressed = compressed
encrypted = N
------
xtrabackup_logfile # 持续备份的 redo, 直接看不了 4.2 恢复一手瞅瞅
step1: 应用日志, 将 backup 恢复
[root@VM_0_5_centos mdata]# innobackupex --apply-log backup
step2：将恢复好的数据拷贝到 datadir, 直接 move 也行
[root@VM_0_5_centos mdata]# innobackupex --copy-back backup
step3：修改文件属主
[root@VM_0_5_centos mdata]# chown -R mysql:mysql mysql_test_data
step4：启动数据库
/etc/init.d/mysql.server start
Starting MySQL. SUCCESS!tips：
- 日志应用完成后,backup 文件中会多出一个文件：xtrabackup_binlog_pos_innodb, 记录的是用于 innodb 的 binlog 的当前 position, 而 xtrabackup_binlog_info 记录的是整个实例当前的 binlog position
- 般情况下, 这两个位置点是一样的, 但备份时两种引擎都存在时, 则有可能出现 xtrabackup_binlog_info.pos > xtrabackup_binlog_pos_innodb.pos
- 所以我们一般用 xtrabackup_binlog_info 中的 binlog position
Ⅴ、其他相关问题
5.1 增量备份
–incremental-history-name=name 可使用改参数做增量备份
但非常不建议用这个增量备份功能, 性能特别差
若昨天全备 100G, 今天更新了 30G, 做增量要扫描 100G 文件才知道哪些页改动了, 再去备份, 线上很难接受
percona 有个参数可以监控哪些页改动了, 所以不用去扫之前的所有备份的表空间, 但用的也比较少
要做增量, 用二进制日志的机制来做即可
5.2 指定库表备份
同样不推荐这种玩法, 强烈建议完整备份
如果实例使用的是共享表空间 (ibdata), 不完整备份的话, 可能会遇到各种问题
比如备份了 a 库, 没备份 b 库, 用这个备份恢复后在 b 库下面创建一个和之前同名的表就创建不了
5.3 远程备份
innobackupex --compress --compress-threads=8 --stream=xbstream --user=root --parallel=4 ./ | ssh root@192.168.1.192 "xbstream -x -C /data/www/mysql/backup"