一、Xtrabackup 说明及安装
一、Xtrabackup 说明及安装
二、Xtrabackup 全备份及恢复
三、Xtrabackup 全备和增量备份及恢复
四、Xtrabackup 部分全备和增量及恢复
一、Xtrabackup 说明及安装
1.Xtrabackup 介绍及说明
Percona XtraBackup是世界上唯一开放源码的免费MySQL热备份软件，可以执行非阻塞
InnoDB和XtraDB数据库的备份。使用Percona XtraBackup，您可以实现以下好处：
快速可靠地完成备份
备份期间不间断的事务处理
节省磁盘空间和网络带宽
自动备份验证
由于更快的恢复时间，更长的正常运行时间
Percona XtraBackup为所有版本的Percona Server，MySQL和MariaDB提供了MySQL热备份。它执行
流式传输，压缩和增量MySQL备份。
Percona XtraBackup适用于MySQL，MariaDB和Percona Server。它支持完全非阻塞备份
的InnoDB，XtraDB和HailDB存储引擎。此外，它可以简单地备份以下存储引擎
在备份结束时暂停写入：MyISAM，Merge和Archive，包括分区表，触发器和
数据库选项。
2、Xtrabackup 下载安装
2.1.下载地址
https://www.percona.com/downloads/XtraBackup/LATEST/
2.3.安装
yum install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm额外需要安装一些依赖perl包
2.4.查看安装结果
# rpm -ql percona-xtrabackup-24-2.4.7-1.el7.x86_64 /usr/bin/innobackupex /usr/bin/xbcloud /usr/bin/xbcloud_osenv /usr/bin/xbcrypt /usr/bin/xbstream /usr/bin/xtrabackup /usr/share/doc/percona-xtrabackup-24-2.4.7 /usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING /usr/share/man/man1/innobackupex.1.gz /usr/share/man/man1/xbcrypt.1.gz /usr/share/man/man1/xbstream.1.gz /usr/share/man/man1/xtrabackup.1.gz二、Xtrabackup 全备份
1.Xtrabackup 准备全备
1.1.开始全备
创建备份目录
# mkdir /data/backups准备前数据查看：
MariaDB [ckldb]> select * from jone; +------+--------+ | id | name | +------+--------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | +------+--------+ 7 rows in set (0.00 sec)1.2.开始备份
# innobackupex --user=root /data/backups/ 170430 23:04:14 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!". 170430 23:04:14 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root' (using password: NO). 170430 23:04:14 version_check Connected to MySQL server 170430 23:04:14 version_check Executing a version check against the server... 170430 23:04:14 version_check Done. 170430 23:04:14 Connecting to MySQL server host: localhost, user: root, password: not set, port: 3306, socket: /tmp/mysql.sock Using server version 10.1.22-MariaDB innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 6f7a799) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /data/mysql/3306/data xtrabackup: open files limit requested 0, set to 1024 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = /data/mysql/3306/data xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = /data/mysql/3306/data xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 67108864 ........ 170430 23:04:24 Executing UNLOCK TABLES 170430 23:04:24 All tables unlocked 170430 23:04:24 Backup created in directory '/data/backups/2017-04-30_23-04-14/' MySQL binlog position: filename 'mysql-bin.000009', position '745', GTID of the last change '0-1-62' 170430 23:04:24 [00] Writing backup-my.cnf 170430 23:04:24 [00] ...done 170430 23:04:24 [00] Writing xtrabackup_info 170430 23:04:24 [00] ...done xtrabackup: Transaction log of lsn (1634473) to (1634473) was copied. 170430 23:04:25 completed OK!1.3.查看备份文件
# ll /data/backups/2017-04-30_23-04-14/ total 75800 -rw-r-----. 1 root root 418 Apr 30 23:04 backup-my.cnf drwxr-x---. 2 root root 52 Apr 30 23:04 ckldb -rw-r-----. 1 root root 77594624 Apr 30 23:04 ibdata1 drwxr-x---. 2 root root 4096 Apr 30 23:04 mysql drwxr-x---. 2 root root 20 Apr 30 23:04 performance_schema drwxr-x---. 2 root root 20 Apr 30 23:04 test -rw-r-----. 1 root root 28 Apr 30 23:04 xtrabackup_binlog_info -rw-r-----. 1 root root 113 Apr 30 23:04 xtrabackup_checkpoints -rw-r-----. 1 root root 494 Apr 30 23:04 xtrabackup_info -rw-r-----. 1 root root 2560 Apr 30 23:04 xtrabackup_logfile # cd /data/backups/2017-04-30_23-04-14/ # cat xtrabackup_binlog_info mysql-bin.000009 745 0-1-62 #二进制日志文件 日志POS点 时间戳 LSN，日志序列号，Innodb的日志序列号是一个64位的整型 # cat xtrabackup_checkpoints backup_type = full-backuped #备份类型为全备 from_lsn = 0 #LSN起始位置 to_lsn = 1634473 #LSN结束位置 last_lsn = 1634473 #最新LSN位置 compact = 0 recover_binlog_info = 01.4.模拟删除文件
# rm -rf /data/mysql/3306/data2.准备恢复
创建备份后，数据未准备好还原。可能会撤销未提交的事务或要重放日志中的事务。做这些等待操作将使数据文件保持一致，这是准备阶段的目的。一旦完成，数据就可以使用了。要使用innobackupex准备备份，您必须使用--apply-log和备份目录的路径作为参数：
2.1.停止数据库
# service mysqld stop2.2.恢复准备
# innobackupex --apply-log /data/backups/2017-04-30_23-04-14/ 170430 23:18:35 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 1634856 170430 23:18:46 completed OK!2.3.开始恢复
# innobackupex --copy-back /data/backups/2017-04-30_23-04-14/ 170430 23:20:31 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!". ...... 170430 23:20:42 [01] Copying ./ibtmp1 to /data/mysql/3306/data/ibtmp1 170430 23:20:42 [01] ...done 170430 23:20:43 completed OK!2.4.查看恢复的文件
# ll total 219148 drwxr-x---. 2 root root 52 Apr 30 23:20 ckldb -rw-r-----. 1 root root 77594624 Apr 30 23:20 ibdata1 -rw-r-----. 1 root root 67108864 Apr 30 23:20 ib_logfile0 -rw-r-----. 1 root root 67108864 Apr 30 23:20 ib_logfile1 -rw-r-----. 1 root root 12582912 Apr 30 23:20 ibtmp1 drwxr-x---. 2 root root 4096 Apr 30 23:20 mysql drwxr-x---. 2 root root 20 Apr 30 23:20 performance_schema drwxr-x---. 2 root root 20 Apr 30 23:20 test -rw-r-----. 1 root root 23 Apr 30 23:20 xtrabackup_binlog_pos_innodb -rw-r-----. 1 root root 494 Apr 30 23:20 xtrabackup_info添加权限，启动数据库：
# chown -R mysql.mysql data/ # service mysqld start查看恢复结果：
MariaDB [(none)]> use ckldb Database changed MariaDB [ckldb]> show tables; +-----------------+ | Tables_in_ckldb | +-----------------+ | jone | +-----------------+ 1 row in set (0.00 sec) MariaDB [ckldb]> select * from jone; +------+--------+ | id | name | +------+--------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | +------+--------+ 7 rows in set (0.01 sec)三、Xtrabackup 全备和增量备份及恢复
1.执行全备，上面已经备份，这次不需要
2.增量备份
2.1.添加第一次数据
MariaDB [ckldb]> insert into jone values(8,'lanester'),(9,'gonku'); Query OK, 2 rows affected (0.39 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | +------+----------+ 9 rows in set (0.00 sec)2.2.执行第一次增量备份
# innobackupex --incremental /data/backups/ --incremental-basedir=/data/backups/2017-04-30_23-04-14/ 170430 23:37: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!". ....... xtrabackup: Transaction log of lsn (1636718) to (1636718) was copied. 170430 23:37:56 completed OK!查看备份结果：
# ls /data/backups/ 2017-04-30_23-04-14 2017-04-30_23-37-49 # cd /data/backups/2017-04-30_23-37-49 backup_type = incremental from_lsn = 1634473 to_lsn = 1636718 last_lsn = 1636718 compact = 0 recover_binlog_info = 02.3.增加第二次数据
MariaDB [ckldb]> insert into jone values(10,'dragon'),(11,'lanli'); Query OK, 2 rows affected (0.61 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | | 10 | dragon | | 11 | lanli | +------+----------+ 11 rows in set (0.00 sec)2.4.执行第二次增量备份
# innobackupex --incremental /data/backups/ --incremental-basedir=/data/backups/2017-04-30_23-37-49/ 170430 23:41:46 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!". ...... xtrabackup: Transaction log of lsn (1638894) to (1638894) was copied. 170430 23:41:54 completed OK!查看备份结果：
# du -sh /data/backups/* 224M /data/backups/2017-04-30_23-04-14 1.3M /data/backups/2017-04-30_23-37-49 1.1M /data/backups/2017-04-30_23-41-46 # cd /data/backups/2017-04-30_23-41-46/ # cat xtrabackup_checkpoints backup_type = incremental from_lsn = 1636718 to_lsn = 1638894 last_lsn = 1638894 compact = 0 recover_binlog_info = 03.模拟删除数据库
# rm -rf /data/mysql/3306/data/4.增量备份恢复
使用innobackupex准备增量备份准备增量备份有点不同
完整的。这也许是需要更多关注的阶段：
首先，必须在每个备份上提交的事务。这将合并基数备份与增量。
然后，必须回滚未提交的事务才能进行即用备份。
如果您重放提交的事务并在基本备份上回滚未提交的事务，那么您将无法进行
添加增量。如果您以增量方式执行此操作，则无法从该时刻添加数据
和剩余的增量。
4.1.全备恢复准备
# innobackupex --apply-log --redo-only /data/backups/2017-04-30_23-04-14/4.1.第一次增量备份恢复准备
# innobackupex --apply-log --redo-only /data/backups/2017-04-30_23-04-14/ --incremental-dir=/data/backups/2017-04-30_23-37-494.2.第二次增量备份恢复准备
# innobackupex --apply-log --redo-only /data/backups/2017-04-30_23-04-14/ --incremental-dir=/data/backups/2017-04-30_23-41-46/5.开始恢复
# innobackupex --copy-back /data/backups/2017-04-30_23-04-14/6.查看恢复数据及文件
# chown -R mysql.mysql /data/mysql/3306/data/ # service mysqld start查看数据
MariaDB [(none)]> use ckldb Database changed MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | | 10 | dragon | | 11 | lanli | +------+----------+ 11 rows in set (0.00 sec)两次增量备份数据都已恢复
四、Xtrabackup 部分全备和增量及恢复
Percona XtraBackup提供部分备份，这意味着您只能备份一些特定的表或
数据库。您备份的表必须在单独的表空间中，因为在您之后创建或更改
在服务器上启用了innodb_file_per_table选项。
只有一个关于部分备份的警告：不要复制备份的备份。恢复部分备份
应该通过导入表来完成，而不是使用传统的-copy-back选项。虽然有一些
通过复制文件可以进行恢复的场景，这可能导致数据库不一致
案例并不是推荐的方法。
创建部分备份有三种方式可以指定要备份整个数据的哪一部分：常规
表达式（--include），枚举文件中的表（--tables-file）或提供数据库列表（-数据库）。
使用--include选项提供给这个的正则表达式将与完全限定匹配
表名，包括数据库名称，格式为databasename.tablename。
1.单个库全备
# innobackupex --include='ckldb' /data/backups/ 170501 00:07:31 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!". ....... 170501 00:07:35 Backup created in directory '/data/backups/2017-05-01_00-07-31/' MySQL binlog position: filename 'mysql-bin.000001', position '313', GTID of the last change '' 170501 00:07:35 [00] Writing backup-my.cnf 170501 00:07:35 [00] ...done 170501 00:07:35 [00] Writing xtrabackup_info 170501 00:07:35 [00] ...done xtrabackup: Transaction log of lsn (1634875) to (1634875) was copied. 170501 00:07:35 completed OK!查看备份文件：
# cd /data/backups/2017-05-01_00-07-31/ [root@localhost 2017-05-01_00-07-31]# ls backup-my.cnf ckldb ibdata1 xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile2.单个库增量备份
#innobackupex --include='ckldb' --incremental /data/backups/ --incremental-basedir=2017-05-01_00-07-313.增加数据，第二次增量备份
3.1.增加数据
MariaDB [ckldb]> insert into jone values(12,'firegod'),(13,'sevenGod'); Query OK, 2 rows affected (0.38 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | | 10 | dragon | | 11 | lanli | | 12 | firegod | | 13 | sevenGod | +------+----------+ 13 rows in set (0.00 sec)3.2.开始第二次增量备份：
# innobackupex --include='ckldb' --incremental /data/backups/ --incremental-basedir=/data/backups/2017-05-01_00-10-00/4.模拟删除数据库
MariaDB [(none)]> drop database ckldb; Query OK, 1 row affected (0.19 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)5.准备恢复
5.1.准备恢复全备
# innobackupex --apply-log --export /data/backups/2017-05-01_00-07-31/5.2.准备第一次增量备份
# innobackupex --apply-log --redo-only /data/backups/2017-05-01_00-07-31/ --incremental-dir=/data/backups/2017-05-01_00-10-00/5.3.准备第二次增量备份
# innobackupex --apply-log --redo-only /data/backups/2017-05-01_00-07-31/ --incremental-dir=/data/backups/2017-05-01_00-13-35/6.开始恢复
# \cp -rf /data/backups/2017-05-01_00-07-31/* /data/mysql/3306/data/权限添加：
# chown -R mysql.mysql /data/mysql/3306/data/ # service mysqld start查看数据：
MariaDB [(none)]> use ckldb Database changed MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | | 10 | dragon | | 11 | lanli | | 12 | firegod | | 13 | sevenGod | +------+----------+ 13 rows in set (0.06 sec)另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。