文章目录
xtrabackup安装xtrabackup常用命令 全量备份模拟删库做软连接恢复数据 增量备份停掉服务删除目录进行数据一致性恢复数据库xtrabackup安装
xtrabackup
概述:是一款开源免费的热备工具,xtrabackup分为xtrabackup和innobackup常用命令
innobackupex
--user # 以什么用户身份进行操作--password# 数据库用户的密码--port # 数据库的端口号,默认3306--stream # 打包(数据流)--defaults-file# 指定默认配置文件,默认读取/etc/f--no-timestamp # 不创建时间戳文件,而改用目的地址(可以自动创建)--copy-back # 备份还原的主要选项--incremental # 使用增量备份,默认使用的完整备份--incremental-basedir # 与--incremental选项联合使用,该参数指定上一级备份的地址来做增量备份
下载xtrabackup包
[root@localhost ~]# wget/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/8/x86_64/percona-xtrabackup-24-2.4.22-1.el8.x86_64.rpm[root@localhost ~]# lsanaconda-ks.cfg passlibev-4.04-2.el6.x86_64.rpm percona-xtrabackup-24-2.4.22-1.el8.x86_64.rpm
安装xtrabackup
[root@localhost ~]# rpm -ivh libev-4.04-2.el6.x86_64.rpm //安装依赖[root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.22-1.el8.x86_64.rpm
全量备份
mysql> select * from hyh.student;//创建一个数据库和表并添加数据+----+---------------+------+| id | name| age |+----+---------------+------+| 1 | tom | 20 || 2 | jerry | 23 || 3 | wangqing| 25 || 4 | sean| 28 || 5 | zhangshan| 26 || 7 | lisi| 50 || 8 | chenshuo| 10 || 9 | wangwu | 100 || 10 | qiuyi | 15 || 11 | qiuyixiaotian | 20 |+----+---------------+------+10 rows in set (0.00 sec)[root@localhost ~]# innobackupex --user root --password hyh123! /backup//全量备份xtrabackup: recognized server arguments: xtrabackup: recognized client arguments: 210510 10:24:20 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.At the end of a successful backup run innobackupexprints "completed OK!".
模拟删库
[root@localhost ~]# service mysqld stop//停止服务Shutting down MySQL.. SUCCESS![root@localhost ~]# rm -rf /opt/data/*//删库
做软连接
[root@localhost ~]# cd /tmp/[root@localhost tmp]# mkdir mysql.sock[root@localhost tmp]# cd /var/lib/[root@localhost lib]# mkdir mysql[root@localhost lib]# ln -sf /tmp/mysql.sock /var/lib/mysql/mysql.sock[root@localhost lib]# ll /tmp/mysql.sock /var/lib/mysql//tmp/mysql.sock:total 0/var/lib/mysql/:total 0lrwxrwxrwx. 1 root root 15 May 10 10:34 mysql.sock -> /tmp/mysql.sock
恢复数据
[root@cloud1 ~]# innobackupex -uroot -phyh123! --copy-back /backup/-05-10_10-24-20///用innobackup进行恢复[root@cloud1 opt]# chown -R mysql.mysql /opt/data///给数据库权限[root@cloud1 ~]# service mysqld start//启动服务mysql> select * from hyh.student;+----+---------------+------+| id | name| age |+----+---------------+------+| 1 | tom | 20 || 2 | jerry | 23 || 3 | wangqing| 25 || 4 | sean| 28 || 5 | zhangshan| 26 || 7 | lisi| 50 || 8 | chenshuo| 10 || 9 | wangwu | 100 || 10 | qiuyi | 15 || 11 | qiuyixiaotian | 20 |+----+---------------+------+10 rows in set (0.00 sec)
增量备份
mysql> insert student (name) value('xiaoming'); //添加数据Query OK, 1 row affected (0.00 sec)mysql> select *from student;+----+---------------+------+| id | name| age |+----+---------------+------+| 1 | tom | 20 || 2 | jerry | 23 || 3 | wangqing| 25 || 4 | sean| 28 || 5 | zhangshan| 26 || 7 | lisi| 50 || 8 | chenshuo| 10 || 9 | wangwu | 100 || 10 | qiuyi | 15 || 11 | qiuyixiaotian | 20 || 12 | xiaoming| NULL |+----+---------------+------+11 rows in set (0.00 sec)root@localhost ~]# innobackupex -uroot -phyh123! --incremental /backup/ --incremental-basedir /backup/-05-10_10-24-20///基于上次的全量进行增量备份
停掉服务删除目录进行数据一致性
[root@localhost ~]# service mysqld stopShutting down MySQL.. SUCCESS! [root@localhost ~]# ss -antlState Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0128 0.0.0.0:220.0.0.0:* LISTEN 0128 [::]:22 [::]:* [root@localhost ~]# rm -rf /opt/data/*
恢复数据库
[root@localhost ~]# innobackupex -uroot -phyh123! --apply-log --redo-only /backup/-05-10_10-24-20/ --incremental-dir /backup/-05-10_11-37-05///操作全量备份进行回滚[root@localhost ~]# innobackupex --apply-log --redo-only /backup/complete/-05-10_10-24-20 --incremental-dir=/backup/inc1/-05-10_11-37-05///操作第一次增量备份将第一次增量备份应用到完全备份[root@localhost ~]# innobackupex -uroot -phyh123! --copy-back /backup/-05-10_10-24-20///恢复第一次全量备份[root@cloud1 opt]# chown -R mysql.mysql /opt/data///给数据库权限[root@cloud1 ~]# service mysqld start//启动服务mysql> select *from hyh.student;+----+---------------+------+| id | name| age |+----+---------------+------+| 1 | tom | 20 || 2 | jerry | 23 || 3 | wangqing| 25 || 4 | sean| 28 || 5 | zhangshan| 26 || 7 | lisi| 50 || 8 | chenshuo| 10 || 9 | wangwu | 100 || 10 | qiuyi | 15 || 11 | qiuyixiaotian | 20 || 12 | xiaoming| NULL |+----+---------------+------+11 rows in set (0.00 sec)
再次添加数据
mysql> insert student (name,age) values('qw',12),('wu',20);mysql> select * from student;+----+---------------+------+| id | name| age |+----+---------------+------+| 1 | tom | 20 || 2 | jerry | 23 || 3 | wangqing| 25 || 4 | sean| 28 || 5 | zhangshan| 26 || 7 | lisi| 50 || 8 | chenshuo| 10 || 9 | wangwu | 100 || 10 | qiuyi | 15 || 11 | qiuyixiaotian | 20 || 12 | xiaoming| NULL || 13 | qw | 12 || 14 | xiaoming| 20 |+----+---------------+------+13 rows in set (0.00 sec)
基于上一次增备进行增备
[root@localhost ~]# innobackupex -uroot -phzy123 --incremental /backup/ --incremental-basedir /backup/-05-10_11-37-05/
停掉服务删除目录进行数据一致性
[root@localhost ~]# service mysqld stopShutting down MySQL.. SUCCESS! [root@localhost ~]# ss -antlState Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0128 0.0.0.0:220.0.0.0:* LISTEN 0128 [::]:22 [::]:* [root@localhost ~]# rm -rf /opt/data/*
恢复数据库
[root@localhost ~]# innobackupex -uroot -phyh123! --apply-log --redo-only /backup/-05-10_10-24-20/ --incremental-dir /backup/-05-10_11-37-05///操作全量备份进行回滚[root@localhost ~]# innobackupex --apply-log --redo-only /backup/complete/-05-10_10-24-20 --incremental-dir=/backup/inc1/-05-10_11-37-05///操作第一次增量备份将第一次增量备[root@localhost ~]# innobackupex --apply-log /backup/complete/-05-10_10-24-20 --incremental-dir=/backup/-05-10_15-26-53///操作第二次增量备份将第二次增量备份应用到完全备份:[root@localhost ~]# innobackupex -uroot -phyh123! --copy-back /backup/-05-10_10-24-20///恢复全备[root@cloud1 opt]# chown -R mysql.mysql /opt/data///给数据库权限[root@cloud1 ~]# service mysqld start//启动服务mysql> select * from hyh.student;+----+---------------+------+| id | name| age |+----+---------------+------+| 1 | tom | 20 || 2 | jerry | 23 || 3 | wangqing| 25 || 4 | sean| 28 || 5 | zhangshan| 26 || 7 | lisi| 50 || 8 | chenshuo| 10 || 9 | wangwu | 100 || 10 | qiuyi | 15 || 11 | qiuyixiaotian | 20 || 12 | xiaoming| NULL || 13 | qw | 12 || 14 | xiaoming| 20 |+----+---------------+------+13 rows in set (0.00 sec)
如果觉得《xtrabackup安装 进行全量备份增量备份》对你有帮助,请点赞、收藏,并留下你的观点哦!