内容简介：在备份的同时，innobackupex还会在备份目录中创建如下文件：另外在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。
一. 全量备份恢复：
- 查看原表内容：
MariaDB [(none)]> select * from testdb.students; +----+------------+------+--------+ | id | name | age | gender | +----+------------+------+--------+ | 1 | zhangsan | 15 | f | | 2 | lisi | 15 | m | | 3 | wanger | 25 | m | | 4 | liuwu | 24 | f | | 5 | wangermazi | 28 | f | +----+------------+------+--------+ 5 rows in set (0.00 sec)
1. 备份：
[root@jenkins ~]# innobackupex --user=lxk --host=localhost --password=lxkpass /tmp
180916 11:56:18 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!".
......
中间省略
......
180916 11:56:22 Executing UNLOCK TABLES
180916 11:56:22 All tables unlocked
180916 11:56:22 Backup created in directory '/tmp/2018-09-16_11-56-18'
180916 11:56:22 [00] Writing backup-my.cnf
180916 11:56:22 [00] ...done
180916 11:56:22 [00] Writing xtrabackup_info
180916 11:56:22 [00] ...done
xtrabackup: Transaction log of lsn (1602080) to (1602080) was copied.
180916 11:56:23 completed OK!
[root@jenkins ~]# cat /tmp/2018-09-16_11-56-18/xtrabackup_checkpoints
backup_type = full-backuped #备份类型:全量备份
from_lsn = 0 #起始lsn
to_lsn = 1602080 #结束lsn
last_lsn = 1602080 #总共多少个lsn
compact = 0
recover_binlog_info = 0
[root@jenkins ~]# cat /tmp/2018-09-16_11-56-18/xtrabackup_info
uuid = 7a05430c-b964-11e8-889e-000c29080758
name =
tool_name = innobackupex #备份 工具 名称
tool_command = --user=lxk --host=localhost --password=... /tmp #备份时使用的命令
tool_version = 2.3.6 #工具版本
ibbackup_version = 2.3.6
server_version = 5.5.60-MariaDB
start_time = 2018-09-16 11:56:18 #备份开始时间
end_time = 2018-09-16 11:56:22 #备份结束时间
lock_time = 0
binlog_pos =
innodb_from_lsn = 0
innodb_to_lsn = 1602080
partial = N
incremental = N
format = file
compact = N
compressed = N #是否启用压缩
encrypted = N #是否加密
2. 准备(apply)备份
[root@jenkins ~]# innobackupex --apply-log /tmp/2018-09-16_11-56-18/
180916 12:06:16 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!".
......
中间省略
......
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1602582
180916 12:06:19 completed OK! #此处显示completed OK即表示完成
3. 恢复备份:
（1）停止 mysql 服务
（2）删库
[root@jenkins ~]# rm -rf /var/lib/mysql/*
（3） 通过全量备份恢复数据
[root@jenkins ~]# innobackupex --copy-back /tmp/2018-09-16_11-56-18/
180916 12:11:19 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
innobackupex version 2.3.6 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
180916 12:11:19 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
180916 12:11:19 [01] ...done
.....
中间省略
.....
180916 12:11:20 [01] ...done
180916 12:11:20 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
180916 12:11:20 [01] ...done
180916 12:11:20 [01] Copying ./test/db.opt to /var/lib/mysql/test/db.opt
180916 12:11:20 [01] ...done
180916 12:11:20 completed OK! #显示completed OK即为完成
（4） 修改恢复后文件的属主,属组为mysql
[root@jenkins ~]# ls /var/lib/mysql -l total 28692 -rw-r----- 1 root root 18874368 Sep 16 12:11 ibdata1 -rw-r----- 1 root root 5242880 Sep 16 12:11 ib_logfile0 -rw-r----- 1 root root 5242880 Sep 16 12:11 ib_logfile1 drwx------ 2 root root 4096 Sep 16 12:11 mysql drwx------ 2 root root 4096 Sep 16 12:11 performance_schema drwx------ 2 root root 4096 Sep 16 12:11 test drwx------ 2 root root 4096 Sep 16 12:11 testdb -rw-r----- 1 root root 437 Sep 16 12:11 xtrabackup_info [root@jenkins ~]# chown -R mysql.mysql /var/lib/mysql/* [root@jenkins ~]# ll /var/lib/mysql/ total 28692 -rw-r----- 1 mysql mysql 18874368 Sep 16 12:11 ibdata1 -rw-r----- 1 mysql mysql 5242880 Sep 16 12:11 ib_logfile0 -rw-r----- 1 mysql mysql 5242880 Sep 16 12:11 ib_logfile1 drwx------ 2 mysql mysql 4096 Sep 16 12:11 mysql drwx------ 2 mysql mysql 4096 Sep 16 12:11 performance_schema drwx------ 2 mysql mysql 4096 Sep 16 12:11 test drwx------ 2 mysql mysql 4096 Sep 16 12:11 testdb -rw-r----- 1 mysql mysql 437 Sep 16 12:11 xtrabackup_info
（5） 启动MySQL并查看
[root@jenkins ~]# systemctl start mariadb [root@jenkins ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> select * from testdb.students; #恢复完成 +----+------------+------+--------+ | id | name | age | gender | +----+------------+------+--------+ | 1 | zhangsan | 15 | f | | 2 | lisi | 15 | m | | 3 | wanger | 25 | m | | 4 | liuwu | 24 | f | | 5 | wangermazi | 28 | f | +----+------------+------+--------+ 5 rows in set (0.00 sec)
二. 增量备份及恢复:
1. 全量备份:
[root@jenkins ~]# innobackupex --user=lxk --host=localhost --password=lxkpass /tmp/
180916 12:17:01 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!".
.....
中间省略
.....
180916 12:17:03 Executing UNLOCK TABLES
180916 12:17:03 All tables unlocked
180916 12:17:03 Backup created in directory '/tmp//2018-09-16_12-17-01'
180916 12:17:03 [00] Writing backup-my.cnf
180916 12:17:03 [00] ...done
180916 12:17:03 [00] Writing xtrabackup_info
180916 12:17:03 [00] ...done
xtrabackup: Transaction log of lsn (1602592) to (1602592) was copied.
180916 12:17:03 completed OK!
2. 修改数据库,进行第一次增量备份
- 在testdb.students中添加一条数据:
MariaDB [testdb]> insert into students values (6,'xiaoming',20,'f'); Query OK, 1 row affected (0.00 sec) MariaDB [testdb]> select * from students; +----+------------+------+--------+ | id | name | age | gender | +----+------------+------+--------+ | 1 | zhangsan | 15 | f | | 2 | lisi | 15 | m | | 3 | wanger | 25 | m | | 4 | liuwu | 24 | f | | 5 | wangermazi | 28 | f | | 6 | xiaoming | 20 | f | +----+------------+------+--------+ 6 rows in set (0.00 sec)
- 增量备份:
[root@jenkins ~]# innobackupex --incremental /tmp --incremental-basedir=/tmp/2018-09-16_12-17-01/
180916 12:23:28 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!".
.....
中间省略
.....
180916 12:23:30 [00] ...done
xtrabackup: Transaction log of lsn (1602735) to (1602735) was copied.
180916 12:23:30 completed OK!
3. 添加一条数据,进行第二次增量备份:
- 增加一条数据
MariaDB [testdb]> insert into students values (8,'daming',20,'m'); Query OK, 1 row affected (0.00 sec)
- 第二次增量备份(若此时--incremental-basedir指的是第一次全量备份路径,则为差异备份):
[root@jenkins ~]# innobackupex --incremental /tmp --incremental-basedir=/tmp/2018-09-16_12-23-28/
180916 12:29:08 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!".
.....
中间省略
.....
180916 12:29:10 [00] Writing xtrabackup_info
180916 12:29:10 [00] ...done
xtrabackup: Transaction log of lsn (1603615) to (1603615) was copied.
180916 12:29:10 completed OK!
4. 恢复数据：
（1） 准备(prepare)数据:
- 需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。
- 基于所有的备份将未提交的事务进行“回滚”
（2）准备全量备份文件
[root@jenkins tmp]# innobackupex --apply-log --redo-only 2018-09-16_12-17-01
180916 12:34:06 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!".
.....
中间省略
.....
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1602592
180916 12:34:06 completed OK!
（3） 准备第一次增量备份文件:
- 注: --incremental-dir所指的目录必须为绝对路径
[root@jenkins 2018-09-16_12-17-01]# innobackupex --apply-log --redo-only ./ --incremental-dir=/tmp/2018-09-16_12-23-28
180916 12:38:17 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!".
.....
中间省略
.....
180916 12:38:18 [00] Copying /tmp/2018-09-16_12-23-28/xtrabackup_info to ./xtrabackup_info
180916 12:38:18 [00] ...done
180916 12:38:18 completed OK!
（4） 准备第二次增量备份文件:
[root@jenkins 2018-09-16_12-17-01]# innobackupex --apply-log --redo-only ./ --incremental-dir=/tmp/2018-09-16_12-29-08/
180916 12:42:56 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!".
.....
中间省略
.....
180916 12:42:57 [01] ...done
180916 12:42:57 [00] Copying /tmp/2018-09-16_12-29-08//xtrabackup_info to ./xtrabackup_info
180916 12:42:57 [00] ...done
180916 12:42:57 completed OK!
（5） 执行回滚操作
[root@jenkins tmp]# innobackupex --apply-log /tmp/2018-09-16_12-17-01
180916 12:46:15 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!".
.....
中间省略
.....
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1604128
180916 12:46:18 completed OK!
（6） 关闭MySQL并删除/var/lib/mysql/下所有文件
（7） 恢复数据：
[root@jenkins tmp]# innobackupex --copy-back 2018-09-16_12-17-01/
180916 12:48:39 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
.....
中间省略
.....
180916 12:48:40 [01] Copying ./test/db.opt to /var/lib/mysql/test/db.opt
180916 12:48:40 [01] ...done
180916 12:48:40 completed OK!
（8） 修改/var/lib/mysql/下文件的属主、属组并启动数据库并查看
[root@jenkins mysql]# chown -R mysql.mysql /var/lib/mysql/* [root@jenkins mysql]# ll total 28692 -rw-r----- 1 mysql mysql 18874368 Sep 16 12:48 ibdata1 -rw-r----- 1 mysql mysql 5242880 Sep 16 12:48 ib_logfile0 -rw-r----- 1 mysql mysql 5242880 Sep 16 12:48 ib_logfile1 drwx------ 2 mysql mysql 4096 Sep 16 12:48 mysql drwx------ 2 mysql mysql 4096 Sep 16 12:48 performance_schema drwx------ 2 mysql mysql 4096 Sep 16 12:48 test drwx------ 2 mysql mysql 4096 Sep 16 12:48 testdb -rw-r----- 1 mysql mysql 462 Sep 16 12:48 xtrabackup_info [root@jenkins mysql]# systemctl start mariadb [root@jenkins mysql]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> select * from testdb.students; #恢复完成 +----+------------+------+--------+ | id | name | age | gender | +----+------------+------+--------+ | 1 | zhangsan | 15 | f | | 2 | lisi | 15 | m | | 3 | wanger | 25 | m | | 4 | liuwu | 24 | f | | 5 | wangermazi | 28 | f | | 6 | xiaoming | 20 | f | | 8 | daming | 20 | m | +----+------------+------+--------+ 7 rows in set (0.00 sec)
三. xtrabackup备份目录下文件解读
在备份的同时，innobackupex还会在备份目录中创建如下文件：
- xtrabackup_checkpoints
backup_type = full-backuped 本次备份类型 = 全量备份 from_lsn = 0 起始日志序列号 to_lsn = 258476374114 结束日志序列号 last_lsn = 258476374114 compact = 0 是否压缩 recover_binlog_info = 0 复制恢复时binlog信息
-
其中包括：备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；
- 每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
-
xtrabackup_binlog_info
- mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
[root@dev-core 11.27]# cat xtrabackup_binlog_info mysql-bin.001102 379212660 ac7a95b5-6507-11e8-b052-702084fbc6aa:1-6694, e755a417-6507-11e8-b054-702084fbc7b6:1-38396411
- xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
- xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；
- backup-my.cnf —— 备份命令用到的配置选项信息；
另外在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。
以上所述就是小编给大家介绍的《xtrabackup全量、增量备份恢复mysql数据库》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持！
猜你喜欢:
- PostgreSQL基础备份_增量备份与任意点恢复
- Postgresql备份与增量恢复
- 实战-MySQL定时增量备份（2）
- 浅谈使用 Binlog 实现 MySQL 增量备份
- percona-xtrabackup实现数据库完全，增量的备份和还原（含一些版本问题与坑）
- 细说HTTP增量更新
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
大数据供应链
娜达·R·桑德斯 (Nada R. Sanders) / 丁晓松 / 中国人民大学出版社 / 2015-7-1 / CNY 55.00
第一本大数据供应链落地之道的权威著作，全球顶级供应链管理专家娜达·桑德斯博士聚焦传统供应链模式向大数据转型，助力工业4.0时代智能供应链构建。 在靠大数据驱动供应链处于领先地位的企业中，45% 是零售商，如沃尔玛、亚马逊，而22%是快消企业，如戴尔电脑。他们都前所未有地掌控了自己的供应链。在库存管理、订单履行率、原材料和产品交付上具有更为广阔的视野。利用具有预见性的大数据分析结果，可以使供需......一起来看看 《大数据供应链》 这本书的介绍吧!
