上传或者下载Xtrabackup工具
|
1 2 3 4 5 6 7 8 9 10 11 12 |
1.上传 [root@db01 ~]# rz percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm #或者 2.下载 #下载epel源 wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-6.repo #安装依赖 yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL #下载Xtrabackup wget httpss://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm |
安装
|
1 2 3 4 |
[root@db01 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm [root@db01 ~]# xtrabackup #旧版本的命令 [root@db01 ~]# innobackupex #新版本的命令 |
xtrabackup特性
|
1 2 3 |
1.对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。 2.对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。 3.备份时读取配置文件/etc/my.cnf |
xtrabackup全量备份
|
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 |
1.创建备份的目录 [root@db01 ~]# mkdir /backup 2.全备 [root@db01 ~]# innobackupex --user=root --password=123 /backup/full/ 3.查看全备的文件 [root@db01 ~]# ll /backup/full/ 总用量 0 drwxr-x--- 9 root root 271 11月 4 17:50 2020-11-04_17-50-18 drwxr-x--- 9 root root 271 11月 4 17:51 2020-11-04_17-51-34 4.去除时间戳进行备份 [root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full/ 5.再次查看备份文件 [root@db01 ~]# ll /backup/full/ [root@db01 /backup/full]# vim xtrabackup_checkpoints #备份的信息 backup_type = full-backuped from_lsn = 0 to_lsn = 2330991066 xtrabackup_binlog_info #binlog的信息 xtrabackup_info #备份工具的信息 xtrabackup_logfile #binlog |
使用xtrabackup进行数据恢复
|
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 |
1.删库 mysql> drop database test; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec) 2.停止数据库 [root@db01 ~]# systemctl stop mysqld 3.将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚，模拟CSR的过程 [root@db01 ~]# innobackupex --user=root --password=123 --apply-log /backup/full 4.恢复数据 [root@db01 ~]# cd /usr/local/mysql [root@db01 /usr/local/mysql]# mv data databack #方式一： [root@db01 /usr/local/mysql]# cp -r /backup/full ./data [root@db01 /usr/local/mysql]# chown -R mysql.mysql data #方式二： [root@db01 /usr/local/mysql]# innobackupex --copy-back /backup/full/ [root@db01 /usr/local/mysql]# chown -R mysql.mysql data |
xtrabackup增量备份
|
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 |
1.首先进行全备 [root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full$(date +%F) 2.模拟新增数据 mysql> create database xtra; mysql> use xtra mysql> create table xtrabackup(id int); mysql> insert xtrabackup values(1),(2),(3); 3.进行增量备份 [root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full2020-11-05 /backup/firstadd 参数说明: --incremental： --incremental-basedir：上一次备份的路径 4.确认增量备份是否准确 [root@db01 /backup]# cat full2020-11-05/xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 2327894806 [root@db01 /backup]# cat firstadd/xtrabackup_checkpoints backup_type = incremental from_lsn = 2327894806 #第一次增备的起始位置点是全备的结束位置点 to_lsn = 2327902069 5.再次新增数据 mysql> use xtra mysql> insert xtrabackup values(4),(5),(6); 6.再次增备 [root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/firstadd /backup/twoadd 7.再次查看是否衔接 [root@db01 /backup]# cat full2020-11-05/xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 2327894806 [root@db01 /backup]# cat firstadd/xtrabackup_checkpoints backup_type = incremental from_lsn = 2327894806 #全备的结束点 to_lsn = 2327902069 [root@db01 /backup]# cat twoadd/xtrabackup_checkpoints backup_type = incremental from_lsn = 2327902069 #第一次增备的结束点 to_lsn = 2327903988 |
xtrabackup增量备份恢复
|
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 |
1.准备恢复 1）full + firstadd + twoadd 2）需要将 firstadd 和 twoadd 按顺序合并到full中 3）分步骤进行 --apply-log 2.第一步：在全备中apply-log时，只应用redo，不应用undo [root@db01 ~]# innobackupex --apply-log --redo-only /backup/full2020-11-05 3.第二步：将 firstadd 合并到 full 中，并且apply-log，只应用redo，不应用undo [root@db01 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/firstadd/ /backup/full2020-11-05 4.确认合并 [root@db01 ~]# cat /backup/full2020-11-05/xtrabackup_checkpoints backup_type = log-applied from_lsn = 0 to_lsn = 2327902069 #全备的值等于第一次增备的值 5.第三步：将 twoadd 合并到 full 中，redo和undo都应用 [root@db01 ~]# innobackupex --apply-log --incremental-dir=/backup/twoadd/ /backup/full2020-11-05 6.再次确认合并 [root@db01 ~]# cat /backup/full2020-11-05/xtrabackup_checkpoints backup_type = full-prepared from_lsn = 0 to_lsn = 2327903988 7.第四步：整体full执行apply-log，redo和undo都应用 [root@db01 mysql]# innobackupex --apply-log /backup/full2020-11-05 [root@db01 ~]# systemctl stop mysqld [root@db01 ~]# mv /usr/local/mysql/data /usr/local/mysql/databack [root@db01 ~]# innobackupex --copy-back /backup/full2020-11-05 [root@db01 ~]# chown -R mysql.mysql /usr/local/mysql/data [root@db01 ~]# systemctl start mysqld |