一，原理及介绍
〇 xtrabackup能做哪些
对innodb引擎的表做热备
增量备份
流压缩传输到另外的服务器上
在线移动表
更简单的创建从库
备份时不增加服务器负载
〇 原理
备份及恢复大致涉及三个步骤：备份 -> prepare -> 恢复
备份运行时，工具会记住当时的lsn号，并打开xtrabackup_logfile，然后开始对datafile进行copy，即ibdata1及ibd文件。
复制需要一定的时间，在复制期间，如果文件被修改，工具将监视redo log file并将每一次更变记录下来，保存在xtrabackup_logfile中。
接下来处理非事务表如myisam的备份操作，innobackupex通过flush tables with read lock来阻塞dml。
并在此时获取binlog的position[和gtid]（此处我理解为和mysqldump --single-transaction处理方式类似）
在做完非事务表的copy之后，执行unlock tables，完成备份，并停止记录xtrabackup_logfile。
接下来就是需要做prepare的过程，该过程类似innodb的crash-recovery。
对redo log进行前滚（到数据文件），并将没提交的事务进行回滚操作（rollback），这样便可以保证数据的一致性，所以对于事务表，整个过程是不会影响写操作的。
注：innodb、xtradb、myisam是肯定支持的，其他的存储引擎没有测过。
〇 权限需求
对datadir需要有rwx的权限。
mysql：
最小所需要的权限有：
reload
lock tables（如果加上--no-lock的话可以不要）
replication client（为了获得binary log的position）
process（为了执行show engine innodb status，并且需要查看所有运行的线程）
其他可能需要用到的权限：
create tablespace（如果需要通过5.6+ 的tts恢复/迁移单个表的话）
super（可能需要在复制环境里启动或者停止slave线程）
create\insert\select（对percona_schema.xtrabackup_history进行操作）
二、下载安装
官方地址：https://www.percona.com/downloads/xtrabackup/latest/
安装：
tar xf percona-xtrabackup-2.4.12-r170eb8c-el7-x86_64-bundle.tar
rpm -vih percona-xtrabackup*.rpm 或者yum install -y percona-xtrabackup-*
三，全量备份与恢复
•备份数据
语法：innobackupex [--defaults-file=/etc/my.cnf] --user=dbuser --password=dbuserpass [--socket=/var/lib/mysql/mysql.sock --port=3306] /path/to/backup-dir/
我要评论
[root@server data]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=939879412 /data/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=server-bin.log --server-id=1
xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --log_bin=server-bin.log --server-id=1
181029 14:41:49 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!".
181029 14:41:50 version_check connecting to mysql server with dsn 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using passw
ord: yes).181029 14:41:50 version_check connected to mysql server
181029 14:41:50 version_check executing a version check against the server...
181029 14:41:50 version_check done.
181029 14:41:50 connecting to mysql server host: localhost, user: root, password: set, port: not set, socket: not set
using server version 5.6.41
innobackupex version 2.4.12 based on mysql server 5.7.19 linux (x86_64) (revision id: 170eb8c)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 65535
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
181029 14:41:50 >> log scanned up to (2048699761)
xtrabackup: generating a list of tablespaces
innodb: allocated tablespace id 4 for mysql/slave_master_info, old maximum was 0
181029 14:41:51 [01] copying ./ibdata1 to /data/2018-10-29_14-41-49/ibdata1
181029 14:41:51 >> log scanned up to (2048699761)
181029 14:41:52 >> log scanned up to (2048699952)
181029 14:41:53 >> log scanned up to (2048700648)
181029 14:41:54 >> log scanned up to (2048700949)
181029 14:41:55 >> log scanned up to (2048701233)
181029 14:41:56 >> log scanned up to (2048701233)
181029 14:41:57 >> log scanned up to (2048701533)
181029 14:41:58 >> log scanned up to (2048701928)
.........
181029 14:42:09 finished backing up non-innodb tables and files
181029 14:42:09 executing flush no_write_to_binlog engine logs...
xtrabackup: the latest check point (for incremental): '2048706153'
xtrabackup: stopping log copying thread.
.181029 14:42:09 >> log scanned up to (2048706153)
181029 14:42:09 executing unlock tables
181029 14:42:09 all tables unlocked
181029 14:42:09 backup created in directory '/data/2018-10-29_14-41-49/'
181029 14:42:09 [00] writing /data/2018-10-29_14-41-49/backup-my.cnf
181029 14:42:09 [00] ...done
181029 14:42:09 [00] writing /data/2018-10-29_14-41-49/xtrabackup_info
181029 14:42:09 [00] ...done
xtrabackup: transaction log of lsn (2048670711) to (2048706153) was copied.
181029 14:42:10 completed ok!
当出现innobackupex: completed ok!
出现上面的信息，表示备份已经ok。
查看备份的结果
[root@server data]# ll total 4 drwxr-x--- 7 root root 4096 oct 29 14:42 2018-10-29_14-41-49 [root@server data]# cd 2018-10-29_14-41-49/ [root@server 2018-10-29_14-41-49]# ls backup-my.cnf ibdata1 mysql performance_schema test test1 xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile zabbix
将数据库备份到远程目录
前提条件：
a.远程机器的目录要存在，同时保证是空目录。远程机器目录非空，备份时报以下错误"xtrabackup: error writing file 'unopened' (errcode: 32 - broken pipe)"
b.ssh要能连接：使用ssh秘钥免密码输入
innobackupex --user=user --password=passwd --socket=/var/lib/mysql/mysql.sock --port=3306 --stream=tar /data/temp/ | gzip | ssh root@远程主机host "cat -> /data/temp/mysql_full_backup_2018-10-29.tar.gz"
在备份的文件夹中，有几个文件值得注意：
xtrabackup_binlog_pos_innodb记录了binlog的position，若开启了gtid，也会将gtid取出。
在用于备份+binlog恢复或建立slave的场景里十分有用。
xtrabackup_checkpoints记录了此次备份的类型和lsn号的起始值，是否压缩等
xtrabackup_info则记录了备份工具的信息，时间，备份对象（是针对全实例还是某库表），是否是增量，binlog位置等
backup-my.cnf文件，则记录了备份时可能涉及到的选项参数，比如系统表空间信息，独立undo表空间信息，redo-log信息等
• 恢复数据
注意：恢复之前
1）要先关闭数据库
2）要删除数据文件和日志文件（也可以mv移到别的地方，只要确保清空mysql数据存放目录就行）
apply-log 类似innodb的crash recovery
[root@server data]# innobackupex --apply-log /data/2018-10-29_14-41-49/ #prepare过程
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=
ibdata1:12m:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 xtrabackup: recognized client arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=
ibdata1:12m:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 181029 14:50:05 innobackupex: starting the apply-log operation
important: please check that the apply-log run completes successfully.
at the end of a successful apply-log run innobackupex
prints "completed ok!".
innobackupex version 2.4.12 based on mysql server 5.7.19 linux (x86_64) (revision id: 170eb8c)
xtrabackup: cd to /data/2018-10-29_14-41-49/
xtrabackup: this target seems to be not prepared yet.
innodb: number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2048670711)
xtrabackup: using the following innodb configuration for recovery:
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following innodb configuration for recovery:
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: starting innodb instance for recovery.
xtrabackup: using 104857600 bytes for buffer pool (set by --use-memory parameter)
innodb: punch hole support available
innodb: mutexes and rw_locks use gcc atomic builtins
innodb: uses event mutexes
innodb: gcc builtin __sync_synchronize() is used for memory barrier
innodb: compressed tables use zlib 1.2.3
innodb: number of pools: 1
innodb: using cpu crc32 instructions
innodb: initializing buffer pool, total size = 100m, instances = 1, chunk size = 100m
innodb: completed initialization of buffer pool
innodb: page_cleaner coordinator priority: -20
innodb: highest supported file format is barracuda.
innodb: log scan progressed past the checkpoint lsn 2048670711
innodb: doing recovery: scanned up to log sequence number 2048706153 (0%)
innodb: database was not shutdown normally!
innodb: starting crash recovery.
innodb: starting an apply batch of log records to the database...
innodb: progress in percent: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 innodb: apply batch completed
innodb: xtrabackup: last mysql binlog file position 11989, file name ./server-bin.000044
innodb: creating shared tablespace for temporary tables
innodb: setting file './ibtmp1' size to 12 mb. physically writing the file full; please wait ...
innodb: file './ibtmp1' size is now 12 mb.
innodb: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
innodb: 32 non-redo rollback segment(s) are active.
innodb: waiting for purge to start
innodb: 5.7.19 started; log sequence number 2048706153
innodb: xtrabackup: last mysql binlog file position 11989, file name ./server-bin.000044
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
innodb: fts optimize thread exiting.
innodb: starting shutdown...
innodb: shutdown completed; log sequence number 2048707915
innodb: number of pools: 1
xtrabackup: using the following innodb configuration for recovery:
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: punch hole support available
innodb: mutexes and rw_locks use gcc atomic builtins
innodb: uses event mutexes
innodb: gcc builtin __sync_synchronize() is used for memory barrier
innodb: compressed tables use zlib 1.2.3
innodb: number of pools: 1
innodb: using cpu crc32 instructions
innodb: initializing buffer pool, total size = 100m, instances = 1, chunk size = 100m
innodb: completed initialization of buffer pool
innodb: page_cleaner coordinator priority: -20
innodb: setting log file ./ib_logfile101 size to 48 mb
innodb: setting log file ./ib_logfile1 size to 48 mb
innodb: renaming log file ./ib_logfile101 to ./ib_logfile0
innodb: new log files created, lsn=2048707915
innodb: highest supported file format is barracuda.
innodb: log scan progressed past the checkpoint lsn 2048708108
innodb: doing recovery: scanned up to log sequence number 2048708117 (0%)
innodb: database was not shutdown normally!
innodb: starting crash recovery.
innodb: xtrabackup: last mysql binlog file position 11989, file name ./server-bin.000044
innodb: removed temporary tablespace data file: "ibtmp1"
innodb: creating shared tablespace for temporary tables
innodb: setting file './ibtmp1' size to 12 mb. physically writing the file full; please wait ...
innodb: file './ibtmp1' size is now 12 mb.
innodb: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
innodb: 32 non-redo rollback segment(s) are active.
innodb: waiting for purge to start
innodb: 5.7.19 started; log sequence number 2048708117
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
innodb: fts optimize thread exiting.
innodb: starting shutdown...
innodb: shutdown completed; log sequence number 2048713467
181029 14:50:09 completed ok!
当出现innobackupex: completed ok!
出现上面的信息，表示恢复已经ok。
# cp -r 2018-10-29_14-41-49/* /var/lib/mysql/ 将数据拷贝到数据库的数据目录下
也可以用此命令拷贝 innobackupex --copy-back /var/lib/mysql/
# chown mysql:mysql /var/lib/mysql/ -r
重启数据库
四，增量备份与恢复
①增备方法与全备不一样：
innobackupex --user= --password= --incremental $new_dir --incremental-basedir=$basedir
其中--incremental是本次增量备份存放目录
$new_dir是表示将增量备份出来的东西放在哪个目录
--incremental-basedir则表示，针对哪一次备份做增量备份
备份的差异在目录的xtrabackup_checkpoints中查看：
比如：
$basedir中内容：
backup_type = full-prepared
from_lsn = 0
to_lsn = 2048706153
last_lsn = 2048706153
compact = 0
recover_binlog_info = 0
$new_bkdir中内容：
backup_type = incremental
from_lsn = 2048706153
to_lsn = 2048876543
last_lsn = 2048876543
compact = 0
recover_binlog_info = 0
可以注意一下增备的from_lsn号
大于这个lsn号的页都是被变更过的，这些偏移量，也就是需要被增量备份出去的
②prepare：
prepare过程：
从第一个备份开始（也就是全量）做prepare，再将往后的增量备份依次添加到全量备份中。
注意，此处多了一个参数即--redo-only，该参数是指将已提交的事务应用，未提交的事务回滚。
此外，--incremental-dir也是在之前没有用到过的，这个参数代表需要被合并进去的增量备份目录。
注意，此处多次的增量备份是指：针对上次的增量备份做的增量。
也就是可以理解为：
全备：500gb
第一次增量备份：2gb
第二次增量备份：1gb（针对第一次增量备份的增量数据）
……
第n次
按照备份顺序做prepare，也就是prepare的顺序为：
第一次全备 -> 增量备份1 -> 增量备份2 -> ... -> 增量备份n
第一次全备的prepare：innobackup --apply-log --redo-only $basedir
第二次prepare：innobackup --apply-log --redo-only $basedir --incremental-dir=$new_dir_1（此处的$new_dir_1也就是第一次增量备份）
......
第n次prepare：innobackup --apply-log $basedir --incremental-dir=$new_dir_n（此处的$new_dir_n也就是最近也就是最后一次的增量备份
最后一次增量备份的prepare，不需要指定--redo-only
最后将增量备份和全备进行合并，将未提交的事务回滚，这个操作和全量prepare无异：
innobackup --apply-log $basedir
③恢复到datadir：
和全量无异，直接copyback就行了
innobackupex --copy-back $basedir
增量备份的prepare有点特殊，还是小结一下：
① prepare完备（加上--redo-only）
② prepare每一次增量备份到完备中，需要加上--redo-only，最后一次增量备份的prepare不需要加--redo-only
③ 对生成的最终完备做--apply-log
执行第一次增量备份之后，可以再做一次增量备份
此时有两种增量备份方法：
第一种，总是针对basedir做增量，这个方式恢复起来就特别简单了，只需要将最后一次的增量备份合并到全量备份里，就可以恢复了。
第二种，总是针对上一次的增量，做增量备份。这个方式的恢复，就要逐一合并了，也就是我上述所说看起来有点复杂的增备思路。
图示：
第一种：
总是将1月1日的全备作为basedir，所以from_lsn号总是5000。
第二种：
总是把上一次（最近一次）的备份作为basedir。
如对本文有疑问， 点击进行留言回复！！
linux 安装 MySql 5.7.20 一主多从配置【亲测】 - 之三
Linux： ------ MySQL 基于MyCat的主从复制 读写分离
lower_case_table_names=1 启动报错 mysql8.0
全网最全经典卷积神经网络架构汇总——深度学习之ILSVRC竞赛(ImageNet竞赛)优胜网络结构
网友评论